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

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.

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

(The "Add Argument" window will appear)

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

- 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...":

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

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

- 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: