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.

Office 365 and Group Moderation Tips

In the process of testing out Office 365 and Exchange hybrid configuration, an interesting thing happened that I want to share with you.

I have an on-premise Exchange 2010 implementation and couple of users are hosted at Office 365. All hybrid configurations are set and connectors are configured to route emails between the two spaces.

Everything is working fine, and mailboxes hosted on Office 365 are working just fine. Things started to get interesting when people start to send emails to moderated distribution groups.

When someone sends email to a moderated groups, and the moderator is hosted on Office 365, the buttons for Approve and Reject are not showing at his email client.

It turned out that a setting called TNEF (Transport Neutral Encapsulation Format) is causing this to happen. We need to make sure TNEF format is enabled when sending emails out to Office 365 tenant.

The TNEF setting is configurable per remote domain (Get-RemoteDomain) and (Set-RemoteDomain).

By default, there is a default RemoteDomain configured in your Exchange environment called (Default). If you hit (Get-RemoteDomain), you will see all settings that controls the behavior of email communications and format when sending emails to external parties. One of the settings is TNEFEnabled.

Now that we have Office 365 hybrid setup, the HCW creates for us a remote domain in the on-premise organization to allow TNEF (

That is great. So all what we need to do is to configure that remote domain (Set-RemoteDomain -TNEFEnabled ….) and all is done, right?

There is a small thing left to say. When Office 365 sends emails regarding moderated groups, the messages come from a system mailbox in the tenant with email address SystemMailbox{bb558c35-97f1-4cb9-8ff7-d53741dc928c} So we need to add a new remote domain called

So let us start typing some PowerShell commands:

New-RemoteDomain -Name “Hybrid Domain –” -DomainName

Now we have two remote domains:


We have then to configure both remote domains to allow TNEF format. I also recommend configuring many other settings on the way.

Set-RemoteDomain -Name “Hybrid*” -IsInternal $true -TargetDeliveryDomain $true -AllowedOOFType InternalLegacy -MeetingForwardNotificationEnabled $true -TrustedMailOutboundEnabled $true -TrustedMailInboundEnabled $true -UseSimpleDisplayName $true -TNEFEnabled $true

That’s great. Now we have configured both remote domains to enable TNEF format. I have also noticed that when on premise mailboxes try to communicate with the Office 365 system mailbox for moderation actions (Approve,Reject), they are receiving authentication errors. To fix that, add the to the address space of the Office 365 connector:

Set-SendConnector “Outbound to Office 365″ -AddressSpaces @{Add=””}

Finally, it makes sense to instruct the Office 365 tenant to treat the on premise Exchange organization the same way. Suppose my on premise domain domain is, then connect to your office 365 Exchange PowerShell, and type:

New-RemoteDomain -Name “Hybrid Domain –” -DomainName

Set-RemoteDomain -Name “Hybrid*” -IsInternal $true -TargetDeliveryDomain $true -AllowedOOFType InternalLegacy -MeetingForwardNotificationEnabled $true -TrustedMailOutboundEnabled $true -TrustedMailInboundEnabled $true -UseSimpleDisplayName $true -TNEFEnabled $true.

Reference Link 

Compare your on Premise AD users and Azure users – One by One !

If you are synchronizing your on premise Active Directory with Azure Active Directory, then you know for sure that maintaining a healthy synchronization is not an easy thing to do.

I want to give you my experience in synchronizing a single domain AD to Azure AD, using ObjectGuid as anchor attribute.

The Challenge

I am using Azure AD Connect to synchronize AD objects to Azure AD. The Azure AD Connect tool is nice, and it gives you the health of the synchronization process and a nice error messages if there is a problem synchronizing one of your on premise AD objects. I used the AD connect tool for a month, and everything was fine. No errors and everything looks clean.

I then noticed that couple of users are having problem with Office 365 and i discovered that they have never synched to Azure AD in the first place. I did Get-MSOLUser on them and no results are returned. I had to go to Azure AD Connect and force Full AD Import to sort out this issue.

I become so worried about this, and I started to compare the number of AD users and Azure AD users to at least ensure matching numbers. Count AD users and Azure AD users and compare them is not enough, as there is no guarantee that the same objects represent these numbers.

I then came with an idea. I wanted to take each AD user, collect his ObjectGuid, and then compute his ImmutableID, go to Azure AD, and search for that ImmutableID, and finally linked the AD user with his Azure AD copy. We will do that for all AD users. Finally, we can identify AD users that does not have Azure copies, and Azure AD users that are not mapped to AD users.

This will guarantee that each one of your AD users are mapped to Azure AD users. Along this journey, the script will generate couple of information:

  • AD users not in Azure
  • Azure users not in AD
  • Azure users with Synch Errors
  • Filtered AD users from Synchronization
  • Total number of AD users
  • Total number of Azure users
  • Last AD Sync time

Compare you on Premise AD users and Azure users 11

Conditions to use the script

  1. This script assumes you are using ObjectGuid as anchor attribute
  2. If you have multiple domains, forests or you are doing filters to scope AD users by OU or attribute, then you must write your script block to populate the $ADusers_Raw variable, by searching inside the script for (Raw data collection from AD) region. You can find examples there to do that. By default the script will do Get-ADuser to retrieve all users.

Compare you on Premise AD users and Azure users 2

Script Visuals

The script provides many visuals to help you see what is going on:

  • The script code is divided into regions that you can expand separately for better script browsing.
  • Progress bars will appear during running the script to give you a feel and sense of what is going on during the run time of the script.
  • Summary information is displayed on the PowerShell console window with summary statistics.
  • Results are written at the end of the script in two locations:
    • The PowerShell console window.
    • Couple of files that are generated on the script running directory.


The script connects to Active Directory and gets a list of users (Get-ADUser) , and then connects to Azure Active Directory AAD and gets all azure users (Get-MSOLUser). A comparison then is performed by mapping each AD user with his Azure user, and identify un-synchronized AD users that do not have a mapped/synched Azure copy.

This script assumes that ObjectGUID is used as the anchor attribute to link/map AD user and Azure AAD user. If you are using any other attribute, then this script is not for your case.

The script needs to get the list of AD users that you are synchronizing to Azure AD. Microsoft Sync tool gives you the ability to filter by OU or by attribute. Another tricky part of the script is when you are synchronizing multiple domains or perhaps multiple forests. For all those different cases, it is your job to populate the variable called ($ADusers_Raw) located under (Raw data collection from AD) region in this script.
By default, the script will do Get-ADUser to populate this variable. In your case, you may need to write your own script block to collect all AD user objects that you are synching to azure.

Script Parameters

.PARAMETER ScriptFilesPath
Path to store the script output files. For example C:\ , or ‘.\’ to represent the current directory.

As the script needs to connect to Azure, an internet connectivity is required. Use this option if you have an internet proxy that prompts for credentials.

.PARAMETER CreateGlobalObject
The switch when used, the script will generate an extra csv file that contains a unified view of each user with properties from the on premise AD and Azure AD.


.\Compare-CorpAzureIDs.ps1 -ScriptFilesPath .\

.\Compare-CorpAzureIDs.ps1 -ScriptFilesPath .\ -Proxy:$true

.\Compare-CorpAzureIDs.ps1 -ScriptFilesPath .\ -CreateGlobalObject

Download the script

You can download the script from here.

Exchange Email Moderation Super Cool Script – Must Have

Hi everyone,

Email Moderation is one of my best features in mail flow restrictions in Exchange. You can assign one or more moderators to groups, so that if any one of them approves the email being sent, then this will release the email to that moderated group.

You can view email moderation information from Exchange GUI admin tools, but for dynamic moderated groups, you shall use PowerShell to view and configure email moderation. Usually, dynamic groups that has country or office filter criteria will contain lots of people and you want them to be moderated.

No Dashboard for moderation info

The first issue people have with email moderation is how to get a report with all email moderated groups and their moderators, and bypass moderation recipients. There is no dashboard that shows all this information in one place.

Disabled or Orphan Moderators

The second issue that Email Administrators will face is moderation list maintenance. Suppose that GroupA has one moderator called John. John decided to leave the company, and his account is now disabled. Now GroupA has no moderators. It has Moderation status set to true, but no moderators. Some cleanup job need to be performed frequently to check for the health and existence of the moderators.

Single Moderator Issue

Moreover, the best recommendation is to have at least two moderators for each moderated group, so that if one of them is not available or on a leave, the other one can moderate that group. You may want to have a regular checks to detect moderated groups with one moderators only.


I have created a PowerShell Script that you can run, and it will do the following:

  1. Generate CSV file that lists all moderated groups in your environment with the following Info:
    1. Group Name
    2. Dynamic mailing group or not
    3. Moderators list
    4. Bypass moderation list
    5. Managed By list.
    6. Email Address
    7. Alert column if a single moderator is detected.
    8. Health Field to indicate if one of the moderators is disabled or does not have mailbox anymore.
    9. Empty Moderator List warning
  2. Three Log Files will be generated. One for information, one for empty moderator groups, and one listing groups with disabled mailbox moderators.


Download the Script here

You can download the script from here Get-CorpModerationInfo

How to run PowerShell Scripts efficiently from Task Scheduler?

Hi everyone,

I got many questions about how to schedule a PowerShell script via Windows Task Scheduler.  It is tricky somehow in certain conditions. So I would like to share my thoughts.

I blogged previously about this topic here and I mentioned what can happen when you enter long strings in the Create Task wizard.

In this blog post, I will try to schedule my Get-EmailCorpReport on my Windows 2012 R2 server that has Exchange 2013 on it.

First step is to go to create a folder on the C drive called MyScripts , and then drop the Get-EmailCorpReport script there.


Second thing is to make sure that the server allows PowerShell scripts to run. To do that, open PowerShell as an administrator and tun:

Set-ExecutionPolicy RemoteSigned

How to run PowerShell Scripts efficiently from Task Scheduler1

Then we will create a dummy PowerShell script with one line on it, to invoke the Get-CorpEmailReport with all its parameters. To do that, create a text file , and type the following inside it:

C:\myscripts\Get-CorpEmailReport_v2.4.9.ps1 -ScriptFilesPath C:\myscripts -SendMail:$true -MailFrom -MailTo -MailServer

Now save the file as RunMe.PS1. Make sure that the extension of the file is PS1 and not txt. Place the file inside C:\myscripts directory.

How to run PowerShell Scripts efficiently from Task Scheduler13

Now, open Windows Task Scheduler, create a Basic Task.

How to run PowerShell Scripts efficiently from Task Scheduler14

On the Action section, choose Start a program.

How to run PowerShell Scripts efficiently from Task Scheduler15

In the Program/Script section, type:


On the Add arguments(optional) section  type:


How to run PowerShell Scripts efficiently from Task Scheduler16

After you finish, open the task properties, and make sure:

  • Run whether user is logged on or not
  • Run with highest privileges

Of course the script must be run with an account that has right to do its purpose. In this example, the account should have Exchange View Administrator, and member of the local administrators group on each Exchange server to collect WMI data.

How to run PowerShell Scripts efficiently from Task Scheduler18

Get Software Update Size from Configuration Manager 20120/R2 [Report/PowerShell]

Hi everyone,

I worked in an environment where distribution points are centralized and we had to plan our software update distribution via config manager 2012/R2 very carefuly, by making sure that a Software Update Group will not have over 300 MB of content.

If you browse the Config Manager Software Updates node, you can filter and sort the updates that you are interested in, but you cannot see the Size of those updates right away. You have to go to each update, check its properties, and see what content files are part of that specific update, and then calculate the size.

I created a SQL query that can filter updates by a time frame, and will report back the list of output updates along with the size of each update, so you can easily plan your Software Update Group membership.

This blog post is moved to my new blog platform

Go here please to continue reading:

Backup Certificate Authority PowerShell Script

Hi everyone,

As it is so important to backup your Certification Authority servers, automating this task is a bonus thing here.

As you may already know, you need to backup usually the following:

  • CA Private Key.
  • CA Database Files.
  • Configuration in the registry.
  • Perhaps the CAPolicy.inf file if any.

I was browsing the internet and i have found a brilliant PowerShell script that will do the trick in a professional way indeed.

The script is written by a PKI geek and you can download his PowerShell Script here. [ I guess it is relocated here: ]

The script will backup all the previous files in a nice way. I have tested the integrity of the script by trying to restore a CA from the backed up files, and everything was working fine.


Now, you can create a scheduled task, with :

Program/script: %SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe

Add Arguments(optional) : type the path of your PowerShell Script, for example C:\Backup_CA.PS1

Finally, make sure it is running as a SYSTEM security context.


Symantec BackupExec PowerShell Job Report

Script Info

This script will connect to Symantec BackupExec server and using PowerShell, it will collect the following  information about the backup jobs:

  • Job Name
  • Selection Summary
  • Storage
  • Start Time
  • Elapsed Time
  • Job Status
  • Media Label
  • Total Data Size Bytes
  • Job Rate MB Per Minute

Moving to a New Blog Platform

This post is now moved to my new blog platform at To continue reading this blog post, please click here

Import from PST to online archive ? You need PowerShell Statistics Report



Well, say you have a project to import PST files to online archives in your Exchange environment. You start importing PST files to online archives one by one, and you need a way to monitor the progress and see which servers are doing the move and how long will it take to finish the current import operations. At the end, you will receive a nicely formatted HTML table to your email address.




Items Reported

This script will gather information about all mailbox import operations that are in progress (status = InProgress) and will report the following:

– User Name
– User SamAccount Name
– Office
– Targeet Database
– Percent Complete
– Queued Time Stamp
– Start Time Stamp
– Last Update Time Stamp
– Overall Duration
– Bytes Transferred
– Target Root Folder
– CAS Server doing the import process


Download the script now

You can download the script here : Get-CorpMailboxImport


Generate the HTML report with SMTP Email option
\Get-CorpMailboxImport.ps1   -MailFrom   -MailTo   -MailServer

Charts and PowerShell !! PowerShell Wrapper ‚ÄúFull Edition‚ÄĚ

I think one of the most interesting things when writing PowerShell scripts, is to make the results appear in a nice chart or couple of them. If you are going to show disk space info, nothing more than a nice chart will worth looking at. If you are sending a report to your management, nothing will take their attention more than charts. I love getting charts as  a high level output, with more tables and data for extra details.

Moving to a New Blog Platform

This post is now moved to my new blog platform at To continue reading this blog post, please click here

Exchange Dashboard Organization – “Email Report” PowerShell Script

Get-CorpEmailReport.ps1 V2.4.8  is an Email Organization Report and a great tool for IT Professionals who are working with Microsoft Exchange systems.

This report will get organization wide information about your Email infrastructure, from Exchange servers O.S info, service health, up time details, beside Exchange and database highly aggregated information.

Not only will you get a nice Dashboard describing your Exchange, you will get aggregated information about how much resources your email infrastructure is consuming in terms of server count, mailboxes and total storage.

Script Charts

What makes this script unique and outstanding, is the chart module. Nothing more exciting than parsing the output data in sorted nice looking charts.

Four charts will be generated after running the script. Each chart is designed carefully to get the information that matters most to IT Professional.

Chart Module uses a smart algorithm that will get information from your Exchange organization, digest it, and then decide what is the best way to output the chart depending on the number of data items. In this way, you will not get small crowded charts with hard to read data, instead, the graph dimensions will be scaled dynamically according to the number of items to draw.

DB vs Backup Since

DB vs Size

MBX vs DBs Mounted

MBX vs Mailboxes

DB Activation Preference Table [New]

Have you used the product team Exchange calculator where you input your data and a nice formatted table get generated for all your databases, and their copy distribution along with the activation preference?

The script is intelligent enough to collect your DAG information, database copy locations, their activation preference, and then generate a similar live dashboard with a colored cell indicating a red alarm if a database is mounted on a non-preferred mailbox server.

DB Activation

Aggregated Data

The script will also present a nicely formatted tables with aggregated information like the mailboxes per type, Exchange servers per role and version, and also mailbox and archives count, sizes and average size.

HTML Table

Scope your script with three Filters [New]

The script ships with a new module to handle filtering and scoping your script. Sometimes, you want to get a report for certain DAGs only, so you can use the DAG filter.

Maybe you want to just get information from certain Exchange servers, so you can use the Server List filter and just write a comma separated list of servers.

Finally, you can use the Expression filter, where you can use the wildcard character (*) to write something like “NL*” to get information from server names started with NL.


Detailed information for Servers and Databases

 Of course the script will not be perfect if a detailed information is not presented. The script will start creating HTML tables for all your Exchange servers with all detailed information, including version, OS Info, Roll Up Updates version, Service health, Up Time information and more.

Also, for each database, you will get a detailed information about the database and all its properties with smart thresholds that you can customize.


Easy Script Code Browsing

The script code is divided to 7 modules to make it easy for you to dig deeply into the script code and reach the functionality you are looking for. The script also uses (Regions), so if you are using new PowerShell script editor, you will be able to expand each script region separately for better script browsing



Download the Full Documentation

Download the script Documentation here:

Email Organization Report v2.4.8 Description

Download the Script here

Download the script from here : Get-CorpEmailReport_V2.4.8

Examples to run the script

Generate the HTML report and supplying the current directry as a script path to create output files
.\Get-CorpEmailReport.ps1 -ScriptFilesPath .\

Generate the HTML report and supplying the custom directory as a script path to create output files
.\Get-CorpEmailReport.ps1 -ScriptFilesPath C:\MyFiles

Generate the HTML report and Filter by servers that start with “NL”
.\Get-CorpEmailReport.ps1 -ScriptFilesPath .\¬† ¬†-ServerFilter “NL*”

Generate the HTML report and Filter by including only Ex1 and Ex2 servers
.\Get-CorpEmailReport.ps1 -ScriptFilesPath  .\  -OnlyIncludedServers  Ex1,Ex2

Generate the HTML report and Filter by including only Servers that are member of a DAG called “DAG1”
.\Get-CorpEmailReport.ps1 -ScriptFilesPath .\  -InputDAGs  DAG1

Generate the HTML report and use PowerShell Remoting for WMI data collection
\Get-CorpEmailReport.ps1 -ScriptFilesPath .\  -WMIRemoting

Generate the HTML report with SMTP Email option
\Get-CorpEmailReport.ps1 -ScriptFilesPath .\  -SendMail:$true -MailFrom -MailTo -MailServer

Generate the HTML report with disabling ViewEntireForest option
\Get-CorpEmailReport.ps1 -ScriptFilesPath .\  -ViewEntireForest:$false

How to schedule it?

If you want this script to be run as a schedule task, check my post here showing step by step walk through.


Here some recommendations:

– to learn more about how to schedule the script using task manager, check this post.

РStart running the script with -OnlyIncludedServers switch at first, so you can scope the script to one Exchange Server at first (.\Get-CorpEmailReport.ps1 -ScriptFilesPath  .\  -OnlyIncludedServers  Ex1 )

– Run the script from Exchange Management Console and from an Exchange Server.

– Best condition to run the script is from PowerShell 3.0 and above. Click here for a nice blog post about this.

– If you have an Exchange Edge Servers and want to the script to access it, see this link.

– If you want to use the SendEmail feature and your SMTP server requires authentication, then locate line 2110:

 $smtp = new-object Net.Mail.SmtpClient($smtphost)

and add the following lines below it:

$user = “contoso\username”
$passw = “mypassword”
$smtp.Credentials = New-Object System.Net.NetworkCredential($user,$passw);
$smtp.EnableSsl = $false

Copy Rights

The script is based on Steve Goodman, a Microsoft MVP at the time of writing this document. Steve Script: ‚ÄúGenerate Exchange Environment Reports using PowerShell‚ÄĚ Version 1.5.8, 2nd Feb 2014, is used heavily as a foundation to build this script.

Steve worked hardly in writing an outstanding script to collect information from all versions of Exchange servers, and get a nicely formatted output. This script extends Steve’s script functionality by adding a lot of functionalities like: new on screen progress, charts, HTML DB Activation output, PowerShell Remoting module, Log files tracking, more aggregated data, new table for Mailbox type, new table for Recovery Databases, threshold options, two new Filter functionality (by DAG names, and by Server names), new module for error handling and logging, more data collected for Exchange and Databases.

A separate communication with Steve has been done to ensure copy rights before releasing this extended script. I encourage you to visit his blog and browse his script: 

Similar Scripts