I'm trying to write a search to count how many days have passed between the current value and a specific one in the past.
For instance with data coming form OSSEC I have more than 400 hosts "Disconnected" and I want a search that counts the number of days between that and the first "Active" status it sees.
I though of using timechart but it's not handy for 400+ hosts.
Anyone has an idea ?
Assuming that you have a field called
status that has values of either
Disconnected, then something like this should work for you:
sourcetype = OSSEC | dedup host status | stats earliest(_time) AS firstTime latest(_time) AS lastTime latest(status) AS currentState by host | secondsInCurrentState = now() - lastTime
You've shown me the right way 🙂 I've adapted your query like this :
sourcetype="ossec" | stats earliest(_time) AS firstTime latest(_time) AS lastTime latest(status) AS currentState by reporting_host | search currentState=Disconnected | eval secondsInCurrentState = lastTime - firstTime | eval time_offline=tostring(secondsInCurrentState, "duration") | convert timeformat="%m/%d/%Y" ctime(lastTime) AS last_checkin, ctime(firstTime) AS first_checkin | table reporting_host, firstTime, first_chekin, last_checkin, time_offline, currentState
However for some reasons the
convert for first_checkin doesn't work, it gives me an empty field. Also I have removed the
dedup because I believe it will stop at the first occurence and won't find the earliest event (unless I'm mistaken)
Here is the output I get :
reportinghost firstTime firstchekin lastTime lastcheckin timeoffline currentState
XXXXX 1422767034 1441122904 09/01/2015 212+10:51:10 Disconnected
Thanks for your help !
dedup to keep the most-recent instance of the field pairings given; try this:
sourcetype="ossec" | eventstats earliest(_time) AS first_checkin BY reporting_host | search status = "Disconnected" | dedup host | eval secondsInCurrentState = now() - _time | eval time_offline=tostring(secondsInCurrentState, "duration") | eval last_checkin = _time | fieldformat last_checkin = strftime(last_checkin, "%m/%d/%Y") | fieldformat first_checkin = strftime(first_checkin, "%m/%d/%Y") | table reporting_host, first_chekin, last_checkin, time_offline, status