Splunk Search

Combining and comparing 2 date results

bartp
New Member

Hi Splunkers,

I'm new to splunk and i'm working on a dashboard for a service/application. What i'm trying to do is the following. I'm searching for the last "shutting down" and the last "starting application" event in my log. Then I want to compare the two. If the stop_date > start_date then I want my single value to display "service down" else "service up".
Beneath is my search so far, but it always displays service up at this moment, what am I doing wrong?

Thanks in advantage for your answers,

index=myindex Starting Application | addinfo | dedup 1 host sortby -_time | eval start_date=strftime(_time, "%m-%d-%Y %H:%M:%S")| append [search index=ieg shutting down | addinfo | dedup 1 host sortby -_time | eval stop_date=strftime(_time, "%m-%d-%Y %H:%M:%S")] | eval status=if(start_date

Tags (1)
0 Karma

Richfez
SplunkTrust
SplunkTrust

bartp,

Several potential solutions have been provided. If one of these have solved your problem, could you please mark that one as Accepted?

If not, please let us know what is or is not working with what you've gotten and maybe we can try again?

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

I assume that the particular service name is part of the actual search, so modify the searches in parenthesis as needed. Also, if the words "Starting Application" and "shutting down" are found exactly like that, with a space between them, put them in quotes like this for a more efficient search. If not, modify the search and the match as needed.

Try this -

| eval whatkind=if(match(_raw,"Starting Application"),1,0) 
 | dedup 1 whatkind
 | table _time whatkind
 | eval StartTime=if(whatkind=1,_time,null())
 | eval EndTime=if(whatkind=0,_time,null())
 | stats max(_time) as _time max(*) as *
 | eval status=if(_time=StartTime,"service up", "service down")

Explanations -
The above code selects all records of the required types in the timeframe of the search... in your case probably 24 or 36 hours. Just as a standard practice, you should avoid append if you can do so, since in larger result sets (not like this one) it can have problems.

It then determines which type each record is, and keeps only the most recent of each type. The sorting was unneeded work, since splunk by default gives you the latest records first.

It then sets fields for whether it was a start or end record, and uses stats to find the most recent time for each kind, and the most recent _time record. It doesn't bother comparing the two to each other, since the same most-recent time will be in the _time value. If that is the value of StartTime, then we're up, otherwise we're down.


Here's a test code generator. You can use it with any of these code samples people have given you, by replacing the search terms with this. Then, just change count%2=0 to count%2=1 to test the other condition.

| gentimes start="01/25/2017:06:00:00" end="01/26/2017:08:24:00" increment=30m | streamstats count | eval _raw=if(count%2=0,"Starting Application","shutting down") | eval _time= starttime| table _time _raw | sort -_time
0 Karma

Richfez
SplunkTrust
SplunkTrust

First, are you sure you have something that should be marked down right now? 🙂

Otherwise, try (NOTE all untested code, I just winged it so it could be a bit off)

index=myindex Starting Application | stats latest(_time) AS start_time BY host
| append 
  [search index=ieg shutting down |stats latest(_time) AS stop_time BY host]
| stats list(start_time) AS started list(stop_time) AS stopped by host
| eval status=if(started>stopped,"UP","DOWN")
| search status="DOWN"

So what am I doing so differently?

1) I replaced your logic with a simple stats latest(_time) AS XYZ for each part. (lines 1-3)
2) After you get all your data, we use stats ... again to re-group each host with itself.
3) Then other things. 🙂

Now, you don't even need the last two lines if you don't care to create a "status" field but just need to list the ones that are down. In that case, drop off the last two eval and search lines and add | where stop_time>start_time, like

... | stats list(start_time) AS started list(stop_time) AS stopped by host
| where stop_time>start_time

Lastly, if you now want your times to display right, it's easy enough at the end to add

...| fieldformat "stop_time"=strftime('stop_time', "%m-%d-%Y %H:%M:%S")
| fieldformat "start_time"=strftime('start_time', "%m-%d-%Y %H:%M:%S")

To the end of it (making sure to use the right field names!)

Save that reformatting for last when possible, using epoch times for date math is way easier for Splunk. Also if you do it that way, you can create a macro to do things like formatting all the fields the right way and creating a pretty table or whatever, then reuse that macro on other similar output.

richgalloway
SplunkTrust
SplunkTrust

Timestamps can only be compared in epoch (integer) form. Try this.

index=myindex Starting Application | addinfo | dedup host | eval start_date=_time | append [search index=ieg shutting down | addinfo | dedup 1 host sortby -_time | eval stop_date=_time] | eval status=if(start_date>stop_date, "service up", "service down") 

If you want to display start_date and stop_date, add the following to the query.

| fieldformat start_date=strftime(start_date, "%m-%d-%Y %H:%M:%S") | fieldformat stop_date=strftime(stop_date, "%m-%d-%Y %H:%M:%S")
---
If this reply helps you, Karma would be appreciated.
0 Karma

Richfez
SplunkTrust
SplunkTrust

Could you please re-paste your search, being sure to use the "code" button (101010) so that special characters don't get eaten?

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 ...