How to find duration between multiple events for multiple occurrences?

[MLL9]

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

Sep 2 10:32:45 SOFTWARE CEF:0|SOFTWARE|CLIENT|42|Agent Log Event|Agent Log Event|high|id=123 shost=Management start=2022-09-02 10:32:42 cs1Label=Affected Agents cs1=[SERVERNAME] (ip: None, component_id: ID) msg='AgentMissing' status flag was raised

Removal

Sep 2 10:34:33 SOFTWARE CEF:0|SOFTWARE|CLIENT|42|Agent Log Event|Agent Log Event|high|id=123 shost=Management start=2022-09-02 10:34:33 cs1Label=Affected Agents cs1=[SERVERNAME] (ip: None, component_id: ID) msg='AgentMissing' status flag was removed

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! 🙂

[gcusello]

Hi @MLL9,

did you tried to use?

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

Ciao.

Giuseppe

[MLL9]

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

[gcusello]

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

[MLL9 · Accepted solution]

Thanks @gcusello , 

| transaction maxevents=2 connection_type

Has pointed me in the right direction 🙂 

[gcusello]

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 😉