Installed Java Software query

InsightVM SQL Queries
1

I’m looking to get Asset name, Asset IP and installed software version of Java, possibly proof (but I don’t want to vulnerability proof) to exclude all assets with the installed software.
I want to locate all the assets with some version of Java installed, and provide the Java version, with IP and Hostname. I’m looked around and I don’t see a specific query that give me that information, with out including the vulnerable java which is not giving me the entire list of assets. I know already that I have over 1300 assets and I have IP, host name, but getting the software version of that java is a bit tricky.

2

I tried to use a query I found in a previous question, but the export to csv didn’t have any data in the file.
Query:
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 = ‘%java’
ORDER BY da.ip_address, da.asset_id, ds.version, da.last_assessed_for_vulnerabilities DESC

Headers looked like this with no data.

“ip” “hostname” “asset_id” “softwarename” “softwareversion” “lastscan”

As any one seen this before? The query validated fine but I didn’t want to preview because of the length of time it needed to preview the info.

3

Hi @brogers1!

Here is a query that should get you on the right track.

Callouts:

  • PostgreSQL default installs are case sensitive, so I typically recommend using the LOWER() function when filtering on text values. This will help prevent unintentional exclusions.

  • The query below is likely restricting results more than you need, so if you would like to return all software where word ‘java’ exists then replace the WHERE clause with the following.

     WHERE LOWER(ds.name) LIKE '%java%'

Here is the query:

SELECT

 da.asset_id
,da.host_name
,da.ip_address
,da.last_assessed_for_vulnerabilities
,ds.vendor AS software_vendor
,ds.name AS software_name
,ds.version AS software_version

FROM 
	dim_asset da
JOIN
	dim_asset_software das ON da.asset_id = das.asset_id
JOIN
	dim_software ds ON das.software_id = ds.software_id
WHERE
    ds.vendor IN ('Oracle Corporation', 'Linux')
	AND (LOWER(ds.name) LIKE 'java%' OR LOWER(ds.name) LIKE '%openjdk%')
	AND ds.name <> 'Java Auto Updater'
	AND current_date - DATE(da.last_assessed_for_vulnerabilities) <= 30
1 Like
4

How do you know which fields are available?

5

Security Console Schema: Under “SQL Query Export” you will find two sections, Understanding the reporting data model: Facts and Understanding the reporting data model: Dimensions. You can see all the tables, their relationships and the fields.

Data Warehouse Schema: If you have a data warehouse, which is 1000% easier for exploring and querying data.

1 Like