Finding time (in days) that server has not been patched for a critical vulnerability

[mbtsoltis]

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)

[PickleRick]

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")

[ITWhisperer · Accepted solution]

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

[mbtsoltis]

Thanks this helped me go in the right direction and focus on using streamstats