Inventory Solution was built to maintain a CURRENT running inventory of Managed Computers and as such it does not maintain an archive of historical or changed data. This results in a unique problem for many IT managers who not only want to know what hardware and software is on the network now, but would also like to monitor changes to this data.
This article is meant for the benefit of those people who would like to monitor changes to specific inventory information for their resources. In this article we will discuss enablinga and using history tables as well as reporting changes to resource inventories using the Notification Servers history tables.
First, lets enable the history for AeX OS Add Remove Programs.
To enable the history tables for specific data classes in the console browse to:
"Configuration -> Server Settings -> Notification Server Settings -> Resource History -> Operating System -> AeX OS Add Remove Program"
Next, and the most important step in setting up reporting of changes using the history tables is understanding how the history tables work and the possible impacts of enabling them.
How history tables work.
When new data arrives on the Notification Server from a client as an NSE, the DataLoader program, the program whose job it is to insert data into the database, parses the NSE file and inserts the necessary data into their corresponding table. If History tables are turned on for the table being written to, a corresponding line will aslo be written to the InvHist_NAME table where NAME is the data class name. In addition to writing a duplicate line to the InvHist_NAME table, all lines of data imported from a single NSE file will be assigned the same SnapshotID number in the history table.
The history tables follow the same schema as the data class for which the history is turned on with the exception of an additional field for "SnapshotID". All history tables also carry the same name as their originating data class with the addition of the prefix InvHist_*.
Warning: The following is impact related and extremely important to understand. Because the dataloader inserts a copy of all data from an NSE file and not just the changes to the base table, history tables can become very large very quickly causing the "Keep History Duration" delete procedure to fail. If you choose to use History tables it is very important to set a short schedule to purge old data or purge this data manually when necessary depending on the data class and your environment. Monitor this carefully so that these tables do not get out of hand.
Resource History Configurations contain a built in maintenance duration which by default is set to 6 months. This setting will delete data older than 6 months from the table and it may be necessary to shorten this duration depending on number of nodes and which table you are keeping a history for as I mentioned before.
So we now know that we have a history enabled for the "AeX OS Add Remove Programs" data class. I have also had a computer send in an inventory and lines have been written to the history table.
Lets take a look at a piece of two of these lines:
As we can see, the SnapshotID for these two lines is the same which means they came from the same Inventory NSE.
In addition, the install of a new software to this resource will result in all the inventory data for the resource being added to the history table for this machine.
So how do we tell what has changed?
Now we have enabled histories, a history table has been created which contains a snapshot of the inventory for the resource before and after a change has been made. We will now need to compare the snapshots to discover what the changes are using SQL.
For this report, I will be using a filter for collection, and "n days" so I can filter for changes on a domain using changes occurring in the "last N days"
First we will select the tables to use. For my report I will use:
vComputer
InvHist_AeX_OS_Add_Remove_Programs
CollectionMembership
Some derived tables I will create for my specific purposes.
? First I will select the fields I want to see. "vc" from vComputer and "ih" from my derived table standing for Inventory History.
SELECT
vc.[Domain] AS 'Domain',
vc.[Name] AS 'Name',
ih.[Added or Removed],
ih.[Inventory Date],
ih.[Software],
ih.[Version]
? As we see, I want my output to show the Domain, computer name, whether the change was added or removed, the date of the inventory showing the change, the software name, and the version.
? Next comes the complex stuff. I now need to say where this information is coming from. We will start with vcomputer with the alias "vc". This is where I am retrieving the information for Domain and Name. I will then join this to some derived tables.
FROM
vComputer vc
JOIN
? Begin derived table "ih" for table where software was added and eventually union to removed.
(SELECT
datesAdd.[_ResourceGuid] AS '_ResourceGuid',
'Added' AS 'Added or Removed',
datesAdd.[LatestSnapshotDate] AS 'Inventory Date',
datesAdd.[ReferenceSnapshotDate] AS 'Previous Inventory Date',
dataLatestAdd.[Name] AS 'Software',
dataLatestAdd.[Version] AS 'Version'
FROM
? Imbedded query for derived table "ih"
(SELECT
latestAdd.[_ResourceGuid],
latestAdd.[LatestSnapshotDate],
referenceAdd.[ReferenceSnapshotDate]
FROM
? Imbedded query inside of derived table "latestAdd" for derived table "latestAdd"
(SELECT
[_ResourceGuid]
,MAX([InventoryDate]) AS LatestSnapshotDate
FROM
InvHist_AeX_OS_Add_Remove_Programs
? Begin a where criteria for the imbedded query used to build the derived table. This is where I will use the parameter %Last n days% to define the number of day to retrieve data for. What I am really doing here is grabbing the MAX inventory date prior to this number of days and comparing it to the most current snapshot.
WHERE
DATEDIFF(dd, [InventoryDate], getdate()) <= %Last n days%
GROUP BY
[_ResourceGuid]
? End imbedded query and name derived table latestAdd
) latestAdd
JOIN
? Second imbedded query inside of derived table "" to create derived table "referenceAdd"
(SELECT
[_ResourceGuid]
,MAX([InventoryDate]) AS [ReferenceSnapshotDate]
FROM
InvHist_AeX_OS_Add_Remove_Programs
? Begin a where criteria for the imbedded query used to build the derived table. This is where I will use the parameter %Last n days% to define the number of day to retrieve data for.
WHERE
DATEDIFF(dd, [InventoryDate], getdate()) > %Last n days%
GROUP BY
[_ResourceGuid]
? End imbedded query and name derived table referenceAdd
) referenceAdd
ON latestAdd.[_ResourceGuid] = referenceAdd.[_ResourceGuid]
? End imbedded query and name derived table datesAdd
) datesAdd
? Joining tables to InvHist_AeX_OS_Add_Remove_Program based on the latest snapshot date being in the derived table we have created.
JOIN
InvHist_AeX_OS_Add_Remove_Programs dataLatestAdd
ON (datesAdd.[_ResourceGuid] = dataLatestAdd.[_ResourceGuid]
AND datesAdd.[LatestSnapshotDate] = dataLatestAdd.[InventoryDate])
-- Left joining InvHist_AeX_OS_Add_Remove_Program on multiple fields such as date, name, and version.
LEFT JOIN
InvHist_AeX_OS_Add_Remove_Programs dataReferenceAdd
ON (datesAdd.[_ResourceGuid] = dataReferenceAdd.[_ResourceGuid]
AND datesAdd.[ReferenceSnapshotdate]= dataReferenceAdd.[InventoryDate]
AND dataLatestAdd.[Name]= dataReferenceAdd.[Name]
AND dataLatestAdd.[Version]= dataReferenceAdd.[Version])
? Setting where statement for all lines that are null to specify that these lines were added
WHERE
dataReferenceAdd.[Snapshotid] is null
? Setting up a Union to perform the same functions as above for all software that was removed. Please see definitions above to follow the logic below as they are identical.
UNION
SELECT
datesRemove.[_ResourceGuid] AS '_ResourceGuid',
'Removed' AS 'Added or Removed',
datesRemove.[LatestSnapshotDate] AS 'Inventory Date',
datesRemove.[ReferenceSnapshotDate] AS 'Previous Inventory Date',
dataReferenceRemove.[Name] AS 'Software',
dataReferenceRemove.[Version] AS 'Version'
FROM
(SELECT
latestRemove.[_ResourceGuid],
latestRemove.[LatestSnapshotDate],
referenceRemove.[ReferenceSnapshotDate]
FROM
(SELECT
[_ResourceGuid]
,MAX([InventoryDate]) AS LatestSnapshotDate
FROM
InvHist_AeX_OS_Add_Remove_Programs
WHERE
DATEDIFF(dd, [InventoryDate], getdate()) <= %Last n days%
GROUP BY
[_ResourceGuid]
) latestRemove
JOIN
(SELECT
[_ResourceGuid]
,MAX([InventoryDate]) AS [ReferenceSnapshotDate]
FROM
InvHist_AeX_OS_Add_Remove_Programs
WHERE
DATEDIFF(dd, [InventoryDate], getdate()) > %Last n days%
GROUP BY
[_ResourceGuid]
) referenceRemove
ON latestRemove.[_ResourceGuid] = referenceRemove.[_ResourceGuid]
) datesRemove
JOIN
InvHist_AeX_OS_Add_Remove_Programs dataReferenceRemove
ON (datesRemove.[_ResourceGuid] = dataReferenceRemove.[_ResourceGuid]
AND datesRemove.[ReferenceSnapshotDate] = dataReferenceRemove.[InventoryDate])
LEFT JOIN
InvHist_AeX_OS_Add_Remove_Programs dataLatestRemove
ON (datesRemove.[_ResourceGuid] = dataLatestRemove.[_ResourceGuid]
AND datesRemove.[LatestSnapshotdate]= dataLatestRemove.[InventoryDate]
AND dataReferenceRemove.[Name]= dataLatestRemove.[Name]
AND dataReferenceRemove.[Version]= dataLatestRemove.[Version])
? Setting where statement to pull out the entries that were removed.
? Ended derived table named "ih" which contains the data for added and removed software.
WHERE
dataLatestRemove.[Snapshotid] is null) ih
ON vc.[Guid] = ih.[_ResourceGuid]
? Joining collectionmembership table as "cm"
JOIN
dbo.CollectionMembership cm
ON vc.[Guid] = cm.[ResourceGuid]
? Setting up the where statement for criteria based on my report.
WHERE
cm.CollectionGuid = '%Collection%'
AND vc.[Domain] LIKE '%Domain%'
AND vc.[Name] LIKE '%ComputerName%'
? Setting up order.
ORDER BY
vc.[Domain],
vc.[Name],
ih.[Software]
There you have it. A report that will show you changes to Add Remove software using a history table. The report created with the SQL above can be obtained at ftp://ftp2.altiris.com/files/CustomInvReports/Windows/SoftwareApplications/AddRemove%20Programs/.
The same logic can be applied to any history table of your choosing and is not limited to Inventory tables though it is most useful to inventory tables. In addition, it is important to keep in mind the purge settings you have set. This type of report is most useful when saved for historical purposes. I have edited the above statement to run for older than 7 days on my specific domain and set a schedule to run my report weekly. I then purge my tables monthly.