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

[ak9092]

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.

[richgalloway]

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

[ak9092]

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

              13

              14

              17

What i want is something like -

name  time1  time2

url1       11         11:08             

               17         17:05 

[to4kawa]

| 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

[ak9092]

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

[to4kawa]

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

[ak9092]

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.