Splunk Search

How to create two searches combined into one chart, or timechart with calculated percent of total (rate) by fields?

rvazquez8113
New Member

I have transactions logged across different sales "channels" (catering, mobileApp, faceToFace, etc.). I am trying to display the transaction Decision counts along with the percentOfTotal (Rate) of each Decision type.

first search:
host=server* "Catering" txnType=order | timechart span=1d dc(txnId) as "totalTxns" << this base search is a discrete total of all transactions in all use cases in the catering channel.

second search:
host=server* "Catering" txnType=order issuerResponse="authorized" NOT ("avsMismatch" OR "cvvMismatch") | timechart span=1d dc(txnId) by Decision | rename APPROVE as "Accept", REJECT as "Decline", ERROR as "Error" << this base search is one use case in the catering channel that is a subset of the first search.

calculation:
| eval percentOfTotal=(round((Decision/totalTxns)*100)."%") << I'm intending for this to be percentOfTotal displayed in a chart for each of the three Decision types (Accept, Decline, Error) found in the second search over a span=1d. However, in attempting to chart this doesn't seem like a way to accomplish what I am trying to do.

Ideally the data could be presented similar to the following:

-totalTxns being greater than the sum of all Decisions is expected as this is only one specific use case within this channel.

alt text

0 Karma
1 Solution

lguinn2
Legend

Try this; it may not be the most efficient search, but it should work

host=server* "Catering" txnType=order | timechart span=1d dc(txnId) as "totalTxns"
| append [ search host=server* "Catering" txnType=order issuerResponse="authorized" 
           NOT ("avsMismatch" OR "cvvMismatch") 
     | timechart span=1d dc(txnId) by Decision 
     | rename APPROVE as "Accept", REJECT as "Decline", ERROR as "Error" ]
| stats first(*) as * by _time
| foreach Accept Decline Error [ eval <<FIELD>>_percentOfTotal=(round((<<FIELD>>/totalTxns)*100)."%") ]
| rename _time as Date, Accept_percentOfTotal as "Accept %", Decline_percentOfTotal as "Decline %",
      Error_percentOfTotal as "Error %", totalTxns as "Total Txn"
| table Date "Total Txn" Accept "Accept %"  Decline "Decline %" Error  "Error %"

View solution in original post

0 Karma

lguinn2
Legend

Try this; it may not be the most efficient search, but it should work

host=server* "Catering" txnType=order | timechart span=1d dc(txnId) as "totalTxns"
| append [ search host=server* "Catering" txnType=order issuerResponse="authorized" 
           NOT ("avsMismatch" OR "cvvMismatch") 
     | timechart span=1d dc(txnId) by Decision 
     | rename APPROVE as "Accept", REJECT as "Decline", ERROR as "Error" ]
| stats first(*) as * by _time
| foreach Accept Decline Error [ eval <<FIELD>>_percentOfTotal=(round((<<FIELD>>/totalTxns)*100)."%") ]
| rename _time as Date, Accept_percentOfTotal as "Accept %", Decline_percentOfTotal as "Decline %",
      Error_percentOfTotal as "Error %", totalTxns as "Total Txn"
| table Date "Total Txn" Accept "Accept %"  Decline "Decline %" Error  "Error %"
0 Karma

rvazquez8113
New Member

Thank you Iguinn! absolutely brilliant!

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...