Goal is to determine, from specific vulnerabilities found in scans, the percentage that have been ‘fixed’, meaning they are not found in the latest scans. So first we get the vulns for all time, then check the ones related to the latest scans. So alltime – latest = fixed. And fixed / alltime * 100 = percentage fixed.
The search/query takes way too long. How to speed it up?
Here is the approach.
| eval comment=”the above gets the results of the scans All Time”
| eval comment=”cve is multivalue field, so split it up so we can lookup a specific CVE”
| makemv delim=";" cve | mvexpand cve | rename cve AS CVE
| eval comment=”now just look at the ones we care about”
| join CVE [|inputlookup PriorityCVE.csv]
| eval comment=”label these as ‘alltime’”
| eval ReportKey="alltime"
| eval comment=”now get the scans representing the latest info”
| append [search sourcetype=alpha | makemv delim=";" cve | mvexpand cve | rename cve AS CVE
| join CVE [|inputlookup PriorityCVE_test.csv]
| eval comment=”last_scan_finished is in terms of date not granularity of _time”
| convert timeformat="%F" ctime(_time) AS CurScan
| eval comment=”the beta source has events over time with last_scan_finished per asset”
| join asset_id, CurScan [search sourcetype=beta | dedup asset_id last_scan_finished
| fields asset_id last_scan_finished | sort 0 - last_scan_finished | dedup asset_id
| convert timeformat="%Y-%m-%d %H:%M:%S.%Q" mktime(last_scan_finished) AS LastScanTmp
| convert timeformat="%F" ctime(LastScanTmp) AS CurScan | fields asset_id CurScan]
| eval comment=”label those that match the last_scan_finished date as ‘nowtime’”
| eval ReportKey="nowtime”
| table asset_id CVE ReportKey
| eval comment=“get counts”
| stats values(ReportKey) as ReportKey by asset_id CVE
| eval comment=”if you only have the alltime label, you have been fixed”
| eval status=case(mvcount(ReportKey)=2, "Persistent", ReportKey="alltime", "Fixed", true(), "New")
| eval comment=”(since all scans are in alltime, there should be no ‘new’)”
| eval comment=”need to get the counts into variables for the equation”
| stats count AS stots BY status | fields stots | mvcombine delim="," stots
| nomv stots | rex field=stots "(?<tot_fix>.),(?<tot_persist>.)"
| eval comment=”now do the calculation”
| eval progress=(tot_fix / (tot_fix + tot_persist)) * 100
| table progress
I also tried an approach that put the last_scan_finished per asset in a lookup table, avoiding the redundant search, but that did not speed it up much (and would introduce the need to keep updating a lookup table).
... View more