Share This Post

BDNA Normalize 2.x – Using CCM_RECENTLY_USED_APPS Data

OVERVIEW

When Normalize version 2.x pulls data from SCCM, the service will use a .CONFIG file to define all the queries it will use to pull data.  In general that file is either SCCM.LIGHT.EXTRACTOR.CONFIG  or SCCM.FULL.EXTRACTOR.CONFIG.   The LIGHT file has the minimum queries needed to normalize SCCM and make it useful.  The FULL file includes a bunch of extra SCCM data for those people who want to just pass extra SCCM data on through and report off Normalize and SCCM data together instead of having to pull some data from Normalize and some data from SCCM.

So, that’s what pulls data from SCCM, but what it actually pulls is data like Add/Remove Programs data (v_GS_Add_Remove_Programs_Data and v_GS_Add_Remove_Programs_64_DATA) as well as software inventory data (v_GS_SoftwareFile and v_ProductFileInfo) and software metering data (v_MonthlyUsageSummary).  I’m not privy to the actual algorithm Normalize uses to match SCCM data to the catalog, but I know it tries to match the ARP, software inventory and metering data to products in the catalog.  Well, I don’t know about you, but in order to keep our SCCM database reasonably sized, we’re only inventorying a few EXEs with Software Inventory, we’ve only got rules on a handful of EXEs for Software Metering and many of our packagers in the past have changed the Add/Remove Programs names of software they repackage so it no longer matches anything in anyone’s catalog.  What this all means is there are actually fewer data points than there should be to match to the Normalize catalog.

ENTER CCM_RECENTLY_USED_APPS (RUA)

For those of you that didn’t know, SCCM has a “CCM_RECENTLYUSEDAPPS” class in the sms_def.mof that will use the software metering agent of SCCM (yes, even if you have no metering rules) and gathers information every time an EXE that’s been executed on the machine and by whom and how many times they’ve been executed, plus some other EXE info and it sends this data back to SCCM as part of hardware inventory  (If you have this class enabled in your sms_def.mof, you can see the data in dbo.v_GS_CCM_RECENTLY_USED_APPS).  Well, if you’re only collecting a few EXEs with software inventory and you’re only metering a few EXEs with software metering, then recently used apps is an awesome place to go to add additional data points on which Normalize can match more products to your machines.

This article will show you how to edit your .CONFIG file in order to add the RUA data to your software files data and get a better discovery of the applications installed and running on your machines.

THE STEPS

1.  Open the .CONFIG file you’re using (SCCM.LIGHT.EXTRACTOR.CONFIG or SCCM.FULL.EXTRACTOR.CONFIG) and replace the ProductFileInfo section with the following code:

<Table Type=”ProductFileInfo”>
<SQL>
<![CDATA[
SELECT DISTINCT
IDENTITY(INT,1,1) as FileID,
FileName,
FileDescription,
FileVersion,
FileSize,
CompanyName,
ProductName,
ProductVersion,
ProductLanguage,
ori_FileId,
ori_Type
INTO #TMP_FILE
FROM (
SELECT
null as ori_FileId,
‘RUA’ as ori_Type,
ExplorerFileName0 as FileName,
FileDescription0  as FileDescription,
FileVersion0 as FileVersion,
FileSize0 as FileSize,
CompanyName0 as CompanyName,
ProductName0 as ProductName,
ProductVersion0 as ProductVersion,
ProductLanguage0 as ProductLanguage
FROM v_GS_CCM_RECENTLY_USED_APPS
WHERE RIGHT(ExplorerFileName0, 4) = ‘.EXE’
UNION
SELECT
FileID as ori_FileId,
‘File’ as ori_Type,
FileName,
FileDescription,
FileVersion,
FileSize,
CompanyName,
ProductName,
ProductVersion,
ProductLanguage
FROM dbo.v_ProductFileInfo
WHERE RIGHT(FileName, 4) = ‘.EXE’
)T

            SELECT
FileID,
FileName,
FileDescription,
FileVersion,
FileSize,
CompanyName,
ProductName,
ProductVersion,
ProductLanguage
FROM #TMP_FILE
]]>
</SQL>
</Table>

