How To: Publish ConfigMgr Data to OMS

This post shows an example of querying data from ConfigMgr, converting to JSON and posting to Microsoft Operations Management Suite (OMS) as a custom log. If you’re not yet familiar with OMS, take a look at the OMS home page and Channel 9 for starters.

In this example, we’re going to use PowerShell to query collection evaluation data from ConfigMgr and post it to OMS. Here are the major steps:

  • Query ConfigMgr for the information we need, translate it (via SQL) to JSON-friendly formatting
  • Convert the query results to JSON
  • Use OMS API to post the information to OMS

Download the entire script from GitHub.

Query ConfigMgr

Here’s the query that will run against your primary site (note, only primary sites perform collection evaluation, so running this against a CAS returns zero results):

SELECT
'PS1' as [SiteCode],
[t0].[CollectionName] as [CollectionName],
[t0].[SiteID] as CollectionID,
[t1].[EvaluationLength] AS [RunTimeMS],
concat(convert(varchar(19), [t1].[LastRefreshTime], 127),'Z') as [LastEvaluationCompletionTime],
concat(convert(varchar(19), [t2].[NextRefreshTime], 127),'Z') as [NextEvaluationTime],
[t1].[MemberChanges] AS [MemberChanges],
concat(convert(varchar(19), [t1].[LastMemberChangeTime], 127),'Z')  AS [LastMemberChangeTime]
FROM [dbo].[Collections_G] AS [t0]
INNER JOIN [dbo].[Collections_L] AS [t1] ON [t0].[CollectionID] = [t1].[CollectionID]
INNER JOIN [dbo].[Collection_EvaluationAndCRCData] AS [t2] ON [t0].[CollectionID] = [t2].[CollectionID]

Notice I made some tweaks on the data returned. I added a SiteCode property because in my environment, I have more than one primary site (luckily, most of you don’t have do deal with this). I also modified the date-time values that were returned to append a Z for Zulu Time. I recall Zulu time from my Marine Corps days, and as I was trying to figure out how to get proper date-time into OMS (via JSON), all roads led to Zulu time (more info here, and here). Luckily, the date-times used in the SQL query above are already in UTC time, so we’re just changing the formatting to make it JSON-Friendly (note: I’m no JSON expert, so if you have identified better methods, please leave a comment). If you reuse this code for other queries and have times in local time, you need to first convert them to UTC time.

collquery-datetime
The columns of the query are described pretty well – I’ll point out the RunTimeMS, as that’s the evaluation time in milliseconds to perform the evaluation of the collection. So as you can see, I have some ugly ones that take 5 to 10 seconds to complete. The full script is posted below. To properly run this query against your environment, modify line 15 (the SQL Connection String). I’m using CM_PS1 as my database name and MyDataBaseServer as the server name. Modify this connection string as needed for your environment.

Convert the query results to JSON

Since we performed conversions in our SQL query, the process to convert to JSON is easy. You can see in lines 44-47, we use the convertto-json cmdlet, and specify the $table variable (which contains the results of the query from ConfigMgr). Also, notice the -Compress argument, which compresses the JSON (and removes a lot of whitespace).

Use OMS API to post the information

Now for the fun part – sending the data to OMS. You can easily setup a free OMS account – for this exercise, you don’t need to connect any servers to send data to OMS – we’ll simply use the API. Navigate to your new OMS workspace, and select Settings, and then Connected Sources. From here, copy the Workspace ID and Primary Key for lines four and seven in the main script. These items authenticate your script when calling the API.

omskeys

Once you have updated the script (and you know you have proper rights to ConfigMgr, as well as the ability to access the internet from where you run the script), run it and cross your fingers for the success code of 200, as shown below. (You can review other return codes here).

apisuccess

