Reporting

How to append a value to results from a lookup table

IsmailK
Engager
 

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 NoOfTransactionsPerStatusPercentTransactions
Authentication Failure2050.40
Successful46,80899.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

Labels (1)
0 Karma
1 Solution

ITWhisperer
Ultra Champion
| 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

View solution in original post

0 Karma

ITWhisperer
Ultra Champion
| 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

View solution in original post

0 Karma

IsmailK
Engager
This solution worked!! Thank you. Greatly appreciated
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.
0 Karma
Take the 2021 Splunk Career Survey

Help us learn about how Splunk has
impacted your career by taking the 2021 Splunk Career Survey.

Earn $50 in Amazon cash!