exporting out of SCSM – clawing back incident and request information

It actually wasn’t difficult at all, after 2 hours of googling and learning what it was I was actually looking for. System Center Service Manager is a beast that can do a lot, particularly with a finely tuned frontend (which I’ll refer to as it’s ‘skirt’ in this post) glued to it’s nether.

Ironic that clippy should be suggesting this. But Microsoft open sourced calc.exe the other day and even run Linux natively now so...who knows...

The flipside is the cost of maintaining the skirt and customisations. I can passably modify or write HTML and Javascript to produce some things of use but from my brief view of SCSM (and it’s Cireson skirt) making a simple change requires full webdev abilities far beyond a service desk normie like myself. Suffice it to say the cost of getting a contractor in just to add a field to a request form (or say, a pocket to a skirt) is not worth it for a smaller enterprise. It’s almost a full time position improving and modifying Cireson for a frequently changing environment.

I can see on the scale of thousands of employees, it would be worth it for the time-savings brought on by the automation it can offer. Or even if the project to put it in started with meticulously gathered and extremely well defined requirements, such that Cireson themselves could sew that whole skirt for you on the spot without requiring any additional measurements or improvements. But in the vein of continuous improvement brought on by frequently changing use cases, it looks (to me, don’t sue me) to be an extremely expensive pursuit for something easily covered by a one-size-fits-all moomoo. It’s also (IMO) heaps expensive.

But anyway, what do I know.

Here’s how to export your incidents out of SCSM to a CSV.

  1. Make sure you have SMLets installed on the SCSM server.
  2. Open up a Powershell window on the SCSM server.
  3. Run these lines, modifying the file path to where you want your .CSV to come out:

import-module SMLets

get-scsmincident | Select-Object -Property ID, Status, Title, @{l='Description';e={$_.Description -replace "`n"," "}}, AffectedUser, AssignedTo, CreatedDate, TierQueue, Urgency, Priority | export-csv C:\temp\SCSM_Incidents.csv -nti

Super easy. You might be saying, gross, what’s that part in the middle with the Description property? Well most Incident descriptions will contain a carriage return which export-csv identifies as a delimiter, chopping off the rest of the description after the first carriage return.