Once you see the success code of 200, you’ll be more than ready to view the OMS portal, but do yourself a favor and get a coffee first – in my experience, it may take an hour or so to see the information the first time data is posted (I’m guessing there’s some processing that has to happen on the back end for the custom log type, indices, etc). In fact, when you do start seeing the data, you may only see one field from the custom log type – if that’s the case, wait longer, or open a private browser to see the rest (there’s some kind of caching going on . .).

Once you have waited that hour or two, navigate to Log Search in the OMS console and select Count of all data collected group by type. You should then see CM_CollevalInfo_CL

cm_collevalinfo_cl

Click on CM_CollevalInfo_CL in the middle pane to display the details.

cl_collevalinfo_cl_details

Now you see the magic. Notice the different property names, and the suffix _s (string), _d (double), _t (date/time) – view all suffixes here. The key is to get these items formatted properly, so that when you send the first run to OMS, the data types are created properly. You can see that I have a problem with NextEvaluationTime_s – it should have been a date/time, but was interpreted as a string. (Please send me your feedback if you find my issue). Once I figure this out, I’ll update this post.

Now that you have this information in OMS, you could use for analytical purposes, trigger an alert or Azure Automation runbook.

The source code is posted below, but depending on your browser, may not show highlighting properly, or make it easy for copy/paste. Download the entire script from GitHub.

For reference, spend some time reading the Log Analytics HTTP Data Collector API doc from Microsoft as well as Stefan Stranger’s Using the HTTP OMS Data Collector API for real-world scenarios Part 1 and Part 2.

Happy Scripting!

Greg

#Publish-CMData - https://gregramsey.net/2016/12/31/how-to-publish-configmgr-data-to-oms/

# Replace with your Workspace ID
$CustomerId = "e4ff0038-49647-4c0c-49647-99a3c49647d5"  

# Replace with your Primary Key
$SharedKey = "CPfHRvey56FI3fzYi6C/mG1FxMt8RcsHkMgCNPWT/tPGGuEY8Tq63WvUaVokla3WVzicGLe00pcf+4t7b1aAMg=="

# Specify the name of the record type that you'll be creating
$LogType = "CM_CollevalInfo"

# Specify a field with the created time for the records
$TimeStampField = "LastEvaluationCompletionTime"

$cnstring = 'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CM_PS1;Data Source=MyDataBaseServer;User ID=;Password='


$cmdtext = @"
SELECT 
    'PS1' as [SiteCode], 
    [t0].[CollectionName] as [CollectionName], 
    [t0].[SiteID] as CollectionID, 
    [t1].[EvaluationLength] AS [RunTimeMS], 
    concat(convert(varchar(19), [t1].[LastRefreshTime], 127),'Z') as [LastEvaluationCompletionTime], 
    concat(convert(varchar(19), [t2].[NextRefreshTime], 127),'Z') as [NextEvaluationTime], 
    [t1].[MemberChanges] AS [MemberChanges], 
    concat(convert(varchar(19), [t1].[LastMemberChangeTime], 127),'Z')  AS [LastMemberChangeTime]
FROM [dbo].[Collections_G] AS [t0]
INNER JOIN [dbo].[Collections_L] AS [t1] ON [t0].[CollectionID] = [t1].[CollectionID]
INNER JOIN [dbo].[Collection_EvaluationAndCRCData] AS [t2] ON [t0].[CollectionID] = [t2].[CollectionID]
"@



$cn = New-Object System.Data.OleDb.OleDbConnection $cnstring
$cn.Open()

$cmd = New-Object System.Data.OleDb.OleDbCommand $cmdtext, $cn
$cmd.CommandTimeout = 0
$reader = $cmd.ExecuteReader()

