topic How to use streamstats and identify consecutive events? in Splunk Search

How to use streamstats and identify consecutive events?

gvk_us — Tue, 09 May 2023 06:27:22 GMT

Hi,

We have applications Availability data in splunk.
With below SPL, I got this data.

Base_SPL..| streamstats reset_on_change=true count as Real_Status by status,JonName

The challenge is to identify, if 2 or more successive failure have happened.

Only show ALL Fail events, if 2 or more successive failures.
In the below table, only the contents highlighted in RED needs to be shown. (Ignore Failure, highlighted in GREEN)

If i filter with below SPL, then i will miss the 1st FAIL event (Highlighted in RED)

where Real_Status > 1

So how do I solve this challenge?

JobName	status	Real_Status
Process1	SUCCESS	1
Process1	SUCCESS	2
Process1	FAIL	1
Process1	SUCCESS	1
Process1	FAIL	1
Process1	FAIL	2
Process1	FAIL	3

Re: Need help with streamstats and identify consecutive events

yeahnah — Mon, 08 May 2023 20:53:22 GMT

Hi @gvk_us

This run anywhere example should meet your requirement...

| makeresults | eval _raw="JobName,status Process1,SUCCESS Process1,SUCCESS Process1,FAIL Process1,SUCCESS Process1,SUCCESS Process1,FAIL Process1,FAIL Process1,FAIL" | multikv forceheader=1 | table JobName,status | streamstats window=2 count as Real_Status BY status JobName | where status="FAIL" AND Real_Status>=2

Hope that helps

Re: How to use streamstats and identify consecutive events?

richgalloway — Tue, 09 May 2023 12:40:53 GMT

Add another streamstats to find the maximum value of Real_Status and then filter out events with a max value < 2.

Base_SPL.. | streamstats reset_on_change=true count as Real_Status by status,JobName | eventstats max(Real_Status) as conseq_status by status,JobName | where conseq_status > 1

Re: Need help with streamstats and identify consecutive events

gvk_us — Tue, 09 May 2023 02:41:53 GMT

This solution is missing the 1st event of the consecutive events, as highlighted.

Re: Need help with streamstats and identify consecutive events

gvk_us — Tue, 09 May 2023 02:45:54 GMT

We are still missing the first event in the series of consecutive failures.

Re: Need help with streamstats and identify consecutive events

yeahnah — Tue, 09 May 2023 03:59:45 GMT

It's only a consecutive failure once the second occurrence happens so the first occurrence needs to be captured expanded out as an result row again.

Extending on @richgalloway's better solution, something like this should work...

| streamstats reset_on_change=true count as Real_Status by status,JobName | streamstats values(Real_Status) as status_values max(Real_Status) as conseq_status by status,JobName | where status="FAIL" AND conseq_status>=2 | eval status_count=mvcount(status_values) ,status_values=if(status_count=2, status_values, null()) | mvexpand status_values | eval Real_Status=if(status_count=2, status_values, Real_Status) | table JobName status Real_Status

Re: Need help with streamstats and identify consecutive events

ITWhisperer — Tue, 09 May 2023 05:37:54 GMT

Building on solutions from both @richgalloway and @yeahnah , try something like this

| streamstats reset_on_change=true count as Real_Status by status,JobName | reverse | streamstats reset_on_change=true max(Real_Status) as conseq_status by status,JobName | where status="FAIL" AND conseq_status>1

Re: Need help with streamstats and identify consecutive events

richgalloway — Tue, 09 May 2023 12:41:15 GMT

I see what I did wrong. I've fixed my answer.