I use this but it’s not as specific as what you’re asking but you can modify the report in Excel:
SELECT ds.name as software_name, ds.version, da.ip_address, da.host_name, ds.vendor, ds.family
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 software_name, ds.version, ds.vendor, ds.name, da.ip_address, da.host_name, ds.family, ds.cpe
ORDER BY software_name ASC
Were you ever able to figure out how to do this with the query builder? I’m running into the same issue. I have a SQL query that gets me the data, but I want to use query builder so I can use it in a remediation project.
I believe so. First, thank you @Nick. That SQL helped me over one of the hurdles I needed to get past, but for the larger project I really needed something in Advanced Query Builder. Regardless, the help is appreciated!
@cchad, I found adding all the devices to a asset group first helped a lot, so you’ll probably try that. For some reason this one worked with the parentheses, also I found “LIKE” worked a lot better than =, but still managed to be exact.
Here’s what that AQ looks like:
asset.groups IN [‘ASSET GROUP NAME’] && (asset.software.version LIKE ‘1.1’ || asset.software.version LIKE ‘1.2’ || asset.software.version LIKE ‘1.3’ || asset.software.version LIKE ‘1.4’ || asset.software.version LIKE ‘1.4.1’ || asset.software.version LIKE ‘1.4.12’ || asset.software.version LIKE ‘1.5’ || asset.software.version LIKE ‘1.51’ || asset.software.version LIKE ‘1.6’ || asset.software.version LIKE ‘1.8’ || asset.software.version LIKE ‘1.9’ || asset.software.version LIKE ‘1.91’)