Vulnerability Aging

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 ran this on my end after fixing the single quotes (they pasted in as backticks so I might recommend looking at those on your end) and removed the WHERE to get data from my environment and the query returned the results that you’re expecting. Did you have another question about the query?

For reference, here’s the one I used (only change is formatting and no where clause):

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;

Thank you very much
It works very well !

Awesome! Sometimes copying and pasting SQL around the quotes can get messed up depending on what operating system that you’re using. That’s usually the first place I start. :slight_smile:

Hi Tyler,
I have another problem with another query. you will find below more information
Hi,

I would like to have a report with the minimum information concerning the conformity of the policies with my assets.

with this request, I have just one problem. it displays several results on rule compliance.

Could you please help me ?

SELECT
ds.name,
dp.title,
da.ip_address,
da.host_name,
dos.description,
dp.category,
fapr.date_tested,
fap.rule_compliance
FROM fact_asset_policy_rule fapr
JOIN dim_policy dp on dp.policy_id = fapr.policy_id
JOIN fact_asset_policy fap on fap.asset_id = fapr.asset_id
JOIN dim_asset da on da.asset_id = fapr.asset_id
JOIN dim_site_asset dsa ON dsa.asset_id = da.asset_id
JOIN dim_site ds ON ds.site_id = dsa.site_id
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
GROUP BY ds.name, dp.title, da.ip_address, da.host_name, dos.description, dp.category, fapr.date_tested, fap.rule_compliance;

result example
|name|title|ip_address|host_name|description|category|date_tested|rule_compliance|
|_BR - Windows NonProd|CIS Microsoft Windows Server 2016 RTM (Release 1607) Level One - Member Server v1.2.0|ip_address|host_name|Microsoft Windows Server 2016 Standard Edition 1607|CIS|2020-10-26 00:41:04.444|0,173611111|
|_BR - Windows NonProd|CIS Microsoft Windows Server 2016 RTM (Release 1607) Level One - Member Server v1.2.0|ip_address|host_name|Microsoft Windows Server 2016 Standard Edition 1607|CIS|2020-10-26 00:41:04.444|0,186851211|
|_BR - Windows UAT-I|CIS Microsoft Windows Server 2016 RTM (Release 1607) Level One - Member Server v1.2.0|ip_address|host_name|Microsoft Windows Server 2016 Standard Edition 1607|CIS|2020-10-26 00:41:04.444|0,173611111|
|_BR - Windows UAT-I|CIS Microsoft Windows Server 2016 RTM (Release 1607) Level One - Member Server v1.2.0|ip_address|host_name|Microsoft Windows Server 2016 Standard Edition 1607|CIS|2020-10-26 00:41:04.444|0,186851211|

Hey @cyber_security, please refrain from posting the same question repeatedly across multiple topics. Our team is doing its best to look into your question. :slight_smile: