I have a search that return the no. of transactions for each individual http result code and maps them to the respective result code name, like "2xx, 3xx maps to "Successful", 4xx maps to "Auth Failures" and 5xx maps to "Server Error"" in a lookup table. I then use Power BI as the reporting tool via Splunk ODBC.
Power BI expects to have all three result code names in the search report every time, so if for instance, there are no failures for that period, then "Server Error" will not be in the search report. Which means that the Power BI report refresh fails. So I need to be able to have all 3 result code names returned regardless, and just fill in 0 as the number of transactions
Part of my search shown below
index search
| lookup AppResultCode ResultCode as TransactionStatus OUTPUT Status
| stats sum(NoOfTransactionsPerStatus) as NoOfTransactionsPerStatus sum(PercentTransactions) as PercentTransactions by Status
| table Status NoOfTransactionsPerStatus PercentTransactions
Status | NoOfTransactionsPerStatus | PercentTransactions |
Authentication Failure | 205 | 0.40 |
Successful | 46,808 | 99.57 |
In the case above, the result doesn't include "Server Error". So am looking at adding it and fill in 0 for No. of transactions and percentage
| lookup AppResultCode ResultCode as TransactionStatus OUTPUT Status
| append [ | makeresults | eval Status=split("Successful,Authentication Failure,Server Error",",") | mvexpand Status | fillnull value=0 NoOfTransactionsPerStatus PercentTransactions | fields - _time ]
| stats sum(NoOfTransactionsPerStatus) as NoOfTransactionsPerStatus sum(PercentTransactions) as PercentTransactions by Status
| table Status NoOfTransactionsPerStatus PercentTransactions
| lookup AppResultCode ResultCode as TransactionStatus OUTPUT Status
| append [ | makeresults | eval Status=split("Successful,Authentication Failure,Server Error",",") | mvexpand Status | fillnull value=0 NoOfTransactionsPerStatus PercentTransactions | fields - _time ]
| stats sum(NoOfTransactionsPerStatus) as NoOfTransactionsPerStatus sum(PercentTransactions) as PercentTransactions by Status
| table Status NoOfTransactionsPerStatus PercentTransactions
Perhaps this will help.
index search
| lookup AppResultCode ResultCode as TransactionStatus OUTPUT Status
| stats sum(NoOfTransactionsPerStatus) as NoOfTransactionsPerStatus sum(PercentTransactions) as PercentTransactions by Status
| inputlookup append=true AppResultCode
| fillnull value=0 NoOfTransactionsPerStatus PercentTransactions
| stats sum(NoOfTransactionsPerStatus) as NoOfTransactionsPerStatus, sum(PercentTransactions) as PercentTransactions
| table Status NoOfTransactionsPerStatus PercentTransactions