topic How to find duration between multiple events for multiple occurrences? in Splunk Search

How to find duration between multiple events for multiple occurrences?

MLL9 — Fri, 02 Sep 2022 14:35:42 GMT

Hi all, I'm hoping that someone can help / point me in the right direction.

I have two events which are being fed into Splunk, one being a raise of an event flag, the other being the removal of the event flag.

Raising

Removal

After some browsing online & through the Splunk support pages I have been able to put together the following query:

(index=[INDEX] *agentmissing*) ("msg='AgentMissing' status flag was raised" OR "msg='AgentMissing' status flag was removed") | rex field=_raw ".*\)\s+(?<status>.*)" | stats latest(_time) as flag_finish by connection_type | join connection_type [ search index=[INDEX] ("msg='AgentMissing' status flag was raised") connection_type=* | stats min(_time) as flag_start by connection_type] | eval difference=flag_finish-flag_start | eval flag_start=strftime(flag_start, "%Y-%m-%d %H:%M") | eval flag_finish=strftime(flag_finish, "%Y-%m-%d %H:%M") | eval difference=strftime(difference,"%H:%M:%S") | table connection_type, flag_start, flag_finish, difference | rename connection_type as Hostname, flag_start as "Flag Raised Time", flag_finish as "Flag End Time", difference as "Total Time" | sort - difference

The above is working, however as I am using the "stats latest" command it is only showing the latest occurrence of the event.

However, I would like to display the time between these events for multiple occurrences. So as an example of the above, it was between 7:47 & 9:31, I would also like to see flags for other time occurrences.

TIA! 🙂

Re: Find duration between multiple events, show multiple occurrences.

gcusello — Fri, 02 Sep 2022 11:58:56 GMT

Hi @MLL9,

did you tried to use?

| stats earliest(_time) AS earliest latest(_time) AS latest by connection_type

Ciao.

Giuseppe

Re: Find duration between multiple events, show multiple occurrences.

MLL9 — Fri, 02 Sep 2022 12:30:00 GMT

Hi @gcusello , thanks for the reply.

Unfortunately yes, I've tried that & its still only shows one results, example below:

Hostname	Start Time	End Time	Difference
Hostname1	Start Time 1	End Time 1	Difference 1

What I was hoping for was:

Hostname	Start Time	End Time	Difference
Hostname1	Start Time 1	End Time 1	Difference 1
Hostname1	Start Time 2	End Time 2	Difference 2
Hostname1	Start Time 3	End Time 3	Difference 3

Re: Find duration between multiple events, show multiple occurrences.

gcusello — Fri, 02 Sep 2022 12:49:55 GMT

Hi @MLL9,

In this case the only way is the transaction command

| transaction maxevents=2 connection_type

in this way you have a transaction every two events,

for this transaction you have: the "Flag raised time", the "End raised time"="Flag raised time"+duration and you have many transaction for each connection time.

if each transaction has a start message and an end message, you could also use the "startswith" and "endswith" options in thetransaction command.

For more infos see at https://docs.splunk.com/Documentation/Splunk/9.0.1/SearchReference/Transaction

Ciao.

Giuseppe

Re: Find duration between multiple events, show multiple occurrences.

MLL9 — Fri, 02 Sep 2022 15:19:27 GMT

Thanks @gcusello ,

| transaction maxevents=2 connection_type

Has pointed me in the right direction 🙂

Re: Find duration between multiple events, show multiple occurrences.

gcusello — Fri, 02 Sep 2022 15:28:57 GMT

Hi @MLL9,

good for you, see next time!

tell me if I can help you more, otherwise, please accept one answer for the other people of Community

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated 😉