Calculate span between two values

[gnoellbn]

Hello,

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 ?

Thanks,
Gaetan

[woodcock · Accepted solution]

Assuming that you have a field called status that has values of either Active or 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

[gnoellbn]

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 :

reporting_host firstTime first_chekin lastTime last_checkin time_offline currentState
XXXXX 1422767034 1441122904 09/01/2015 212+10:51:10 Disconnected

Thanks for your help !

[woodcock]

Use 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