SIMS PowerShell CommandReporter Wrapper (including photo export)

[psynegy]

We've been doing PowerShell SIMS extraction for a little while, but never got round to making image extraction work until now. (farewell SalamanderSoft!)

 

Here's a little script to help get anyone started with using SIMS data in PowerShell:

 

[color=#333333][font=Consolas][color=#0000ff]Clear-Host[/color]

[color=#ff4500]$SIMSDirectory[/color] [color=#a9a9a9]=[/color] [color=#000000]'[/color][color=#8b0000]C:\Program Files (x86)\SIMS\SIMS .net[/color][color=#000000]'[/color]
[color=#ff4500]$SIMSServer[/color] [color=#a9a9a9]=[/color] [color=#000000]'[/color][color=#8b0000]SIMS\SIMS2016[/color][color=#000000]'[/color]
[color=#ff4500]$SIMSDatabase[/color] [color=#a9a9a9]=[/color] [color=#000000]'[/color][color=#8b0000]SIMS[/color][color=#000000]'[/color]

[color=#006400][i]# Set the console encoding to UTF-8[/i][/color]
[color=#006400][i]# This is the encoding that SIMS uses in it's reports, and we must honour it, otherwise special characters in names will become b0rked.[/i][/color]
[color=#ff4500]$[/color][color=#008080]null[/color] [color=#a9a9a9]=[/color] cmd [color=#a9a9a9]/[/color]c [color=#000000]''[/color] [color=#006400][i]# This is a fix for the ISE. [Console] doesn't get initialised properly until an executable command is run - stupid.[/i][/color]
[color=#000000][[/color][color=#00008b]Console[/color][color=#000000]][/color]::OutputEncoding [color=#a9a9a9]=[/color] [color=#000000][[/color][color=#00008b]System.Text.Encoding[/color][color=#000000]][/color]::UTF8

[color=#006400][i]# Get SIMS Data[/i][/color]
[color=#0000ff]Write-Output[/color] [color=#000000]"[/color][color=#8b0000]Running Staff SIMS Report[/color][color=#000000]"[/color]
[color=#000000][[/color][color=#00008b]xml[/color][color=#000000]][/color][color=#ff4500]$SIMSXML[/color] [color=#a9a9a9]=[/color] [color=#000000]([/color][color=#a9a9a9].[/color] [color=#000000]"[/color][color=#ff4500]$SIMSDirectory[/color][color=#8b0000]\CommandReporter.exe[/color][color=#000000]"[/color] [color=#a9a9a9]/[/color]QUIET [color=#a9a9a9]/[/color]TRUSTED [color=#a9a9a9]/[/color]SERVERNAME:[color=#000000]"[/color][color=#ff4500]$SIMSServer[/color][color=#000000]"[/color] [color=#a9a9a9]/[/color]DATABASENAME:[color=#000000]"[/color][color=#ff4500]$SIMSDatabase[/color][color=#000000]"[/color] [color=#a9a9a9]/[/color]REPORT:[color=#000000]"[/color][color=#8b0000]SIMS Powershell Export Staff[/color][color=#000000]"[/color][color=#000000])[/color]

[color=#006400][i]# Check for errors running the report[/i][/color]
[color=#00008b]If[/color][color=#000000]([/color][color=#ff4500]$SIMSXML.CommandReporterError[/color][color=#000000])[/color] [color=#000000]{[/color]
   [color=#0000ff]Write-Warning[/color] [color=#000000]$([/color][color=#ff4500]$SIMSXML.CommandReporterError[/color][color=#000000])[/color]
   [color=#00008b]Exit[/color]
[color=#000000]}[/color]

[color=#006400][i]# Example export photos by name[/i][/color]
[color=#00008b]ForEach[/color][color=#000000]([/color][color=#ff4500]$Record[/color] [color=#00008b]in[/color] [color=#ff4500]$SIMSXML.SuperStarReport.Record[/color][color=#000000])[/color] [color=#000000]{[/color]
   [color=#006400][i]# Check that the record has a photo[/i][/color]
   [color=#00008b]If[/color][color=#000000]([/color][color=#ff4500]$[/color][color=#008080]null[/color] [color=#a9a9a9]-ne[/color] [color=#ff4500]$Record.Photo[/color][color=#000000])[/color] [color=#000000]{[/color]
       [color=#006400][i]# Convert image from Base64 to byte array[/i][/color]
       [color=#ff4500]$ImageDecoded[/color] [color=#a9a9a9]=[/color] [color=#000000][[/color][color=#00008b]System.Convert[/color][color=#000000]][/color]::FromBase64CharArray[color=#000000]([/color][color=#ff4500]$Record.Photo[/color][color=#a9a9a9],[/color] [color=#800080]0[/color][color=#a9a9a9],[/color] [color=#ff4500]$Record.Photo.Length[/color][color=#000000])[/color]

       [color=#006400][i]# Check first 2 bytes of image for a valid image header, and determine image type[/i][/color]
       [color=#00008b]Switch[/color][color=#000000]([[/color][color=#00008b]System.BitConverter[/color][color=#000000]][/color]::ToString[color=#000000]([/color][color=#ff4500]$ImageDecoded[/color][color=#000000][[/color][color=#800080]0[/color][color=#a9a9a9]..[/color][color=#800080]1[/color][color=#000000]]))[/color] [color=#000000]{[/color]
           [color=#000000]"[/color][color=#8b0000]89-50[/color][color=#000000]"[/color] [color=#000000]{[/color] [color=#ff4500]$FileExtension[/color] [color=#a9a9a9]=[/color] [color=#000000]'[/color][color=#8b0000]png[/color][color=#000000]'[/color] [color=#000000]}[/color]
           [color=#000000]"[/color][color=#8b0000]FF-D8[/color][color=#000000]"[/color] [color=#000000]{[/color] [color=#ff4500]$FileExtension[/color] [color=#a9a9a9]=[/color] [color=#000000]'[/color][color=#8b0000]jpg[/color][color=#000000]'[/color] [color=#000000]}[/color]
           [color=#000000]"[/color][color=#8b0000]42-4D[/color][color=#000000]"[/color] [color=#000000]{[/color] [color=#ff4500]$FileExtension[/color] [color=#a9a9a9]=[/color] [color=#000000]'[/color][color=#8b0000]bmp[/color][color=#000000]'[/color] [color=#000000]}[/color]
           [color=#00008b]Default[/color] [color=#000000]{[/color] [color=#ff4500]$FileExtension[/color] [color=#a9a9a9]=[/color] [color=#ff4500]$[/color][color=#008080]False[/color] [color=#000000]}[/color]
       [color=#000000]}[/color]

       [color=#00008b]If[/color][color=#000000]([/color][color=#ff4500]$FileExtension[/color][color=#000000])[/color] [color=#000000]{[/color]
           [color=#006400][i]# Output image[/i][/color]
           [color=#0000ff]Set-Content[/color] [color=#a9a9a9]-[/color]Path [color=#000000]"[/color][color=#000000]$([/color][color=#ff4500]$Record.Forename[/color][color=#000000])[/color][color=#000000]$([/color][color=#ff4500]$Record.Surname[/color][color=#000000])[/color][color=#8b0000].[/color][color=#000000]$([/color][color=#ff4500]$FileExtension[/color][color=#000000])[/color][color=#000000]"[/color] [color=#a9a9a9]-[/color]Value [color=#ff4500]$ImageDecoded[/color] [color=#a9a9a9]-[/color]AsByteStream
       [color=#000000]}[/color]
   [color=#000000]}[/color]
[color=#000000]}[/color]

[color=#006400][i]# Example export to CSV[/i][/color]
[/font][/color]
[color=#FF4500][font=Consolas]$SIMSXML.SuperStarReport.Record[/font][/color][color=#A9A9A9][font=Consolas]|[/font][/color][color=#0000FF][font=Consolas]Select-Object[/font][/color][color=#333333][font=Consolas] Forename[/font][/color][color=#A9A9A9][font=Consolas],[/font][/color][color=#333333][font=Consolas] Surname [/font][/color][color=#A9A9A9][font=Consolas]|[/font][/color][color=#0000FF][font=Consolas]Export-Csv[/font][/color][color=#333333][font=Consolas] Names.csv
[/font][/color]

 

Screenshot of SIMS report:

 

With the data in XML object format, it's super easy to work with. We used to do export to CSV, then read the CSV, but this is much cleaner.

 

Hope this helps someone!

Edited December 8, 2022 by psynegy