topic Re: Last 3 occurrence not like in Splunk Search

Last 3 occurrence not like

praddasg — Wed, 30 Sep 2020 04:18:09 GMT

So my below query gives the result of Rejection % but I need to also filter this one step more where it should not show me the results where last 3 consecutive occurrences of a merchantId had been status "CONFIRMED", is this possible?

Re: Last 3 occurrence not like

to4kawa — Fri, 21 Feb 2020 09:00:30 GMT

| xyseries merchantId, status, count

please provide this result.

Re: Last 3 occurrence not like

praddasg — Fri, 21 Feb 2020 14:15:37 GMT

Re: Last 3 occurrence not like

praddasg — Fri, 21 Feb 2020 14:16:00 GMT

https://share.getcloudapp.com/JruWXOLx

Re: Last 3 occurrence not like

skoelpin — Fri, 21 Feb 2020 15:08:35 GMT

Use streamstats to count for consecutive values. Here's an example to get you started

index=apps
status=CONFIRMED OR status=REJECTED
partner_account_name="Level Up"
| stats count by status, merchantId
| xyseries merchantId, status, count
| eval result = (REJECTED)/((CONFIRMED+REJECTED))*100
| eval count = CONFIRMED + REJECTED
| where count >= 5
| where result >= 20
| sort result desc
| streamstats last(status) AS prev_status by merchantId
| streamstats last(prev_status) AS two_prev_status by merchantId
| eval consecutive_alerts=if(status="CONFIRMED" AND prev_status="CONFIRMED" AND two_prev_status="CONFIRMED","ALERT","GOOD")
| search consecutive_alerts="ALERT"

Re: Last 3 occurrence not like

praddasg — Wed, 30 Sep 2020 04:18:40 GMT

I am not sure if this is working, I removed the just to rule out this condition
| where count >= 5
| where result >= 20

For merchantId=1341282 there has status rejected consecutive
https://share.getcloudapp.com/04uKr6nk

but when I run the below i do not get any result, even though the search criteria is more than 4 days

index=apps
status=CONFIRMED OR status=REJECTED
partner_account_name="Level Up"
| stats count by status, merchantId
| xyseries merchantId, status, count
| eval result = (REJECTED)/((CONFIRMED+REJECTED))*100
| eval count = CONFIRMED + REJECTED
| sort result desc
| streamstats last(status) AS prev_status by merchantId
| streamstats last(prev_status) AS two_prev_status by merchantId
| eval consecutive_alerts=if(status="CONFIRMED" AND prev_status="CONFIRMED" AND two_prev_status="CONFIRMED","ALERT","GOOD")
| search consecutive_alerts="ALERT"

https://share.getcloudapp.com/E0uqlXBp

Re: Last 3 occurrence not like

praddasg — Wed, 30 Sep 2020 04:18:43 GMT

I am not sure if this working, i removed the to remove the condition
| where count >= 5
| where result >= 20

If you see merchantId=1341282 has consecutive REJECTED but not showing up anything in the other query
https://share.getcloudapp.com/04uKr6nk

https://share.getcloudapp.com/E0uqlXBp
used this
index=apps
status=CONFIRMED OR status=REJECTED
partner_account_name="Level Up"
| stats count by status, merchantId
| xyseries merchantId, status, count
| eval result = (REJECTED)/((CONFIRMED+REJECTED))*100
| eval count = CONFIRMED + REJECTED
| sort result desc
| streamstats last(status) AS prev_status by merchantId
| streamstats last(prev_status) AS two_prev_status by merchantId
| eval consecutive_alerts=if(status="CONFIRMED" AND prev_status="CONFIRMED" AND two_prev_status="CONFIRMED","ALERT","GOOD")
| search consecutive_alerts="ALERT"

Re: Last 3 occurrence not like

skoelpin — Fri, 21 Feb 2020 16:02:19 GMT

Remove the last line to see your table format. This will show 3 additional columns, first is prev_status, second is two_prev_status and lastly consecutive_alerts. Verify this is working as expected

Re: Last 3 occurrence not like

praddasg — Wed, 30 Sep 2020 04:18:52 GMT

I executed this

I am getting the result like this https://share.getcloudapp.com/p9uKjoKm

there are now new columns as prev_status , two_prev_status & consecutive_alerts ( i am fine if these columns are not showing up)
but my main objective is to show be merchants in table with their reject % which did not have last 3 consecutive status as confirmed.

As per the result tried checking with merchantId=1286021 but i can see there was one last confirmed
https://share.getcloudapp.com/lluyzAg7

BTW I tried changing this line
| eval consecutive_alerts=if(status="CONFIRMED" AND prev_status="CONFIRMED" AND two_prev_status="CONFIRMED","ALERT","GOOD")

| eval consecutive_alerts=if(status!="CONFIRMED" AND prev_status!="CONFIRMED" AND two_prev_status!="CONFIRMED","ALERT","GOOD")

