Let’s say you have a list of CVEs and you want to see what assets are vulnerable to them in your environment. You have a few options - type each CVE into InsightVM and see what hosts are returned, use the Lookup Vulnerable Hosts from Slack workflow (https://extensions.rapid7.com/extension/Lookup_Vulnerable_Hosts_From_Slack), or build your own workflow to do it!
Now, I’m not wanting to reinvent the wheel here - the Lookup Vulnerable Hosts from Slack workflow (hereafter known as “source workflow”) already contains the logic and steps to convert one CVE to a list of InsightVM Vuln IDs. What I’m wanting to share is converting a list of CVEs in bulk!
The secret is using the ad-hoc sql report along with some fancy handle-bars to construct the query.
Here’s the query from our source workflow:
WITH prep AS (SELECT vulnerability_id,lower(reference) AS reference FROM dim_vulnerability_reference)
SELECT nexpose_id FROM prep JOIN dim_vulnerability USING (vulnerability_id) WHERE reference=lower(’{{[“Extract Vulnerability”].[vulnerability]}}’)
That’s great if we only have one CVE we’re looking for but what about a list? With a static list, our SQL should look like this:
WITH prep AS (SELECT vulnerability_id,lower(reference) AS reference FROM dim_vulnerability_reference)
SELECT nexpose_id FROM prep JOIN dim_vulnerability USING (vulnerability_id) WHERE reference ILIKE ANY (array[‘CVE1’,‘CVE2’,‘CVE3’])
So how do we get our InsightConnect Array into our SQL? Just inserting {{[“ourarray”].[array]}} won’t work - it’ll output a json array like [“CVE1”,“CVE2”,“CVE3”]. We could use string processing, replace the ‘[’ and ‘]’ with ‘(’ and ‘)’, then swap the quotes, but that’s an awful lot of steps, it’s confusing, and handlebars is such a better way!
With handlebars, we’re going to use a little trick of the #each helper - the array index variables:
{{#each [“ourarray”].[array]}}{{#unless @first}},{{/unless}}’{{this}}’{{/each}}
To translate this out into english (ish): For each element in our array, unless we are processing the first element in the array, render a , then for every element of the array render a ’ followed by the current array element, then another ’
Practical Example:
ourarray = {“array”: [“CVE1”,“CVE2”,“CVE3”]}
handlebars = {{#each [“ourarray”].[array]}}{{#unless @first}},{{/unless}}’{{this}}’{{/each}}
result = ‘CVE1’,‘CVE2’,‘CVE3’
That looks pretty darned close to what we need for our SQL doesn’t it? In fact, here’s the new SQL statement:
WITH prep AS (SELECT vulnerability_id,lower(reference) AS reference FROM dim_vulnerability_reference)
SELECT nexpose_id FROM prep JOIN dim_vulnerability USING (vulnerability_id) WHERE reference ILIKE ANY (array[{{#each [“ourarray”].[array]}}{{#unless @first}},{{/unless}}’{{this}}’{{/each}}])
Run that through an InsightVM Ad-Hoc report step and you’ll get a CSV file with all the InsightVM vuln-id’s that map to your list of CVEs! The remaining wiring is in our “source workflow”