SharePoint

List Lookup Control filtering in Nintex Forms not working

Bart

List Lookup control filtering in Nintex Forms is one of the more interesting features of this control because it gives you the ability to filter the values based on the value of another control and create cascading dropdowns. But this is also where it can go wrong at times.

Consider the following scenario… you have a SharePoint 2019 site running under the /sites managed path in a web application and there you have 3 lists:

  • User Access Requests: your main list where your Nintex Form will be used as a list form.
  • UARCountries: A simple list with a title column that is used as a lookup list in your form and contains countries.
  • UARRegions: A simple list with a title column and a country lookup column towards the UARCountries list.

On your main list, you have a Nintex form that has 2 List Lookup controls.

List Lookup Control filtering 1

The Country lookup list is configured as a normal list lookup. It pulls the countries from the UARCountries list.

List Lookup Control filtering 2

The Region lookup is configured to pull the regions from the UARRegions list.

List Lookup Control filtering 3

But we also configured a filter on this lookup because we only want to see the regions of the selected country in the Countries dropdown. For that, we applied a filter based on a control value.

List Lookup Control filtering 4

When you publish this form or just preview it, you would expect this to work. And in a lot of cases it is going to work. But not always.

I was in a situation where the Region dropdown was completely disabled when the form opened.

List Lookup Control filtering 5

And when I selected a country, the control was still in a disabled state.

List Lookup Control filtering 6

I started experimenting with this and started playing around with configuration changes for the control and noticed some things.

In the ULS, the following error messages were logged when the form was opened.

List Lookup Control filtering 7
List Lookup Control filtering 8

When I removed the filtering from the regions and just displayed all of them, the errors went away and all of the regions were displayed. So, the list was definately there…

Because this didn’t make any sense, I decided to take this to Nintex Support. They suggested upgrading to the latest version of Nintex Forms (v5.1.2). That didn’t help.
Then I found a post on the Nintex Community where the same errors were discussed in the context of list lookup control filtering.

Somebody posted a possible cause… the absence of a root site collection in the web application.

And sure enough… my web application didn’t have a root site collection. I only had sites running under /sites/…

I created a site in the root of the web application. Reopened my form and there you go… it worked!!

List Lookup Control filtering 9

The Region control was no longer disabled and the values were filtered as expected.

The Importance of Completing Tasks in Workflows

Bart

When you use workflows in your organization to automate business processes, you have to deal with the fact that people are lazy. They don’t like work. One of the things you do a lot in workflows, is create tasks. Somewhere in a process, somebody needs to complete a task before the next step in the process can be completed. So, the task is created and your workflow waits until someone completes it. The importance of completing tasks in the context of workflows is often underestimated. Completing a task in the context of a workflow is a 2-step process and this is a concept which lots of people don’t seem to understand and this leads to a lot of problems.

  • Step 1: Executing the action in the task
  • Step 2: Mark the task completed

Let me give you an example on how this might lead to issues.

You have an onboarding workflow for new employees. This workflow initiates several other workflows.
One of those other workflows is the creation of an Exchange mailbox. This “Exchange Mailbox Creation” workflow creates a task for the Exchange admins which instructs them to create a mailbox for the new employee.

Meanwhile, the main workflow handles some other stuff which is important for onboarding the new employee. Somewhere at the end of the process, the main workflow is going to check if the mailbox has been created. As long as the mailbox is not created, the onboarding process cannot be completed.
To do this, the workflow contains a loop which checks if the Exchange Mailbox Creation workflow is completed. If it’s not, It’s going to pause for 1 hour and check again. This repeats itself until the Exchange Mailbox Creation workflow is completed.

The problem is… that workflow will NEVER end because the people who are responsible for creating Exchange mailboxes do understand the importance of creating a mailbox for a new employee, but don’t understand the importance of marking the task completed in the system. They don’t know that the overall onboarding process relies on the completion of those tasks.

