Trying to get reltime from last searched for event

[mmelnick]

I'm trying to show the relative time for the last time data was refreshed successfully. I search for all success text strings in the log file and then I need to get that time and do a reltime. I tried:

searchstring | stats last() as _time | reltime

But of course "stats last()" isn't a time and putting it into _time doesn't work. I tried extracting the fields from last(), concatenating them and then strptime'ing then assigning it to _time:

searchstring | stats last(date_hour) as HOUR, last(date_minute) as MINUTE, last(date_year) as YEAR, last(date_month) as MONTH, last(date_second) as SECOND, last(date_mday) as DAYN | eval _time=strptime(YEAR . "-" . MONTH . "-" . DAYN . " " . HOUR . ":" . MINUTE . " " . SECOND,"%Y-%B-%d %H:%M:%S") | reltime

But it only added a reltime column to the result and put in unknown for the value, so I'm still doing something wrong. Besides, I really hope there's an easier way to do this than that last query (yuck!)

[gkanapathy · Accepted solution]

First of all, what are you planning to do with that value? Print it by itself? Pass it to something? Create a table of them? There may be better ways to do whatever you want, using a different path.

To answer the narrow problem you have, it's much simpler than what you've been doing:

searchstring | head 1 | reltime 

At least, that's what you do if you want the most recent time. your above query is getting you last(), which is actually the oldest time in your search. (Because Splunk returns results in reverse-time order, first() is the most recent event, and last() is the oldest. Starting in 4.3, you can use latest() and earliest() instead so it's less confusing.) If you really wanted the oldest event, then:

searchstring | tail 1 | reltime 

Your first try would have worked a lot easier if you just did:

searchstring | stats latest(_time) as _time | reltime

But using head (if you really mean the most recent time) is more efficient, or

searchstring | stats last(_time) as _time | reltime 

if you really meant last(), i.e., the oldest.

[mmelnick]

Well, I'm not sure what's going on now. Putting your eval suggestion at the end gives me the time of the event which is what I'd expect. I put in exactly what you had, so no overriding _time. To further muddy the waters, it works for one search string, but not another. Both strings occur once each in the indexed data, so it should find both of them normally and do the reltime step. But instead, one works and the other doesn't. The eval step works for both as well, so I dunno. Bewildering to say the least! Thanks for the help!

[gkanapathy]

Hmm, not sure. I hven't really used reltime. If you instead use eval tm=strftime(_time,"%Y-%m-%d %H:%M:%S") and show tm, does it display the right time? Also, are you sure you're not accidentally setting _time or overriding it, or accidentally printing out date_zone instead of the reltime field?

[mmelnick]

Thanks for the info on last. The documentation didn't jump out at me that it was the oldest. We're running 4.2.1 right now, so latest isn't an option right now. I'm trying to print out the relative time to a single value UI on my dashboard. When I use "searchstring | head 1 | reltime", I get the word "local" as output. How do I configure reltime to output the relative time instead of "local"? Thanks!

[gkanapathy]

also note that your method will give wrong results if the time zone of your data is different from the time zone of the search head. (assuming the time zone is correctly identified.)

[mmelnick]

I managed to get it to work by extending the ugly query:

searchstring | stats last(date_hour) as HOUR, last(date_minute) as MINUTE, last(date_year) as YEAR, last(date_month) as MONTH, last(date_second) as SECOND, last(date_mday) as DAYN | eval timestr=YEAR . "-" . MONTH . "-" . DAYN . " " . HOUR . ":" . MINUTE . ":" . SECOND  | eval _time=strptime(timestr,"%Y-%B-%d %H:%M:%S") | reltime | fields reltime

Still hoping someone has a nicer solution for this.