SharePoint Workflow and PowerShell Report

Hi everyone,

If you have ever worked on SharePoint workflows, you will notice that sometimes your task list gets crowded with many entries, and you want a way to query that task list to get some information, like number of pending tasks, tasks overdue, or any other kind of information.

For example, I may want to create a PowerShell script to query a specific task list, and send weekly report to each user about his pending tasks via email in a nicely formatted HTML table. While SharePoint workflow task actions can send overdue emails, it will do that per task, and not by aggregating all pending tasks per user.

People do not want to get email for each pending task, they want instead to have a weekly or daily email showing all their pending tasks. You can do anything once you know how to interact with SharePoint lists using PowerShell.

I will not give you a ready PowerShell script to do all the magic, but instead, I will show you how to start doing that, and how to use PowerShell to get the essential data. You can then use your PowerShell skills to do whatever you want with it (Send email, create an HTML table, aggregate data..).

First of all, you must load the SharePoint snap-in inside your PowerShell host environment.

Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue

Next, you want to connect to your list:

$weburl = ""

$web = get-spweb $WebUrl

$list = $web.GetList("")

here my SharePoint site is while my list URL is the one I used in the last command.

Now that we are connected to the list using $List variable, $List is an array of all items in that list. So for example, to get the first item of the list:

$FirstItem = $List[0]

This will return to us the first item of the list, and since we are talking about task list, we can expect this item to have all the columns in a typical task list.

To get this tasks’ due date:


Now we can go and get task assignee for this task. This can be tricky as this field is of type (People or Group), and you may need the user/Group ID, or perhaps the email address in case you want to send them email notifications about their pending tasks:

$myField=$FirstItem["Task Status"].ToString()
$userfield = New-Object Microsoft.SharePoint.SPFieldUserValue($web,$myField.ToString());

This will give you the DisplayName and email address for task assignee. Although it says User.DisplayName and User.Email, even if the task assignee is a group, it will bring the group email and displayname also.

Finally, you can also do some cool stuff. Instead of getting all the task list items and store them in the $List variables and then looping through those tasks, it is much easier if you only get the items you want. Suppose you only want to process tasks not completed and due. To do this, you will use a CAMEL query to query only those tasks. Here is an example:

#We will set the Enddate to today
$EndDate = [Microsoft.SharePoint.Utilities.SPUtility]::CreateISO8601DateTimeFromSystemDateTime([DateTime]::Now)

#we will define a query that will get only tasks that are NOT Completed and DueDate less then Today
$caml='<Where><And><Neq><FieldRef Name="Status"/><Value Type="Text">Completed</Value></Neq><Lt><FieldRef Name="DueDate"/><Value Type="DateTime">{0}</Value></Lt></And></Where> ' -f $EndDate
$query=new-object Microsoft.SharePoint.SPQuery

#Then will only get list items matching the query we did

Now the sky is the limit. You can do any kind of crazy thing using the above commands 🙂 Enjoy.

SharePoint Workflow : You write a code that is too long to upload!!

I want to share with you a funny situation while writing extremely long and complex SharePoint workflow. I was creating a workflow that controls deployment requests and there is a requirement to throw an approval for 9 stages. After each stage, there is couple of email notifications and web services to call.

The code got extremely big that when I tried to publish my workflow code inside SharePoint designer, I got this message:

“The request message is too big. The server does not allow messages larger than 2097152 bytes.”

It seems I reached the maximum allowed size for a workflow code definition :). After opening a case with Microsoft, we came up with couple of workarounds:

  • Divide the workflow to smaller workflows
  • Re-write the workflow code in a way that redundant code can be removed.
  • Increase the upload limit of 2097152 bytes.

I worked with Microsoft engineer to apply the third workaround by applying the following PowerShell code on all SharePoint front end servers, and doing IIS reset after that:

$ws = [Microsoft.SharePoint.Administration.SPWebService]::ContentService

$ws.ClientRequestServiceSettings.MaxReceivedMessageSize = 5120000

$ws.ClientRequestServiceSettings.MaxParseMessageSize  = 5120000


This will increase the upload limit to 5 MB instead of the default 2 MB. When I initially try to get the default value of the above values, I got empty values, so me and Microsoft support engineer assumed that empty value is actually 2 MB.

I hope this will help someone out there. Of course, the best practice is to do smart coding for workflows so that the code never exceed 2 MB for performance reasons as per my talk with Microsoft engineer.

Tip: How you can know the size of your workflow code?