$table = new-object "system.data.datatable"
$table.Load($reader)
$json = convertto-json -inputobject (
    $table | select SiteCode, CollectionName, CollectionID, `
     RunTimeMS, LastEvaluationCompletionTime, NextEvaluationTime, 
     MemberChanges, LastmemberChangeTime) -Compress



# Create the function to create the authorization signature
Function Build-Signature ($customerId, $sharedKey, $date, $contentLength, $method, $contentType, $resource)
{
    $xHeaders = "x-ms-date:" + $date
    $stringToHash = $method + "`n" + $contentLength + "`n" + $contentType + "`n" + $xHeaders + "`n" + $resource

    $bytesToHash = [Text.Encoding]::UTF8.GetBytes($stringToHash)
    $keyBytes = [Convert]::FromBase64String($sharedKey)

    $sha256 = New-Object System.Security.Cryptography.HMACSHA256
    $sha256.Key = $keyBytes
    $calculatedHash = $sha256.ComputeHash($bytesToHash)
    $encodedHash = [Convert]::ToBase64String($calculatedHash)
    $authorization = 'SharedKey {0}:{1}' -f $customerId,$encodedHash
    return $authorization
}


# Create the function to create and post the request
Function Post-OMSData($customerId, $sharedKey, $body, $logType)
{
    $method = "POST"
    $contentType = "application/json"
    $resource = "/api/logs"
    $rfc1123date = [DateTime]::UtcNow.ToString("r")
    $contentLength = $body.Length
    $signature = Build-Signature `
        -customerId $customerId `
        -sharedKey $sharedKey `
        -date $rfc1123date `
        -contentLength $contentLength `
        -fileName $fileName `
        -method $method `
        -contentType $contentType `
        -resource $resource
    $uri = "https://" + $customerId + ".ods.opinsights.azure.com" + $resource + "?api-version=2016-04-01"

    $headers = @{
        "Authorization" = $signature;
        "Log-Type" = $logType;
        "x-ms-date" = $rfc1123date;
        "time-generated-field" = $TimeStampField;
    }

    $uri
    $method
    $contentType
    $headers
    #$body
    $response = Invoke-WebRequest -Uri $uri -Method $method -ContentType $contentType -Headers $headers -Body $body -UseBasicParsing
    return $response.StatusCode

}

# Submit the data to the API endpoint
Post-OMSData -customerId $customerId -sharedKey $sharedKey -body ([System.Text.Encoding]::UTF8.GetBytes($json)) -logType $logType  
 

Download the entire script from GitHub.

Code from my Session at the Northwest System Center User Group

Big thanks to the Northwest System Center User Group for hosting me at their March meeting. We had a great demo session to show how to use PowerShell with ConfigMgr.

To enable ease of search, I’m posting the PowerShell code into this post. If you want all the code, you can download here.

Demo 1 – Samples of using PowerShell to quickly pull info from ConfigMgr, and to see the ‘guts’ behind a ConfigMgr cmdlet (see the WMI that is being called):

#http://www.gregramsey.net
#Test, test, test  - test everything you - safety First!
#general overview of using Powershell with configMgr - examples of show-command, debug, etc.

#region Import CM Module
$CMModulePath = `
    $Env:SMS_ADMIN_UI_PATH.ToString().SubString(0,$Env:SMS_ADMIN_UI_PATH.Length - 5) `
    + "\ConfigurationManager.psd1" 
Import-Module $CMModulePath -force
cd GMR:
#endregion

#get CM cmdlets
get-command -Module ConfigurationManager | Out-GridView

#show commands for set-cmapplication
show-command Set-CMApplication

#Show Devices
Get-CMDevice | Out-GridView

#List all members
Get-CMDevice | Get-Member

#select Specific Objects
Get-CMDevice | Select-Object Name, SiteCode, ADSiteName, DeviceOS, 
    Domain, IsActive, LastActiveTime, LastClientCheckTime, LastDDR, 
    LastHardwareScan, LastMPName, LastPolicyReqeust | Out-GridView
#Group-By
Get-CMDevice | Select-Object Name, SiteCode, ADSiteName, DeviceOS, 
    Domain, IsActive, LastActiveTime, LastClientCheckTime, LastDDR, 
    LastHardwareScan, LastMPName, LastPolicyReqeust | Group-Object DeviceOS | Out-GridView

#Show Debug
Get-CMDevice -Debug | Out-GridView

#grab wmi query and run it.

Demo 2 – Samples of manipulating collecftions, handling lazy properties, etc.

#http://www.gregramsey.net
#Samples of manipulating collecftions, handling lazy properties, etc.


#region Import CM Module
$CMModulePath = `
    $Env:SMS_ADMIN_UI_PATH.ToString().SubString(0,$Env:SMS_ADMIN_UI_PATH.Length - 5) `
    + "\ConfigurationManager.psd1" 
Import-Module $CMModulePath -force
cd GMR:
#endregion

#quick display of Client info for a collection
get-cmdevice -CollectionName "Test Coll" | select Name, ADLastLogonTime, ADSiteName, `
    ClientVersion, DeviceOwner,Domain,LastActiveTime,LastHardwareScan, LastMPServerName, `
    SiteCode | Out-GridView

#write to .csv
get-cmdevice -CollectionName "Test Coll" | select Name, ADLastLogonTime, ADSiteName, `
    ClientVersion, DeviceOwner,Domain,LastActiveTime,LastHardwareScan, LastMPServerName, `
    SiteCode | export-csv c:\logs\ClientInfo.csv -NoTypeInformation
notepad c:\logs\ClientInfo.csv

#View Collection info
get-cmdevicecollection -name "Test Coll"

#look at schedule
show-command New-CMSchedule

#create a collection, with schedule
$Sched = New-CMSchedule -DayOfWeek Saturday
$MyColl = New-CMDeviceCollection -Name "Test Coll2" -LimitingCollectionName `
    "All Desktop and Server Clients" -RefreshSchedule $Sched -RefreshType Periodic

#Add rule to exclude my "All DCs Collection"
Add-CMDeviceCollectionExcludeMembershipRule -CollectionName "Test Coll2" `
    -ExcludeCollectionName "All DCs"

#now, update the schedule (dipping into wmi) - #Chris Mosby Special 🙂
$Sched = New-CMSchedule -RecurCount 7 -RecurInterval Days -Start ([datetime] "2/27/2015 02:00")
$Coll = Get-WmiObject -Class SMS_Collection -Namespace "Root\SMS\Site_gmr" `
    -Filter "Name='Test Coll2'"
$Coll.RefreshType = 6  #1=manual, 2=periodic, 4=constant, 6=periodic+constant
$Coll.RefreshSchedule = $Sched.psbase.ManagedObject
$Coll.Put()

#look at the schedule with gwmi
$Coll = Get-WmiObject -Class SMS_Collection -Namespace "Root\SMS\Site_gmr" `
    -Filter "Name='Test Coll2'"
$Coll.refreshschedule    # (Refreshschedule is blank...)

#you see refreshschedule info here:
$coll = Get-CMDeviceCollection -name "Test Coll2"
$coll.RefreshSchedule

#why is refreshschedule missing when get-wmiobject?

#need to handle lazy property..... (check sms_collection Class for lazy props)
$Coll = Get-WmiObject -Class SMS_Collection -Namespace "Root\SMS\Site_gmr" `
    -Filter "Name='Test Coll2'"
$coll.__path
$coll2 = [wmi]$coll.__path  #get specific instance using the [wmi] and __Path 
$coll2.RefreshSchedule

#Create new Maintenance Window
#Create new MW Schedule - every Saturday at 2:00AM for 3 Hours
$mwSched = New-CMSchedule -DayOfWeek Saturday -Start ([datetime]"2:00 am") `
    -End ([datetime]"2:00 am").addhours(3) 
$CollID = (Get-CMDeviceCollection -name "Test Coll2").CollectionID
New-CMMaintenanceWindow -CollectionID $CollID -ApplyToSoftwareUpdateOnly `
    -Name "Test Coll2 MW" -Schedule $mwsched

#show existing mw
Get-CMMaintenanceWindow -CollectionID $CollID

#Add Direct Membership Rule
Add-CMDeviceCollectionDirectMembershipRule -CollectionName $Coll2.Name `
    -ResourceId (get-CMDevice -name CM2012R2).ResourceID

#Add Multiple Direct Membership Rules
notepad c:\logs\systems.txt
get-content c:\logs\systems.txt | foreach-object {
    Add-CMDeviceCollectionDirectMembershipRule -CollectionName $Coll2.Name `
        -ResourceId (get-CMDevice -name $PSItem).ResourceID
}

