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
#$data.request
#subsequent items 
#$data.request.subject

#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?
#questions/issues: 
#
#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.

the outageboard

A few months ago I whipped up (I say “whipped”, it took about 16 hours) an outage board style tool to capture some information about current or impending (ie, unscheduled or scheduled) outages. Unfortunately my zero UX skills and weak attempt at programming has resulted in something that is visually pleasing in neither the front nor backend but, granted, which does function as a basic outage board.

This badboy is written in Visual Studio 2017, C# MVC and HTML + Javascript. It basically uses the StreamWriter class to write to a CSV file when you add an outage, and then uses StreamReader to populate the table with the contents of that CSV. This done properly would connect to a database rather than a flat text file but here we are.

This could easily be adapted to any other use for sharing communal or department-wide information, eg. Listing invoices that have come in and what actions have been taken against them – just rename the table headings to something like Invoice ID, Date Received, Amount, Due Date, Forwarded To, etc.

This is the dazzling frontend:

Captures basic info and lets you delete an outage based on the Outage Ref.
The outage reference is generated by using the first 6 characters of a GUID generated in the C#.

To match the Outage Ref string and delete the line/data associated with it, I pulled (copied) basically the entire class from an answer from a lad named Håvard Fjær on this StackOverflow topic:

https://stackoverflow.com/questions/668907/how-to-delete-a-line-from-a-text-file-in-c

If you click the Add Advisory button you get this cute little form where you can specify the details of the outage you want to add – and get this, it will PRE-POPULATE the current time and date in there for you! If that’s not a consumer-focused enhancement, I don’t know what is!

The actual C# operating in the background is revolting, and the CSS to format these somewhat decent looking (if i say so myself) buttons is sloppy as *redacted*, but it’s tight, it works, that’s all I need.

Also if you put a comer in the ETR or Outage Details it will throw another random column in the table for you – sweet bug but it’s mine so I’ll keep it.

Odds that i’ll ever improve this to be something viable enough to live outside of my own code graveyard are slim to none, especially given we don’t even use it in the workplace I built it for. But it could be useful one day (like if there was no outage-board solution at all) so I wont shift-delete the project just yet.

If anyone wants the source I’ll put it somewhere, but I strongly urge you to look elsewhere for a quality solution first. If you end up actually making use of it, beyond as a submission to Coding Horror or a cautionary tale for new programmers, ill be very surprised, but also pleased…..no pleased isn’t a strong enough word. I’ll find it pleasurable.

The IIS_IUSRS account needs read/write/create on the C:\inetpub\wwwroot directory to create the CSV and write to it.

I had no idea what I was doing when I created it, so this project is version controlled in Azure Devops using TFS.

Would rather git but I couldn’t easily convert it and it’s fair to say I still don’t know what I’m doing.