Backdate the Qualys Compliance Control watermark

Products

• Information Centric Analytics

Issue/Introduction

You have added a new policy to a Security Configuration Assessment in Qualys but a control attached to that policy has not been updated recently. Because the control had not been imported previously, it is missing even after running the the Qualys Import Utility because the API option updated_after_datetime is passing a timestamp later than the last updated date for the control.

What method can be used to force the Qualys Import Utility to backdate this timestamp in order to pull the missing control?

Environment

Version : 6.x

Component : Qualys Import Utility

Resolution

To backdate the updated_after_datetime timestamp, set the LastRetrievalStarted value in the Application_Statistics table within the QualysGuardDW relational database to a date predating the last date on which the compliance control was updated and increase the ComplianceControlMaxHistoryDays in the ApiOptions table to accommodate a range going back to that date.

1. Open SQL Server Management Studio (SSMS)
2. Connect to the Database Engine hosting the QualysGuardDW relational database
3. From the File menu, select New > Query with Current Connection
   A new query editor window opens
4. Copy the following statement and paste it in the new query editor window:
   

   USE QualysGuardDW;
   GO
   
   UPDATE  dbo.Application_Statistic
   SET     LastRetrievalStarted = <date>
   WHERE   ID = 4;

   NOTE: Replace <date> with a date predating the last date on which the control was updated
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.ApiOptions
   SET     OptionValue = <days>
   WHERE   OptionField = N'ComplianceControlMaxHistoryDays';

   NOTE: Replace <days> with a value equal to or greater than the number of days since the date set in the statement in step 4 and today's date
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

Additional Information

The following is an example of the API call with the updated_after_datetime timestamp:

https://qualysapi.qualys.com/api/2.0/fo/compliance/control/?action=list&updated_after_datetime=2025-02-23T12:31:46Z&details=All&truncation_limit=25000