Direct results :

  • The Exchange Mailbox creation workflow will be waiting indefinetely unless someone sets that task to completed
  • The onboarding process for this employee will never finish because of the Exchange Mailbox Creation workflow will never finish

Indirect results:

  • The history list will grow constantly because of the loop which generated 2 list items every hour (for the pause action). Read this post to understand the importance of maintaining a history list.
  • The workflowprogress data table will grow constantly because of the same loop. Read this post to understand the importance of workflowprogress data.

A workflow designer has the responsibility of covering this by :

  • including reminder notifications in the tasks
  • Have some kind of escalation procedure in the case nobody bothers to complete those tasks. Escalating to a manager often helps
  • Make sure that the task notifications stress the importance of completing the tasks for a proper process termination. This should also be emphasized in the reminder notifications or escalations as well

For the example of the onboarding process, I had 91 running instances which have been running for months up until a year because of these incompleted tasks. These 91 instances are responsible for a history list that grows with almost 4400 list items every single day.

You can avoid all of this by thinking through your process and include logic in your workflow and tasks to avoid these situations. But if you are tasked with cleaning up an environment where these kind of issues exist for a while, you might have an environment which has thousands of running workflows that keep filling your history lists and workflowprogress tables with useless information and you need a way to stop this madness.

If you are using Nintex Workflow, you can use the below which allows you to terminate Nintex Workflows in batch. You specify your Nintex content database and a date and the script will terminate all running instances that didn’t have any activity since the specified date.

Keep in mind that terminating workflows might result in notification emails being sent by the system.

 

Maintaining Nintex Workflow Progress Data

Bart

In my last post I talked about maintaining the workflow history lists throughout SharePoint. This post is about maintaining Nintex workflow progress data. This data is found in the WorkflowProgress table in a Nintex content database.

When a Nintex Workflow is executed, each action is recorded in the WorkflowProgress table. This gives the system the opportunity to show a graphical representation of the workflow history for a specific instance. You can imagine that this table can contain a lot of information. Nintex recommends to keep this table below 15 million rows to avoid performance issues.

To maintain this table, you can use the PurgeWorkflowData operation of nwadmin.

The PurgeWorkflowData operation has a lot of parameters to specify which records it needs to purge. These parameters might not be enough to limit the records you want to purge. There’s a little catch in purging workflow progress data. To allow proper maintenance of the workflow history data, you need to make sure that you only purge workflow progress data for workflow instances where NO workflow history exists anymore. This means that there’s an order in how to maintain both the history lists and the workflowprogress table:

  1. Workflow History Lists
  2. WorkflowProgress table

If you purge the WorkflowProgress table before you clean the workflow history, you will end up with history list items which cannot be purged anymore in a selective way (using the ‘PurgeHistoryListData’ operation of nwadmin). You can only purge them by clearing the list completely.

Read more →

Cleaning Workflow History in SharePoint

Bart

A lot of organizations that use SharePoint, use the platform to automate business processes using workflows. But little organizations are aware that because of these workflows, your SharePoint environment needs to be maintained a little more than usual. Almost all organizations which use a lot of workflows start having performance issues. SharePoint is starting to get slow, workflows are starting to show weird startup behaviors or don’t start at all. Those kind of things. And they don’t know why this is happening. One of the causes for this are probably the workflow history lists.

Which lists? Workflow history? We don’t have such lists.
Yes you do, but you don’t know it, because they are hidden! The comments and events you see when you look at a started workflow… these are stored in the workflow history list. Because those lists are hidden, most of the organizations who never had to deal with these issues, don’t know these lists exist and so, those lists grow and grow and grow. Up to a point it starts to get problematic.

To give an example, I had a case where the largest workflow history list contained 24 million items. For the entire environment, the combined size of all workflow history was 39 million items. That’s a lot of history. And honestly… nobody cares about this information. It’s only used in the event something goes wrong in a workflow and you need to find out why. But in highly regulated or audited environments, this history data can be important.