#Create Query Rule
$WQLQuery = `
    "select * from sms_r_system Where OperatingSystemNameAndVersion like '%Workstation%'"
Add-CMDeviceCollectionQueryMembershipRule -CollectionName $Coll.Name `
    -RuleName "All Workstations" -QueryExpression $WQLQuery


#perform surgery - remove one rule
Remove-CMDeviceCollectionDirectMembershipRule -CollectionName "Test Coll2" `
    -ResourceName "W81x64"  #use -force toskip confirmation

#Remove all direct membership rules
$CollectionName = "Test Coll2"
$coll = Get-CMDeviceCollection -name $CollectionName 
$coll.CollectionRules | where-object {$PSItem.ObjectClass -eq "SMS_CollectionRuleDirect"} |
    foreach-object { 
        Remove-CMDeviceCollectionDirectMembershipRule -collectionname $CollectionName `
        -resourceID $_.ResourceID -force
    }

#Update Collection membership now
Invoke-CMDeviceCollectionUpdate -Name "Test Coll2"

#Remove Collection use -force to not get prompted
Remove-CMDeviceCollection -name "Test Coll2" -force

Demo 3 – examples of manipulating packages, programs, programflags, etc.

#http://www.gregramsey.net
#examples of manipulating packages, programs, programflags, etc.


