All Apps and Add-ons

Calculate span between two values

gnoellbn
Explorer

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

0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

woodcock
Esteemed Legend

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
Explorer

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 !

0 Karma

woodcock
Esteemed Legend

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
0 Karma
Get Updates on the Splunk Community!

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...

Avoid Certificate Expiry Issues in Splunk Enterprise with Certificate Assist

This blog post is part 2 of 4 of a series on Splunk Assist. Click the links below to see the other ...

Using Machine Learning for Hunting Security Threats

REGISTER NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more ...