As I mentioned in Part I, 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 article is meant to teach people who completed part one to change their report for use monitoring changes to specific inventory information for their resources in a non-software table. In this article we will discuss enabling and using history tables as well as reporting changes to resource inventories using the Notification Servers history tables. For this test we will monitor changes in Memory for all resources.
First, lets enable the history for AeX HW Memory.
To enable the history tables for specific data classes in the console browse to:
"Configuration -> Server Settings -> Notification Server Settings -> Resource History -> Hardware -> AeX HW Memory"
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.
A review of how history tables work. If you have recently read Part I, please skip below.
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_*.
Note: In the previous article I mentioned the problem with history tables. The memory table rarely changes, however, and would be unlikely to cause problems. Feel free to set the purge maintanance schedule for a longer period for this table if necessary.
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 by default.
So we now know that we have a history enabled for the "AeX HW Memory" data class. I have also had a computer send in an inventory and lines have been written to the history table.
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 as we did with the Software tables.
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_HW_Memory
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.[Total Physical Memory(MB)]
? 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, and the total memory at that date.
? 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 memory 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.[Total Physical Memory (MB)] AS 'Total Physical Memory(MB)'
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_HW_Memory
? 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_HW_Memory
? 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_HW_Memory based on the latest snapshot date being in the derived table we have created.
JOIN
InvHist_AeX_HW_Memory dataLatestAdd
ON (datesAdd.[_ResourceGuid] = dataLatestAdd.[_ResourceGuid]
AND datesAdd.[LatestSnapshotDate] = dataLatestAdd.[InventoryDate])
-- Left joining InvHist_AeX_HW_Memory on multiple fields such as date, name, and total memory.
LEFT JOIN
InvHist_AeX_HW_Memory dataReferenceAdd
ON (datesAdd.[_ResourceGuid] = dataReferenceAdd.[_ResourceGuid]
AND datesAdd.[ReferenceSnapshotdate]= dataReferenceAdd.[InventoryDate]
AND dataLatestAdd.[Name]= dataReferenceAdd.[Total Physical Memory(MB)]
)
? 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 memory 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',
dataLatestAdd.[Total Physical Memory (MB)] AS 'Total Physical Memory(MB)'
FROM
(SELECT
latestRemove.[_ResourceGuid],
latestRemove.[LatestSnapshotDate],
referenceRemove.[ReferenceSnapshotDate]
FROM
(SELECT
[_ResourceGuid]
,MAX([InventoryDate]) AS LatestSnapshotDate
FROM
InvHist_AeX_HW_Memory
WHERE
DATEDIFF(dd, [InventoryDate], getdate()) <= %Last n days%
GROUP BY
[_ResourceGuid]
) latestRemove
JOIN
(SELECT
[_ResourceGuid]
,MAX([InventoryDate]) AS [ReferenceSnapshotDate]
FROM
InvHist_AeX_HW_Memory
WHERE
DATEDIFF(dd, [InventoryDate], getdate()) > %Last n days%
GROUP BY
[_ResourceGuid]
) referenceRemove
ON latestRemove.[_ResourceGuid] = referenceRemove.[_ResourceGuid]
) datesRemove
JOIN
InvHist_AeX_HW_Memory dataReferenceRemove
ON (datesRemove.[_ResourceGuid] = dataReferenceRemove.[_ResourceGuid]
AND datesRemove.[ReferenceSnapshotDate] = dataReferenceRemove.[InventoryDate])
LEFT JOIN
InvHist_AeX_HW_Memory dataLatestRemove
ON (datesRemove.[_ResourceGuid] = dataLatestRemove.[_ResourceGuid]
AND datesRemove.[LatestSnapshotdate]= dataLatestRemove.[InventoryDate]
AND dataReferenceRemove.[Total Physical Memory(MB)]= dataLatestRemove.[Total Physical Memory(MB)]
)
? Setting where statement to pull out the entries that were removed.
? Ended derived table named "ih" which contains the data for added and removed memory.
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.[Total Physical Memory(MB)]
Above is the SQL for the report for changes in memory data. You will now need to create variables in the report for Domain, Computer Name, Collection, and Last N days. As this article is concerning the SQL to tracking inventory changes and not to teach Report building, it is assumed that knowledge on how to create the report is already known.
So in review, the main changes we have made to make this report work from the old report is:
- Enable the history table.
- Edit the report and replaced all occurences of ih.[name] with ih.[Total Physical Memory(MB)] and removed the ih.[version].
- Replaced all occurrences of InvHist_AeX_OS_Add_Remove_Programs with InvHist_AeX_HW_Memory.
You now have a working report which will track changes to Memory in the last N days.