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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...