Retrieve specific columns showing java path using sql query

I’m attempting to retrieve critical / high fixes related to Java. I’m new to sql query export. here is the information looking to extract. #3 is not available in the “export” canned reports.

  1. Server name
  2. Vulnerability name
  3. “Path to java” that is exploited

Hope that makes sense.

Here is a useful query I have been using for Java. I found it on github, but I think the repo has since been removed. It extracts the Oracle/IBM java versions/paths and aggregates the associated vulnerabilities by asset. This is to be run in the console.

WITH
    dim_asset AS ( -- Filters out miscellaneous sites
        SELECT asset_id, ip_address, host_name, operating_system_id, sites
        FROM dim_asset
        --WHERE sites !~* '^((SERVICE)|(UNKNOWNS)|(TEST))'
    ),
    dim_aggregated_credential_status AS ( -- Filters out 'N/A' descriptions
        SELECT aggregated_credential_status_id, aggregated_credential_status_description
        FROM dim_aggregated_credential_status
        WHERE aggregated_credential_status_description <> 'N/A'
    ),
    owner_tag AS (
        SELECT dt.tag_name, dta.asset_id
        FROM dim_tag_asset dta
        JOIN dim_tag dt USING(tag_id)
        WHERE dt.tag_type = 'OWNER'
    ),
    dim_asset_group AS ( -- Filters on DEPT DAG and Orphans
        SELECT asset_group_id, name
        FROM dim_asset_group
        --WHERE name ~* '(Internal)|(External)'
    ),
    dim_vulnerability AS ( -- Filters on Critical/High or Riskscore > 650
        SELECT vulnerability_id, nexpose_id
        FROM dim_vulnerability
        --WHERE severity = 'Critical' OR severity = 'High'
    ),
    fact_asset_scan_vulnerability_finding AS ( -- Filters out most recent instance
        SELECT asset_id, vulnerability_id, MAX(scan_id) "scan_id"
        FROM fact_asset_scan_vulnerability_finding
        WHERE scan_id = lastScan(asset_id)
        GROUP BY asset_id, vulnerability_id
    ),
    vulnerabilities AS ( -- Categorizes vulns
        SELECT vulnerability_id,
            CASE
                WHEN nexpose_id ~* '(^jre)' THEN 'o_java'
                WHEN nexpose_id ~* '(^ibm-java)[^jre]' THEN 'i_java'
            END "category"
        FROM dim_vulnerability
    ),
    proofs AS ( -- Extracts details from Proof
        SELECT asset_id, vulnerability_id, scan_id,
            SUBSTRING(proof FROM 'Vulnerable software installed: (Oracle JRE.*)\([CD\/][^<>]*\)') "o_java",
            SUBSTRING(proof FROM 'Vulnerable software installed: (IBM JRE.*)\([^<>]*\)') "i_java",
            SUBSTRING(proof FROM '\(([^)]+)\)') "file_path"
        FROM fact_asset_scan_vulnerability_finding
        JOIN fact_asset_scan_vulnerability_instance USING(asset_id, scan_id, vulnerability_id)
    ),
    assets AS ( -- Groups details for an asset
        SELECT da.asset_id, da.ip_address, da.host_name, dos.system, da.sites,
            dag.name, fa.scan_finished, dacs.aggregated_credential_status_description
        FROM dim_asset da
        JOIN fact_asset fa USING(asset_id)
        JOIN dim_asset_group_asset daga USING(asset_id)
        JOIN dim_asset_group dag USING(asset_group_id)
        LEFT JOIN dim_operating_system dos USING(operating_system_id)
        LEFT JOIN dim_aggregated_credential_status dacs USING(aggregated_credential_status_id)
    ),
    summary AS ( -- Counts total vulns per category, and strings together vulnerable versions
        SELECT asset_id,
            NULLIF(SUM((v.category = 'o_java')::INT), 0) "o_java_ct",
            NULLIF(SUM((v.category = 'i_java')::INT), 0) "i_java_ct",
            STRING_AGG(DISTINCT p.o_java, ',' ORDER BY p.o_java DESC) "o_java",
            STRING_AGG(DISTINCT p.i_java, ',' ORDER BY p.i_java DESC) "i_java",
            STRING_AGG(DISTINCT p.file_path, E'\n\r') "file_path"
        FROM vulnerabilities v
        JOIN proofs p USING(vulnerability_id)
        GROUP BY p.asset_id
    ),
    final AS ( -- Formats data for final select
        SELECT DISTINCT
            a.asset_id, a.ip_address, a.host_name, o.tag_name,
            s.o_java_ct , s.o_java , s.i_java_ct , s.i_java , s.file_path,
            a.aggregated_credential_status_description,
            ROUND(age(a.scan_finished, 'days'), 0) "scan_finished",
            CASE WHEN a.sites ~* '(EXT)' THEN 'External' ELSE 'Internal' END "scope"
        FROM assets a
        JOIN summary s USING(asset_id)
        JOIN owner_tag o USING(asset_id)
        WHERE s.o_java_ct > 0 OR s.i_java_ct > 0
    )
SELECT
    ip_address "IP Address",
    host_name "Hostname",
    scan_finished "Scan Age",
    tag_name "Owner",
    o_java "Oracle Java Versions",
    o_java_ct "Oracle Vuln Count",
    i_java "IBM Java Versions",
    i_java_ct "IBM Vuln Count",
    file_path "File Path",
    scope "Scope",
    aggregated_credential_status_description "Credential Status"
FROM final ORDER BY host_name, ip_address
1 Like

I tried this query and it does not pull any results. Thoughts?

I should have specified this is for running in the data warehouse, not in a sql export in the console.

We’re trying to achieve the same via SQL query export reporting.
Any help on that please?