With @{l=’Description’;e={$_.Description -replace “`n”,” “}} you will replace all carriage returns in the description with spaces, retaining all that preciously descriptive information. I could describe my lawn in the request, in perfect detail, and you’ll get it.

You can see properties I’ve opted to keep in the select-object -properties. If you want to see all the available fields, just pipe get-scsmincident straight to export-csv and have a look at the resulting file – then you can edit the command above to include the extra columns you want.

Exporting all the Service Requests was slightly harder because the affected user doesn’t seem to be stored in the Service Requests class – it strikes me as painfully obvious that it should be but I reiterate: what do I know? For some reason you have to get that information from the Relationship class and then the way I joined them was to stick them in Excel sheets and do a VLOOKUP to cross-reference the request ID to the Affected User.

Here’s how to export your service requests out of SCSM to a CSV.

  1. Make sure you have SMLets installed on the SCSM server.
  2. Open up a Powershell window on the SCSM server.
  3. Run these commands:
import-module SMLets

$SRClass = Get-SCSMClass System.WorkItem.ServiceRequest$

Get-SCSMObject -Class $SRClass | Select-Object -Property ID, Status, Urgency, Priority, Title, @{l='Description';e={$_.Description -replace "`n"," "}}, Notes, CreatedDate, SupportGroup | export-csv C:\temp\SCSM_Service_Requests.csv -nti

$Relclass = Get-SCSMRelationshipClass System.WorkItemAffectedUser

Get-SCSMRelationshipObject $Relclass | Export-Csv C:\temp\SCSCM_SR_Affected_User.csv -nti

You will see that this gives you two files which you can now open up and do a VLOOKUP on. There are other values you can get this way – one such useful one which I did not get (but you could!) is the System.WorkItemAssignedToUser which will tell you which technician it has been assigned to.

You could grab this by substituting System.WorkItemAssignedToUser in the place of System.WorkItemAffectedUser in the lines above. Obviously then you’d have yet another CSV to do another VLOOKUP on, but it should work. And more importantly, I couldn’t find an easier way.

sdp powershell automation – at least that was the intent

Manage Engine ServiceDeskPlus has an option to call cmd.exe (and by extension any command line tools from there) which you can use to crank out some automation from a submitted service request.

If you open up SDP Admin and head on over to Custom Triggers, or I think Business Rules, you can set up an action to this effect:

cmd /c powershell.exe -WindowStyle Hidden -file D:\ManageEngine\ServiceDesk\integration\custom_scripts\test_add_distribution_group_member.ps1 "$COMPLETE_JSON_FILE"

Should be obvious that this runs Powershell in a hidden windows and hands it $COMPLETE_JSON_FILE which is a capture in JSON format of the available fields (including any custom ones you’ve added) which are created as part of a request in SDP. To funnel the JSON into the Powershell script your .ps1 file needs to open with the following (ie. this should be the very first line in the script):

param ( 
[string]$json = "none" 

To get this into a useable Powershell object (because objects are the whole point of Powershell) you want to pipe it into a new object thusly:

$data = Get-Content -Raw $json | ConvertFrom-Json

Now you’ve got yourself a friendly old buddy called $data containing all of the properties of the request. You can access the properties with $data.request.name or $data.request.subject, $data.request.customfield, etc.

I’ve tried to automate adding someone to a distribution group by installing Exchange Management Tools on the server and calling the cmdlet for updating a distro group per below:

$name = $data.request.login_name
$name = $name + '@domain.com.au'

#and what distribution group they want to be added to ('Distribution Group' is a custom field added to the request form)
$dgroup = $data.request.'Distribution Group'

#import exchange tools (have to install Exchange Management Tools on the server
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.SnapIn

#add to the group
Add-DistributionGroupMember -Identity $dgroup -Member $name

Unfortunately this doesn’t work completely yet, it throws a permissions error – I think it’s because the user running Powershell is the ServiceDeskPlus service account, which doesn’t have the appropriate privileges. Still working on it, but it looks like it should work once that’s sorted.

When I googled the error thrown by Powershell initially it led me to go into AD and allow the Exchange Trusted Subsystem to have modify permissions on all objects – I thought that would fix it at first but alas! It did not.

I’ve also tried to automate updating AD photos – that’s not working either but by George i’ll keep trying until it all goes down the same hole.

Here’s where I’m at with that one:

#paste this pipe to an actual file to see what JSON youre receiving
#| Out-File "D:\ManageEngine\ServiceDesk\integration\custom_scripts\SDP_test_data_AD.json"

####start actual script

#receive a parameter, should be a JSON file from SDP ($COMPLETE_JSON_FILE)
param (
    [string]$json = "none"


#get it and turn it into a powershell object #put -Raw back in get-content and the convert from right after the get-content
$data = Get-Content -Raw $json | ConvertFrom-Json

#can print the request object info with
#subsequent items 

#images pasted into a submitted request go to  /inlineimages/WorkOrder/*REQUESTID*/*UNIXTIMESTAMPINMILLISECONDS*.png
#could try if theyre attached they go to /fileAttachments/request/*MONTHYEAR*/*REQUESTID*/*ATTACHMENT_NAME*.EXT
#think its easier to get the images if they were pasted into the request rather than attached

#could specify the path to the photo like this 
$requestID = $data.request.workorderid
$photoPath = "D:\ManageEngine\ServiceDesk\inlineimages\WorkOrder\" + $requestID + "\*"

#then get the actual file like this
#there should only be one file in this directory, looking for either a png or a jpg
$photoFile = Get-ChildItem -Path $photopath -Include *.jpg,*.png

#then do this part to get the image as a byte file or something, i dont know
$photo = [byte[]](Get-Content $photoFile -Encoding byte)

#then get the user
$username = $data.request.login_name

#then set their profile photo to the one we just grabbed
############ NEED TO IMPORT-MODULE ACTIVEDIRECTORY before this will work
############ the module isnt available in get-module -listavailable
Import-Module ActiveDirectory
Set-ADUser $username -Replace @{thumbnailPhoto=$photo}

#need AD computers and users module maybe?
#if we set it in AD does it then push out to the rest (exchange, skype, sharepoint)
#maximum file size is 100kb and 96x96 - what happens if it's too large?
#could use this script to resize it? https://gallery.technet.microsoft.com/scriptcenter/Resize-Image-A-PowerShell-3d26ef68

Again it looks alright to me, but this also throws permissions errors – need to look at permissions for the SDP service account, or else try run the Powershell as another, privileged, user/service account.