Open SharePoint designer, Go to Workflows, pick your workflow, click Save As Template. This will save the template to “Site Assets“.

SharePoint Workflow Code is too long 23232

Next go to Site Assets,  you will see your workflow file with WSP extension, along with its size. This size does not reflect the actual size of your workflow code. Now go and click Export File, and save it to your desktop.

SharePoint Workflow Code is too long 233232

Now go to your desktop, and change the extension of the exported file to .CAB , then double click the file to open it:

SharePoint Workflow Code is too long 233378

SharePoint Workflow Designer Tips P5

View other parts:

I want to start this part by talking about the rule of abstraction. Again, it is best practice to not hard-code any values inside your workflow code. Your workflow code should read at execution time all needed configuration values from external source. This is what we talked about in the previous part of workflow designer tips.

There is another place where abstraction can be applied. Inside your workflow code, you may need to interact with user values and groups. Usually you interact with users and groups in two places:

  • When you assign a task, you usually assign it to user or a group of users.
  • When you send email notification, you send it to a mail enabled user or group.

First tip is to avoid working with users and instead work with groups. This is the first level of abstraction. When you work with groups, you can externally modify the membership of the group and the magic happens right away. Even if you are assigning a task for a single user, you can assign it to a group that contains only that user. Later when that user leave the company and there is a need to change the task assignee, you can just change the membership of that group.

The second part of abstraction is to create a separate SharePoint list called Workflow Subscriptions for example, that contains two columns:

  • Subscription. (Data Type: Single line of text)
  • Subscriber. (Data Type: Person or Group)

Now, you can fill that list with values you wish. For example, if your workflow is sending email notifications to a group of people upon finishing the process of a new hire, you could create for example a new SharePoint or Active Directory group called (New Hire Notification Group), that contains the people that should be notified upon a new hire, and then create an entry in the Workflow Subscriptions like this:

  • Subscription: New Hire Notification.
  • Subscriber: New Hire Notification Group.

Now inside your workflow designer, when you want to send email to notify people for a new hire, you do the following:

SharePoint Workflow Designer Tips P1 65554.JPG

The same applies if you want to assign a task to a group, you create an entry in the Workflow People list after you create a group called (New Hire First Approval Group):

  • Subscription: First Approval.
  • Subscriber: New Hire First Approval Group.

You can see that we talked in the previous part about the Configuration List and now we are talking about Workflow People list. The main difference between them is the data type of the columns. The Configuration list has two single line of text columns, while the Workflow People has one column with single line of text column, and the other is People of Group column. Together, these two lists will give you enough abstraction to remove any hard-coding from your workflow code, and makes your workflow code safe from direct changes.

SharePoint Workflow Designer Tips P4

View other parts:

In this part of the tips, I want to talk about abstraction concept. When you write a good workflow code and everything works just fine, you should not face a situation where you open the SharePoint designer to do some changes unless there is a major change in the logic of how the workflow works.

If you have a change management request list that is served by your workflow that requires three approvals, and you wrote the workflow code, you test your code, you put the workflow in production, and everyone is happy with it, then theoretically speaking, you should not have a case where you need to open the workflow code again. Even if one of the approvals is changed to someone else, this should not be a cause to change the workflow code.

This can be accomplished by introducing a level of abstraction, by not hard-coding values inside the workflow code, and by maintaining all configuration values in separate SharePoint list, that your workflow will read from in real time.

SharePoint Workflow Dashboard Tip 651681

So now, you shall create a SharePoint List called (Workflow Configuration) for example, with two columns:

  • Configuration Name (Single line of text)
  • Configuration Value (Single line of text)

You then start working on populating this list with any value that you may use in your workflow. Examples are:

  • If you assigning tasks, then you can put the task due date and task title in the configuration list.
  • If you are calling web service, then you can put the web service URL in the configuration list.
  • If you have any static values or counter values, you put them here also.
  • Any switch values, for example, you may have a variable that is named (SendNotificationEnabled) and if it is true, your workflow will send notification. You can read this variable from the configuration list. That way, you can change the variable value from the configuration list without opening the workflow code.

Inside the Workflow Designer, you can read values from the Configuration List:

SharePoint Workflow Dashboard Tip 4538

Just thing of this case with me. Your workflow is calling a web service, and you kept the web service variables in a confirmation list like we did here. If the URL of the web service is changed for any reason, then you can ask anyone to change it from the configuration list. No need for you to go and open the Workflow SharePoint designer, and do some changes, and hit the scary Publish button.

