i am looking difference of 2 events in one source file and those events having same values and have to calculate difference.
After append search got below values.
_time | Customer | order |
2021-03-25 08:30:28.485 | 123456 | |
2021-03-25 03:53:57.201 | 123457 | |
2021-03-25 04:43:50.254 | 123458 | |
2021-03-25 12:59:31.464 | 123459 | |
2021-03-25 08:30:28.485 | 123456 | |
2021-03-25 03:53:57.201 | 123457 | |
2021-03-25 04:48:50.254 | 123458 | |
2021-03-25 12:59:31.464 | 123451 | |
2021-03-25 22:59:31.464 | 123452 | |
2021-03-25 04:59:31.464 | 123454 | |
2021-03-25 04:59:33.464 | 123454 | |
2021-03-25 05:00:31.464 | 123454 | |
2021-03-25 05:05:31.464 | 123454 | |
2021-03-25 05:10:35.464 | 123454 |
Output be like:
Starttime | EndTime | ID | (Start- END)Diff |
2021-03-25 08:30:28.485 | 2021-03-25 08:30:28.485 | 123456 | |
2021-03-25 03:53:57.201 | 2021-03-25 03:53:57.201 | 123457 | |
2021-03-25 04:43:50.254 | 2021-03-25 04:48:50.254 | 123458 | |
2021-03-25 04:59:31.464 | 2021-03-25 04:59:33.464 | 123454 | |
2021-03-25 05:05:31.464 | 2021-03-25 05:10:35.464 | 123454 |
i am trying to take like below but all values are not coming properly and after calculating the difference i have to visualization greater than 10 mins chart.
base search
| table _time customer
| rename customer as ID _time as Starttime
| append
[search base search
|table _time order
| rename order as ID _time as Endtime ]
| table Starttime Endtime ID
| stats count as new values(*) as * by ID
| eval new1=mvzip(Starttime, Endtime)
| mvexpand new1
| makemv delim="," new1
| eval Starttime1=mvindex(new1,0)
| eval Endtime1=mvindex(new1,1)
| table Senttime1 Endtime1 ID new
Thanks in advance.
Obviously, there are a couple of situations in your data that you need to cope with, e.g. 123454 "starts" multiple times and you appear to only want the most recent start for your calculation, and a number of starts which don't have ends e.g. 123459
base search
| table _time customer
| eval Starttime=_time
| append
[search base search
|table _time order
| eval Endtime=_time ]
| eval ID=coalesce(customer,order)
| sort ID _time
| filldown Starttime
| where isnotnull(order)
| eval Diff=tostring((Endtime-Starttime),"duration")
| fields - customer order _time
| fieldformat Starttime=strftime(Starttime,"%Y-%m-%d %H:%M:%S.%Q")
| fieldformat Endtime=strftime(Endtime,"%Y-%m-%d %H:%M:%S.%Q")
| fields Starttime Endtime ID Diff
Thanks for the quick reply.