Gaining insights in your Nintex Workflow Data

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.

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

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!  😎 

Change SharePoint Service Identities using PowerShell

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

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”.

Manage Office 365 using PowerShell

As someone who’s primary job is being a SharePoint administrator, the Rise of the Cloud (Office 365) was something which scared me in the beginning. Was my job doomed? Well, not exactly. You see, Office 365 is complex and it evolves constantly. Yes, a lot of stuff you do as an administrator on-premise is taken off your hands by Microsoft engineers at the datacenters but there’s plenty of stuff to “administer” in a cloud environment. You still need to manage users and their habits of losing passwords. You still need to create site collections, manage mailboxes, set up retention policies, and so on. Microsoft did a great job of providing a nice administration portal for all of these tasks but they also provide PowerShell management modules for all of the services in Office 365 to allow administrators to manage their tenant locally from their machine. This post gives an overview of the things you need to manage the different areas (services) in your Office 365 tenant.

Users/Licenses/Subscriptions 

A few weeks ago, the Azure AD PowerShell v2.0 module hit general availability and is now the recommended module to use when you want to do Azure AD management using PowerShell. At the time of this update, the latest stable version is 2.0.0.33. The old module (msonline) will be deprecated in the near future.

To use this new module, you need to install it from the PowerShell Gallery. To install a module from the PS Gallery, you need to install PowerShellGet on your machine. If you have a Windows 10 machine, this will already be installed and you can proceed with the installation of the module itself. If you have Windows 7 or 8.x, you have to install PowerShellGet first. You can find a link to the .MSI on the link below.

Once this package is installed, you can proceed with the installation of the AzureAD module.

The first line will set the PowerShell Gallery as a trusted repository. If you don’t set it as a trusted repository, you will see a warning when you run the Install-Module command.
If you have already an earlier version of this module (ex. 2.0.0.30), you can update it to a newer version by specifying the -Force parameter with the Install-Module command.

After the module has been installed, you can use it to manage your users, groups, licenses, and so on.

Skype for Business Online

To manage Skype for Business Online, you need to install the following package:

Once the package is installed, you can open a PowerShell console and connect to Skype for Business Online.

SharePoint Online

To manage SharePoint Online, you need to install the following package:

Once the package is installed, you can open a PowerShell console and connect to SharePoint Online.

The URL you need to provide to connect to SharePoint Online, is the URL of the SharePoint admin center in the Office 365 portal. For instance: https://mytenant-admin.sharepoint.com.

Exchange Online

To manage Exchange Online, you don’t need to install additional packages. You can execute the lines of PowerShell below to get started. When these lines are executed, the Exchange Online cmdlets are imported in your session. When you change Management Roles in Exchange Online however, you need to relaunch the session because changing roles also means that the cmdlets you CAN use, will change.

Rights Management

To manage Azure RMS in your Office 365 tenant, you need to install the following package:

When this package is installed, the PowerShell module will be installed on your machine.

 

An eye for details… changing the ImageUrl for migrated lists

Migrating from SharePoint 2007 to SharePoint 2013 can cause all kind of headaches but this post is not about those headaches. It’s about details, or better… having an eye for details. Ever noticed that after you migrate a site to SharePoint 2013 and you complete the actual visual upgrade to SharePoint 2013 mode, the list icons which are used, are not the fancy list icons which you get when you create a new list or library in SharePoint 2013? The icons for migrated lists and libraries are still the old icons from the early days of SharePoint 2007.

ImageUrl - 1

The icon for a list or library is stored in the ImageUrl property of a list and this property points to a gif or png in the “/_layouts/images/” folder for migrated lists. When you create a new list in SharePoint 2013, the value of the property points to “/_layouts/15/images”. Furthermore, if you compare for instance a migrated document library with a new document library, you notice that the value of the property differs, not only in the location where the icon is displayed from, but also the type of file. For instance, a simple document library.

  • Migrated document library : /_layouts/images/itdl.gif
  • New document library : /_layouts/15/images/itdl.png?rev=23

While I can imagine that a lot of people really don’t see any issue with this and don’t care how those icons look like, I don’t like loose ends. If you migrate an environment, you might as well get it done completely and replace the list icons with new versions as well and get the following result in the end.

ImageUrl - 2

Admit it, this looks much better than those old school icons. It’s a small detail, but it just makes more sense. If you have a smart user who actually cares about the environment, the question why new lists have different icons than existing lists, will eventually pop up anyway and if you tell them that this the result of the migration, the next question will be whether you can change them to resemble the new lists. Show your customers or users you have an eye for detail and do it proactively.

Changing these icons can be done very easily using PowerShell. The only thing you need is a mapping between the old and new icon.

I created a script which replaces all icons for lists and libraries. In this script, a mapping is done for the mostpart of the icons which are used. It might not be the complete list, but feel free to add missing icons. There are some scripts out there which replace icons, not based on a mapping but just replace all .gif icons with .png’s. However, there are some icons which don’t have .png counterparts. So, if you replace those, your list icon will be broken.

You can find this script in my PowerShell repository on GitHub

Replacing event receivers in SharePoint

I’m currently migrating a SharePoint 2007 to SharePoint 2013. For this particular environment, a custom solution was made which involves a number of event receivers. The customer wanted to retain this functionality, so I had to port this solution to SharePoint 2013. One problem though… the source code was not available. We had to revert to reverse engineering the solution using ILSpy to recreate the source code and build a new solution. We made sure that all feature ID’s were the same and that our namespaces and class names were also the same. After deploying and testing the solution, it worked.

