Splunk Search
Highlighted

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

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
Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

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

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
Highlighted

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

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

Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

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
Highlighted

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

Engager

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

0 Karma