Assets which does not have a specific software installed

InsightVM
1

Let’s say I want to identify on which assets chrome is not installed.
How can I change the below query (which returns the assets where it is installed) to get the assets where it is not installed?

SELECT da.ip_address, da.host_name, ds.vendor, ds.name as software_name, ds.family, ds.version
FROM dim_asset_software das
JOIN dim_software ds using (software_id)
JOIN dim_asset da on da.asset_id = das.asset_id
where ds.name ilike ‘%chrome%’

2

So most likely you would want to remove the family and version columns and do a string agg on the software name column to put ALL installed software into a single column and then do a NOT ilike ‘%chrome%’

3

I actually had that a bit wrong as the where statement would still preface the group by, so it might be better to create a temporary table first and then filter that out with a where statement. There’s probably other ways to do it as well but the below query should get you what you’re looking for.

WITH software as (
SELECT da.ip_address, da.host_name, string_agg(ds.name, ', ') as all_software
FROM dim_asset_software das
JOIN dim_software ds using (software_id)
JOIN dim_asset da on da.asset_id = das.asset_id
GROUP BY da.ip_address, da.host_name
)

SELECT ip_address, host_name
FROM software
WHERE all_software NOT ilike '%chrome%'

Let me know if there’s anything wrong with the query or if it identifies assets that do in fact have chrome installed.

2 Likes