How to populate object properties with data from a database using PowerShell

 

You probably know how you can use a registry discovery to populate properties on windows computer instances, but what if you already have the data in SQL database/CMDB and want to avoid having to first write the data to the registry and then use a discovery to get the data into SCOM? Or what if the objects are not windows computers and hence the registry method is not an option?

The solution is to use PowerShell to read  the data from the database and then update the objects in SCOM. This post will show you how to do this on distributed applications, but you can of course do on any object type in SCOM.

Here is a screenshot of the details of a distributed application (DA), where I use data from a SQL database (service description, business detailed description, Is business servers, owned by organization and availability schedule).

image

And here’s the script:

#Create connection to management group #
New-SCOMManagementGroupConnection SCMS1

### Update Distributed Applications (DA’) ###

#Get DA’s and values from database
$Services = Invoke-Sqlcmd -ServerInstance scsql -Database CMDBDev -Query “SELECT ServiceDisplayname,ServiceDescription,BusinessDetailedDescription,IsBusinessService,OwnedByOrganization,AvailabilitySchedule FROM [CMDBDev].[dbo].[Services]”

#Get SCOM System.Service class #
$class=get-scomclass -Name "System.Service"

#ForEach ($Item in $Services){Write-Host $Item} #

#Iterate through each DA instance within the hashtable #
ForEach ($service in $Services)

{
    #Get the service instances in SCOM #
 
    $instance=Get-SCOMClassInstance -Class $class | Where-Object {$_.DisplayName -eq $service[0]}
   
    #Logging
    if (!$instance)
    {
    Write-Host "No match in SCOM for: $($Service[0])"
    Break
    }
    Else
    {
    if ($instance) { Write-Host "SCOM match for: $($instance.DisplayName): CMDB value is: $($Service[0])" }
    }

    #Retrieve custom value property #
   
    $ServiceDescription = $instance | Select-Object -ExpandProperty *.ServiceDescription
    $BusinessDetailedDescription = $instance | Select-Object -ExpandProperty *.BusinessDetailedDescription
    $IsBusinessService = $instance | Select-Object -ExpandProperty *.IsBusinessService
    $OwnedByOrganization = $instance | Select-Object -ExpandProperty *.OwnedByOrganization
    $AvailabilitySchedule = $instance | Select-Object -ExpandProperty *.AvailabilitySchedule
  
    #Assign hashtable value to the custom value property #

    $ServiceDescription.Value=$($Service[1])
    $BusinessDetailedDescription.Value=$($Service[2])
    $IsBusinessService.Value=$($Service[3])
    $OwnedByOrganization.Value=$($Service[4])
    $AvailabilitySchedule.Value=$($Service[5])

    #Commit changes to SCOM
    $instance.Commit()
   
    }

 

This method is also great if you need to group your servers by priority, life cycle phase etc for dashboards and alert views. Extend the windows computers class and then use powershell to get the get and write the data to the custom properties.

Merry Christmas.


Leave a Reply