If you use Nintex Workflow, you can purge those lists using a single command. But if you try to do this on a list that contains millions of items, chances are that you will run into issues. The list has become too big. And again… purging the data is not always possible due to policies.

Now what? You will probably start searching for a solution… in the end, it’s just a SharePoint list like any other list, right? Online, you find some hints on how to approach those lists:

  • Easiest option… create a new list, change the workflows to use that new list and just delete the old one. That’s the quickest way to deal with this. But then you will lose all history. Each history list item has an event type from 1 to 11. Suppose you want to retain the events which hold a record of task outcomes. Deleting the list isn’t an option anymore and you need to find a way to selectively delete list items.
  • Create a script that deletes the items. Well, seems logical to do this. But you need to find the most optimal way of deleting items. Ever deleted SharePoint items in large lists? If you have, you know that SharePoint takes it time to do this. In a specific case I had a delete frequency of 1 item per 9 seconds, for a list of 32000 items. You do the math… that’s 80 hours. Which is a lot. Imagine you have to do this on a list of 24 million items. Best case… it takes 9 seconds per item. That’s 2500 days! Or almost 7 years! Completely insane.

So, out of options?

Well no. If you need to retain specific items on that list, deleting individual list items is still an option. But you need to use a different approach.

Instead of iterating over the complete collection of items in a list and delete one by one, you can use a batch processing method which exists on the SPWeb object. This batch processing method accepts an XML structure that contains “methods”. Each method is an instruction to delete an item in a list.

Each method contains the GUID of the list you are targeting, the ID of the item, and a command. In our case “Delete”.

Once you have assembled this structure, you pass it as a parameter to the ProcessBatchData method on the SPWeb object and SharePoint will perform all of the methods in batch.

To give you an idea on the performance of deleting items using this method. It deleted 215000 items in 16 hours. Compare this to 32000 items in 80 hours. That’s a huge improvement.

How would you practically do this?

Well, you use a CAML query to get a bunch of items from your history list and you assemble your batch xml for these items. Once it has been processed, you repeat the query and do it again… until you run out of items.

Here’s an example. The script below is going to remove all list items which have an event type of 0, 4, 5 or 11. The query returns 2000 items at a time and assembles the required batch xml for these 2000 items. The “ListItemCollectionPosition” is used to know when we are at the end of the list and out of items to delete. When this is null after you execute a query, there are no more items to query.

Running this on a list with 24 million items still requires a lot of time. And you might question the importance of retaining events for such lists… but in any case, you now have a faster way of deleting items in a SharePoint list.

In the end, you need to avoid getting in such a situation in the first place. You can do this by establishing some governance policies regarding workflows and create some routines to enforce those policies by performing maintenance on a continuous basis. That way, you will be able to keep those history lists under control.

Gaining insights in your Nintex Workflow Data

Bart

Working with workflows in SharePoint is always a challenging task. Especially when you are an administrator and people start complaining about performance issues, workflows that don’t start or resume, and so on. Using Nintex Workflow doesn’t change anything, but it gives you a bit more leverage.

The first thing I always do, is try to get some insights in the size of the issue. So, we’re talking about data. How much workflow data is involved. When Nintex is involved, there are 2 places where I’m going to look:

  • Workflow History Lists (SharePoint)
  • WorkflowProgress table (SQL Database)

Nintex has an article on both topics which I can definately recommend.

https://support.nintex.com/SharePoint/Forms/SharePoint_Maintenance_for_Nintex_Workflow

This article links to a PDF which explains how to maintain the WorkflowProgress table. You can find that PDF here.

In this PDF, you will find a SQL query which queries this table and the WorkflowInstance table to give you an insight in the sheer amount of data which is stored and which might be the root cause of your issues with workflows.

Just for you lazy people, here’s the query:

This gives you a lot of information which you can use in your remediation strategy.
Just export that information to a CSV and load it in Excel and you can have the following information at hand in no time.

workflow data

