Vulnerability response: Importing from tenable.sc using tenable provided connector has become slow.

[stevemac]

Instance: San Diego Patch 9a, using tenable 3.x connector

Our vulnerability imports from tenable.sc have failed for the last 3 days.  We increased timeout today from 300 secs to 600 secs and managed to get though the import and processing of the cumulative and patched updates, but are yet to work out why the change in behaviour.

we have observed the following

• no delay in the REST call to our tenable.sc environment. 
  • the ECC queue output record is processed almost immediately
  • the corresponding ECC queue input record creates about 10 seconds later and the attachment containing the vulnerabilities looks OK
• There appears to be a delay updating the tenable job chunk record, including the processing of the ECC queue input record attachment (payload.txt) and attaching the output to the tenable job chunk record (as an example JOB0007631-15.json).   Time varies from a couple of mins to almost 10 mins.
• Once all tenable job chunks are processed, the creation of import sets and import set row tables is very quick, as are their transforms.

Working through the tenable code / business rules / jobs is difficult.  Hoping someone can assist with the following

• what is processing the ECC queue input record?  I believe it gets triggered from scheduled job JOB:VR:P:0060: Process SC Vulnerability Queue, but not sure which script include / function is doing the actual work.
• Where is the code for the processing of the payload.txt file into the json file?
• any thoughts on what might be causing the increase in processing time (as job chunk size has not changed and expect the a payload contents from tenable to be similar)?  Note I have logged a case with ServiceNow asking about the async business rules and how to report on queue depth / delays actioning them at a point in time

thanks,

Steve

[stevemac · Accepted solution]

we got to the bottom of this and addressed it by adding an index to the Sources [sys_object_source] table on id, last_scan.

The tenable job that processes the ECC input queue record inserts and / or reads records in the Sources [sys_object_source] table.  It does this in the process of taking the payload on the ECC input record and converting it to what's attached to the job chunk record.  It was querying with fields id, last_scan and strangely not name.

In our case the Sources [sys_object_source] table has had a significant increase in records since we enabled ITOM.  Lack of an appropriate index caused the query time to blow out to 1600ms.  Adding the index reduced it to sub-millisecond.