Clean SVM's nsi_queue DB table [SVM On-Prem]

Clean SVM's nsi_queue DB table [SVM On-Prem]

Summary

This article explains in detail how to remove the entries from the 'Software Vulnerability Manager Agent scan queue' to clear up disk space and for other reasons you might want that. 

Synopsis

The SVM On-Prem solution uses a queue for handling incoming agent scans. Scans end up in this table before they are actually submitted to other tables in the database and thereby made visible in the SVM web interface. This database location can get congested and problems may result out of that. 

In the SVM edition for Red Hat Enterprise 7, there is a service named scandaemon which will remove the entries when the entry status is 4, indicating that the scan result has been submitted successfully. It is sometimes necessary to delete old entries from this "queue" due to e.g. lack of disk space.

Discussion

You can use the below queries to delete the information in the "queue" or identify an ID of an entry (row) from a time frame and delete the entries older than the specified time frame.

Workaround

NOTE: In the below information where nsi_results/nsi_queue is mentioned the appropriate table name must be selected according to OS version, e.g. nsi_results or nsi_queue.

First, find the relevant ca_xxxxx database (where xxxx is your customer ID)

show databases;
use ca_xxxx;

SQL Queries

To delete all entries (rows) in the queue:

#On RHEL 6 and RHEL 7 these may differ, use the right one for your setup

Truncate Table nsi_results;
Truncate Table nsi_queue;

To identify entry IDs within a certain time frame: 

 SELECT id, status_date FROM nsi_results/nsi_queue WHERE status_date > DATE_SUB(NOW(), INTERVAL 3 WEEK) ORDER BY ID asc LIMIT 10; 

This will list the first 10 entry IDs and related scan dates which are 3 weeks old, the INTERVAL can be increased or decreased as needed

To delete all entries older than the selected timeframe from the above SELECT query:

DELETE FROM nsi_results/nsi_queue WHERE status_date < DATE_SUB(NOW(), INTERVAL 3 WEEK);

This will delete all entries older than 3 weeks, the INTERVAL can be increased or decreased as needed.

Was this article helpful? Yes No
No ratings
Version history
Revision #:
2 of 2
Last update:
‎Sep 25, 2019 02:55 PM
Updated by:
 
Contributors