topic Re: I Need Help Filling Null Fields with Zero in Splunk Search

I Need Help Filling Null Fields with Zero

skoelpin — Wed, 02 Sep 2015 14:31:20 GMT

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

Re: I Need Help Filling Null Fields with Zero

thirumalreddyb — Tue, 29 Sep 2020 07:13:08 GMT

Hope this helps.

Re: I Need Help Filling Null Fields with Zero

skoelpin — Wed, 02 Sep 2015 14:45:05 GMT

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

Re: I Need Help Filling Null Fields with Zero

somesoni2 — Wed, 02 Sep 2015 15:36:39 GMT

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

Re: I Need Help Filling Null Fields with Zero

woodcock — Wed, 02 Sep 2015 15:47:20 GMT

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.

Re: I Need Help Filling Null Fields with Zero

skoelpin — Tue, 29 Sep 2020 07:13:10 GMT

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

Re: I Need Help Filling Null Fields with Zero

skoelpin — Wed, 02 Sep 2015 17:30:34 GMT

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!!!

Re: I Need Help Filling Null Fields with Zero

woodcock — Wed, 02 Sep 2015 18:15:06 GMT

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.

Re: I Need Help Filling Null Fields with Zero

skoelpin — Wed, 02 Sep 2015 18:19:55 GMT

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?

Re: I Need Help Filling Null Fields with Zero

woodcock — Wed, 02 Sep 2015 18:38:26 GMT

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.

Re: I Need Help Filling Null Fields with Zero

skoelpin — Wed, 02 Sep 2015 18:42:30 GMT

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