I started with the following query, required to join a knowledge library with discovered hosts. The results are stored in a summary index for quick(er) retrieval. The first query is usually less than 100 events, the second is 70,000+ every time, but the whole thing runs in less than 60s. The problem is the 50,000 JOIN subsearch limitation.
index=qualys sourcetype="qualys:hostDetection" QID=* SEVERITY IN (3 4 5) LAST_SCAN_DATETIME=* earliest=-15min latest=now()
| join type=inner QID [search index=qualys sourcetype="qualys:knowledgebase" QID=* earliest=1 latest=now()
| dedup QID | table QID THREAT_INTEL_VALUES CVSS_V3_BASE PUBLISHED_DATETIME THREAT_INTEL_IDS VENDOR_REFERENCE TITLE]
|table _time IP DNS NETBIOS TRACKING_METHOD OS TAGS QID TITLE TYPE SEVERITY STATUS LAST_SCAN_DATETIME LAST_FOUND_DATETIME LAST_FIXED_DATETIME PUBLISHED_DATETIME THREAT_INTEL_VALUES THREAT_INTEL_IDS CVSS_V3_BASE VENDOR_REFERENCE RESULTS
In order to overcome the JOIN/subsearch limit and maybe gain some efficiencies I tried using eventstats instead. The resultant query is below, and runs for over an hour with questionable results (never really finishes. Pretty sure it is not giving me the same output as the JOIN. What am I doing wrong? New query
(index=syn_sec_qualys sourcetype="qualys:hostDetection" QID=* SEVERITY IN(3 4 5) LAST_SCAN_DATETIME=* earliest=-15m@m latest=now) OR (index=syn_sec_qualys sourcetype="qualys:knowledgebase" QID=* earliest=1 latest=now)
| eventstats values(_time) AS _time, values(DNS) AS DNS values(TRACKING_METHOD) AS TRACKING_METHOD values(NETBIOS) AS NETBIOS values(OS) AS OS, values(TAGS) AS TAGS, values(TITLE) AS TITLE, values(TYPE) AS TYPE, values(SEVERITY) AS SEVERITY, values(LAST_SCAN_DATETIME) AS LAST_SCAN_DATETIME, values(LAST_FOUND_DATETIME) AS LAST_FOUND_DATETIME, values(LAST_FIXED_DATETIME) AS LAST_FIXED_DATETIME values(PUBLISHED_DATETIME) AS PUBLISHED_DATETIME values(THREAT_INTEL_VALUES) AS THREAT_INTEL_VALUES, values(THREAT_INTEL_IDS) AS THREAT_INTEL_IDS values(CVSS_V3_BASE) AS CVSS_V3_BASE, values(VENDOR_REFERENCE) AS VENDOR_REFERENCE, values(RESULTS) AS RESULTS BY IP, QID
| TABLE _time IP DNS NETBIOS TRACKING_METHOD OS TAGS QID TITLE TYPE SEVERITY STATUS LAST_SCAN_DATETIME LAST_FOUND_DATETIME LAST_FIXED_DATETIME PUBLISHED_DATETIME THREAT_INTEL_VALUES THREAT_INTEL_IDS CVSS_V3_BASE VENDOR_REFERENCE RESULTS
... View more