Vulnerability Aging Summary Query

Hello All,

I am trying to pull high level numbers from our datawarehouse and was wondering if there was a way to optimize my sql query. I am trying to pull a count of vulnerabilities within a specific CVSS score with assets scanned within a specific timeline and the vulnerabilities first found within a specific timeline. Very similar to how the dashboards are set up in insightvm. I have a SQL query attached below of what I am trying to do, but instead of using smaller SQL queries I am trying to combine them. Examples of what I am trying to pull are CVSS score 9-10 first found 0-29 days, CVSS score 9-10 first found 30-59 days and etc.

SELECT dag.name AS asset_group, COUNT(dv.vulnerability_id) AS crits_0_29 FROM dim_asset_group dag JOIN dim_asset_group_asset USING (asset_group_id) JOIN dim_asset USING (asset_id) JOIN fact_asset_vulnerability_finding favf USING (asset_id) JOIN dim_vulnerability dv USING (vulnerability_id) WHERE dag.name like (’’) AND last_assessed_for_vulnerabilities >= now() - interval ‘30 days’ AND favf.date >= now() - interval ‘29 days’ AND (GREATEST(dv.cvss_score, dv.cvss_v3_score) BETWEEN 9.0 AND 10.0) GROUP BY dag.name

Thanks in advance,
Jacob

1 Like

I think if you’re trying to combine them, you could remove the AS crits_0_29 and potentially use CASE statements instead to populate a new column for each grouping of vulnerabilities that you’re pulling. There’s a good CASE example here. Then you could expand your WHERE to have additional conditions based on the time range and CVSS that you specify. Here’s an example that shows this while using parentheses to make sure you get the order of operations correct.

Here’s a good example of what I think that you’re trying to accomplish. It provides an aging summary similar to the across all asset groups, with vulnerabilities broke out into critical, severe, and moderate by their age in days. I wrote this for the Reporting Data Model, but it may work for the Data Warehouse. (I haven’t tested that though).

WITH
   vuln_result_categorization AS (
      SELECT asset_id,
         CASE WHEN (age_in_days <= 30 AND severity_score < 4) THEN 1 ELSE 0 END AS "<30m",
         CASE WHEN (age_in_days <= 30 AND severity_score >= 4 AND severity_score < 7) THEN 1 ELSE 0 END AS "<30s",
         CASE WHEN (age_in_days <= 30 AND severity_score >= 7) THEN 1 ELSE 0 END AS "<30c",
         CASE WHEN (age_in_days <= 60 AND age_in_days >= 31 AND severity_score < 4) THEN 1 ELSE 0 END AS "31-60m",
         CASE WHEN (age_in_days <= 60 AND age_in_days >= 31 AND severity_score >= 4 AND severity_score < 7) THEN 1 ELSE 0 END AS "31-60s",
         CASE WHEN (age_in_days <= 60 AND age_in_days >= 31 AND severity_score >= 7) THEN 1 ELSE 0 END AS "31-60c",
         CASE WHEN (age_in_days <= 90 AND age_in_days >= 61 AND severity_score < 4) THEN 1 ELSE 0 END AS "61-90m",
         CASE WHEN (age_in_days <= 90 AND age_in_days >= 61 AND severity_score >= 4 AND severity_score < 7) THEN 1 ELSE 0 END AS "61-90s",
         CASE WHEN (age_in_days <= 90 AND age_in_days >= 61 AND severity_score >= 7) THEN 1 ELSE 0 END AS "61-90c",
         CASE WHEN (age_in_days > 90 AND severity_score < 4) THEN 1 ELSE 0 END AS "91+m",
         CASE WHEN (age_in_days > 90 AND severity_score >= 4 AND severity_score < 7) THEN 1 ELSE 0 END AS "91+s",
         CASE WHEN (age_in_days > 90 AND severity_score >= 7) THEN 1 ELSE 0 END AS "91+c"
      FROM (
         SELECT fav.asset_id, fav.vulnerability_id, fava.age_in_days, dv.severity_score
         FROM fact_asset_vulnerability_finding fav
            JOIN dim_vulnerability dv ON fav.vulnerability_id = dv.vulnerability_id
            JOIN fact_asset_vulnerability_age fava ON fava.asset_id = fav.asset_id AND fava.vulnerability_id = fav.vulnerability_id
         GROUP BY fav.asset_id, fav.vulnerability_id, dv.date_published, dv.severity_score, fava.age_in_days
      ) avd
   )
