Get Query Job

Gets a query job by ID.

Parameters

Parameter	Description
Job ID	The job ID.

Example Output

{
    "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
}

Workflow Library Example

Get Query Job with Uptycs and Send Results Via Email

Preview this Workflow on desktop