In this article I will explain the following
- Why the Inv_AddRemoveProgram table is deprecated and it's limitations
- What the better table is to use to create software inventory reports
- The 'Source' column and why it is useful for troubleshooting and understanding your environment
- How to interpret the Source column's numbers
- A sample T-SQL script which uses a CASE statement to convert the source number into English language
There has always been some confusion with inventory due to the different sources inventory can arrive from. Many people travel down the table list in the CMDB and arrive at the [Inv_AddRemoveProgram] table and stop. This is because it is the first descriptive table we come to that sounds like it is what we want. From here on I will call Add/Remove Programs "ARP".
This is not where to stop at all, but first I will explain how the infamous ARP data is generated.
It should be noted straight away that the ARP table is provided by the basic Symantec Management Agent, specifically the Software Management Framework Agent located at .\Altiris Agent\Agents\Software Management\smfagent.dll. The SMF agent does a scan of the uninstall strings in the registry as part of the basic Inventory. We can all think of ways that this data can be bad. For a start, not all software created uses Microsoft Installer Technology. Some kind packagers my add a registry line to the uninstall string, but let's face it, even top software companies don't follow MS recommendations with their software so the list goes beyond simply devious, forgetful and bad. Secondly, administrators can manipulate these uninstall strings. Thus we must consider ARP to be legacy information, part of a basic free inventory provided with the Symantec Management Platform.
So where we should be looking is a little further down in [Inv_InstalledSoftware].
At first glance we get the typical table full of GUIDs
Typically this puts off most admins who realise that to get to the juicy detail in natural language, they have to start wading through joins. But persist with this because the real nugget is in the "Source" column.
Here is a list of sources
16 is for 'Patch Management'
8 is for 'Add Remove Programs'
4 is for 'File List'
2 is for 'Inventory Rule' -compliance (Managed Delivery)
1 is for 'MSI Scanner'
So, now we actually know where the inventory came from! The programmers amongst you may already have noticed the source numbers above are in a format that allows combinations that can be derived into separate pieces. What I mean is that in my example report above the source shows as '9'. This value is derived from 8 + 1, or in English terms the source is both MSI scanner and ARP scans.
The Inv_InstalledSoftware table is the master table that all solutions flow into. So now we can see the ARP table forms only a small part of the total of inventory information in the CMDB. What is more, using the Inv_InstalledSoftware table allows us to create reports on software AND know how the information is derived. Because all the solutions update this table, there are less holes in the data and even where there are holes we can work out which solution was in play and pursue a fix. For example, a solution plug-in may have had an incomplete roll out.
Before I provide a sample that allows us to see the sources of software inventory I want to emphasise what the source column is going to show you and how to interpret the data. I'm going to throw some examples out rather than do the thinking for you.
- What can we infer from Adobe Flash Player 11 ActiveX when it shows only a source of ARP and not MSI scanner?
- What do we know about Altiris Server 7.1 English x64 showing in an MSI scan and not an ARP scan?
- Which scan would a typical Autoit or VB bootstrap installer show up under?
- How useful would it be to search for MS Patches discovered by the Assessment scan?
The last thing that remains is to provide an example TSQL that illustrates leveraging 'source' whilst providing a simple inventory. What's more is that in the provided report there is a CASE statement to provide the source in natural English. This is not required and you may wish to leverage source as a mathematical item for use in evaluating numerical statements (ie if source > 7 then...) The columns and joins are for a typical software source per computer, please feel free to adapt this code as you desire. The CASE statement I will have to bow my head in acknowledgment to a colleague who used to be a Rocket Scientist in a previous career :)
SELECT
vcomputer.Name
,RM_ResourceSoftware_Component.Name
,REVERSE( STUFF( REVERSE(
CASE WHEN [Source]&16 = 16 THEN 'PM, ' ELSE '' END
+ CASE WHEN [Source]&8 = 8 THEN 'ARP, ' ELSE '' END
+ CASE WHEN [Source]&4 = 4 THEN 'File Scan, ' ELSE '' END
+ CASE WHEN [Source]&2 = 2 THEN 'Inv Rule/MSD, ' ELSE '' END
+ CASE WHEN [Source]&1 = 1 THEN 'MSI Scan, ' ELSE '' END
), 1, 2, '')) [Source]
,[InstallDate]
,[InstallFlag]
,RM_ResourceSoftware_Component.ProductUninstalled
-- ,vcomputer.IsManaged
FROM Vcomputer
Join Inv_InstalledSoftware
On _ResourceGuid=Guid
Join RM_ResourceSoftware_Component
ON Inv_InstalledSoftware._SoftwareComponentGuid = RM_ResourceSoftware_Component.Guid
WHERE
vcomputer.IsManaged = '1'
AND RM_ResourceSoftware_Component.Name like '%'
AND vcomputer.Name like '%'
Will give something like:
So we have shown the basis of where to generate software reports from, manually, along with how to see where the results are derived from. Additionally we have provided a T-SQL CASE statement to translate the options into readable text for reporting purposes.
I hope you found this informative and useful.