topic Re: How to create two searches combined into one chart, or timechart with calculated percent of total (rate) by fields? in Splunk Search

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

rvazquez8113 — Mon, 05 Feb 2018 18:37:54 GMT

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.

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

lguinn2 — Mon, 05 Feb 2018 20:33:04 GMT

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 %"

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

rvazquez8113 — Tue, 06 Feb 2018 23:49:46 GMT

Thank you Iguinn! absolutely brilliant!