Extending OMS with SCCM Information

First published on TechNet on Jun 26, 2017
Brad Watts here to talk with you today about extending OMS with SCCM information. Microsoft Operations Management Suite (OMS) is a very powerful cloud based tool. There are bunch of out of the box solutions like Alert Management, Active Directory Assessment, SQL Assessment, Update Compliance, etc.. We're not going into details of what OMS is or how to use the solutions that are available to you. If you aren't familiar with the what OMS is and the benefits of the tool I strongly suggest you spend just a little bit of time browsing the OMS website here: https://www.microsoft.com/en-us/cloud-platform/operations-management-suite All of the out the box solutions available in OMS are great but what if we have information that isn't ingested through a solution but we'd like to take advantage of the extremely powerful Log Analytics Search API. Well that's where the OMS Ingestion API comes into play.

OMS Ingestion API

The OMS Ingestion API is a PowerShell module that is available that allows us to send any information that we can gather through PowerShell to our OMS Workspace. The gist of how it works is the following

1. Gather the information you want to send to the OMS Workspace
2. Put the gathered information into a JSON payload
3. Use the Send-OMSAPIIngestionFile command from the OMS Ingestion API to send the information to your OMS Workspace.

Below we will walk through these steps with an example of sending some of your inventory information from SCCM. To find out more and get the OMS Ingestion API go here:

https://www.powershellgallery.com/packages/OMSIngestionAPI/1.5

SCCM Installed Software

Our first example will query the SCCM database to get all of the installed software for our systems. We'll walk through the PowerShell script so you understand it. I will also provide a link to the script file at the bottom of the blog. Our first step in the process is to gather the information we want to send to OMS. In this example we are getting the information from the SCCM Database. In this case I typically start in SQL Management Studio to get a valid query. Below is the query we'll use to get our Installed Software: SELECT vrs.Netbios_Name0 AS [Device Name],vrs.Name0 + '.' + vrs.Full_Domain_Name0 as FQDN,vProg.DisplayName0 as [Program Name],vProg.InstallDate0 as [Install Date],vProg.Version0 as [Version] from dbo.v_r_system as vrs Left OUTER JOIN dbo.v_Add_Remove_Programs as vProg on vProg.ResourceID=vrs.ResourceID WHERE vProg.DisplayName0 != 'NULL' and vrs.Active0=1 order by vrs.Netbios_Name0,vProg.Displayname0 I'm not going to bore you with the details of the SQL Query but this query run against your SCCM DB will bring back the Computer Name, FQDN, Program Name, Install Data, and Version for each computer where the SCCM Hardware Inventory has been run. Now that we have our query we need to move this over to PowerShell and make a connection to our database. To simplify this process I'm utilizing the SQLManagement module that is available here: https://gallery.technet.microsoft.com/scriptcenter/SQL-PowerShell-Module-0d1e38ec Once you've imported this module into your script you can utilize the following PowerShell code: $instancename="<your db server/instance name>" $dbname="<SCCM DB Name>" $query="<insert query from above>" $connectionstring = "Data Source=$instancename;Integrated Security=SSPI;Initial Catalog=$dbname" $dataset = Invoke-SqlQuery -ConnectionString $connectionstring -Query $query In the above code block you will need to fill in the $instancename variable. An example would be "LAB-SQL\SCCM" if you have a named instance. You also need to fill in the $dbname variable with the name of your SCCM database. After you run this set of code you will have a variable named $dataset that contains all of your information. We want to take this information and send it to OMS but in order to do this we need to convert it to a JSON payload. Luckily PowerShell makes this a relatively painless process. It just takes a couple of steps. First step, take our $dataset and create an array that contains PSObjects. To represent our Installed Software Query our PSObject will have the following Properties:

• FQDN
• NetBiosName
• ProgramName
• Version
• InstallDate

You'll notice we just created a property for each of the values returned from the query. Below is what this looks like in PowerShell. $DataComponentArray=@() foreach($item in $dataset) { $sx = [pscustomobject]@{ "FQDN"=$item.FQDN; "NetBiosName"=$item.'Device Name'; "ProgramName"=$item.'Program Name'; "Version"=$item.Version; "InstallDate"=$item.'Install Date' } $DataComponentArray+=$sx } Basically, we are looping through our $dataset, creating a new PSObject for each row and assigning it to $sx, and then adding $sx to our array which is $DataComponentArray. Second step is to convert your array to a JSON payload. Once you have your array of PSObjects this is a single line of code. =$json = $DataComponentArray | ConvertTo-Json Now that we have the JSON payload we just need to send the information to our OMS Workspace. In order to do that you'll need to import the OMS Ingestion module using import-module OMSIngestionAPI (make sure you've already installed it on the box your running the script from) and gather the following information from your OMS Workspace:

• Customer ID: OMS Portal -> Settings -> Connected Sources -> Windows Servers -> Workspace ID
• Shared ID: OMS Portal -> Settings -> Connected Sources -> Windows Servers -> Primary Key

Here is what our PowerShell code looks like: $customID = "<Workspace ID>" $sharedID = "<Shared ID>" $time = Get-Date Send-OMSAPIIngestionFile -customerId $customID -sharedKey $sharedID -body $json -logType "SCCMSoftware" -TimeStampField $time -Verbose We know have a PowerShell script that when run will send our Installed Software information from SCCM up to our OMS Workspace. The only thing left is to schedule this to run on a regular basis. I'll leave that up to you but some of your options would be:

• Windows Scheduled Task
• System Center Orchestrator
• Azure Automation with Hybrid Worker

That's pretty much it! We named our field "SCCMSoftware" so now that we've run the script, lets explore it in OMS.

Exploring the Data in OMS

In the OMS Portal we'll open the log search page and do a simple search on "*" just to make sure our data has arrived: Now that we see there is "SCCMSoftware_CL" data available we'll just select it to explore the info more. Now you see each one of the records along with the properties we added to our array in PowerShell. We can continue to explore this data just like any other data sent to OMS. I'll finish off this blog by showing a couple of searches that may be useful for you. Our first search will give us the count of each program by version. To do this we can simply run this query: Type=SCCMSoftware_CL | measure count() by ProgramName_s,Version_s What if we wanted to look at a specific program and get the computers it's installed on, the version installed, and the install date. In this case we could simply run the following query: Type=SCCMSoftware_CL ProgramName_s="Microsoft Silverlight" | select NetBiosName_s,Version_s,InstallDate_s | display Table This is just a couple examples of how you can use the search in OMS to quickly gather the information you need.

Summary

My goal in this blog is to show the power of the PowerShell OMS Ingestion API. The power of the Log Analytics search is something we want to take advantage of. When you combine PowerShell with the OMS Ingestion API this allows us to send whatever data that we want to our OMS Workspace. Today we looked at pulling the SCCM Installed Software Inventory and sending that to OMS. But the true power of this solution is that whatever we can pull with PowerShell we can ingest into our OMS Workspace. Here is the link to an example PowerShell script to ingest SCCM information: https://1drv.ms/f/s!Am5WunbinyvqtclH2TC-1DnKbWy0tA