Get _time on your side - How to sort by more than one time field

When you are working with data that has more than one date field and the date field you want to sort by is not _time, you may want to sort by the alternate time field in your search. You may also want to use the time picker with that other time field in a search or dashboard.

Here is a solution you might use to make time selections work in every case including in panels. We are going to work with the following search.

| inputlookup SampleData.csv

| eval _time=strptime(claim_filing_date," %m/%d/%y")

| sort - _time

| addinfo

| where _time>=info_min_time AND (_time<=info_max_time OR info_max_time="+Infinity")

Explain what just happened in that search

1. This is an example that pulls data directly from a .csv file. It behaves just like it would from one of your searches against a data source that has no meaningful _time value or more than one time-based field. 

This is the part of the search that pulls in the sample data for this illustration. You will write your own search here to pull in your data.

| inputlookup SampleData.csv

Note:  Add enough filters to the search so that you aren’t working with the entire data set. Remember filter first > munge later. Get as specific as you can and then the search will run in the least amount of time. Your Search might begin like this…

index=myindex something=”thisOneThing” someThingElse=”thatThing”

2. Next, we need to copy the time value you want to use into the _time field. The following statement converts the date in claim_filing_date into epoch time and stores it in _time. Notice that claim_filing_date is a field in my sample data containing a date field I am interested in. Use your field name here.

        | eval _time=strptime(claim_filing_date," %m/%d/%y")

        Learn to specify Date and Time variables here.

https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Commontimeformatvariables

3. Now sort all of the records by time since they weren’t in order according to the new time field we have chosen.

| sort - _time

4. This statement adds info_min_time and info_max_time fields which are the min and max of the new values for _time that you have. The statement is needed for the time control in reports and panels to make it work properly.

| addinfo

5. This is where the magic happens. Here we are filtering the results based on comparisons between your _time field and the time range you created with the time picker.

| where _time>=info_min_time 

AND (_time<=info_max_time 

OR info_max_time="+Infinity")

Notice that we also had to compare against “+infinity”. This is what Splunk uses for the info_max_time field when you select “All Time” on the time picker. 

Next, Make the time picker work

Now you have moved your time field into _time. Let’s make it really work for us.

We need to format the output to make it easier to work with the results using the time picker. We do this by creating Start_Time and Stop_Time which the time picker in Splunk screens will use to place boundaries around your search.

    | eval Start_Time=strftime(info_min_time,"%m/%d/%y")

    | eval Stop_Time=strftime(info_max_time,"%m/%d/%y")

    | table claim_filing_date _time Start_Time info_min_time Stop_Time info_max_time "Provider Name"

Now we have a working search, try it with your data………

Using your results in a Panel

Next, you may want to put your great search into a Dashboard Panel. We will have to do a few more things before this works as you expect. 

If you simply click Save As > Dashboard Panel you can create a panel that looks like this. Notice I have called my panel “Time done my way”

Let’s add a time picker and a start button. Click Edit and then Add Input and select the Time control and the Submit control.

There are two things we have to do with the panel to make it work. To make these two changes while we are still in edit mode, click Source. 

First, we are going to provide the time evaluation in our where clause so we don’t need the token=”field1″ statement.

The changed source will look like this.

Next, we need to remove the earliest and latest clauses from the query.

The changed source should look like this.

That’s it. Save the changes and enjoy the results.

Let’s make this process easier because easier is well, easier

These statements could be added to a macro which will eliminate the need to add all of this code every time you want to do this.

If macros are new to you, here is the link for building macros…

https://docs.splunk.com/Documentation/Splunk/latest/Knowledge/Definesearchmacros

In the process, you will make the following changes to macros.conf. You can do all of this from the menu in Settings > Advanced Search > Search macros

Now your simplified search we built earlier will look like this.

    | inputlookup SampleData.csv 

    | `setsorttime(claim_filing_date, %Y-%m-%d)`

    | table _time claim_filing_date info_min_time "Provider Name"

OK, try it now…….

Hey, Can I pick which time field to use?

Good, you’re doing great. Now maybe you would like to add a Radio Button to allow you to pick the field you want to sort on.

In the panel editor, add a drop down and let’s give it a Label of “Pick a Date” and a Token Name of selected_date_field.

Now we can add a few fields to select from. Note that we are simply adding field names and a pretty description of each field.

Next, we need to add the Radio Button variable to the search string. 

Click Edit Search.

What you are focused on here is the field claim_filing_date.

Notice that we have replaced claim_filing _date with $selected_date_field$. This is the magic sauce that will allow the search to know which field you choose in the Time Picker.

The final result should look something like your very own masterpiece.

Now you can sort on any time field you have and use it for time pickin anytime and anywhere you want.

Use Knowledge Wisely.

SplunkYoda

splunkyoda@splunk.com

----------------------------------------------------
Thanks!
David Clawson