Hello, the below sql query is the closest I can find for our use case. Basically I would like to pull out a couple assets for a client to see their results without showing other clients. The issue is from the scope from under reports doesn’t give an option for certain assets, and cannot work vice versa in the remote site in which pulls the recent scans. In this case I need to pull from an older scan to show that the assets had no vulnerabilities from a point in time.
SELECT
da.ip_address,
fa.asset_id as id,
fa.critical_vulnerabilities,
fa.severe_vulnerabilities,
fa.moderate_vulnerabilities,
fa.vulnerabilities as total_vulnerabilities,
da.host_name
FROM
fact_asset fa
JOIN dim_asset da USING (asset_id)
I’m not sure I understand your comment ‘and cannot work vice versa in the remote site in which pulls the recent scans’, so ignoring that for now…
There are a couple of options I can think of to get specific assets. Which you choose really depends on if this is a one-off request or something you need to repeat.
If it’s just a one-off you can add a line to your existing query that says WHERE da.host_name = ‘abcd’ or da.host_name = ‘efgh’. (or use IP address instead of hostname as required). If there are more than a couple assets you might want to look at using IN instead of =.
If this needs to be repeatable then you could create a tag with a name that makes sense to you, like ‘ClientA_report’ then assign that tag to the assets required. Then create an Asset Group with a dynamic criteria of ‘User-added custom tag’ IS, and then pick your tag from the dropdown. Then you can use that asset group in the criteria in the report. To rerun the reports with different assets you would then just unassign and reassign tags without needing to edit the report criteria or SQL.