Thank you very much @holly_wilsey for your suggestions.
After a minor rewrite we can confirm that the counts from PostgreSQL are now very close to those reported in insightVM. Please find the amended queries below:
–windows os–
select count(favf.vulnerability_id)
from fact_asset_vulnerability_finding favf
join dim_asset da using(asset_id)
join fact_asset_date fa using (asset_id)
join dim_vulnerability dv using(vulnerability_id)
join fact_vulnerability fv using (vulnerability_id)
join dim_asset_group_asset daga using(asset_id)
join dim_asset_group dag using(asset_group_id)
join dim_vulnerability_category dvc using(vulnerability_id)
where lower(dvc.category_name) in (‘microsoft’)
and lower(dvc.category_name) not in (‘microsoft sql server’, ‘microsoft .net framework’, ‘microsoft asp’, ‘microsoft exchange’, ‘microsoft sharepoint’, ‘microsoft asp.net’, ‘microsoft iis’, ‘microsoft msxml’, ‘microsoft xml’)
and dag.name = ‘Class - Windows - Wintel Team’
and fa.day IN (CURRENT_DATE-1)
and COALESCE(cvss_v3_score, cvss_score) < 9
–windows os critical–
select count(favf.vulnerability_id)
from fact_asset_vulnerability_finding favf
join dim_asset da using(asset_id)
join fact_asset_date fa using (asset_id)
join dim_vulnerability dv using(vulnerability_id)
join fact_vulnerability fv using (vulnerability_id)
join dim_asset_group_asset daga using(asset_id)
join dim_asset_group dag using(asset_group_id)
join dim_vulnerability_category dvc using(vulnerability_id)
where lower(dvc.category_name) in (‘microsoft’)
and lower(dvc.category_name) not in (‘microsoft sql server’, ‘microsoft .net framework’, ‘microsoft asp’, ‘microsoft exchange’, ‘microsoft sharepoint’, ‘microsoft asp.net’, ‘microsoft iis’)
and dag.name = ‘Class - Windows - Wintel Team’
and fa.day IN (CURRENT_DATE-1)
and COALESCE(cvss_v3_score, cvss_score) >= 9
–unix os–
select count(favf.vulnerability_id)
from fact_asset_vulnerability_finding favf
join dim_asset da using(asset_id)
join fact_asset_date fa using (asset_id)
join dim_vulnerability dv using(vulnerability_id)
join fact_vulnerability fv using (vulnerability_id)
join dim_asset_group_asset daga using(asset_id)
join dim_asset_group dag using(asset_group_id)
join dim_vulnerability_category dvc using(vulnerability_id)
where lower(dvc.category_name) in (‘red hat enterprise linux’,
‘centos’, ‘suse’, ‘ibm aix’, ‘snmp’, ‘ssh’, ‘samba’)
and dag.name = ‘Class - AIX - Linux - Unix Team’
and fa.day IN (CURRENT_DATE-1)
and COALESCE(cvss_v3_score, cvss_score) < 9
–unix os critical–
select count(favf.vulnerability_id)
from fact_asset_vulnerability_finding favf
join dim_asset da using(asset_id)
join fact_asset_date fa using (asset_id)
join dim_vulnerability dv using(vulnerability_id)
join fact_vulnerability fv using (vulnerability_id)
join dim_asset_group_asset daga using(asset_id)
join dim_asset_group dag using(asset_group_id)
join dim_vulnerability_category dvc using(vulnerability_id)
where lower(dvc.category_name) in (‘red hat enterprise linux’,
‘centos’, ‘suse’, ‘ibm aix’, ‘snmp’, ‘ssh’, ‘samba’)
and dag.name = ‘Class - AIX - Linux - Unix Team’
and fa.day IN (CURRENT_DATE-1)
and COALESCE(cvss_v3_score, cvss_score) >= 9
The only queries where we still see a (large) variance between PostgreSQL and insightVM are the so-called ‘3rd parties’. Please find the insightVM screen prints attached and the queries as follows:
–windows 3rd–
select count(favf.vulnerability_id)
from fact_asset_vulnerability_finding favf
join dim_asset da using(asset_id)
join fact_asset_date fa using (asset_id)
join dim_vulnerability dv using(vulnerability_id)
join fact_vulnerability fv using (vulnerability_id)
join dim_asset_group_asset daga using(asset_id)
join dim_asset_group dag using(asset_group_id)
join dim_vulnerability_category dvc using(vulnerability_id)
where lower(dvc.category_name) not in (‘microsoft’)
or lower(dvc.category_name) in (‘microsoft sql server’, ‘microsoft .net framework’, ‘microsoft asp’, ‘microsoft exchange’, ‘microsoft sharepoint’, ‘microsoft asp.net’, ‘microsoft iis’, ‘microsoft msxml’, ‘microsoft xml’)
and dag.name = ‘Class - Windows - Wintel Team’
and fa.day IN (CURRENT_DATE-1)
and COALESCE(cvss_v3_score, cvss_score) < 9
–unix 3rd–
select count(favf.vulnerability_id)
from fact_asset_vulnerability_finding favf
join dim_asset da using(asset_id)
join fact_asset_date fa using (asset_id)
join dim_vulnerability dv using(vulnerability_id)
join fact_vulnerability fv using (vulnerability_id)
join dim_asset_group_asset daga using(asset_id)
join dim_asset_group dag using(asset_group_id)
join dim_vulnerability_category dvc using(vulnerability_id)
where lower(dvc.category_name) not in (‘red hat enterprise linux’,
‘centos’, ‘suse’, ‘red hat’, ‘ibm aix’, ‘snmp’, ‘ssh’, ‘samba’)
and dag.name = ‘Class - AIX - Linux - Unix Team’
and fa.day IN (CURRENT_DATE-1)
and COALESCE(cvss_v3_score, cvss_score) < 9
FYI, PostgreSQL is reporting many more 3rd party vulnerabilities than insightVM and it is unclear why. Could you please have a look at these last two queries to help us determine why we end up with such a difference in counts? Could there maybe be some syntax issues with ‘NOT IN’ and ‘OR’ ( ‘||’) ?
That’s a bit tricky to figure out without seeing all the data in your environment. One of the things you could do to try to further troubleshoot is look at the data from several vulnerabilities that are included in the results of the SQL query, but are NOT included in the results of the query builder. To get that data, you could use the “select” statement from the suggested query that I originally posted and combine it with your own updated queries.