Lists in SCSM can sometimes be pretty dynamic. As an example it may be the case that you want to publish job titles for an employee joiner offering to the end users. Job titles may change over time and if your company is using kind of an external system to manage these titles together with other employee data, it would be nice to have your list updated dynamically right?

Besides many other commands, SMLets module provides you also with the following cmdlets to manage enumerations.

With these cmdlets in mind it is pretty straight forward to add new and remove existing enumerations. The only challenge when adding new enums can be the definition of the appropriate ordinal. Easiest way of solving this is to always add new enums to the very bottom of an existing list. With this approach, one only has to get the largest ordinal of all child enums and increment this for the newly added one.

Below you can find a script which gets job titles from a MySQL database and creates an enumeration for each of the received titles if not already existing in Service Manager. Furthermore the script checks whether a enum does not anymore exist in MySQL database and removes it from Service Manager if applicable. Be aware that this would have impact on CIs or WorkItems where the enum to be removed is selected.

For the MySQL part, Thomas Maurer wrote a great post about querying MySQL with PowerShell. I recommend reading his post if you need more details about this step. When querying MS SQL databases one could use Invoke-Sqlcmd cmdlet instead.

The script is available on my Service Manager GitHub repository. Please feel free to get it from there and contribute to the project. If you have any suggestions/issues concerning the script, please report them on the corresponding GitHub issue page or even better implement changes by yourself and push back to the repository so that others can benefit from your ideas as well :-)

PowerShell
Edit|Remove
<# 
 
.SYNOPSIS 
This script updates Service Manager enums based on a MySQL query. 
 
.DESCRIPTION 
The script establishes a connection to a MySQL database and updates a defined enumeration list according to the defined MySQL query 
 
.EXAMPLE 
.\Update-Enums.ps1 -Verbose 
 
.NOTES 
The user which runs the script needs to be part of the "Author" role to update enum lists. Also the script needs to be executed on a 
Service Manager management server where MySQL Connector/Net ADO.NET driver and SMLets are installed (see http://dev.mysql.com/downloads/connector/net/) 
 
.LINK 
http://jhnr.ch 
 
#> 
 
[cmdletbinding()] 
 
# Setup the script to stop on error 
$ErrorActionPreference = "Stop" 
 
#Define MySQL query to get job titles 
$MySqlQuery = "select title from jobtitles where deleted = 0" 
 
#Define MySQL credentials and connection string 
$MySQLUserName = 'mysqluser' 
$MySQLPassword = 'p@ssword' 
$MySQLDatabase = 'databasename' 
$MySQLHost = 'mysql.scsmlab.com' 
$ConnectionString = "server=" + $MySQLHost + "; port=3306; uid=" + $MySQLUserName + "; pwd=" + $MySQLPassword + "; database="+$MySQLDatabase 
 
try 
{ 
    #Used to invoke MySQL query 
    Function Invoke-MySQL { 
        Param 
            ( 
            [Parameter(Mandatory = $true,ParameterSetName = '',ValueFromPipeline = $true)][string]$Query 
            ) 
 
        #Load assembly. MySQL Connector/Net ADO.NET driver has to be installed http://dev.mysql.com/downloads/connector/net/ 
        [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data") 
 
        #Create new connection by using previousely defined connection string 
        $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection 
        $Connection.ConnectionString = $ConnectionString 
        $Connection.Open() 
 
        #Excute query and close connection 
        $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query$Connection) 
        $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command) 
        $DataSet = New-Object System.Data.DataSet 
        $RecordCount = $dataAdapter.Fill($dataSet"data") 
        $Connection.Close() 
 
        #Return query results 
        Return $DataSet.Tables[0] 
    } 
 
    #Get Job Titles from Tippps MySQL database 
    $Result = Invoke-MySQL -Query $MySqlQuery 
    Write-Verbose "Retreived data from MySQL database ($($Result.Rows.Count) rows)" 
 
    #Import SMLets 
    Import-Module smlets 
 
    #Get Management Pack to save enumerations and appropriate parent enum 
    $EnumManagementPack = Get-SCSMManagementPack -Name ^tph.listelements.library$ 
    $ParentEnum = Get-SCSMEnumeration -Name ^tphenumemploymentstatus$ 
 
    #Get child enumsn using Get-SCSMChildEnumeration which is much faster 
    #PS C:\> (Measure-Command {Get-SCSMChildEnumeration -Enumeration $ParentEnum}).TotalMilliseconds 
    #1,3816 
    #PS C:\> (Measure-Command {(Get-SCSMEnumeration | where {$_.Parent -match $($ParentEnum.Id)})}).TotalMilliseconds 
    #242,5261 
    $ChildEnums = Get-SCSMChildEnumeration -Enumeration $ParentEnum 
 
    #Add new titles to enum list if not already in enum list 
    Foreach($Title in $Result.Title) { 
        if($($ChildEnums.DisplayName) -notcontains $Title) { 
 
            #Get highest ordinal value using Measure-Object. Seems to be much faster than sorting an getting last objects ordinal value 
            #PS C:\> (Measure-Command {($ChildEnums | Measure-Object -Property Ordinal -Maximum).Maximum}).TotalMilliseconds 
            #0,892 
            #PS C:\> (Measure-Command {($ChildEnums | Sort-Object Ordinal | select -Last 1).Ordinal}).TotalMilliseconds 
            #4,0093 
            $Ordinal = (($ChildEnums | Measure-Object -Property Ordinal -Maximum).Maximum) + 1 
 
            #Create child enum internal name (convert to lower case and remove spaces) 
            $ChildEnumName = "$($ParentEnum.Name)$Title".ToLower() -replace '\s','' 
 
            #Add new child enum as last element in list (increment hightest ordinal) 
            Add-SCSMEnumeration -Parent $ParentEnum -Name $ChildEnumName -DisplayName $Title -Ordinal $Ordinal -ManagementPack $EnumManagementPack 
            Write-Verbose "Added $Title to Enum List" 
        } 
    } 
 
    #Remove obsolete titles from enum list if not anymore in database 
    Foreach($ChildEnum in $ChildEnums) { 
        if($($Result.Title) -notcontains $($ChildEnum.DisplayName)) { 
            Remove-SCSMEnumeration -Enumeration $ChildEnum 
            Write-Verbose "Removed $($ChildEnum.DisplayName) from Enum List" 
        } 
    } 
 
    # Remove smlets 
    Remove-module smlets -force 
} 
 
catch { 
 
    Throw "@ 
 
    $error[0] 
 
    @" 
 
}