Video Screencast Help

Tracking Changes in Inventory Solution Data Using History Tables

Created: 27 Jun 2007 • Updated: 20 Jul 2007 | 2 comments
Language Translations
snowjgj's picture
0 0 Votes
Login to vote

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/Wind....

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.

Comments 2 CommentsJump to latest comment

Joel Smith's picture

Could there be a 'Part 2' that gives a good example on how to take the existing report and convert it to a different database table?

I think that would be useful, especially if the examples are common items like the Memory or CPU data classes.

Regards,

Joel Smith
Altiris Support
Principle Support Engineer

Joel Smith
Symantec Services Group (SSE)
Sr. Principal Support Engineer

+1
Login to vote
Pascal KOTTE's picture

Hi, nice article, concerns CMS 6 I think - With CMS 7, we have an additional table

  • History Delta Table Name

Perhaps, at last, Altiris will stop storing all collected data in history, all the same was not changing from previous information collected !!!

Because, History can not be used in a large environment for "often" collected data.
That's why the "delta inventory" is really important, to avoid overloading your history tables.

I see CMS6 customer with 200GB history 1 table (for getting "add-remove" data history on 6 months).
But as used for "dynamic" software deployment, customer was collecting the hardware inventory every hour.
CMS 7 Should mitigate this kind of issue & should be a must for them. ;-)

Hope the new table I see is working better, any Article writer candidate to share knowledge regarding the new History Design with CMS7 ?

~Pascal @ Kotte.net~ Do you speak French? Et utilisez Altiris: venez nous rejoindre sur le GUASF

+1
Login to vote