Scans Manual Deprecation
The SQL script is for tagging all scans listed under CxArchiveHistory as successfully exported as deprecated.
Danger
This is an auxiliary script that can lead to destructive results and it must be used with extreme caution. If data retention executes after a scan is marked as deprecated, there is no way to rollback.
It also might cause performance degradation and it is advised to run the script during low performance periods, such as on a weekend.
Prerequisites
CxSAST with a version higher than 9.2 with CxArchive installed.
Access to CxSAST DB - User must have a SYSADMIN role, which must be mapped to the CxSAST database to be able to create tables, select/update/delete data from them, and alter the tables.
Recommendations
It is recommended to run the script as follows:
The script must be used manually after an archive job using CxArchive.
It marks the scans archived as deprecated.
Run the script in granular updates in batches, such as 1000 scans at a time. Otherwise the script will attempt to update millions of lines at one time, which can be risky.
Additional information: The script is not part of CxArchive solution but rather a complement.
Execute the following script:
UPDATE top (1000) TS
SET TS.is_deprecated=1
FROM [CxDB].[dbo].[TaskScans] TS
INNER JOIN [CxDB].[CxArchive].[ExportHistory] export
ON TS.Id=export.ScanId
WHERE TS.is_deprecated = 0
AND export.Jobresult = 0