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?
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
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.
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
| 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.
I am not sure I understood completely
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
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"
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"
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
I executed 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")
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")
to
| 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
BTW I tried changing this line
| eval consecutive_alerts=if(status="CONFIRMED" AND prev_status="CONFIRMED" AND two_prev_status="CONFIRMED","ALERT","GOOD")
to
| 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
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
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"
| xyseries merchantId, status, count
please provide this result.