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
SplunkTrust
SplunkTrust
| 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
SplunkTrust
SplunkTrust
| 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
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, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Security Highlights: September 2022 Newsletter

 September 2022 The Splunk App for Fraud Analytics (SFA) is now Splunk SupportedUse your existing Splunk ...

Platform Highlights | September 2022 Newsletter

 September 2022 What’s New in 9.0 and How to UpgradeGet a walk through of what is new Splunk Enterprise 9.0 ...

Observability Highlights | September 2022 Newsletter

 September 2022 Splunk Observability SuiteAccess to "Classic" SignalFx Interface Will be Removed on Sept 30, ...