Advanced query for specific versions of software?

I’m trying to create a simple query to locate devices with X software that has version A, the query looks like this:

asset.software.product CONTAINS ‘X’ && asset.software.version = ‘A’

It returns a list of devices with software X, and any software that has version A, not a list of devices with software X version A.

How do I make the query so that it checks for the specific version of only the software in question.

1 Like

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’)

1 Like