Adding Time Input In Custom Date Time Field
I hope everyone is doing well and also enjoying Splunk from home. Today I am going to unpack a wonderful trick of time input. As we all know that if we apply time input in Splunk it by default works on the “_time” field i.e. known as event time. Now suppose we want to apply the time input filter on a custom date time field which is ingested in our event instead of the “_time” field.
Now here is a catch for you, let if we have any date time portion in data that will be treated as “_time” by default. Try to understand from the below figure.
Here we have two custom date time field one is “log_in_time” and another one is “log_out_time” now one can see from the figure that “log_in_time” is same as “_time” i.e. Splunk will by default take the first custom date time field as “_time”.
Now we will try to apply a time input filter with the “log_out_time” field. So let’s start.
Let’s take a sample query then
index="time_event" sourcetype="csv" |stats count by log_out_time e_id
Here we will apply a time input filter with the “log_out_time” field.
So Save this result in a dashboard.
Now go to that dashboard and click on Edit and then click on Add Input from the top and select Time to create the time input option and then do as shown in the below figure.
Apply and Save the dashboard.
Now replace your quarry in the panel with this following one.
index="time_event" sourcetype="csv" |stats count by log_out_time e_id |eval aa=strptime('log_out_time',"%Y-%m-%d %H:%M:%S.%3N") , a="$time.earliest$" , b ="$time.latest$" |eval earliest = if(isnum(a),a,relative_time(now(),a)),latest=if(isnum(b),b ,relative_time(now(),if(b="now","-0",b))) |where aa >= earliest AND aa <=latest |fields - aa,earliest,latest,a,b
Now Save the dashboard and that’s it. So test it. From the time input select any time range for which you want to see the result.
You can see the result right.
“$time.earliest$” and “$time.latest$” these two tokens are responsible for the earliest and latest time. If you follow step 2 then you can see that we have set a token in the time input option as “time”, that’s why tokens of earliest and latest are written in this way. Now take an example in case of time if we assign a token name in time input option as time_token then, it tokens for the earliest and latest time will be like “$time_token.earliest$” and “$time_token.latest$”.
As you can see that at first we converted the “log_out_time” field into epoch, then we took “$time.earliest$” and “$time.latest$” tokens into “a” and “b” respectively. Now if you see carefully we have used conditional function with the eval command to create the earliest and latest field. Let’s discuss this,
If we select any date time range as we did in step 3 then it will pass(in “a” and “b”) epoch time i.e. a and b fields will have numeric value (that’s why we have used isnum() function to compare “a” and “b” is numeric value or not), now we will have numeric value in latest and earliest field (same as “a” and “b”) , then we will compare those with “aa” field i.e. epoch time of “log_out_time” field (condition will be “log_out_time” should be greater than equal to earliest and less than equal to latest). This is how you can get the result.
Now in the second condition of “if” part, If we select any relative time from the time input i.e. 24hours ago, 7days ago etc then within the token it will pass a non numeric value(in “a” and “b”), after that using relative_time() we will convert that time into epoch time and the rest are same as condition 1, but when we select times like 7 days ago, 24 hours ago then in latest time it will carry the value as “now” i.e. now() time, that’s why we have the last red underlined portion in below figure. The logic is that, whenever it will get “now” in “b” it will return -“0” i.e. now() time otherwise it will return “b” itself. Rest portion is the same as condition 1.
I hope you all understand this trick on “Adding Time Input In Custom Date Time Field”