topic Re: How to subtract the date in my search? in Splunk Search

How to subtract the date in my search?

abhi04 — Mon, 04 Jun 2018 16:01:49 GMT

How to subtract the below date?

End Time is 2018-06-04-10.45.09
Start Time is 2018-06-04-10.45.00

End Time - Start Time

Re: How to subtract the date in my search?

mdsnmss — Mon, 04 Jun 2018 16:20:50 GMT

You could convert to epoch and subtract. Try something like: | eval epoch1=strptime(endTime,"%Y-%m-%d-%H.%M.%S") | eval epoch2=strptime(startTime,"%Y-%m-%d-%H.%M.%S") | eval timediff=epoch1-epoch2

This gives you the difference in seconds. You can convert as needed.

Re: How to subtract the date in my search?

jconger — Mon, 04 Jun 2018 16:22:36 GMT

What do you want as the difference? If it is just a number of seconds between the two, the following will work:

| makeresults | eval End_Time="2018-06-04-10.45.09" | eval Start_Time="2018-06-04-10.45.00" | convert timeformat="%Y-%m-%d-%H.%M.%S" mktime(End_Time) AS EndTime  mktime(Start_Time) AS StartTime | eval diff = EndTime - StartTime

Re: How to subtract the date in my search?

adonio — Mon, 04 Jun 2018 16:38:51 GMT

adding to the comments and nice answer above by @jconger,
below is a sample search to run anywhere. i added a field some_id that you can group your query by. for example if there is a transaction id or something of that sort. th efirst 6 lines are generating data, the rest is the solution

| makeresults count =1 
| eval field = "some_id"
| eval data = "End_Time=2018-06-04-10.45.09,Start_Time=2018-06-04-10.45.00"
| makemv delim="," data
| mvexpand data
| rex field=data "(?<time_field>\S+)\=(?<time_value>\S+)"
| eval time_in_epoch = strptime(time_value, "%Y-%m-%d-%H.%M.%S")
| eval end_time_epoch = if(time_field="End_Time",time_in_epoch,null())
| eval start_time_epoch = if(time_field="Start_Time",time_in_epoch,null())
| stats values(*_time_epoch) as *_time_epoch by field
| eval diff_in_sec = round(end_time_epoch - start_time_epoch, 0)
| eval diff_human=tostring(diff_in_sec, "duration")

see screenshot below:

hope it helps