{
"agentType": "asset",
"createdAt": "2023-12-31T08:06:37.007Z",
"createdBy": "aca014ba-2c3b-4456-b3df-046e0166801c",
"customerId": "string",
"endTime": null,
"error": null,
"filters": null,
"id": "string",
"incompleteResults": false,
"links": [
{
"href": "string",
"rel": "self",
"title": "Query job information"
},
{
"href": "string",
"rel": "parent",
"title": "Query jobs information"
}
],
"name": "string",
"parameterValues": null,
"parameters": null,
"purged": false,
"query": "WITH assets AS (\n SELECT *\n FROM upt_assets_all\n \n ),\n latest_scan AS (\n SELECT upt_asset_id, MAX(upt_time) AS latest_scan_time\n FROM vulnerabilities\n WHERE upt_day >= CAST(date_format((CURRENT_DATE - INTERVAL '3' DAY), '%Y%m%d') AS INT)\n GROUP BY upt_asset_id\n ),\n vulnerabilities AS (\n SELECT v.*\n FROM vulnerabilities v\n JOIN latest_scan s ON v.upt_asset_id = s.upt_asset_id AND v.upt_time = s.latest_scan_time\n WHERE v.upt_day >= CAST(date_format((CURRENT_DATE - INTERVAL '3' DAY), '%Y%m%d') AS INT)\n AND (system_type IN ('host', 'volume') OR system_type is NULL)\n ),\n\n vulnerabilities_without_exceptions AS\n (\n SELECT *\n FROM vulnerabilities\n WHERE upt_day >= CAST(date_format((CURRENT_DATE - INTERVAL '3' DAY), '%Y%m%d') AS INT)\n AND cve_list NOT IN (SELECT cve_id FROM upt_vulnerability_exceptions)\n ),\n\n latest_vulnerabilities AS (\n SELECT upt_asset_id, upt_hostname, cve_list, cvss_score, os, os_version ,package_name, package_version, package_type, indicator_hash, upt_asset_group_name,\n IF((indicator_operator = 'lt' OR indicator_operator='rng_e') AND indicator_version IS NOT NULL, true, false) AS fixAvailable,\n CASE\n \tWHEN EXISTS (SELECT 1 FROM upt_threat_exploits ute WHERE ute.cve_id = v.cve_list AND description IS NOT NULL)\n THEN true\n ELSE false\n END AS exploitable,\n CASE\n WHEN indicator_operator = 'lt' AND indicator_version IS NOT NULL THEN indicator_version\n WHEN indicator_operator = 'rng_e' AND indicator_version IS NOT NULL THEN split_part(indicator_version, ',', 2)\n ELSE ''\n END fixVersion,\n CASE\n WHEN MAX(CAST(cvss_score AS REAL)) < 4 THEN 'low'\n WHEN MAX(CAST(cvss_score AS REAL)) >= 4 AND MAX(CAST(cvss_score AS REAL)) < 7 THEN 'medium'\n WHEN MAX(CAST(cvss_score AS REAL)) >= 7 AND MAX(CAST(cvss_score AS REAL)) < 9 THEN 'high'\n WHEN MAX(CAST(cvss_score AS REAL)) >= 9 THEN 'critical'\n ELSE 'unknown'\n END AS severity,\n max(upt_time) AS last_seen_vln_at\n FROM vulnerabilities_without_exceptions v\n \n GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14\n ),\n\n published_cves AS\n (\n SELECT\n indicator_hash,\n MAX(published) as published,\n MAX(date_diff('day', DATE(published), CURRENT_DATE)) AS age\n FROM upt_threat_indicators\n WHERE cve_list IS NOT NULL AND is_active=TRUE\n GROUP BY indicator_hash\n\n ),\n\n vln_with_published_date AS (\n SELECT lv.*, uti.published, uti.description\n FROM upt_threat_indicators uti\n JOIN latest_vulnerabilities lv ON uti.indicator_hash = lv.indicator_hash\n \n ),\n\n vln_summary AS (\n SELECT upt_asset_id as assetId, upt_hostname as hostName,\n \t\t\t cve_list AS cveId,\n \t\t\t severity,\n \t\t\t fixAvailable,\n \t\t\t cvss_score AS cvssScore,\n \t\t\t description,\n \t\t\t exploitable,\n published AS cvePublishedAt,\n upt_asset_group_name AS assetGroup,\n last_seen_vln_at AS cveLastSeenAt,\n COUNT(DISTINCT package_name) AS packagesAffectedCount,\n \t\t\t COUNT(*) FILTER (WHERE fixAvailable=true) AS totalFixAvailable\n FROM vln_with_published_date\n GROUP BY 1,2,3,4,5,6,7,8,9,10,11\n ),\n\n vln_summary_with_assets AS (\n SELECT vln.*, a.live as isLive, a.last_activity_at as recentHostActivity, os, os_version AS osVersion, a.tags\n FROM vln_summary vln JOIN assets a\n ON a.id = vln.assetId\n )\n\n SELECT * FROM vln_summary_with_assets ORDER BY assetId, cveId\n OFFSET 0 LIMIT 1000",
"queryId": null,
"queryStats": null,
"resourceType": "asset",
"rowCount": 0,
"source": "DASHBOARDS",
"startTime": null,
"status": "QUEUED",
"type": "global",
"updatedAt": "2023-12-31T08:06:37.007Z",
"updatedBy": null
}
