Splunk Search

format my successRate query into a week over week comparison

billycn20
Explorer

i have a working query which is monitoring the success rate based off a value called app_id. i want to extend the current query i have and also show the success rate for each app_id but broken down by currentWeek, lastWeek, 2weeksago success rate percentage.

 

My current query is:
index=jj3 "TRANSACTIONA" OR "TRANSACTIONB" | rex field=log "\"app_id\": \W(?<app_id>\w+)\W" | rex field=log "\"event_name\": \W(?<event_name>[a-zA-Z-|_|:]+)\W" | eval firstTransaction=if(event_name=="TRANSACTIONA", 1, 0) | eval secondTransaction=if(event_name=="TRANSACTIONB", 1, 0) | stats sum(firstTransaction) as TotalfirstTransaction sum(secondTransaction) as TotalsecondTransaction by app_id | dedup app_id | eval successRate=round(TotalsecondTransaction/TotalfirstTransaction*100, 1)."%" | fillnull successRate | sort - successRate | search NOT successRate=0

Labels (1)
Tags (2)
0 Karma
1 Solution

sravankaripe
Communicator

try with this at the end  
select timerange last 30 days like that 

---  ----  ---- | bin _time span=1w | stats count


View solution in original post

0 Karma

sravankaripe
Communicator

try with this at the end  
select timerange last 30 days like that 

---  ----  ---- | bin _time span=1w | stats count


0 Karma

billycn20
Explorer

no, that doesn't seem to provide any desirable results.

0 Karma

sravankaripe
Communicator

remove dedup after stats you may get some results

 

0 Karma

billycn20
Explorer

using that suggestion as well, doesn't provide any change in results. my stats actually still looks identical to my original query:

here is the updated query as per your suggestions:

index=jj3 "TRANSACTIONA" OR "TRANSACTIONB" | rex field=log "\"app_id\": \W(?<app_id>\w+)\W" | rex field=log "\"event_name\": \W(?<event_name>[a-zA-Z-|_|:]+)\W" | eval firstTransaction=if(event_name=="TRANSACTIONA", 1, 0) | eval secondTransaction=if(event_name=="TRANSACTIONB", 1, 0) | bin _time span=2w | stats sum(firstTransaction) as TotalfirstTransaction sum(secondTransaction) as TotalsecondTransaction by app_id | eval successRate=round(TotalsecondTransaction/TotalfirstTransaction*100, 1)."%" | fillnull successRate | sort - successRate | search NOT successRate=0

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...