and

| eval consecutive_alerts=if(status="REJECTED" AND prev_status="REJECTED" AND two_prev_status="REJECTED","ALERT","GOOD")

but not luck

Re: Last 3 occurrence not like

praddasg — Wed, 30 Sep 2020 04:18:54 GMT

BTW I tried changing this line
| eval consecutive_alerts=if(status="CONFIRMED" AND prev_status="CONFIRMED" AND two_prev_status="CONFIRMED","ALERT","GOOD")

| eval consecutive_alerts=if(status!="CONFIRMED" AND prev_status!="CONFIRMED" AND two_prev_status!="CONFIRMED","ALERT","GOOD")

and

| eval consecutive_alerts=if(status="REJECTED" AND prev_status="REJECTED" AND two_prev_status="REJECTED","ALERT","GOOD")

but not luck

Re: Last 3 occurrence not like

praddasg — Wed, 30 Sep 2020 04:18:57 GMT

not sure if this makes any sense, I was trying to break the query little bit more to understand why it is showing merchantId where prev status was confirmed,

Executed this
index=apps
sourcetype="pos-generic:prod" Received request to change status=CONFIRMED OR status=REJECTED
partner_account_name="Level Up"
| streamstats last(status) AS prev_status by merchantId
| streamstats last(prev_status) AS two_prev_status by merchantId
| eval consecutive_alerts=if(status="CONFIRMED" OR prev_status="CONFIRMED" OR two_prev_status="CONFIRMED","ALERT","GOOD")
| table merchantId, status, prev_status, two_prev_status, consecutive_alerts
| search consecutive_alerts="GOOD"

merchantId = 1290828 shows rejected in all three columns status, prev_status & two_prev_status

https://share.getcloudapp.com/04uKrBQ4

But if individually try to find this merchantId, the last 2 status is rejected but the 3rd one is confirmed

sourcetype="pos-generic:prod" partner_account_name="Level Up" merchantId=1290828
| table _time, status
| stats count by _time, status
|sort _time desc

https://share.getcloudapp.com/xQug9rRz

Re: Last 3 occurrence not like

to4kawa — Fri, 21 Feb 2020 21:40:22 GMT

UPDATE:

index=apps status=CONFIRMED OR status=REJECTED
AND partner_account_name="Level Up"
| reverse
| streamstats window=3 list(status) as check_status by merchantId
| streamstats count as session by merchantId
| eventstats max(session) as last_session by merchantId
| stats count(eval(status="REJECTED")) as REJECTED 
,count(eval(status="CONFIRMED")) as CONFIRMED
,values(eval(if(session==last_session,check_status,NULL))) as check_status by merchantId
| eval check=if(mvcount(check_status)=1 AND match(check_status,"CONFIRMED"),1,0)
| where check > 0
| eval result = (REJECTED)/((CONFIRMED+REJECTED))*100
| eval count = CONFIRMED + REJECTED
| where count >= 5 AND result >= 20
| sort result desc

@praddasg
This query aims to exclude the result that has consecutive status CONFIRMED three times last.

Re: Last 3 occurrence not like

praddasg — Wed, 30 Sep 2020 04:19:00 GMT

yes your understanding is correct exclude result that CONFIRMED for last 3 times. But this query also giving me a merchantId which has status CONFIRMED.

Not sure if this relevant but why the below query of streamstats is giving so many result instead just 1, I think the time factor needs to be included to resolve the overall

index=apps
sourcetype="pos-generic:prod" AND "Received request to change" AND (status=CONFIRMED OR status=REJECTED) merchantId=1400622
partner_account_name="Level Up"
| streamstats last(status) AS ABC
| table merchantId, ABC

https://share.getcloudapp.com/E0uqlD1p

Re: Last 3 occurrence not like

to4kawa — Fri, 21 Feb 2020 23:13:06 GMT

my query

Aggregate:

status
---------
REJECTED
CONFIRMED
CONFIRMED
REJECTED

Not aggregate:

status
---------
REJECTED
CONFIRMED
CONFIRMED
CONFIRMED

but , your request seems to be:

aggregate:

status
---------
REJECTED
REJECTED
REJECTED

Re: Last 3 occurrence not like

to4kawa — Fri, 21 Feb 2020 23:23:12 GMT

| makeresults count=10
| eval status=mvindex(split("CONFIRMED,REJECTED",","),random() %2)
| streamstats last(status) as status_last

Check this.

index=apps
sourcetype="pos-generic:prod" AND "Received request to change" AND (status=CONFIRMED OR status=REJECTED) merchantId=1400622
partner_account_name="Level Up"
| streamstats last(status) AS ABC
| table merchantId, ABC

This your query's ABC is same of status.

Re: Last 3 occurrence not like

praddasg — Sat, 22 Feb 2020 14:06:28 GMT

I am not sure I understood completely