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!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...