I usually do not keep any single hard-coded value in any of my workflow code. Usually, when I write a workflow code, I do not open the code again until there is major changes the affects the way the workflow logic is happening. Any other changes, or customization are all kept and maintained in a separate configuration list. This even include the subject of workflow email notifications.

SharePoint Workflow History Data and Logs Tips – P3

We talked about the workflow log types (Audit and Debug), and we agreed that using the built in “Log to History List” action inside SharePoint designer is not a preferred way from my personal point of view. So what is the better way to do logging?.

You can think of a SharePoint list as a database table. It has columns and each column has specific type. Tables in a relational database model can relate to each other using keys. In SharePoint, lists can relate to each other using a column type called (Lookup) in the same way.


We have a process to log changes happening in data centers. We need to create a process to track those changes and require an approval for every change. Finally, auditors will require some kind of logs to be exported as a proof of the integrity of such change control process. You also need some debug logs for troubleshooting purposes.

Let us start designing the solution by defining couple of content types. If you are not familiar with content types inside SharePoint, I suggest you start learning about this concept. In my own words, content types are like creating a class in any programming language. You create a class, define some properties and the data type for each property, and then you can create instances of the class whenever you want.

Same thing applies here.Content Types are like classes, and Site Columns are like Class Properties. If you are still not that comfortable with content types, just create normal lists and columns, but in this example, I will use content types just because I like to do that.

We will create three content types “Parent Content Type shown in the picture”:

  • Data Center Changes
  • Audit Log
  • Debug Log

SharePoint Workflow Designer Tips P1 3

We will also create three lists:

  • Data Center Changes List.
  • Audit Log List.
  • Debug Log List

Data Center Changes content type has two columns:

  • Title (Single Line of Text)
  • Data Center Change Reason (Multiple lines of text)

SharePoint Workflow Dashboard Tip 32

Debug Log content type has two columns:

  • Debug Message (Type: Single line of text)
  • Change ID ( Type: Lookup) – maps to the Data Center Changes “ID” column.

SharePoint Workflow Dashboard Tip 33339

SharePoint Workflow Dashboard Tip 333391

The same thing with the Audit Log content type, it has two columns in the same way:

  • Audit Message (Type: Single line of text)
  • Change ID (Type: Lookup) – maps to the Data Center Changes “ID” column.

Now we have the following relation between our three lists:

SharePoint Workflow Dashboard Tip 99

When you are inside the SharePoint designer, and you want to throw a debug message, choose action (Create List Item), pick the Debug Log list, as per the following:

SharePoint Workflow Dashboard Tip 456

As shown, you have to populate the (Change ID) with (Current Item:ID), and then fill the (Debug Message) with the log content you want.

The same applies when you want to create an audit message. Finally, you can go to the Debug Log List and the Audit Log List, create a view with (Group By) using the Change ID field, and you will get all debug messages and audit messages per Change ID.

You can use SharePoint Information Management Policy to apply retention policy to purge the debug messages from the Debug Log List after say one month, and another retention policy to purge the audit messages from the Audit Log List after say one year.

I hope this highlight some of the great benefits from using such approach to create and maintain logs generated from your workflows.

SharePoint Workflow History Data and Logs Tips – P2

We have talked in part one that the workflow logs can be classified as Audit Logs or Debug Logs. Audit logs have long retention and are used by auditors and security teams as a proof of a controlled process, while Debug logs have low retention and are used by the people maintaining the workflow for troubleshooting purposes and to track the execution state of the workflow at different execution times.

SharePoint Workflow Designer gives you a built-in way to through some log data to a history list using an action (Log to History List). I am not fan at all of using this way of logging for different reason.

Let us talk a little bit about the Log to History List and about the History list itself. By default, there is a hidden list that get created by default when you first create your first workflow in a site called History List. You can use this history list for different workflows or you can choose or create different history list for each workflow.

One of things I do not like in such history lists is that it accept only single string at a time. There is no other columns in that history list that you can benefit from. Not that this is a big limitation, but I do not like to be restricted with only sending a string at a time. It makes it hard to filter and analyze the log data as I will described later because of the lack of other columns.

Second thing is that a workflow can be associated with only one history list to be used for logging. Take this example: You have a workflow that calls a web service, and you want to log the status code or perhaps the returned value from that web service call. The only option you have here is to use the Log to History List. You also want to log other events during the workflow execution. Now, someone came to you and ask you to give a report of all web service calls and their return code for analysis. You have to go and open that hidden history list from SharePoint designer, and then what you will see? You will see a lot of lines without any ability to filter the logs related to the web service calls and to track them back to the list item that cause the service to start. My point is it is very hard to look at the history list and track certain events, or do any kind of filtering.

