Modify the Qualys Compliance Posture retrieval watermark

Products

• Information Centric Analytics

Issue/Introduction

How do I configure the Qualys Import Utility to retrieve Compliance policy records for dates that were previously skipped?

Resolution

The column PostureQueryRan in the table CompliancePolicy table in the QualysGuardDW database contains the watermark date provided for each Compliance posture request. To retrieve older posture data, update that column for each policy of interest by following this procedure:

1. Open SQL Server Management Studio (SSMS)
2. Connect to the Database Engine hosting the QualysGuardDW database
3. From the File menu, select New > Query with Current Connection
   A new query editor window will open
4. To retrieve a list of policies and their corresponding IDs, copy the following query and paste it in the new query editor window:

   SELECT Title, PolicyID FROM QualysGuardDW.dbo.CompliancePolicy;

5. Execute the statement by pressing the F5 key, clicking the Execute button in the SQL Editor toolbar, or selecting Execute from the Query menu
6. Copy the following statement and paste it in the new query editor window:
   

   USE QualysGuardDW;
   GO
   
   UPDATE  dbo.CompliancePolicy
   SET     PostureQueryRan = '<YYYY-MM-DD hh:mm:ss.mmm>'
   WHERE   PolicyID = <policy ID to update>;
   GO

   Replace <YYYY-MM-DD hh:mm:ss.mmm> with an ISO-8601 timestamp representing the date and time you wish the importer to retrieve. Replace <policy ID to update> with the ID of the policy you wish to backdate. To set the policy date for multiple policies simultaneously, replace the = operator with an IN operator, as follows:

   USE QualysGuardDW;
   GO
   
   UPDATE  dbo.CompliancePolicy
   SET     PostureQueryRan = '<YYYY-MM-DD hh:mm:ss.mmm>'
   WHERE   PolicyID IN (<first policy ID>, <second policy ID>, <third policy ID>, <etc.>);
   GO

7. Execute the statement by pressing the F5 key, clicking the Execute button in the SQL Editor toolbar, or selecting Execute from the Query menu