SQL query for installed software per device

InsightVM SQL Queries
1

I have tried the SQL queries listed in the github, but they no longer verify.

2

what are you trying to acomplish, are you looking to generate a REPORT of the Nexpose Console reporting engine using a SQL query to pull all Software on a SINGLE host by defining that IP address in the Query?

3

I would like to be able to produce a report of all devices with a specific application installed or all installed applications on a single device.

4

Well this is 2 Different Queries,

Software Specific Report

WITH asset_sites AS (
    SELECT dsa.asset_id, array_to_string(array_agg(s.name), ',') AS sites
    FROM dim_site_asset dsa
    JOIN dim_site s ON dsa.site_id = s.site_id
    GROUP BY dsa.asset_id
),
latest_scans AS (
    SELECT fa.asset_id, MAX(fa.scan_finished) AS last_scan_date
    FROM fact_asset fa
    GROUP BY fa.asset_id
)

SELECT 
    asi.sites AS "Site",
    da.ip_address AS "IP Address",
    da.host_name AS "Host Name",
    dos.vendor AS "OS Vendor",
    dos.family AS "OS Family",
    dos.name AS "OS Name",
    dos.version AS "OS Version",
    dos.architecture AS "OS Architecture",
    sw.name AS "Software Name",
    sw.version AS "Software Version",
    ls.last_scan_date AS "Last Scan Date"
FROM dim_asset_software das
JOIN dim_software sw ON das.software_id = sw.software_id
JOIN dim_asset da ON das.asset_id = da.asset_id
JOIN dim_operating_system dos ON da.operating_system_id = dos.operating_system_id
LEFT JOIN asset_sites asi ON das.asset_id = asi.asset_id
LEFT JOIN latest_scans ls ON das.asset_id = ls.asset_id
WHERE sw.name ILIKE 'softwarename%'  -- Case-insensitive search
ORDER BY asi.sites, da.ip_address;

Next is All Software installed on a single (IP) Asset..

WITH latest_scans AS (
    SELECT fa.asset_id, MAX(fa.scan_finished) AS last_scan_date
    FROM fact_asset fa
    GROUP BY fa.asset_id
)

SELECT 
    sw.vendor AS "Software Vendor",
    sw.family AS "Software Family",
    sw.name AS "Software Name",
    sw.version AS "Software Version",
    ls.last_scan_date AS "Last Scan Date"
FROM dim_asset da
JOIN dim_asset_software das ON da.asset_id = das.asset_id
JOIN dim_software sw ON das.software_id = sw.software_id
LEFT JOIN latest_scans ls ON da.asset_id = ls.asset_id
WHERE da.ip_address = '192.168.1.1'
ORDER BY sw.vendor, sw.family, sw.name, sw.version;