Thanks in advance for any help.
I'm trying to find the days that a Device has not been patched for Critical Severity vulnerability (currently not patched). The example below should return 3 days for Device Server01. Tried stats and streamstats but not able to get it to to produce below results
Device | Message | _time |
Server01 | Severity Critical Patch Missing | 11/1/2021 2PM |
Server01 | Ok (Fully Patched) | 11/2/2021 2PM |
Server01 | Severity Critical Patch Missing | 11/3/2021 2PM |
Server01 | Severity Critical Patch Missing | 11/3/2021 6PM |
Server01 | Severity Critical Patch Missing | 11/4/2021 2PM |
Server01 | Severity Critical Patch Missing | 11/5/2021 6PM (latest event) |
Try something like this
| makeresults
| eval _raw="Device Message _time
Server01 Severity Critical Patch Missing 11/1/2021 2PM
Server01 Ok (Fully Patched) 11/2/2021 2PM
Server01 Severity Critical Patch Missing 11/3/2021 2PM
Server01 Severity Critical Patch Missing 11/3/2021 6PM
Server01 Severity Critical Patch Missing 11/4/2021 2PM
Server01 Severity Critical Patch Missing 11/5/2021 6PM (latest event)"
| multikv forceheader=1
| eval _time=strptime(time,"%d/%m/%Y %I%p")
| bin span=1d _time
| stats values(*) as * by _time Device
| streamstats count(eval(Message=="Severity Critical Patch Missing")) as days reset_before="("match(Message,\"Ok \(Fully Patched\)\")")" by Device
Question is whether you want current state or any historic occurrences?
Because if you just want to have current status - it's relatively easy
<your search>
| stats max(_time) as maxtime by Message Device
| xyseries Device Message maxtime
| rename "Ok (Fully Patched)" as ok
| rename "Severity Critical Patch Missing" as crit
| eval state=case(ok>crit,"OK",1=1,tostring((crit-ok)/86400)." days overdue")
Try something like this
| makeresults
| eval _raw="Device Message _time
Server01 Severity Critical Patch Missing 11/1/2021 2PM
Server01 Ok (Fully Patched) 11/2/2021 2PM
Server01 Severity Critical Patch Missing 11/3/2021 2PM
Server01 Severity Critical Patch Missing 11/3/2021 6PM
Server01 Severity Critical Patch Missing 11/4/2021 2PM
Server01 Severity Critical Patch Missing 11/5/2021 6PM (latest event)"
| multikv forceheader=1
| eval _time=strptime(time,"%d/%m/%Y %I%p")
| bin span=1d _time
| stats values(*) as * by _time Device
| streamstats count(eval(Message=="Severity Critical Patch Missing")) as days reset_before="("match(Message,\"Ok \(Fully Patched\)\")")" by Device
Thanks this helped me go in the right direction and focus on using streamstats