Custom SQL for security metrics

InsightVM SQL Queries
1

I would like to use this sample SQL query with a few slight modifications: SQL example - new and remediated vulnerabilities | Nexpose Documentation

For this to be useful to me I need to be able to identify the asset owner by inlcuding the ‘OWNER’ tag and SITE. How would I include these fields?

Also, I don’t understand the current ‘datetime’ field format xx:xx.x (ex: 10:16.2) How do I convert these or use them to calculate a duration time (to mitigate the vulnerability)?

Thank you.

2

Hi Chad,

I use this code in some of our reports for the tags -

WITH
custom_tags AS (
SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS custom_tags
FROM dim_tag
JOIN dim_tag_asset USING (tag_id)
WHERE tag_type = ‘CUSTOM’
GROUP BY asset_id
),
location_tags AS (
SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS location_tags
FROM dim_tag
JOIN dim_tag_asset USING (tag_id)
WHERE tag_type = ‘LOCATION’
GROUP BY asset_id
),
owner_tags AS (
SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS owner_tags
FROM dim_tag
JOIN dim_tag_asset USING (tag_id)
WHERE tag_type = ‘OWNER’
GROUP BY asset_id
)

We don’t look at time taken to remediate in our MI reporting, what we look at is age of vulnerabilities by using this -

CASE
WHEN fava.age_in_days <= ‘14’ THEN ‘1-14 days’
WHEN fava.age_in_days <= ‘29’ THEN ‘15-29 days’
WHEN fava.age_in_days <= ‘59’ THEN ‘30-59 days’
WHEN fava.age_in_days <= ‘89’ THEN ‘60-89 days’
WHEN fava.age_in_days <= ‘120’ THEN ‘90-120 days’
WHEN fava.age_in_days <= ‘150’ THEN ‘121-150 days’
WHEN fava.age_in_days <= ‘180’ THEN ‘151-180 days’
ELSE ‘180+ days’
END AS “Age”,

Right JOIN fact_asset_vulnerability_age AS fava ON fava.asset_id = da.asset_id AND fava.vulnerability_id = fav.vulnerability_id

Hope that helps.

1 Like
3

Thanks Bruce. I will certainly use that part for the tags and I have another report that works for the age.

4

I’m wondering if anyone can explain the time component. It seems like the data is there but we need the duration to calculate the Mean Time To Mitigate (MTTM) which is a CIS-recommended metric that we are trying to implement.