2.   Also replace the SystemSoftwareFile section with this code:

<Table Type=”SystemSoftwareFile”>
<SQL>
<![CDATA[
SELECT DISTINCT
v_GS_CCM_RECENTLY_USED_APPS.ResourceID as ResourceID,
#TMP_FILE.FileID as FileId,
NULL as InstallDate
FROM v_GS_CCM_RECENTLY_USED_APPS
INNER JOIN #TMP_FILE ON  ISNULL(#TMP_FILE.FileName,”)=ISNULL(v_GS_CCM_RECENTLY_USED_APPS.ExplorerFileName0,”)
AND ISNULL(#TMP_FILE.FileDescription,”)=ISNULL(v_GS_CCM_RECENTLY_USED_APPS.FileDescription0,”)
AND ISNULL(#TMP_FILE.FileVersion,”)=ISNULL(v_GS_CCM_RECENTLY_USED_APPS.FileVersion0,”)
AND ISNULL(#TMP_FILE.FileSize,”)=ISNULL(v_GS_CCM_RECENTLY_USED_APPS.FileSize0,”)
AND ISNULL(#TMP_FILE.CompanyName,”)=ISNULL(v_GS_CCM_RECENTLY_USED_APPS.CompanyName0,”)
AND ISNULL(#TMP_FILE.ProductName,”)=ISNULL(v_GS_CCM_RECENTLY_USED_APPS.ProductName0,”)
AND ISNULL(#TMP_FILE.ProductVersion,”)=ISNULL(v_GS_CCM_RECENTLY_USED_APPS.ProductVersion0,”)
AND ISNULL(#TMP_FILE.ProductLanguage,”)=ISNULL(v_GS_CCM_RECENTLY_USED_APPS.ProductLanguage0,”)
UNION ALL
SELECT DISTINCT
vSMS_G_System_SoftwareFile.ClientId as ResourceID,
#TMP_FILE.FileID as FileId,
null as InstallDate
FROM dbo.vSMS_G_System_SoftwareFile
INNER JOIN #TMP_FILE ON  #TMP_FILE.ori_FileID=vSMS_G_System_SoftwareFile.FileId
AND #TMP_FILE.ori_Type=’File’
]]>
</SQL>
</Table>

 

3.   Save the .CONFIG file and run Normalize against SCCM like you normally would.

At this point when Normalize runs it will pull the Software Inventory data AND pull the recently used apps data and union them together.  This should make for many more EXEs to match and give you a better indication of what’s actually installed on your machines.

NOTES

Make sure you replace only the sections between the <Table Type=xyz> and the </Table> tags. You’re ultimately just copying that code directly over the top of what’s there.

With any modifications to systems like this, you should always TEST, TEST, TEST ahead of time on non-production systems and backup your existing .CONFIG file in case you need to go back. You never know what characters might have been eaten by the web servers when I uploaded this.

 

CONCLUSION

When I ran this, I was expecting my matching percentage to go WAY up, but the opposite happened (at first). My percentage actually went down a couple of percent. At first blush this didn’t make any sense but when I looked at the numbers I realized why this should be expected…

Let’s say you were getting 100% coverage from Normalize before you did this with 100,000 rows of data coming from SCCM.  Everything that’s being inventoried would match a product in the catalog.  Perfect.  But now, let’s modify the .CONFIG file like I’ve outlined above and run it again.  Suddenly you might only be getting 95% coverage from Normalize.  However, it may have matched like 250,000 rows instead of just 100,000 rows.  So even though the matching PERCENTAGE is lower on the first run, the number of PRODUCTS matched will likely be higher.  Also, because BDNA has SLAs with their customers to map the unmatched products for Tier1 and Tier2 vendors, the matching percentage should go right back up on subsequent runs once their huge crew of catalog editors chew through all that data you uploaded to their servers.

So, that’s it, really…if you want to get more data points into Normalize in order to get a better matching of software installed, you can use CCM_RECENTLY_USED_APPS data by editing the .CONFIG file and using the above updated queries.

Share This Post

Leave a Reply