Is it possible to create a simple query based on specific software products? For example, could I create a report that shows asset detail for all devices running vulnerable instances of Chrome?
Tried writing the basic query below but running into errors. My Kung Fu is weak!
SELECT asset_id AS “ASSET ID”, proof AS “Proof”
FROM fact_vulnerability
JOIN fact_asset_vulnerability_instance (vulnerability_id)
JOIN dim_vulnerability (vulnerability_id)
WHERE title LIKE ‘%chrome%’
First thing I suggest is starting here with our example queries for software listings and then reading through the other docs about our available columns in the fact and dimension tables (which looks like you may have already referenced).
Otherwise you have the right idea for the query, just missing some important parts. For starters you can drop the fact_vulnerability table. Then I would give the tables aliases. Also I would join dim_asset so you can actually get the IP/hostname of the asset instead of trying to figure out the asset_id. Also keep in mind that the WHERE statement would be case sensitive. Below is essentially what you were aiming for.
SELECT da.ip_address AS "ASSET IP", da.host_name AS "Host Name", favi.proof AS "Proof"
FROM fact_asset_vulnerability_instance AS favi
JOIN dim_vulnerability AS dv USING (vulnerability_id)
JOIN dim_asset AS da USING (asset_id)
WHERE dv.title LIKE '%Chrome%'
Very helpful - thanks Josh!