Also we talked about two types of logs, audit and debug logs. Your only option here is to use the Log to History List and through logs related to auditing and other log entries (debug data) for you to troubleshoot the workflow. Now when the auditors ask you to extract a report for a certain item, the logs are mixed between audit and debug. Also, you may want to keep log entries used for auditing for longer time than those used for debugging, which you cannot do in this case because both are saved in the same history list.

Things become more interesting when you read about the “Workflow Auto Cleanup“. It is not best practice to disable this job or change its duration by the way. This job will remove the association of the workflow tasks and history data after 60 days by default. You can read more about this here. But what about the need to keep audit data for one year for example?. What will do then? People will disable this timer job, but Microsoft keep saying it will affect the performance of the product or something. If Microsoft implemented this timer job then there is a reason.

I think for a professional workflows, especially those that requires auditing, you should not use the built in way (Log to History List). I will describe the way I prefer in the next part.

SharePoint Workflow Designer as RDP APP

If you are responsible of writing SharePoint workflows using the SharePoint designer, I want to share with you a small tip when it comes to using the SharePoint designer console.

Usually, you have SharePoint servers and perhaps Workflow Manager in your data center, and you may have installed the SharePoint designer at your machine and connect remotely in order to start coding workflows.

SharePoint Workflow Designer Tips P1 233622

What I do not like in this case is the dependency on the link.  Sometimes you work remotely from a hotel room connecting to unreliable wireless network, connecting VPN to your corporate network, opening the SharePoint designer console from your machine and opening a very big workflow definition code, do some modifications and hitting Publish. You do not know what will happen if the network connectivity is not reliable.

What I prefer is to have a Remote Desktop server in the data center to do administrative tasks for different thing. You can then install the SharePoint designer in that remote desktop server, and then you can log remotely to that server and open the SharePoint designer from there. That way, when you hit Publish workflow, the changes will be pushed from the terminal server to that SharePoint farm without depending on any unreliable connection. Furthermore, I also have exported the SharePoint designer as a remote web app and copy it to my desktop. Whenever I want to use the designer, I just open the RDP file in my machine, which will connect using RDP to the RDS server in the data-center and give me a great experience.

SharePoint Workflow Dashboard Tip 4269

Even if you connect from your hotel room, connecing via unreliable wireless network via VPN to your corporate network, you will RDP to that RDS server, use the SharePoint designer from there, open your big workflow code, do your changes, hitting Publish, and you do not worry about anything. In fact, you can close your VPN connection and the background, the SharePoint designer will take its time publishing your workflow changes without any networking issues.

SharePoint Workflow History Data and Logs Tips – P1

I want to talk about metadata, SharePoint Workflow History logs, and how to use this data for different purposes. People underestimate this part when thinking about workflows, and just focus on how to do the workflow logic.

I usually classify the log data the comes out of a workflow into two types: Audit data and Debug data.

Audit data is the data that auditors ask for in order to validate the process integrity. If you have a solid change control policy in place, and you have a workflow in SharePoint to control a process, auditors usually ask for proof in form of workflow logs. For example, if you have a workflow to control the process of creating service account in active directory, auditors will come and will ask you for a proof that these accounts are created in active directory after passing through a workflow approval cycle. To do that, you can code your workflow to generate audit log data to be exported and shown to the auditing team once requested.

SharePoint Workflow Dashboard Tip 12342

Let me give you another example. I had a solution that is used to track new changes in a data center. To do that, I created a list in SharePoint and anyone can submit a change request that passes through couple of approvals. Auditors and security team will come every quarter and ask: “Give us a proof that any change in the data center is logged and passed through approval”. IT Admins will then go and export the Audit logs as a proof. Auditors also require audit data to be available for a full year for any change happening in the data center.

Audit data is used mainly for security reviews and should have long retention (one year for example). The main purpose for such log is only for auditing and proof that an approval cycle is in place to control the process actions.

On the other hand, SharePoint workflow logs can be Debug Logs. Debug logs are used mainly for the team working on writing and supporting the workflow. If you write a complex workflow, you would like to through couple of logs at certain points of the workflow life cycle to track what the workflow is doing and if it is working as expected.

