Converting a list of CVEs (or other vuln identifiers) to InsightVM Vulnerability IDs with ICON SQL and handlebars

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”

3 Likes

Getting error when validating this query.

@victor_beary Thanks for letting me know. I’ll updated the query in the original post as well but here’s the corrected query. The only difference is explicitly casting the array after ANY

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}}])

The query still gets a syntax error