This is a table which gives you, per Nintex content database, the amount of records in the workflowProgress table (ActionCount) and the number of workflow instances involved. And it splits up this information for the different states workflows can be in (running, completed, canceled, error). Whoever came up with the concept of Pivot tables… thank you! 🙂

While this query is great for getting that information and report on it, it’s not that useful for people who are responsible for creating or maintaining those workflows. Look at the query. It gives you the site, web, list and even item that’s involved. But those are the ID’s. You still need to resolve these to a URL or title of a list to be useful for anyone who’s not an administrator on SharePoint and knows his way around in PowerShell.

You could customize this query and include the SharePoint content databases in there to get you the needed information but you know you shouldn’t touch those databases in SQL! So, that’s not an option.

I decided to make my life a little less complicated and use PowerShell to solve it.
The script below is going to execute the query above (with some minor adjustments to get some extra information) and after getting the results, it will get the corresponding URL’s for site and web, and the title of the list (if needed). All of this information is exported to a CSV.

This not only gives me information I can work with immediately, but it also allows me to do it all from my SharePoint server. I don’t need a SQL Management studio anymore or physical access to the database server.

Depending on the amount of rows that is returned from SQL, the execution of the script can take some time though. I tried to minimize the calls to SharePoint by using arrays where I store the URL’s and titles for found GUID’s. This reduces the stress on SharePoint a bit but it still requires some time to go through each row.

Using this list, you can prioritize the things to focus on during a cleanup. And it gives you also the ability to predict the impact of a purge on that table using specific parameters.

Following sites not working in a Hybrid Sites scenario – 401 Unauthorized

Bart

Earlier this week, I was at one of my customers which has a SharePoint 2013 implementation. They had an issue where following sites was not working anymore. When they clicked the Follow link, they got an error that the site could not be followed.

They have a hybrid implementation with OneDrive for Business and Hybrid Sites setup.
When I looked in the ULS, I saw the following error popping up

Loud and clear… authentication issues.

Microsoft has an excellent resource where they outline the roadmap to implement hybrid features.

Both roadmaps outline the steps which are needed to set up those features. Since OneDrive for Business was working fine, I focused on the Hybrid Sites features and started going through the steps of the roadmap to see if everything was set up correctly.

  1. Configure Office 365 for SharePoint hybrid – Check!
  2. Set up SharePoint services for hybrid environments – Check!
  3. Install the September PU for SharePoint Server 2013 – We were on the December 2016 CU, so … Check!
  4. Configure S2S authentication from SharePoint Server 2013 to SharePoint Online –  Hmmm… I don’t recall doing this in the past.
  5. Configure hybrid sites features in Central Administration – Check!

Since I was getting authentication issues, and I didn’t recall me doing the S2S authentication configuration step, I figured that this was the cause of the problem.

When you follow the link for that step, you will see that there’s some work to do to set it up. Luckily, Microsoft provided a tool which actually does it for you. It’s called the Hybrid Picker. This simplifies things a bit.

Read more →

Convert a SharePoint 2016 front-end to a front-end with Distributed Cache

Bart

I have been running a SharePoint 2016 farm with 4 servers (Front-End, Application, Distributed Cache, Search) but now Feature Pack 1 has been released, I’m looking to reduce this to 2 servers and use the 2 combined minroles which have been added. To be more precise, I want to eliminate my Distributed Cache server and convert my Front-End to a Front-End with Distributed Cache. After that, I want to do the same for my Search server and convert my application server to an application server with search. But doing this for my Distributed Cache server, proved to be a challenge. I was getting all kind of errors. This might have something to do with the order I did things though.
Before I did the conversion, I removed my Distributed Cache server from the farm and I didn’t do this in a graceful way… I just disconnected it from the farm.

My first attempt to convert a server failed with a very self-explanatory error:

The Distributed Cache service instance must be configured. Run ‘Add-SPDistributedCacheServiceInstance -Role WebFrontEndWithDistributedCache’ on this server to configure the Distributed Cache service instance.