During the migration, we attached the content database to the SharePoint 2013 web application and that’s when we noticed something.
When you add an event receiver to a SharePoint list, the “Assembly” property of the event receiver contains the assembly signature of the DLL which contains the event receiver class. When we attached the database, SharePoint complained it was missing an assembly. The assembly of the old solution. When we compared the assembly signature of the old solution with the signature of our new solution, we saw it had a different publickeytoken. We completely overlooked this. This was one of those “Doh!” moments.

It seems that it’s not that straightforward to change the publickeytoken. I found a way to extract this publickeytoken from a DLL and generate a strong name key (SNK) file.

sn.exe -e myassembly.dll mykey.snk

But this strong name key is missing one crucial piece of information. The private key. If you want to sign your solution with this strong name, you need to do this using delay signing. Your solution will build and the signature matches the one from the old assembly, but when you try to deploy it to SharePoint, it fails because it can’t add the assembly to the GAC due to the missing private key.

I figured that instead of looking for workarounds, the most easy way to solve this, is to replace the old event receivers with new ones which have the correct signature. This proved to be an easy solution. I created 2 scripts which helped me with this.

Get all event receivers with a specific signature

This scripts returns all event receivers which have a specific signature.

You can export this output to a CSV file, which can be used in the next script. All information which is needed to replace these eventreceivers is included in the output.

Delete and recreate event receivers

Using the .CSV file which can be created from the previous script, the script below deletes the old eventreceivers and replaces them with new ones. It uses the information from the old eventreceivers which is included in the CSV and uses the signature which is passed in as a parameter, as the new assembly signature for the new event receivers.

You can find these scripts in my PowerShell repository on GitHub.

Add a SQL Alias using PowerShell

Setting a SQL alias on every SharePoint server is a common task when you are installing SharePoint. You use the SQL Server Client Network Utility (cliconfg.exe) for this. This tool is available on every SharePoint server because it’s part of the SQL Server Native Client prerequisite.

Setting a SQL alias is a best practice because it makes your life a whole lot easier when you want to change the actual database server in some point of time. If you use an alias, the only thing you need to do at that moment, is change the target of your alias and you’re good to go. If you install SharePoint and you reference the database server directly, your only way of pointing SharePoint to the new database server painlessly, is to create an alias at that time, set the name of the alias to the name of the old database server and have it point to the new server. Definately not cool because when someone looks at the Servers in Central Administration, it will list the old name and it’s not clear that this is not a server anymore but an alias.

When you want to set an alias, you run cliconfg.exe on each SharePoint server. In this tool, you have an “Alias” tab, where you can set it. You provide a name, the type of connection (Named Pipes, TCP/IP) and a server name. You can also select a custom port if you use TCP/IP or keep the default.

sql alias - 01

If you don’t want to do this manually, there’s also a way of doing this with PowerShell. The only thing this tool does, is create a string value in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

sql alias - 01

So, doing this using PowerShell is easy.

You can find this script in my PowerShell Repository on GitHub.

Toggle audience targeting using PowerShell

Audience targeting in SharePoint is a great way of hiding irrelevant information from users and likewise, only show relevant information. You could create a list with a lot of items and target those items to specific groups of users. Users who are targeted will see the items while other users will not see them… audience targeting. This has nothing to do with permissions. It’s just a filter. Users who are not targeted can still access those items if they want.

To be able to use audience targeting on a list, you have to enable it first. It’s disabled by default. To enable this, you can go to the list settings of your list and open the Audience Targeting settings. There you will find a checkbox to enable this.

Once it’s enabled, you will see an extra column in your list “Target Audiences” of the type “Audience Targeting”.

AudienceTargeting

To disable it, go back into the audience targeting settings and uncheck the checkbox. The extra column will be removed.

That’s the UI way… now, how about PowerShell? Can you enable/disable audience targeting? Offcourse!

We only need to add a field with a specific ID and attributes to the list and it’s done.

Read more

Get Web Template Usage in SharePoint

Having a SharePoint platform which is widely adopted and used is cool. But as a SharePoint administrator I’m also interested in how this platform is used and where it’s used for. One of the things I like to do is to find out which kind of sites are used and how many of them exist.

This can also be quite useful when you are considering a migration to a new SharePoint version. Not all site templates are supported anymore in SharePoint. For example, the document workspaces, meeting workspaces, and group work templates. These are partially supported when migrating from SharePoint 2010 to SharePoint 2013 or are just supported for backward compatibility. At the same time, there are also custom solutions out there which add site templates and it’s good to know when preparing for a migration, if you have sites which are based on those kind of templates.

The tricky thing is… how to get that information. There’s a cmdlet which is called Get-SPWebTemplate. This gives you a list of all web templates which have been installed in the environment.

Get-SPWebTemplate

This doesn’t give you which templates are actually used. For this, you need to combine some information. The name of a web template consists of 2 parts:

  • Web Template
  • Configuration ID

When you look at the properties of an SPWeb object, you find these 2 specific properties in the list of properties. You need to combine these 2 with a “#” between them to have the webtemplate.Get-SPWebTemplate1

This allows you to identify the template for each web which exists in your farm. But this is only part of the info we need. We need the usage count of each used template and get something like this:

Get-SPWebTemplate2

To get this output, I wrote a small script.

If you would like to sort the output of the script to have the most used templates first, you can execute it like this:

which gives you the following:

Get-SPWebTemplate3