This post has been republished via RSS; it originally appeared at: Device Management in Microsoft articles.
First published on TECHNET on May 19, 2012
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. :-) 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]
FROM (
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;
GO
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!