We are creating some report about our scan environment, and we need very simple information in 2023. We would like to find monthly how many assets did we discover or assess? I looked the executive summary report, and it has only assessed asset as an information. How can it be so difficult to find such a simple information, am I missing something? There is some SQL Query method, but I couldn’t find an applicable SQL query. I will appreciate any of your suggestions.
1 Like
I have been seeking for this information also. I have gone above my license usage, and I need to review the list of assessed assets so as to delete the duplicate in order to be within the license usage. Therefore, where can I find the list of my assessed assets? If there is an SQL Querry, I will be most grateful to get it.
Try this SQL query to report on assets:
SELECT da.asset_id, da.ip_address, CONCAT(split_part(da.ip_address,‘.’,1),‘.’,split_part(da.ip_address,‘.’,2),‘.’,split_part(da.ip_address,‘.’,3),‘.1’) AS Subnet, CONCAT(split_part(da.ip_address,‘.’,1),‘.’,split_part(da.ip_address,‘.’,2),‘.’,split_part(da.ip_address,‘.’,3),‘.255’) AS Subnet,da.host_name, string_agg(DISTINCT daia.ip_address, ', ') AS ip_addresses, string_agg(DISTINCT dah.host_name, ', ') AS host_names, dos.description, max(daos.certainty) AS max_certainty, da.sites, string_agg(DISTINCT dag.name, ', ') AS asset_groups, string_agg(DISTINCT dt.tag_name, ', ') AS tag, da.last_assessed_for_vulnerabilities, fad.last_discovered, fad.first_discovered, fa.aggregated_credential_status_id, dacs.aggregated_credential_status_description, fa.vulnerabilities, fa.critical_vulnerabilities, fa.severe_vulnerabilities
FROM dim_asset da
LEFT JOIN dim_asset_group_asset daga ON da.asset_id = daga.asset_id
LEFT JOIN dim_asset_group dag ON daga.asset_group_id = dag.asset_group_id
LEFT JOIN dim_tag_asset dta ON da.asset_id = dta.asset_id
LEFT JOIN dim_tag dt ON dta.tag_id = dt.tag_id
LEFT JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
LEFT JOIN dim_asset_operating_system daos ON daos.asset_id = da.asset_id
LEFT JOIN dim_asset_ip_address daia ON daia.asset_id = da.asset_id
LEFT JOIN dim_asset_host_name dah ON dah.asset_id = da.asset_idLEFT JOIN fact_asset_discovery fad ON fad.asset_id = da.asset_id
LEFT JOIN fact_asset fa ON fa.asset_id = da.asset_id
LEFT JOIN dim_aggregated_credential_status dacs ON dacs.aggregated_credential_status_id = fa.aggregated_credential_status_id
GROUP BY da.asset_id, da.ip_address, da.host_name, da.sites, last_assessed_for_vulnerabilities, dos.description, fad.last_discovered, fad.first_discovered, fa.aggregated_credential_status_id, dacs.aggregated_credential_status_description, fa.vulnerabilities, fa.critical_vulnerabilities, fa.severe_vulnerabilities
last_assessed_for_vulnerabilities
last_discovered
first_discovered
Values should be there and basing on that you should be able to calculate that.
There’s more than that in the report but I keep the rest in case it might be useful for anyone.
Thank you so much finally someone answered at least. I will try it and give a feedback.