#region Import CM Module
$CMModulePath = `
    $Env:SMS_ADMIN_UI_PATH.ToString().SubString(0,$Env:SMS_ADMIN_UI_PATH.Length - 5) `
    + "\ConfigurationManager.psd1" 
Import-Module $CMModulePath -force
cd GMR:
#endregion

Get-CMPackage | select Manufacturer, Name, PackageID, LastRefreshTime, PkgSourcePath | Out-GridView
Get-CMPackage | export-csv c:\logs\Allpackages.csv -NoTypeInformation


#Explore Program
$Program = Get-CMProgram -ProgramName "Cumulative update 1 - console update install" `
    -PackageId "GMR00006"
#Look at ProgramFlags
$Program
$Program.ProgramFlags  # should be 135307264
$oldPFlag = $Program.ProgramFlags
$oldbinary = [Convert]::ToString($Program.ProgramFlags, 2)
$oldbinary

#now manually change it in the gui - 'allow this program to install...'
$Program = Get-CMProgram -ProgramName "Cumulative update 1 - console update install" `
    -PackageId "GMR00006"
$Program.ProgramFlags  # should be 135307265
$binary = [Convert]::ToString($Program.ProgramFlags, 2)
$binary
$oldbinary

# List all Programs with this enabled:
# "Allow this program to be installed from the Install 
#  Package task sequence action without being deployed."
Get-CMProgram | foreach {
    if ($_.ProgramFlags -band 0x1) {
    "{0} {1} {2}" -f $_.ProgramName, $_.PackageID, "Enabled"
    }
}

#Enable this setting on all Programs
Get-CMProgram | foreach {
    if  ($_.ProgramFlags -band 0x1) {
        #Already Enabled
    }
    else {
        #currently disabled, enable now
        "{0} {1} {2}" -f $_.ProgramName, $_.PackageID, "Enabling"
        $_.ProgramFlags = $_.ProgramFlags -bxor 0x1
        $_.Put()
    }
}

