Assessed Assets and discovered Assets

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.

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_id

LEFT 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.