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.