SELECT dag.name AS asset_group,
   fag.assets AS asset_count,
   fag.vulnerabilities AS vulnerabilities,
   SUM("<30m") AS "<30_moderate",
   SUM("31-60m") AS "31-60_moderate",
   SUM("61-90m") AS "61-90_moderate",
   SUM("91+m") AS "91+_moderate",
   SUM("<30s") AS "<30_severe",
   SUM("31-60s") AS "31-60_severe",
   SUM("61-90s") AS "61-90_severe",
   SUM("91+s") AS "91+_severe",
   SUM("<30c") AS ">30_critical",
   SUM("31-60c") AS "31-60_critical",
   SUM("61-90c") AS "61-90_critical",
   SUM("91+c") AS "91+_critical"
FROM (
   SELECT da.asset_id, 0 AS "<30m", 0 AS "<30s", 0 AS "<30c", 0 AS "31-60m", 0 AS "31-60s", 0 AS "31-60c", 0 AS "61-90m", 0 AS "61-90s", 0 AS "61-90c", 0 AS "91+m", 0 AS "91+s", 0 AS "91+c"
   FROM dim_asset da
   WHERE da.asset_id IN (SELECT asset_id FROM vuln_result_categorization)
   UNION ALL
   SELECT *
   FROM vuln_result_categorization
) v
JOIN dim_asset_group_asset daga ON daga.asset_id = v.asset_id
JOIN dim_asset_group dag ON daga.asset_group_id = dag.asset_group_id
JOIN fact_asset_group fag ON daga.asset_group_id = fag.asset_group_id
WHERE dag.asset_group_id IN (SELECT asset_group_id FROM dim_scope_asset_group)
GROUP BY dag.name, fag.assets, fag.vulnerabilities;

HI Tyler,

I need your help about this query :

SELECT ds.name, da.host_name, da.ip_address, dv.title, dv.severity, cast(to_char((da.last_assessed_for_vulnerabilities)::TIMESTAMP,‘dd/mm/yyyy’) as varchar) as last_assessed_for_vulnerabilities,
MAX( cast(fava.age_in_days as int) )as age_in_days, MIN( cast(to_char((fava.first_discovered)::TIMESTAMP,‘dd/mm/yyyy’) as varchar)) as first_discovered,
MAX (cast(to_char((fava.most_recently_discovered)::TIMESTAMP,‘dd/mm/yyyy’) as varchar)) as most_recently_discovered
FROM dim_asset da

 JOIN fact_asset_vulnerability_finding favf ON favf.asset_id = da.asset_id 
 JOIN dim_vulnerability dv ON favf.vulnerability_id = dv.vulnerability_id
 JOIN fact_asset_vulnerability_age fava ON fava.asset_id = favf.asset_id AND fava.vulnerability_id = favf.vulnerability_id
 JOIN dim_site_asset dsa ON dsa.asset_id = da.asset_id 
 JOIN dim_site ds ON dsa.site_id = ds.site_id  

GROUP BY ds.name, da.host_name, da.ip_address, dv.title, dv.severity, da.last_assessed_for_vulnerabilities

I would like to have this output :
site name, host_name, ip_address, title of vuln, severity of vuln, the date of the last assessed for vulnerabilities of my asset, the age of the vulnerability since the first discovery, the date of the first discovery of the vulerability attach to my asset, and the date of the most recently discovery of the vulnerability attach to my asset.

thank you very much for your help
Alain

Hi @cyber_security, I’m going to reply to your thread that you created. Do you mind removing this comment?

Yes thank you
it was a mistake.
When you want to go too fast :wink:

Hi @tyler_schmidtke

