Splunk Search

How to calculate time difference between two identical events

peeeeeeeeeeter
Engager

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.

0 Karma

DavidHourani
Super Champion

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?childToV...

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

jacobpevans
Motivator

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.

Cheers,
Jacob

If you feel this response answered your question, please do not forget to mark it as such. If it did not, but you do have the answer, feel free to answer your own post and accept that as the answer.
0 Karma

DavidHourani
Super Champion

@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.

peeeeeeeeeeter
Engager

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.

0 Karma

jacobpevans
Motivator

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.

Cheers,
Jacob

If you feel this response answered your question, please do not forget to mark it as such. If it did not, but you do have the answer, feel free to answer your own post and accept that as the answer.
0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...