This request probably is very simple, but I struggle with IVM sql reports. I feel to pull any ‘advanced’ info I am forced to use sql queries, and I am just not good at them or the IVM data structure.
So, I am looking for a basic report to query assets and report on any that have not been scanned within X amount of time. Really 7 days is fine.
I am also looking for a query to report on scan status of all assets. complete/failed/partial success etc.
Is sql needed for my requests? Am I overthinking this and there is an easier way? I was even thinking maybe just a dashboard report, but I ran through one and it was not very customizable and spit out too much data.
For the first ask of assets that have not been scanned in the last 7 days you could just use the query builder and the query shown in the screenshot below. You could load this query into a Dashboard card, Remediation project, Goal,or set up a recurring report.
For the credential portion you could also use the query builder to simply report on either credential success or failure but you would need two different queries to do so. There is no option for partial in this method.
However, if you actually want a report that spits out the Asset names all together with a column that specifically lists if their status was failed/success/partial success then SQL would be your best bet. There are a couple different dimension tables for authentication status though.
If the query builder isn’t sufficient let me know and I’ll see what I can work up for a SQL query to get you started.
When I setup the first query with >= /Now - P7D/ it gave me results including assets that were scanned today?
I changed it to <= /Now - P7D/ and it now is giving me assets older than 7 days (the most recent being 7/21/22).
Not sure why, as the below statement to me reads: asset last scan time is less than today - 7 days ago. I would think the ‘more’ operand would be needed.
Now that I think of it this makes sense, less than today = past, more than today = future. All good this is working!
@john_hartman But I would also love to see an SQL query for this too, because I would like to see the various statuses for each asset i.e. failed/partial, etc.
Thank you John,
This query looks promising however it looks to be a query to pull authentication status. I was looking for a query to pull scan status of the asset. Is that possible via sql?
Sorry I’m a little confused on how you worded it originally saying complete/failed/partial success for a scan. To me that means authentication status.
What exactly do you mean by scan status? Are you referring to whether the asset has been discovered vs assessed? Or are you talking more about whether you ran a scan against a /24 for example and a specific asset came back as a dead asset although you know it was online?
I am ideally looking for the scan status of each asset (ideally from the most recent scan).
So was the asset scanned complete? Was the asset down (for example)? Was the asset scan partially complete (if that is even a thing).
Basically was the asset scanned or not?
Ahh ok that makes more sense. So yeah there is no partially complete status that I know of.
So unfortunately I don’t think we have anything in the SQL schema to report on those indicators per asset on a scan, only the scan as a whole. The only way that I know of for getting that type of data would be through the actual scan logs which will tell you if an asset was found as offline during the scan. There will be a specific log line of “DEAD ASSET”.
I can see how this extra metric could be useful though and it might be worthy of opening a product request for enhancement on.
So @john_hartman, can can I combine these to queries so they can be in one report?
This is my vulnerability query I use:
select da.ip_address, da.host_name, dv.nexpose_id, dv.title,
dv.cvss_score, dv.riskscore
from fact_asset_vulnerability_finding favf
join dim_asset da
on favf.asset_id = da.asset_id
join dim_vulnerability dv
on favf.vulnerability_id = dv.vulnerability_id
where dv.cvss_score > 8
and dv.riskscore > 900
group by da.ip_address, da.host_name, dv.nexpose_id, dv.title,
dv.cvss_score, dv.riskscore
order by dv.cvss_score DESC
Then I have the authentication query you helped me with:
SELECT ds.name “Site”, da.ip_address as “IP Address”, da.host_name as “Host Name”, dacs.aggregated_credential_status_description “Access Level”
FROM dim_asset da
JOIN fact_asset fa using (asset_id)
JOIN dim_site_asset dsa using (asset_id)
JOIN dim_site ds using (site_id)
JOIN dim_aggregated_credential_status dacs using (aggregated_credential_status_id)
GROUP BY ds.name, da.ip_address, da.host_name, dacs.aggregated_credential_status_description
ORDER BY da.ip_address DESC
How can I put this all together into one query? If possible?
SELECT
ds.name AS "Site",
da.ip_address AS "IP Address",
da.host_name AS "Host Name",
dv.nexpose_id AS "Nexpose ID",
dv.title AS "Vulnerability",
dv.cvss_score AS "CVSS Score",
dv.riskscore AS "Risk Score",
dacs.aggregated_credential_status_description as "Asset Access Level"
FROM fact_asset fa
JOIN dim_asset da ON fa.asset_id=da.asset_id
JOIN dim_site_asset dsa ON fa.asset_id=dsa.asset_id
JOIN dim_site ds ON dsa.site_id=ds.site_id
JOIN dim_aggregated_credential_status dacs ON fa.aggregated_credential_status_id=dacs.aggregated_credential_status_id
JOIN fact_asset_vulnerability_finding favf ON fa.asset_id=favf.asset_id
JOIN dim_vulnerability dv ON favf.vulnerability_id = dv.vulnerability_id
ORDER BY ds.name DESC, da.ip_address DESC, dv.title DESC
The dimension and fact tables that exist within our database. Theres even another page after that full of example queries.
Past those two documents just know that our database uses postgresql so any time you have a question in logic or anything just google away and make sure its psql specific.
Alot of it just comes with experience. The more queries you write the easier it gets. By now I mostly reuse a lot of the same stuff. And ofcourse always feel free to start a new topic here in the discuss forums anytime you get stuck.