minrole1

That’s pretty clear. This is indeed a front-end server and in SharePoint 2016, this role doesn’t have the Distributed Cache service instance. So, I added it like the error proposed.

The result however was not what I expected. I got the error:

Failed to connect to hosts in the cluster

minrole2

Come to think of it… this actually makes sense. I already mentioned I kicked out the Distributed Cache server in a very direct way. Probably, my cluster is still referencing this host. The only way to verify this is to look at the cluster configuration. To get this information, you can export it to a text file.

Somewhere near the end of the file a “hosts” section can be found and this contained the following information:

minrole2a

And this confirmed it. The old host was still listed in the configuration. I had to remove this host first. To do this, I executed the following command:

This succesfully unregistered my host from the cluster and I re-ran the command to add the Distributed Cache Service Instance.

This time, no error! Hooray!

I proceeded to run the Set-SPServer command again to start the conversion. Again, an error appeared. This time, the error was different.

The Distributed Cache service instance must be reconfigured. Run ‘Remove-SPDistributedCacheServiceInstance’ on this server to remove the Distributed Cache service instance. Then run ‘Add-SPDistributedCacheServiceInstance -Role WebFrontEndWithDistributedCache’ on this server to reconfigure the Distributed Cache service instance….

minrole3

At this point, when you look at the Distributed Cache service in Central Administration, you can see that it’s stopped. If you open the Windows Services console on the server, the AppFabric Caching Service is disabled.

I tried to remove this service using Remove-SPDistributedCacheServiceInstance, but it failed with the error:

cacheHostInfo is null

minrole4

To get past this point and get the service removed, you can execute the following piece of PowerShell script:

This worked and it removed the service instance completely from my server.

I then proceeded to execute the Add-SPDistributedCacheServiceInstance command again but this time without the -Role parameter!!!

When the command finished, I went to the Services on Server page in Central Administration and I noticed that the Distributed Cache service was added again. This time it was started! It also stated that my server was not compliant anymore but who cares…. we are going to change the role anyway.

minrole5

I re-ran the Set-SPServer command to start the conversion:

And yes, now it worked. I was notified that a timer job had been scheduled to do the conversion.

minrole6

I only had to wait until the job was completed. When I looked at the Servers page in Central Administration, I could clearly see that my role was changed and that the server was compliant.

minrole7

Part of this could possibly have been avoided by removing the Distributed Cache server in a more graceful way. Lessons learned… don’t cut corners!  😎 

DistributedCache Server role vs SkipRegisterAsDistributedCacheHost

Bart

Since SharePoint 2013, the New-SPConfigurationDatabase and Connect-SPConfigurationDatabase cmdlets have a parameter called “SkipRegisterAsDistributedCacheHost”. When this switch parameter is specified during the creation of a new  farm or when a server is added to an existing farm, the local server will not have the Distributed Cache. With the arrival of SharePoint 2016, we also got the MinRole feature. This feature enables you to designate the local server to be a “DistributedCache” server. How to do this, is explained in one of my previous posts where I provide a script to create a farm and to join a farm. I was wondering what happens when you use the DistributedCache server role together with the -SkipRegisterAsDistributedCacheHost switch.

Best way of finding this out is to try it. I did, and found out that the server in fact was a DistributedCache host after I joined it to the farm. So, it ignores the switch completely.

But what with the other roles? How about Custom? And Search? To see how this is handled, I fired up ILSpy and started digging in the code.

In the Microsoft.SharePoint.DistributedCaching.Utilities.SPDistributedCacheServiceInstance class, a method ShouldRegisterAsDistributedCacheHost exists and this is called somewhere during the execution of one of the above 2 cmdlets.

This method contains a test which illustrates how the decision is made if a server should be a DistributedCache host or not.

Bottomline… 

If the ServerRole parameter is DistributedCache, SingleServerFarm or WebFrontEndWithDistributedCache, the presence of the SkipRegisterAsDistributedCacheHost doesn’t matter. The server will be a Distributed Cache host. Period.

