SQL Query Export - Search for specific software across assets

InsightVM SQL Queries
,
1

So far I have the below SQL Query that creates a report for a specific software that is installed across all assets and reports other various information as well. I am having trouble trying to figure out how to add to this query to allow the ability to search for multiple-named software’s rather than just one, and have tried using AND statements and adding WHERE but has not worked as of yet.

SELECT DISTINCT ON (da.ip_address) da.ip_address AS “IP”, da.host_name AS “Hostname”, da.asset_id AS “Asset ID”, ds.name as “SoftwareName”, ds.version AS “SoftwareVersion”, date(da.last_assessed_for_vulnerabilities) AS “LastScan”
FROM dim_asset_software das

JOIN fact_asset fa USING (asset_id)
JOIN dim_software ds using (software_id)
JOIN dim_asset da on da.asset_id = das.asset_id
WHERE ds.name ~* ‘steam’
ORDER BY da.ip_address, da.asset_id, ds.version, da.last_assessed_for_vulnerabilities DESC

2

Can you replace the WHERE line with something below:

WHERE ds.name = '%steam'
1 Like
3

This works as well, but the query alone already works but it only allows me to be able to search across all assets for one software when I want to be able to search for several at once. Ive seen that I can use the AND function and HAVING function but so far, they havent worked yet or im not implementing properly

4

You can use the OR function in SQL

WHERE ds.name = '%steam' OR ds.name = 'xyz' OR ds.name = 'abc'
2 Likes
5

That is a good command too but sadly it doesnt do the job either on my part, it just ends up not bringing up any results. What I need to try and do is get my query to where if can make 2 pages or more on the report so that each page can display another software. The issue with my current query is that it has the functionality to only bring up one page/search on the report and not multiple ones