topic Re: How to calculate time difference between two identical events in Splunk Search

How to calculate time difference between two identical events

peeeeeeeeeeter — Mon, 23 Sep 2019 06:00:21 GMT

I have the following events

**2019-09-20 01:39:25 INFO Listener processing event with message metal:AUD:ADJ
2019-09-19 23:58:27 INFO Listener processing event with message metal:USD:ADJ
2019-09-19 23:58:20 INFO Listener processing event with message metal:USD:ADJ
2019-09-19 23:19:30 INFO Listener processing event with message metal:AUD:ADJ
2019-09-21 04:15:55 INFO Listener processing event with message metal:AUD:ADJ
2019-09-20 21:12:11 INFO Listener processing event with message metal:USD:ADJ
2019-09-21 04:15:45 INFO Listener processing event with message metal:AUD:ADJ

2019-09-20 21:12:17 INFO Listener processing event with message metal:USD:ADJ**

These events are exactly same in pattern, and they only differ in timestamps (The events come in pairs, sometimes there are many pairs with the same key). The timestamps are startTime and endTime. I am trying to summarise these events, by grouping the pair into one row, and calculating the duration.

So the output will look like this.

**product | currency | type | startTime | endTime | duration
metal | AUD | ADJ | 2019-09-20 01:39:25 | 2019-09-19 23:19:30 | 5
|2019-09-21 04:15:45 | 2019-09-21 04:15:55 | 10
metal | USD | ADJ | 2019-09-19 23:58:20 | 2019-09-19 23:58:27 | 7

| 2019-09-20 21:12:11 | 2019-09-20 21:12:17 | 6**

I have began my search as follows:

source="*.log"
| rex field=_raw "message (?<product>.*?):(?<Currency>.*?):(?<type>.*)"

| table Config Intent Currency RunType AccountingDate _time

I have tried both the transaction and stats function, yet in vain. Maybe I am not using them correctly.

Thanks to David, I made some progress by using the first/last(earliest/latest) function. However they only capture the first and last event. Is there a way to group them by the same key, and aggregate the values like my output as above.

Is there anyone who can give me any advice on this issue? Any help will be highly appreciated.

Re: How to calculate time difference between two identical events

DavidHourani — Mon, 23 Sep 2019 07:27:20 GMT

Hi Peter with many e,

This seems to be the same as :
https://answers.splunk.com/answers/772916/time-difference-by-grouping-identical-events.html?childToView=773813#answer-773813

You should be able to use the following command as well:

 source="*.log"
 | rex field=_raw "message (?<product>.*?):(?<Currency>.*?):(?<type>.*)"
 |stats earliest(_time) as StartTime latest(StartTime) as EndTime by product,Currency,type
 | eval duration= tostring(EndTime-StartTime,"duration")

Let me know how that works out for you.

Cheers,
David

Re: How to calculate time difference between two identical events

jacobpevans — Mon, 23 Sep 2019 23:01:24 GMT

Good answer, but you'd really want to use earliest() and latest() in case the events are not sorted chronologically (or in the reverse direction).

https://splunkbase.splunk.com/app/1603/

Keep up the great work @DavidHourani 😉 you always give good answers.

Re: How to calculate time difference between two identical events

peeeeeeeeeeter — Mon, 23 Sep 2019 23:46:23 GMT

Hi David, thank you for the answer and I apologise for repeated question. It was the first time I used this site...

The query is great, it gives me the correct format. However, the first and last function only capture the first and last event with the same key. In my logs, there are more than one pair of such events, they normally come next to each other.

Re: How to calculate time difference between two identical events

jacobpevans — Tue, 24 Sep 2019 03:14:31 GMT

The two options that come to mind to me are using a stats (building off of David's answer) with a _time delimiter, or using transaction (https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Transaction)

Normally, stats is preferred, but that might not work out for you. Do you know the maximum time between events? Transaction performs a similar thing to David's suggestion, but you can specify a max gap time between events so that you get multiple matches even when the events are otherwise identical (besides time). To use stats, you need to add bin (https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Bin) e.g. | bin _time span=5m | stats earliest(_time) as StartTime latest(StartTime) as EndTime by product,Currency,type

Transaction also gives you a built-in duration field so no need to calculate yourself.

Re: How to calculate time difference between two identical events

DavidHourani — Tue, 24 Sep 2019 03:20:06 GMT

@jacobevans Thanks man! And you're right, I've edited the answer ^^

@peeeeeeeeeeter for some reason I thought that those fields would build a sort of serial number, but I guess I missed many pairs with the same key. So I see two ways to do this :
1- Make a new field using streamstats to include the latest time, then use that field for the duration.

       source="*.log"
      | rex field=_raw "message (?<product>.*?):(?<Currency>.*?):(?<type>.*)"
      | streamstats current=f window=1 latest(StartTime) as EndTime earliest(_time) as StartTime by product,Currency,type
      | eval duration= tostring(EndTime-StartTime,"duration")
      | table product Currency type EndTime StartTime duration

This might not do the trick though because there is no way to tell which event is a start and which is an end and that means that we will get the duration between any two consecutive events with the same keys.

2- Create a serial number using streamstats or eval then use that serialnumber to build the transaction either using stats or transaction . This is the right way to do it, but I don't see how we can do that since all the events look the same.

Hope this helps.