Splunk Search

How to generate a search that finds the closest log record based on a user's input timestamp?

vzed
Engager

I'm trying to figure out a way to get the closest log record to a user input timestamp. I'm thinking about making a dashboard where the user would input a time and it returns something. I will eventually be using information in the closest log record to query another sourcetype for more information but I'm stuck on how to do this first part. Any tips?

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

In both cases, before or after, you need to setup a time range window, e.g. closes records within 2 hrs of users input timestamp. This will help subsearch that we're going to use to be executed fully without timing out. Also, you need to fix a timestamp format in which user will be entering time. Assuming the timerange window is 2 hour, user input time token is $usertime$, and timestamp format is %Y-%m-%d %H:%M, try this:-

For before use case.

index=foo sourcetype=bar [| gentimes start=-1 | eval userinput=strptime("$usertime$","%Y-%m-%d %H:%M") | eval earliest=userinput-7200 | latest=userinput | table earliest latest | format "" "" "" "" "" "" ] | head 1 | table fields that you need

After use-case

index=foo sourcetype=bar [| gentimes start=-1 | eval userinput=strptime("$usertime$","%Y-%m-%d %H:%M") | eval earliest=userinput | latest=userinput+7200 | table earliest latest | format "" "" "" "" "" "" ] | tail 1 | table fields that you need

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

In both cases, before or after, you need to setup a time range window, e.g. closes records within 2 hrs of users input timestamp. This will help subsearch that we're going to use to be executed fully without timing out. Also, you need to fix a timestamp format in which user will be entering time. Assuming the timerange window is 2 hour, user input time token is $usertime$, and timestamp format is %Y-%m-%d %H:%M, try this:-

For before use case.

index=foo sourcetype=bar [| gentimes start=-1 | eval userinput=strptime("$usertime$","%Y-%m-%d %H:%M") | eval earliest=userinput-7200 | latest=userinput | table earliest latest | format "" "" "" "" "" "" ] | head 1 | table fields that you need

After use-case

index=foo sourcetype=bar [| gentimes start=-1 | eval userinput=strptime("$usertime$","%Y-%m-%d %H:%M") | eval earliest=userinput | latest=userinput+7200 | table earliest latest | format "" "" "" "" "" "" ] | tail 1 | table fields that you need

vzed
Engager

Thank you very much for the answers! I'm having a great deal of trouble with time formatting though. As a test, I've tried this search query:

sourcetype=bordernat [| gentimes start=-1 | eval userinput=strptime("2017-02-05 15:00:00","%Y-%m-%d %H:%M:%S") | eval earliest=userinput-7200]

But that always returns:

Unable to parse 1486529999 with format: %m/%d/%Y:%H:%M:%S

I see that it's trying to parse epoch -> standard datetime but I don't know where in the query it's doing this and how to fix it.
Would you happen to have any ideas?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Use like this (missing the table and format command). The purpose here is the override the time range based on user input so you need to only return earliest and latest from the subsearch.

sourcetype=bordernat [| gentimes start=-1 | eval userinput=strptime("2017-02-05 15:00:00","%Y-%m-%d %H:%M:%S") | eval earliest=userinput-7200 | latest=userinput | table earliest latest | format "" "" "" "" "" ""]
0 Karma

vzed
Engager

Oh! It worked! Interesting! I guess the "table earliest latest | format "" "" "" "" "" ""]" portion is what caused the error? I wonder why.

Thank you very much for the assistance! I'm going to add the "head 1" portion and dial it down to one log record.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

The gentimes return many other fields with some in the epoch format. With other fields being returned (not only the special fields earliest/latest), Splunk will try to parse any returned field containing timestamp to default format and was failing. The | table earliest latest resolved that issue.

0 Karma

vzed
Engager

Oh. I see now. Thank you for the explanation and again, thank you for the help!

0 Karma

somesoni2
SplunkTrust
SplunkTrust

So the closest can be before OR after the user's INPUT time OR just Before?

0 Karma

vzed
Engager

Just before the user's INPUT in this case.

However, if you have the time I would enjoy learning about how to fiddle with the search query to search both before and after the user INPUT. It might be a useful sort of query in the future.

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