Splunk Search

Trying to get reltime from last searched for event

mmelnick
Path Finder

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!)

Tags (2)
0 Karma
1 Solution

gkanapathy
Splunk Employee
Splunk Employee

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.

View solution in original post

gkanapathy
Splunk Employee
Splunk Employee

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
Path Finder

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!

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

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?

0 Karma

mmelnick
Path Finder

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!

0 Karma

gkanapathy
Splunk Employee
Splunk Employee

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
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...