If the ServerRole parameter is not specified, “Custom” or we don’t have the MinRole feature enabled, the SkipRegisterAsDistributedCacheHost is taken into account and used in the decision.

Change SharePoint Service Identities using PowerShell

Bart

After installing SharePoint and setting up my farm, one of the first things I always do is change SharePoint service identities. In a freshly installed SharePoint farm, most services are running under the farm account or under a local identity (LocalService, LocalSystem). Some of the services I change right away:

  • Search Host Controller Service
  • SharePoint Server Search
  • Distributed Cache
  • SharePoint Tracing Service

With the exception of the SharePoint Tracing Service, all of these identities can be changed from the “Service Accounts” page in Central Administration. But where’s the fun in that… furthermore, this page has one big disadvantage. You can change a service to run with a managed account but you can’t set it to run under a local account (LocalService, LocalSystem, NetworkService). So, if you changed your service from a local account to a domain account, you can’t undo this change using the UI. You need to use PowerShell.

The script below allows you to set a domain account or a local account.

 

Cleaning up obsolete SharePoint groups

Bart

During the lifetime of a SharePoint implementation, sites come and go. When a site collection grows, you typically see the amount of SharePoint groups growing as well because you want to give people access to those sites in a sort of organized way. When sites go, those groups are left behind. Removing those obsolete SharePoint groups can be a challenging task because groups which have been created for a specific site can be used for other sites as well. So, before removing a group, you need to be sure that it’s not used on any other sub sites.

SharePoint groups live on the root web of a site collection.

If a group is created as part of the site creation process, it will have a description which clearly states for which site is has been created. This doesn’t mean it can’t be used on any other sites. If you want to get a list of all SharePoint groups which exist in a site collection, you can also use the following PowerShell snippet to get the collection.

obsolete-sharepoint-groups-1

If you want to know which groups are used on a specific subsite of the site collection, you can use the UI and check the Site Permissions section of a site. This will give you all permissions for that site. You can also use the following PowerShell snippet to get these.

obsolete-sharepoint-groups-2

See the difference? The SiteGroups has a group “MyCustomGroup” which is not part of the Groups collection of the same web. This means that the group exists in the site collection but at this point, it’s not used. When I give this group explicit permissions to my site, it will be added to this collection and it will be in use.

So, the process of cleaning up obsolete groups is to check the Groups collection on each sub site and see which site groups are used for giving people access. If you have a site group which is not part of any Groups collection of any site, it’s not used and you can remove that group from the site collection.

You can do this manually, or you can automate this process and use the following script for this task.

This script will do 2 things.
If run in Simulation mode, it will look for obsolete groups and ouput them to the console. Nothing more.
If run in Execution mode, it will look for obsolete groups and delete them from the site collection.

My advice… run it in Simulation mode before running it in Execution mode. That way, you have an idea of what groups were found and will be deleted.

There are some situations which need clarifications.

Inherited permissions

What happens when you have subsites which inherit permissions? This is no issue. Suppose you have a subsite which inherits permissions of the root web of the site collection. When a SharePoint group is given access to the root web of the site collection, it will be given access to all sub sites which inherit their permissions and as such, that group will be part of the Groups collection of those sub sites.

Audience targeting

What happens when a group is exclusively used for audience targeting? Well, this is a problem because that group is not part of the Groups collection of the site where you have used it as an audience. In my opinion, this is a situation you should avoid doing because you are going to give a collection of users access to a site. In theory, an audience is a subset of authorized users, right? You want to target specific content on a site to specific users. If they can’t reach the site, what’s the point in targeting content to them?

If you do find yourself in such a situation where you have SharePoint groups which are exclusively used for audience targeting, a good approach would be to give those groups distinctive names, clearly indicating they are audience targeting groups. For example, you could start each group name with “AUD_”. This way, you can extend the script above and include a check to skip groups which start with “_AUD”.