Splunk Search

I Need Help Filling Null Fields with Zero

skoelpin
SplunkTrust
SplunkTrust

I have 2 tax calls (CalculateTax and LookupTax) and want to count their errors for the previous day's hour. I then added a row which would sum the totals up. So My table looks like this

Tax Call     |   YesterdayLastHour 
CalculateTax |        70
Total        |        70

But I want it to look like this

Tax Call     |   YesterdayLastHour 
CalculateTax |        70
LookupTax    |         0
Total        |        70

Currently, if there are values for both CalculateTax and LookupTax then it will display correctly like this

    Tax Call     |   YesterdayLastHour 
    CalculateTax |        70
    LookupTax    |        10
    Total        |        80

Here's my search

index=vertex7-access RTG_Error="500" earliest=-26h@h latest=-25h@h 
| stats count AS YesterdayLastHour by RTG_Tax 
| addtotals col=t row=f labelfield=RTG_Tax label=Total 
| table RTG_Tax, YesterdayLastHour | rename RTG_Tax AS Total

So the problem I'm facing is, if there is a zero count for a LookupTax call, then the tax call will not display. I want the LookupTax call to display, but show a 0 for the count

0 Karma
1 Solution

woodcock
Esteemed Legend

Try this (very hackish but will work):

 index=vertex7-access RTG_Error="500" earliest=-26h@h latest=-25h@h | append [| noop | stats count AS RTG_Tax | eval RTG_Tax = "LookupTax"] | stats count AS YesterdayLastHour by RTG_Tax | eval YesterdayLastHour = YesterdayLastHour - if((RTG_Tax = "LookupTax"), 1, 0) | addtotals col=t row=f labelfield=RTG_Tax label=Total | table RTG_Tax, YesterdayLastHour | rename RTG_Tax AS Total

It works by artificially inflating "LookupTax" by 1 to ensure that it exists and then decreasing it by 1 once the row/event is established.

View solution in original post

woodcock
Esteemed Legend

Try this (very hackish but will work):

 index=vertex7-access RTG_Error="500" earliest=-26h@h latest=-25h@h | append [| noop | stats count AS RTG_Tax | eval RTG_Tax = "LookupTax"] | stats count AS YesterdayLastHour by RTG_Tax | eval YesterdayLastHour = YesterdayLastHour - if((RTG_Tax = "LookupTax"), 1, 0) | addtotals col=t row=f labelfield=RTG_Tax label=Total | table RTG_Tax, YesterdayLastHour | rename RTG_Tax AS Total

It works by artificially inflating "LookupTax" by 1 to ensure that it exists and then decreasing it by 1 once the row/event is established.

skoelpin
SplunkTrust
SplunkTrust

I had an error on my end.. Testing it now, so far it's working correctly. Will post back with final results soon. Thanks for the help!!!

0 Karma

woodcock
Esteemed Legend

Note that I only addressed the possibility of LookupTax being 0 so if other fields may be 0 also, the same approach can be used for them.

0 Karma

skoelpin
SplunkTrust
SplunkTrust

You're pretty good at reading my mind.. I just got done fixing that

This search was only a piece of a larger search I had to integrate it with aswell. I just got done cleaning everything up and now I'm testing it. It looks good so far, just need to go over a few test cases still

P.S. Can you explain what noop does?

0 Karma

woodcock
Esteemed Legend

It does nothing ("NO OPeration"); it is the most minimalist way to generate a single event by piping to stats count which will always return 0.

0 Karma

skoelpin
SplunkTrust
SplunkTrust

This search works perfectly!! Thanks for your help, I appreciate it

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try something like this (assuming that you have "CalculateTax" and "LookupTax" hardcoded values for RTG_Tax)

     index=vertex7-access RTG_Error="500" earliest=-26h@h latest=-25h@h 
     | stats count AS YesterdayLastHour by RTG_Tax 
 | append [| gentimes start=-1 | eval RTG_Tax="CalculateTax LookupTax" | table RTG_Tax makemv RTG_Tax | mvexpand RTG_Tax | eval YesterdayLastHour=0] | stats max(YesterdayLastHour) as YesterdayLastHour by RTG_Tax
     | addtotals col=t row=f labelfield=RTG_Tax label=Total 
     | table RTG_Tax, YesterdayLastHour | rename RTG_Tax AS Total

skoelpin
SplunkTrust
SplunkTrust

Thanks for help @somesoni2

This solution partially worked.. Unfortunately I did not hard code CalculateTax and LookupTax for RTG_Tax.. I did a field extraction for RTG_Tax and wrote a regular expression which would pick up CalculateTax and LookupTax. When I tried your solution, it is indeed working if LookupTax has a value of 0. But when I modify the time where we had values for both Lookup and Calculate, then there is an additional row which is showing 0.. Is there a way we can write an IF statement saying IF both calculatetax and lookuptax have values, THEN ignore the hard coded value

Here's what it looks like if CalculateTax and LookupTax have values>0

    Tax Call       |   YesterdayLastHour
    CalculateTax   |    54 
    LookupTax      |     0 
    LookupTax      |     2
    TOTAL          |    56
0 Karma

thirumalreddyb
Communicator

index=vertex7-access RTG_Error="500" earliest=-26h@h latest=-25h@h
| fillnull value=0 LookupTax
| stats count AS YesterdayLastHour by RTG_Tax
| addtotals col=t row=f labelfield=RTG_Tax label=Total
| table RTG_Tax, YesterdayLastHour | rename RTG_Tax AS Total

Hope this helps.

0 Karma

skoelpin
SplunkTrust
SplunkTrust

I tried that with no luck already 😕

I'm thinking that I have to hardcode in CalculateTax and Lookuptax rather than calling RTG_Tax so it's forced to pick it up..

The field 'Lookuptax' doesn't even show up if there's no value for YesterdayLastHour

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...