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!

More Ways To Control Your Costs With Archived Metrics | Register for Tech Talk

Tuesday, May 14, 2024  |  11AM PT / 2PM ET Register to Attend Join us for this Tech Talk and learn how to ...

.conf24 | Personalize your .conf experience with Learning Paths!

Personalize your .conf24 Experience Learning paths allow you to level up your skill sets and dive deeper ...

Threat Hunting Unlocked: How to Uplevel Your Threat Hunting With the PEAK Framework ...

WATCH NOWAs AI starts tackling low level alerts, it's more critical than ever to uplevel your threat hunting ...