Auditing Software Inventory Rules in System Center 2012 Configuration Manager

I recently conducted an audit of all software inventory rules set within the various environments my team manages. I wanted to perform this audit for several reasons; one of which was to ensure there were no “*.exe” rules still lingering in any environment. Smile In this blog I’ll show how much easier this is in System Center 2012 Configuration Manager as opposed to Configuration Manager 2007.

As you may already know, the sitectrl file doesn’t live in the file system in System Center 2012 Configuration Manager. It lives in the database and is replicated via SQL to all sites. This alone makes System Center 2012 Configuration Manager easier to manage software inventory rules because you no longer have to update the rules on every site within your environment. Now you simply make the change on any site and the information is sent to all other sites. Thus, in my System Center 2012 Configuration Manager environments I don’t have to create another audit to ensure all sites have the same rules configured as I do for my Configuration Manager 2007 environments.

One thing to know about me is that I really like seeing data in tables (or spreadsheets) – the data is nicely laid out and the information is straight forward and easy to read. Therefore, I find audits much easier to perform if I have a spreadsheet to look at and reference. So when I performed this recent software inventory rules audit I captured the data into spreadsheets.

The Configuration Manager 2007 environments were certainly not the easiest on which to perform this action. Besides the numerous sites to compare and look at, it required a lot of copying and pasting of the information from the Admin UI or the sitectrl file. This is a tedious task (assuming you have several rules defined).

In System Center 2012 Configuration Manager I could get the information the same way – I could use the Admin UI or display the sitectrl file from the database (SELECT SiteControl FROM vSMS_SC_SiteControlXML WHERE SiteCode = 'YourSiteCodeHere'). I could do that, but why would I want to if I can simply query the database and have the software inventory rules displayed in a nice, neat, beautiful table? Here’s how to query the rules:

-- View the Site Control File information for Software Inventory Rules in table format
SELECT MAX(CASE WHEN pvt.[Option] = 'Inventoriable Types' THEN pvt.Value END) AS [FileName]
,MAX(CASE WHEN pvt.[Option] = 'Path' THEN pvt.Value END) AS [Path]
,MAX(CASE WHEN pvt.[Option] = 'Subdirectories' THEN pvt.Value END) AS [Search Subfolers]
,MAX(CASE WHEN pvt.[Option] = 'Exclude' THEN pvt.Value END) AS [Exclude Encrypted and Compressed files]
,MAX(CASE WHEN pvt.[Option] = 'Exclude Windir and Subfolders' THEN pvt.Value END) AS [Exclude files in the Windows folder]
SELECT swi.SiteCode
,nds.col.value('@Name[1]','nvarchar(2000)') AS [Option]
,row.val.value('.', 'nvarchar(2000)') AS [Value]
,ROW_NUMBER() OVER (PARTITION BY swi.SiteCode, nds.col.value('@Name[1]','nvarchar(2000)') ORDER BY (SELECT 0)) AS [RowNum]
FROM dbo.vSMS_SC_ClientComponent_SDK swi
-- Only get the CAS info as the Primaries do not matter!
INNER JOIN dbo.ServerData srv
ON swi.SiteCode = srv.SiteCode
AND srv.ID = 0
CROSS APPLY swi.RegMultiStringList.nodes('//RegMultiStringLists/RegMultiStringList') nds(col)
CROSS APPLY nds.col.nodes('Value') row(val)
WHERE swi.ClientComponentName = 'Software Inventory Agent' -- Only get Software Inventory Rules
) pvt
GROUP BY pvt.RowNum
ORDER BY pvt.RowNum;

As you can see, System Center 2012 Configuration Manager makes performing audits of software inventory rules much easier because of the fact that the sitectrl file is stored in the database and replicated. I perform quarterly audits of the software inventory rules as well as software metering and hardware inventory rules to minimize client impact and network usage. Now, you can use this query to help ensure you are following the best practices of software inventory rules and minimize client impact in your environment!

Comments (7)

  1. Sorry Paul, was out on vacation…

    The problem with a *.exe rule is that it is totally unnecessary and provides very little (if any) benefit.

    I've never seen a *.exe rule defined with a location other than "All client hard disks". This means you're performing a complete disk scan of all disks every time software inventory runs. It also means more network usage and larger database sizes (due to the amount of data collected, sent, and stored).

    The reason this data provides little benefit is because hardware inventory is already collecting this information (most likely). If the application registers itself in Add/Remove Programs (ARP) hardware inventory is picking this information up already. If the application you want to report on doesn't play nice and doesn't register itself in ARP then you can turn on Asset Intelligence (AI) which will look in several sources (MSI, registry, and WMI) to compile the list of installed applications which is picked up by hardware inventory. If for some reason the application you want to report on isn't listed in any of these locations then you can create a software inventory rule for that specific exe name and provide it a relative path (rather than running a complete disk scan). Does that help answer your question?

  2. Anonymous says:

    can you explain what is the problem with a *.exe rule running in SCCM?

  3. Dustin Estes says:

    What about if a machine is running a “portable” application. More and more these are becoming prevalent. In order to find them you would have to scan for all EXEs in an environment so you didn’t omit them by accident.

    Auditing all EXEs also gives you the added benefit of having this data on hand, so that if an issue is discovered you can then immediately pull back info on all the machines at will. Rather than having to wait for everything in your environment to return data after a specified interval.

    Also, inventories after a machine sends back it’s initial inventory are all delta submissions, so they are very small. I have EXE inventorying turned on and I haven’t seen an initial file come back that was larger than 1mb. It’s just text information.

    Your concerns about database size and transmission saturation are unwarranted. The database has maintenance tasks in place to churn old and obsolete information so it is by default watching its own weight all the time. The only time you need to really worry about the size of the transmission and the size of the database is when you start “Collecting” files. This is when the database actually stores the files as well as information about those files.

  4. JustMatthew says:

    Ben this is very slick. Are you aware (do you have an example) of a way to extract data from the ConfigMgr database for hardware inventory rules and classes? Or, a good way to parse out all client settings from vSMS_SC_ClientComponent_SDK? Thanks.

    1. JustMatthew, I do have a script that essentially creates your SMS_DEF.mof from the database. I’ve been meaning to create a blog post on that for over 3 years now…maybe it’s finally time. 🙂 Stay tuned…
      As for client settings, do you have an example of what you’re looking for? I’m not completely sure I understand what you’re looking for…

      1. JustMatthew says:

        Ben, this post is extremely helpful! Thanks for sharing this

Skip to main content