Asset Management: How to query the database for agents that report a software signature XML file older than a specified number of days.

Document ID : KB000074317
Last Modified Date : 20/03/2018
Show Technical Document Details
Introduction:

The Asset Management agent plugin utilizes a signature-based software scanner to find software installed on your computer.  This scanner works much like a virus scanner, taking a signature file for input, and producing an output based on the matches that are found.

Periodically, the CA Content Team updates software signature files for Windows and Unix/Linux.  The Content Download Task, typically running on the SystemEngine on the Domain Manager (or Enterprise) is responsible for pulling down signature file updates and storing them in the database.

The Collect Tasks running on the engines, each one associated with a specific scalability server, are responsible for updating the signature file on the Scalability Servers.  During the "Validation" stage of the Collect Task, the signature files are updated.

When the AM agent plugin is triggered to run on an Agent, it connects to the Scalability Server and downloads a new signature file, if available.  This new signature file is then used for software detection.

As the transmission of new software signatures is driven through many workflows, from CA's Content Servers, all the way to each Client Automation Agent, the date (or age) of the software signature file is a good litmus test/confirmation whether or not all workflows are working properly in the environment.

Question:
How do I query the database for Agents with a software signature XML file older than a specified number of days?
Environment:
Client Automation (ITCM) -- Any version.
Answer:
Important: The following steps are only relevant to a Microsoft SQL Server database, as the document will take advantage of built-in SQL-transact functions for retrieving the current system time on the SQL server.

1- Create a new query in ITCM to return "Computer" objects:

User-added image

2- Select Inventory.Discovered Inventory from the query designer:

User-added image

(The "Add Argument" window will appear)

3- From the "Add Argument" window, select Information.Inventory.General Inventory.System Status.Software Signature File Delivery Date:

User-added image

- Change the "Operator" to "<".

- The "Value" is irrelevant as we will be manually modifying the SQL of the query to compare the reported time to the current time on the SQL server.

- Press "OK" to return to the query designer.

4- Right click on the item generated in the query designer and select, "Edit SQL...":

User-added image

5- Scroll to the right to locate the date inputted by the "Add Argument" window:

User-added image

6- Change the "hard-coded" date to read, "DATEDIFF(s, '19700101', GETUTCDATE()) - 864000":

User-added image

- By using the DATEDIFF() and GETUTCDATE() functions built-in to MS-SQL, we can compare the reported time of the file from the agent to the current time on the SQL server.

- The AM agent reports the delivery date of the signature XML file in GMT, hence we are using the GETUTCDATE() function from SQL to compare the current time in GMT.

- In this example, the constant, "864000", represents the number of seconds in ten (10) days of time. If you wish to query a shorter or longer interval, adjust this value for the number of seconds you desire for your interval.

- Press "OK" to return to the query designer.

7- You can "Preview" the results of the query and save with a meaningful query name:

User-added image

Additional Information:
Using the query created above, it is recommended to create a dynamic/query group, in which you can periodically monitor for violators, as a potential indication of problems with the workflow of the signature file.