#Disable this setting on all Programs
Get-CMProgram | foreach {
    if  ($_.ProgramFlags -band 0x1) {
        #currently enabled, disable now
        "{0} {1} {2}" -f $_.ProgramName, $_.PackageID, "Disabling"
        $_.ProgramFlags = $_.ProgramFlags -bxor 0x1
        $_.Put()
    }
    else {
        #Already Disabled
    }
}


#List all disabled programs   
Get-CMProgram | foreach {
    if  ($_.ProgramFlags -band 0x1000) {
        #disabled
        "{0} {1} {2}" -f $_.ProgramName, $_.PackageID, "Disabled"
    }
    else {
        #enabled
    }
}

#Update Supported OperatingSystems - Need to step into WMI
$prg = gwmi sms_program -namespace root\sms\site_gmr `
    -filter "ProgramName='Fake Program' and PackageID='GMR00017'"
$prg = [wmi]$prg.__PATH
$prg.SupportedOperatingSystems

#look at example supported OS (to make sure correct min/max version
$pkgID = (Get-CMPackage -name "MDT Settings").PackageID
$prg = Get-CMProgram -ProgramName "fp2" -PackageId $pkgID
$prg.SupportedOperatingSystems

#use WMI to grab the program
$prg = gwmi sms_program -namespace root\sms\site_gmr -filter "ProgramName='Fake Program' and PackageID='GMR00017'"
#neet to getinstance to pull the lazy properties
$prg = [wmi]$prg.__PATH
$prg.SupportedOperatingSystems

#create a new instance of SMS_OS_Details, so we can add it to Supported OS (Win8.1 x64)
$OS = ([wmiclass]"\\CM2012R2.GMR.net\root\sms\site_GMR:SMS_OS_Details").Createinstance()
$OS.MaxVersion = "6.30.9999.9999"
$OS.MinVersion = "6.30.0000.0"
$OS.Name = "Win NT"
$OS.Platform = "x64"
$OS

$prg.SupportedOperatingSystems = $prg.SupportedOperatingSystems + $OS
$prg.Put()

#List all packages configured to "Copy the content in this package 
# to a package share on distribution points"
#PackageFlag = 0x80
Get-CMPackage | foreach {
   if  ($_.PkgFlags -band 0x80) {
        #Enabled
        $_.Packageid + " - " + $_.Name

    }
    else {
        #disabled
    }

}

Demo 4 – add 8.1 support to all package/programs that currently have support for 8.0.

#http://www.gregramsey.net
#add 8.1 support to all package/programs that currently have support for 8.0.

#create a new instance of SMS_OS_Details, so we can add it to Supported OS (Win8.1 x64)
$OS = ([wmiclass]"\\CM2012R2.GMR.net\root\sms\site_GMR:SMS_OS_Details").Createinstance()
$OS.MaxVersion = "6.30.9999.9999"
$OS.MinVersion = "6.30.0000.0"
$OS.Name = "Win NT"
$OS.Platform = "x64"

Get-WmiObject sms_program -Namespace root\sms\site_gmr | foreach-object {
    $prg = [wmi]$PSItem.__Path
    #supported platform is set, and Win8x64 is supported platform
    if (($prg.ProgramFlags -band 0x8000000) -eq 0 -and `
        ($prg.SupportedOperatingSystems.minversion -contains "6.20.0000.0") ) { 
            #check if Win81x64 is a supported platform
            if ($prg.SupportedOperatingSystems.minversion -notcontains "6.30.0000.0") {
                #no. .so add it
                "Updating {0}" -f $prg.programname
                $prg.SupportedOperatingSystems = $prg.SupportedOperatingSystems + $OS
                $prg.Put()
            }
        }
    #}
}

Happy Scripting!

Greg

ramseyg@hotmail.com

This post first appeared on http://www.gregramsey.net