Suppose you have a workflow that calls a web service, sends couple of email notifications, and start approval requests. For you, it would make sense to generate a log before calling the web service, and after you got the web response back, and perhaps log the response code, to make sure the web service call is working just fine. Also, you could log that you are trying to send email notifications now, and that a task is generated for this person waiting for his approval. All these logs are only used for you as a workflow programmer to track the workflow status and action at different points of execution.

Usually debug logs will have a very short retention, as you only want to keep such logs for a month in most cases.Auditors and security team do not care about these log messages.

In the next parts, I will start talking about how to plan these logs and why I personally do not like to use the built in workflow history for this purpose.

SharePoint (Workflow) Dashboard Tip – P2

Creating the workflow

I will not go through how to create the workflow, but i want to point to an important point.

When you create a task for approval, you have to inset the following logic.

If task is approved > Set the Photo hidden column to the URL of the matching photo in the picture library you created previously.

This means if for example the stage one approval is completed and approved, then we will set the Photo field to the URL of the picture describing this phase.

SharePoint (Workflow) Dashboard Tip19

So now after creating a request and approving the first task, we can see the workflow filling the Photo field.

SharePoint (Workflow) Dashboard Tip20

Creating the Dashboard

First of all, go to the Submit Request list, and create a view that includes the columns you want to show in the dashboard. Do not select the Photo column. I prefer the Box style for the view.

SharePoint (Workflow) Dashboard Tip21

Finally, it is time to create the dashboard. Simply, add a new Page and call it (Dashboard).

Go to Inset Tab and click App Part > Submit Request.  Make sure to edit the web part and choose the view you previously created. Also choose (No Toolbar) for the toolbar type.

SharePoint (Workflow) Dashboard Tip22.JPG

Now add another App Part in the page, which is Image Viewer under Media and Content.

You can use Query String URL filter and data connections to send data between the two web parts so that the Image Viewer web part will read the photo URL from the Submit Request Photo column.

SharePoint (Workflow) Dashboard Tip – P1

Hi everyone,

I was working on a project to implement a process for three stage approvals using SharePoint workflows.

Someone will submit a request, it goes for first approval (Stage 1), if approved it will go to the second approval (Stage 2), and finally if approved a third approval is required (Stage 3).

You can simply do this with SharePoint. The challenge is how to present such solution in a visual way and create a dashboard to present the current request state. So at any point of time, it is nice to see where the request is standing in terms of approvals, and perhaps a dashboard showing this in a nice way. After all, a picture worth a million words.

SharePoint (Workflow) Dashboard Tip23

How to do this?

We need to create a list called (Submit  Request). But keep in mind that we need to have some hidden columns in this list to host some metadata that we may use to create the dashboard.

The best way is to use SharePoint Content Types. Content types are reusable structures that you can attach to list and libraries to describe the columns schema from central place.

Creating Content Type

Go to Site Settings > Site Content types > Create

SharePoint (Workflow) Dashboard Tip1

Name: Submit Request Type.

Select Parent Content Type From: List Content Types.

Parent Content Type: Item.

SharePoint (Workflow) Dashboard Tip10.JPG

Then start adding columns by clicking (Add from new site column). You can add columns like (Request Title), (Request Reason), and any other data you need.

The most important thing is that you create a new column called (Photo) with type (Single line of text).

SharePoint (Workflow) Dashboard Tip11.JPG

Now, let us move and create a new custom list) and name it Submit Request. Then opening the List Settings> Advance Settings> Enable Management of Content Type.

SharePoint (Workflow) Dashboard Tip101

SharePoint (Workflow) Dashboard Tip5

Then click Add from existing site content types, and add the newly created one (Submit Request).


The new content type will appear now as an available Content Type

SharePoint (Workflow) Dashboard Tip12.JPG

Finally, click on the content type called (Item) and then click Delete this content type.

SharePoint (Workflow) Dashboard Tip13.JPG

SharePoint (Workflow) Dashboard Tip9

Finally, we will go back to the (Submit Request Type) content type, and mark the Photo column as hidden.

SharePoint (Workflow) Dashboard Tip14.JPG

Creating Picture Library

The next step is to create Picture Library, and add to it a photo that describes each possible outcome from submitting the request.

Since our request consists of three stages, then we need total of 6 pictures:

  • Stage One Completed
  • Stage One Rejected
  • Stage Two Completed
  • Stage Two Rejected
  • Stage Three Completed
  • Stage Three Rejected

Here is an example of couple of photos I am using:

SharePoint (Workflow) Dashboard Tip16

SharePoint (Workflow) Dashboard Tip18SharePoint (Workflow) Dashboard Tip17