Recently a customer decided to start using System Center Orchestrator to automate some of the recurring tasks with regards to the management and support of their Exchange organization. More specifically, they wanted to create a custom interface to which the support organization would get access to in order to request specific actions like mailbox moves, message tracking logs etc. This would allow them to execute some (basic) tasks themselves without needing any Exchange permissions or being forced to use PowerShell or the Exchange Management Console.
In fact, Orchestrator is very powerful when it comes down to automating things like this. The Integration Packs for products like Exchange, Active Directory and SharePoint allow you to easily develop such solutions.
What is described below are merely some thoughts I had while building the solution along with a colleague of mine. I’m in no way an expert in the use of Orchestrator, but that’s why I thought this information might be interesting to you.
Before diving into the quirks which may (or may not) be “by design”, let’s have a look at what we were trying to build.
The solution
The customer wanted to have a custom interface (not specified what it needed to be) which would allow members of the Service Desk to request message tracking information without the need for an Exchange administrator to intervene. After some debates on whether or not PowerShell should be leveraged to develop such a GUI, we decided to move forward using SharePoint lists. SharePoint lists are easy to setup and use and because it was already heavily used within this organization, it seemed like the logical step.
In order not to complicate things, we decided that we would ask the support engineer for the following information:
- Sender Email Address
- Recipient Email Address
- Date + Time (hour)
To enter the information into the SharePoint list, a custom form was created which also took care of data validation. As such, the engineer would be able to enter only a sender address, a recipient address or both. The date + time are always required and needed to be in a specific format (yyyy-mm-dd). The reason why we chose to do data validation in the form is two-fold. First, it’s extremely easy to do and secondly, it would save us quite a bit of code later on when building the solution in Orchestrator. The date + hour indication are used to indicate for what time the message tracking logs are requested. We found that 95% of all requests all were within a 24 hour timespan. That’s why the code we built (more about that later) would look in the message tracking logs 12 hours before and 12 hours after the indicated time.
Whenever an engineer would enter a new item in the list, it should automatically get processed by Orchestrator which would then fetch the information from the Message Tracking logs and write it to a CSV file which would be attached to the list item in SharePoint when finished processing.
All-in-all, the requirements were are pretty straight forward which should make the final solution relatively easy to build. In order to meet these requirements, we needed to following components in Orchestrator:
- SharePoint Integration Pack
- Exchange Integration Pack
- Active Directory Integration Pack
Below is a screenshot of the actual runbook which we built:
As you can see, it’s not the most advanced runbook (and maybe not the most efficient either). However, it does the trick. As you will notice, we did built in some logging (in order to have a history what happened) and also do some data validation using the built-in capabilities of Orchestrator.
The quirks
Monitor Item Lists activity
The first problem we came across was using the “Monitor List Items” activity. When first adding this activity to the runbook, it automatically detects all the fields in the list and allows you to use them throughout the runbook as published data from that activity:
The problem, though, is that – for some reason – it doesn’t pick up changes made to the list after the activity has been added to the runbook. When you have designed your SharePoint list to have all the necessary fields from the get-go, this probably isn’t much of an issue. In our case, unfortunately, the customer tends to changes his mind once in a while…
No matter what we tried, newly added fields would not show up in the published data for that activity. The only workaround is to remove the activity and add it again. While this might work for very simple workflows, in larger workflows – like the one above – dealing with this is particularly painful. Especially because the published data from the activity is used in multiple places. This means that when you remove/re-add the activity, you have to go in to all the other activity where you referenced the data and ‘reconfigure’ it. Again, this is not a big problem if you know this up front, but it can cost you some time if you decide to make changes to the SharePoint list. So one word of advice: plan carefully!
Exchange Management Shell code
The Exchange Integration Pack allows you to directly run a bunch of Exchange PowerShell commands when you add the Run Exchange Management Shell activity. Pretty easy, right?! Not really. As it turns out, not everything you can do in the Exchange Management Shell works in this activity.
For instance, in the Exchange Management Shell, the following code would work beautifully:
[code language=”powershell”]Get-TransportServer | Get-MessageTrackinglog[/code]
Bizarrely enough, this won’t work here. Instead, you have to break the pipeline as follows:
[code language=”powershell”]$servers = Get-TransportServer
$servers | Get-MessageTrackingLog[/code]
My best guess is that the runbook activity is already executing a pipeline which limits what you can do with it. Again, this isn’t a big problem. But it’s just something to keep in mind when developing code to be used in Orchestrator.
Here’s the code I (we) used:
[code language=”powershell”]#Defining Variables
$Subject = "{MT-Subject from "Monitor List Items"}"
$Sender = "{MT-Sender from "Monitor List Items"}"
$Recipient = "{MT-Recipient from "Monitor List Items"}"
$data = "{MT-Date from "Monitor List Items"}"
$Hour = "{MT-Hour from "Monitor List Items"}"
#Check Date Input
try{
[datetime]$inputdate = $date+" "+$Hour+":00:00"
[datetime]$startDate = $inputdate.AddHours(-12)
[datetime]$endDate = $inputdate.AddHours(+12)
}
catch{
$returnMsg = "Input date format invalid"
$returnMsg
exit
}
#Convert DateTimes back into usable format
$strStartDate = $startDate.ToString("yyyy/MM/dd HH:mm:ss")
$strEndDate = $endDate.ToString("yyyy/MM/dd HH:mm:ss")
#Construct cmdlet
$cmd += "Get-MessageTrackingLog -Server `$_.Name"
if($Subject -ne $null -and $Subject -ne ""){
$cmd += " -MessageSubject `"$Subject`""
}
if($Sender -ne $null -and $Sender -ne ""){
$cmd += " -Sender `"$Sender`""
}
if($Recipient -ne $null -and $Recipient -ne ""){
$cmd += " -Recipient `"$Recipient`""
}
$cmd += " -Start `"$strStartDate`""
$cmd += " -End `"$strEndDate`""
#workaround for Orchestrator limitation (limitation while executing pipeline)
$servers = Get-TransportServer
$result = $servers | %{
Invoke-Expression $cmd
}
$result | Select EventID,Source,Sender,Recipients,MessageSubject,MessageID[/code]
Exchange Management Shell code output
This is probably the biggest quirk we had to deal with. When you execute code within Orchestrator, the output from the code is then pushed to the next activity (provided that you subscribed to the data, of course). In this case, if multiple results came back from the Exchange Management Shell activity, it would fire the following activity for each result. To avoid this and to bundle the results and send them off to the next activity as a whole, you need to “flatten” the output first:
That was easy, wans’t it…? Well, that wasn’t really the problem either. When you take a closer look at the symbol used for separating the results, you’ll notice that we reverted to using a rather rarely used symbol. There’s a specific reason.
Here’s why: for some (stupid) reason, the results which are returned by the EMS runbook activity look like the following:
“[EventId: RECEIVE]
[Source: SMTP]
[Sender: member@linkedin.com]
[Recipients:email@domain.com]
[MessageSubject: email, please add me to your LinkedIn network]
[MessageId: somethinghere@somethingelse]
,[EventId: SEND]
[Source: SMTP]
[Sender: member@linkedin.com]
[Recipients: email@domain.com]
[MessageSubject: email, please add me to your LinkedIn network]
[MessageId: somethinghere@somethingelse]”
No this is not a weird way of display a hash table, it’s literally a large string which is passed on. This makes it incredibly difficult to deal with. In fact, there is no way that you can just push this into a CSV file and make something useful out of it without dealing with the output first.
A quick look on the internet, brought me to the following article: http://blog.coretech.dk/jgs/scorchestrator-2012-ps-function-for-parsing-the-result-of-the-run-exchange-management-shell-cmdlet-activity/
The author of that article suggested to parse the code and to convert the output into something usable. This seemed fair enough to me and I decided to re-use the code (although I needed to modify it to make it work in our case). This was then added to a second activity called “Convert Output” which is the “Run .NET Script” runbook activity. Here’s the code I used:
[code language=”powershell”]$inputText = "{Command Output for "Get MessageTracking Results"}"
if($inputText -ne ""){
$collection = $inputText.Split("¶")
$returnCollection = @()
foreach ($item in $collection)
{
$keypairCollection = $item.Split("`n")
$EventID = $keypairCollection[0].Trim().TrimStart("[").TrimEnd("]").TrimStart("EventId: ")
$Source = $keypairCollection[1].Trim().TrimStart("[").TrimEnd("]").TrimStart("Source:").TrimStart(" ")
$Sender = $keypairCollection[2].Trim().TrimStart("[").TrimEnd("]").TrimStart("Sender: ")
$Recipients = $keypairCollection[3].Trim().TrimStart("[").TrimEnd("]").TrimStart("Recipients: ")
$MessageSubject = $keypairCollection[4].Trim().TrimStart("[").TrimEnd("]").TrimStart("MessageSubject: ")
$MessageId = $keypairCollection[5].Trim().TrimStart("[").TrimEnd("]").TrimStart("MessageId: ")
$obj = New-Object -TypeName PSObject
Add-Member -InputObject $obj -MemberType NoteProperty -Name "EventID" -Value $EventID
Add-Member -InputObject $obj -MemberType NoteProperty -Name "Source" -Value $Source
Add-Member -InputObject $obj -MemberType NoteProperty -Name "Sender" -Value $Sender
Add-Member -InputObject $obj -MemberType NoteProperty -Name "Recipients" -Value $Recipients
Add-Member -InputObject $obj -MemberType NoteProperty -Name "MessageSubject" -Value $MessageSubject
Add-Member -InputObject $obj -MemberType NoteProperty -Name "MessageId" -Value $MessageId
$returnCollection += $obj
}
$output = $returnCollection | convertto-csv -delimiter ";"
}
else{
[string]$output = "No messages could be found with the specified parameters."
}[/code]
You might still wonder why I had to use the ¶ symbol as a delimeter for the output from the “Get MessageTracking Results” activity… The problem with parsing the code is that you have to split the individual results returned by the activity. Using a regular comma or semi-colon is out of the question as it’s quite likely that the subject for a message contains that same character which would cause the output conversion to fail (I found out the hard way). So we needed to decide on a symbol which allowed us to safely parse the code. Hence the ¶ symbol. For now, this does mean that if someone uses the ¶ symbol in the subject and it is returned by the message tracking logs, the above code will fail and there won’t be any output for the message tracking request.
If you have a better alternative: feel free to comment and let me know!
Some final thoughts
All in all, Orchestrator proves to be quite a flexible platform to perform tasks like the one described in this article. Especially the combination with SharePoint makes it very easy to build ‘custom’ tools which can be used by anyone without needing to provide them with permissions in Exchange (even though RBAC provides a nice framework to do just that). I only wish that some of the quirks got solved as it will remove the need for additional steps (and code) which only add complexity…
