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
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?
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")
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=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
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
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.
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")