I had a question regarding the sql query you posted on here earlier. I am trying to configure it to get the numbers in the categories shown below with our specific asset groups. I so edited it to look like this, but keep running into errors. Saying I am missing some syntax with ‘(’ and that dv does not show up in the FROM section. Hopefully I can get it to work let me know what you think.

WITH
vuln_result_categorization AS (
SELECT asset_id,
CASE WHEN (age_in_days <= 30 AND GREATEST(dv.cvss_v3_score, dv.cvss_score)< 4) THEN 1 ELSE 0 END AS “<30L”,
CASE WHEN (age_in_days <= 30 AND GREATEST(dv.cvss_v3_score, dv.cvss_score)>= 4 AND GREATEST(dv.cvss_v3_score, dv.cvss_score)< 7) THEN 1 ELSE 0 END AS “<30M”,
CASE WHEN (age_in_days <= 30 AND GREATEST(dv.cvss_v3_score, dv.cvss_score) >= 7) AND GREATEST(dv.cvss_v3_score, dv.cvss_score) < 9) THEN 1 ELSE 0 END AS “<30H”,
CASE WHEN (age_in_days <=30 AND GREATEST(dv.cvss_v3_score, dv.cvss_score) >8.9) THEN 1 ELSE 0 END AS “<30C”,
CASE WHEN (age_in_days <= 60 AND age_in_days >= 31 AND GREATEST(dv.cvss_v3_score, dv.cvss_score)< 4) THEN 1 ELSE 0 END AS “31-60L”,
CASE WHEN (age_in_days <= 60 AND age_in_days >= 31 AND GREATEST(dv.cvss_v3_score, dv.cvss_score) >= 4 AND GREATEST(dv.cvss_v3_score, dv.cvss_score)< 7) THEN 1 ELSE 0 END AS “31-60M”,
CASE WHEN (age_in_days <= 60 AND age_in_days >= 31 AND GREATEST(dv.cvss_v3_score, dv.cvss_score)>= 7) AND GREATEST(dv.cvss_v3_score, dv.cvss_score) <9 THEN 1 ELSE 0 END AS “31-60H”,
CASE WHEN (age_in_days <=60 AND age_in_days >=31 AND GREATEST(dv.cvss_v3_score, dv.cvss_score)>= 8.9) THEN 1 ELSE 0 END AS “31-60C”,
CASE WHEN (age_in_days <= 90 AND age_in_days >= 61 AND GREATEST(dv.cvss_v3_score, dv.cvss_score)< 4) THEN 1 ELSE 0 END AS “61-90L”,
CASE WHEN (age_in_days <= 90 AND age_in_days >= 61 AND GREATEST(dv.cvss_v3_score, dv.cvss_score)>= 4 AND GREATEST(dv.cvss_v3_score, dv.cvss_score)< 7) THEN 1 ELSE 0 END AS “61-90M”,
CASE WHEN (age_in_days <= 90 AND age_in_days >= 61 AND GREATEST(dv.cvss_v3_score, dv.cvss_score)>= 7) AND GREATEST(dv.cvss_v3_score, dv.cvss_score) <9 THEN 1 ELSE 0 END AS “61-90H”,
CASE WHEN (age_in_days <=90 AND age_in_days >=61 AND GREATEST(dv.cvss_v3_score, dv.cvss_score)>= 8.9) THEN 1 ELSE 0 END AS “61-90C”,
CASE WHEN (age_in_days > 90 AND GREATEST(dv.cvss_v3_score, dv.cvss_score)< 4) THEN 1 ELSE 0 END AS “91+L”,
CASE WHEN (age_in_days > 90 AND GREATEST(dv.cvss_v3_score, dv.cvss_score)>= 4 AND severity_score < 7) THEN 1 ELSE 0 END AS “91+M”,
CASE WHEN (age_in_days > 90 AND GREATEST(dv.cvss_v3_score, dv.cvss_score)>= 7 AND GREATEST(dv.cvss_v3_score, dv.cvss_score)>=8.9 THEN 1 ELSE 0 END AS “91+H”,
CASE WHEN (age_in_days > 90 AND GREATEST(dv.cvss_v3_score, dv.cvss_score)>= 9 AND THEN 1 ELSE 0 END AS “91+C”
FROM (
SELECT fav.asset_id, fav.vulnerability_id, fava.age_in_days, GREATEST(dv.cvss_v3_score, dv.cvss_score)
FROM fact_asset_vulnerability_finding fav
JOIN dim_vulnerability dv ON fav.vulnerability_id = dv.vulnerability_id
JOIN fact_asset_vulnerability_age fava ON fava.asset_id = fav.asset_id AND fava.vulnerability_id = fav.vulnerability_id
GROUP BY fav.asset_id, fav.vulnerability_id, dv.date_published, GREATEST(dv.cvss_v3_score, dv.cvss_score), fava.age_in_days
) avd
)
SELECT dag.name AS asset_group,
fag.assets AS asset_count,
fag.vulnerabilities AS vulnerabilities,
SUM("<30C") AS “<30_Critical”,
SUM(“31-60C”) AS “31-60_Critical”,
SUM(“61-90C”) AS “61-90_Critical”,
SUM(“91+C”) AS “91+_Critical,
SUM(”<30H") AS “<30_High”,
SUM(“31-60H”) AS “31-60_High”,
SUM(“61-90H”) AS “61-90_High”,
SUM(“91+H”) AS “91+_High,
SUM(”<30M") AS “<30_Med”,
SUM(“31-60M”) AS “31-60_Med”,
SUM(“61-90M”) AS “61-90_Med”,
SUM(“91+M”) AS “91+_Med”,
SUM("<30L") AS “<30_Low”,
SUM(“31-60L”) AS “31-60_Low”,
SUM(“61-90L”) AS “61-90_Low”,
SUM(“91+L”) AS “91+_Low”
FROM (
SELECT da.asset_id, 0 AS “<30m”, 0 AS “<30s”, 0 AS “<30c”, 0 AS “31-60m”, 0 AS “31-60s”, 0 AS “31-60c”, 0 AS “61-90m”, 0 AS “61-90s”, 0 AS “61-90c”, 0 AS “91+m”, 0 AS “91+s”, 0 AS “91+c”
FROM dim_asset da
WHERE da.asset_id IN (SELECT asset_id FROM vuln_result_categorization)
UNION ALL
SELECT *
FROM vuln_result_categorization
) v
JOIN dim_asset_group_asset daga ON daga.asset_id = v.asset_id
JOIN dim_asset_group dag ON daga.asset_group_id = dag.asset_group_id
JOIN fact_asset_group fag ON daga.asset_group_id = fag.asset_group_id
WHERE dag.asset_group_id IN (SELECT asset_group_id FROM dim_scope_asset_group)
GROUP BY dag.name, fag.assets, fag.vulnerabilities;

image

Thanks,
Jacob

There were a couple missing ) and a couple missing ". I might recommend using something like https://www.pgadmin.org/ to help with finding that kind of stuff with syntax highlighting (especially if you’re using the data warehouse). Additionally, one thing that needed to be updated was the main SELECT:

SELECT da.asset_id, 0 AS “<30m”, 0 AS “<30s”, 0 AS “<30c”, 0 AS “31-60m”, 0 AS “31-60s”, 0 AS “31-60c”, 0 AS “61-90m”, 0 AS “61-90s”, 0 AS “61-90c”, 0 AS “91+m”, 0 AS “91+s”, 0 AS “91+c”

Note that it doesn’t match the column names in the subquery. The names need to match (as you’ll see below), but that was a separate issue to the missing quotes/parens. Here’s an updated one that should work.

WITH vuln_result_categorization AS (
    SELECT asset_id,
           CASE WHEN (age_in_days <= 30 AND greatest_cvss_score < 4) THEN 1 ELSE 0 END AS "<30L",
           CASE
               WHEN (age_in_days <= 30 AND greatest_cvss_score >= 4 AND greatest_cvss_score < 7) THEN 1
               ELSE 0 END                                                              AS "<30M",
           CASE
               WHEN (age_in_days <= 30 AND greatest_cvss_score >= 7) AND greatest_cvss_score < 9 THEN 1
               ELSE 0 END                                                              AS "<30H",
           CASE
               WHEN (age_in_days <= 30 AND greatest_cvss_score > 8.9) THEN 1
               ELSE 0 END                                                              AS "<30C",
           CASE
               WHEN (age_in_days <= 60 AND age_in_days >= 31 AND greatest_cvss_score < 4) THEN 1
               ELSE 0 END                                                              AS "31-60L",
           CASE
               WHEN (age_in_days <= 60 AND age_in_days >= 31 AND greatest_cvss_score >= 4 AND greatest_cvss_score < 7)
                   THEN 1
               ELSE 0 END                                                              AS "31-60M",
           CASE
               WHEN (age_in_days <= 60 AND age_in_days >= 31 AND greatest_cvss_score >= 7) AND greatest_cvss_score < 9
                   THEN 1
               ELSE 0 END                                                              AS "31-60H",
           CASE
               WHEN (age_in_days <= 60 AND age_in_days >= 31 AND greatest_cvss_score >= 8.9) THEN 1
               ELSE 0 END                                                              AS "31-60C",
           CASE
               WHEN (age_in_days <= 90 AND age_in_days >= 61 AND greatest_cvss_score < 4) THEN 1
               ELSE 0 END                                                              AS "61-90L",
           CASE
               WHEN (age_in_days <= 90 AND age_in_days >= 61 AND greatest_cvss_score >= 4 AND greatest_cvss_score < 7)
                   THEN 1
               ELSE 0 END                                                              AS "61-90M",
           CASE
               WHEN (age_in_days <= 90 AND age_in_days >= 61 AND greatest_cvss_score >= 7) AND greatest_cvss_score < 9
                   THEN 1
               ELSE 0 END                                                              AS "61-90H",
           CASE
               WHEN (age_in_days <= 90 AND age_in_days >= 61 AND greatest_cvss_score >= 8.9) THEN 1
               ELSE 0 END                                                              AS "61-90C",
           CASE WHEN (age_in_days > 90 AND greatest_cvss_score < 4) THEN 1 ELSE 0 END  AS "91+L",
           CASE
               WHEN (age_in_days > 90 AND greatest_cvss_score >= 4 AND greatest_cvss_score < 7) THEN 1
               ELSE 0 END                                                              AS "91+M",
           CASE
               WHEN (age_in_days > 90 AND greatest_cvss_score >= 7 AND greatest_cvss_score >= 8.9) THEN 1
               ELSE 0 END                                                              AS "91+H",
           CASE WHEN (age_in_days > 90 AND greatest_cvss_score >= 9) THEN 1 ELSE 0 END AS "91+C"
    FROM (
             SELECT fav.asset_id,
                    fav.vulnerability_id,
                    fava.age_in_days,
                    GREATEST(dv.cvss_v3_score, dv.cvss_score) AS greatest_cvss_score
             FROM fact_asset_vulnerability_finding fav
                      JOIN dim_vulnerability dv ON fav.vulnerability_id = dv.vulnerability_id
                      JOIN fact_asset_vulnerability_age fava
                           ON fava.asset_id = fav.asset_id AND fava.vulnerability_id = fav.vulnerability_id
             GROUP BY fav.asset_id, fav.vulnerability_id, dv.date_published, GREATEST(dv.cvss_v3_score, dv.cvss_score),
                      fava.age_in_days
         ) avd
)

SELECT dag.name            AS asset_group,
       fag.assets          AS asset_count,
       fag.vulnerabilities AS vulnerabilities,
       SUM("<30C")         AS "<30_Critical",
       SUM("31-60C")       AS "31-60_Critical",
       SUM("61-90C")       AS "61-90_Critical",
       SUM("91+C")         AS "91+_Critical",
       SUM("<30H")         AS "<30_High",
       SUM("31-60H")       AS "31-60_High",
       SUM("61-90H")       AS "61-90_High",
       SUM("91+H")         AS "91+_High",
       SUM("<30M")         AS "<30_Med",
       SUM("31-60M")       AS "31-60_Med",
       SUM("61-90M")       AS "61-90_Med",
       SUM("91+M")         AS "91+_Med",
       SUM("<30L")         AS "<30_Low",
       SUM("31-60L")       AS "31-60_Low",
       SUM("61-90L")       AS "61-90_Low",
       SUM("91+L")         AS "91+_Low"
FROM (
         SELECT da.asset_id,
                0 AS "<30L",
                0 AS "<30M",
                0 AS "<30H",
                0 AS "<30C",
                0 AS "31-60L",
                0 AS "31-60M",
                0 AS "31-60H",
                0 AS "31-60C",
                0 AS "61-90L",
                0 AS "61-90M",
                0 AS "61-90H",
                0 AS "61-90C",
                0 AS "91+L",
                0 AS "91+M",
                0 AS "91+H",
                0 AS "91+C"
         FROM dim_asset da
         WHERE da.asset_id IN (SELECT asset_id FROM vuln_result_categorization)
         UNION ALL
         SELECT *
         FROM vuln_result_categorization
     ) v
         JOIN dim_asset_group_asset daga
              ON daga.asset_id = v.asset_id
         JOIN dim_asset_group dag ON daga.asset_group_id = dag.asset_group_id
         JOIN fact_asset_group fag ON daga.asset_group_id = fag.asset_group_id
WHERE dag.asset_group_id IN (SELECT asset_group_id FROM dim_scope_asset_group)
GROUP BY dag.name, fag.assets, fag.vulnerabilities;

@tyler_schmidtke

Thank you so much for you help. That pgadmin is a great find. Looking to get the pgAgent now on our Postgres DB for automated jobs. I had one more question. Currently I am using this sql query in the console. Before with the Data warehouse I pulled our weekly patch numbers with the fact_asset_vulnerability_finding _remediation_date section with day being set to seven and counting all vulnerability_ids. Is this possible to add to this query? I see we are using fact_asset_vulnerability_finding not sure if that makes a difference. Thanks again for all of the help.

The remediation table is only available in the data warehouse. Unfortunately, there isn’t really an easy way to get the same information in the reporting data model within the console. The fact_asset_vulnerability_finding table will include the findings, but not whether a vulnerability was remediated.

@tyler_schmidtke That’s okay thanks again for all of the help!