topic Re: How to compare and match timestamp of one field with the latest timestamp of other field before calculating duration in Splunk Search

How to compare and match timestamp of one field with the latest timestamp of other field before calculating duration ?

ak9092 — Fri, 26 Jun 2020 15:00:27 GMT

Hi All,

I am currently getting following results from my search query -

time1 time2 duration

06/26/2020 07:42:11 06/26/2020 07:42:55 0.73

06/26/2020 07:47:10 06/26/2020 07:55:39 8.48

06/26/2020 07:51:09

Following is the example of results which I am trying to get –

time1 time2 Duration

06/26/2020 07:42:11 06/26/2020 07:42:55 0.73

06/26/2020 07:47:10

06/26/2020 07:51:09 06/26/2020 07:55:39 4.30

So basically what i want is that, "time2" field should look for the latest timestamp in "time1" field before calculating duration.

Thanks in advance.

Re: How to compare and match timestamp of one field with the latest timestamp of other field before calculating duration

richgalloway — Fri, 26 Jun 2020 17:31:42 GMT

More information is needed. Where do these timestamps come from? Can you share your search(es)?

Re: How to compare and match timestamp of one field with the latest timestamp of other field before calculating duration

ak9092 — Mon, 29 Jun 2020 06:27:34 GMT

These timestamps are coming from two different searches which i am later grouping them by one common field.

Basically it is like -

my search ..

|table name time1

|append[search ... table name time2]

stats list(time1) as time1 list(time2) as time2 by name

So in my first search, I am reading a log file which appends timestamp every time the HTTP service is restarted through our set script.

and my second search is coming from the HTTP monitoring set, which is giving me the time when the URL was down.

Now from my second search ,I also can get the time of when the URl came up but I wont be sure if it came up through set script or any other way.

So time1 field will update the timestamp every time when the http service goes down, but time2 field will only be updated if the script was triggered.

So basically what i want is that, whenever the time2 field is updated, it should have exact corresponding timestamp in time1 field which would be nothing but the latest timestamp match of time1 field as compare to time2 field.

For e.g -

let us consider in last 24 hour our url went down multiple times but only twice it came up by our script. So currently from my search results will be just sorted according to time like -

name time1 time2

url1 10am 11:08

11 17:05

What i want is something like -

name time1 time2

url1 11 11:08

17 17:05

Re: How to compare and match timestamp of one field with the latest timestamp of other field before calculating duration

to4kawa — Fri, 03 Jul 2020 22:36:38 GMT

| makeresults | eval name="url" ,time1=split("10,11,14,17",","), time2=split("11:08,17:05",",") | rename COMMENT as "This is your sample" | rename COMMENT as "the logic" | eval date=strftime(now(),"%F") | stats values(name) as name values(date) as date min(time1) as firstline max(time1) as lastline by time2 | streamstats count | eval time1=if(count=1,firstline,lastline) | eval duration=tostring(round(strptime(date." ".time2,"%F %H:%M") - strptime(date." ".time1.":00","%F %H:%M")),"duration") | table name time1 time2 duration

Re: How to compare and match timestamp of one field with the latest timestamp of other field before calculating duration

ak9092 — Tue, 07 Jul 2020 07:03:36 GMT

Hi @to4kawa ,

Thank you for your response

In the given query i am getting only min and max time results for time1 field i.e. it will consider only two entries in time1 field while matching the time2 field which should not be the case as there can be multiple entries.

Also i cannot group my results by time2 field as this is coming from other search.

Basically there are two searches which I am using ,one returns 'time1' field and other returns 'time2' field and both searches have common 'name' field.

So basically what i want is, once the results are returned, the time2 field should match the latest timestamp in time1 field before calculating the duration.

So for example - say 'time1' fields have returned below five timestamps in results and 'time2' fields has returned just one timestamp(can also be more), so currently my result in splunk will look something like

name time1 time2

url1 07/07/2020 07:11:06 07/07/2020 08:30:10

07/07/2020 07:15:05

07/07/2020 08:20:10

07/07/2020 08:27:19

07/07/2020 09:11:05

What i would like to have is just the latest timestamp match of time1 field next to time2 field -

name time1 time2

url1 07/07/2020 08:27:19 07/07/2020 08:30:10

Re: How to compare and match timestamp of one field with the latest timestamp of other field before calculating duration

to4kawa — Wed, 08 Jul 2020 09:25:17 GMT

What is the formula?
latest time1 is 07/07/2020 09:11:05

Re: How to compare and match timestamp of one field with the latest timestamp of other field before calculating duration

ak9092 — Wed, 08 Jul 2020 12:00:39 GMT

Yes that is correct, but it should not consider that.

It should look for all time2 timestamps and map the closest time1 timestamps in line with that time2 timestamps .

So in the above example you can see that time2 field has only one timestamp entry i.e. - 07/07/2020 08:30:10 so this entry should be mapped against the latest near by timestamp in time1 field which would be 07/07/2020 08:27:19 and as of now it is showing against 07/07/2020 07:11:06 in time1 field, which is not correct.

So say if i add one more timestamp entry in time2 field of 07/07/2020 07:18:25 in above example ,so this entry should be mapped against the timestamp of 07/07/2020 07:15:05 in time1 field as it is the latest near by timestamp for the added timestamp in time2 field.

So now the result should look something like

name time1 time2

url1 07/07/2020 07:15:05 07/07/2020 07:18:25

07/07/2020 08:27:19 07/07/2020 08:30:10

Actually there is no specific formula to it, just trying to figure out how to build the logic to get the desired result.