Splunk Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Ask a Question

Last 3 occurrence not like

praddasg
Path Finder
‎02-20-2020 10:14 AM

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

Tags (1)
0 Karma
Reply

to4kawa
Ultra Champion
‎02-21-2020 01:40 PM

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.

0 Karma
Reply

praddasg
Path Finder
‎02-21-2020 01:56 PM

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

0 Karma
Reply

to4kawa
Ultra Champion
‎02-21-2020 03:23 PM 
| 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.

0 Karma
Reply

praddasg
Path Finder
‎02-22-2020 06:06 AM

I am not sure I understood completely

0 Karma
Reply

to4kawa
Ultra Champion
‎02-21-2020 03:13 PM

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
0 Karma
Reply

skoelpin
SplunkTrust
SplunkTrust
‎02-21-2020 07:08 AM

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"
0 Karma
Reply

praddasg
Path Finder
‎02-21-2020 07:53 AM

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"

0 Karma
Reply

skoelpin
SplunkTrust
SplunkTrust
‎02-21-2020 08:02 AM

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

0 Karma
Reply

praddasg
Path Finder
‎02-21-2020 09:28 AM

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

0 Karma
Reply

praddasg
Path Finder
‎02-21-2020 09:38 AM

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

0 Karma
Reply

praddasg
Path Finder
‎02-21-2020 12:33 PM

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

0 Karma
Reply

praddasg
Path Finder
‎02-21-2020 07:50 AM

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

0 Karma
Reply

to4kawa
Ultra Champion
‎02-21-2020 01:00 AM 
| xyseries merchantId, status, count

please provide this result.

0 Karma
Reply

praddasg
Path Finder
‎02-21-2020 06:16 AM

https://share.getcloudapp.com/JruWXOLx

0 Karma
Reply

praddasg
Path Finder
‎02-21-2020 06:15 AM

alt text

0 Karma
Reply
Get Updates on the Splunk Community!

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...

Introducing Splunk Enterprise 9.2

WATCH HERE! Watch this Tech Talk to learn about the latest features and enhancements shipped in the new Splunk ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...