topic Re: Why is my fillnull search with a BY clause not returning any results? in Splunk Search

Why is my fillnull search with a BY clause not returning any results?

tkwaller — Tue, 15 Nov 2016 14:59:17 GMT

Hello

Trying to get this search to work, it works if I remove the BY clause:

index=java host=*myhost* "PLACEORDER_API_UNSUCCESSFUL" orderSourceId=7 OR orderSourceId=6 OR orderSourceId=5 | eval Channel=case(orderSourceId == 7, "Desktop", orderSourceId == 6, "Andriod", orderSourceId == 5, "iOS") | stats count AS Failure BY Channel

The issue is that the base search does not return any results.

I tried | fillnull value=NULL but it doesn't seem to work because of the BY clause. If I remove it, it works fine.

Any ideas?
Thanks!

Re: Why is my fillnull search with a BY clause not returning any results?

gcusello — Tue, 15 Nov 2016 15:05:27 GMT

Hi tkwaller,
If your search runs when you remove the BY clause means that, in your events, you haven't the "Channel" field with values.
Verify if the field name is correct and in how many events is present.

Bye.
Giuseppe

Re: Why is my fillnull search with a BY clause not returning any results?

tkwaller — Tue, 15 Nov 2016 15:07:53 GMT

No meaning if I remove the BY clause and run it with the fillnull command at the end it works fine:

index=java host=*myhost* "PLACEORDER_API_UNSUCCESSFUL" orderSourceId=7 OR orderSourceId=6 OR orderSourceId=5 | eval Channel=case(orderSourceId == 7, "Desktop", orderSourceId == 6, "Andriod", orderSourceId == 5, "iOS") | stats count AS Failure | fillnull value=NULL`

Re: Why is my fillnull search with a BY clause not returning any results?

gcusello — Tue, 15 Nov 2016 15:22:53 GMT

Hi tkwaller,
Sorry but I didn't understand your need:

you have results running a stats count with BY clause,
why you add the fillnull command? after a stats command you haven't any null results!
"fillnull" replaces null values with a specified value;
do you want to have the Channel values also without events?

if you need is to have all results for all the Channel values (both with or without events) you have to create a lookup table with all your Channels and run something like this:

 | inputlookup Channels.csv | eval count=0, Channel=lower(Channel) | append [ search yoursearch | eval Channel=lower(Channel) | stats count by Channel ] | stats sum(count) AS Total

In this way you have all the results for Channels, both with or without events.

Bye.
Giuseppe

Re: Why is my fillnull search with a BY clause not returning any results?

niketn — Tue, 15 Nov 2016 15:24:41 GMT

There are two ways you could do it.

Option 1
Using your case statements you need to add double quotes for values 5, 6 and 7 on right side of evaluation expression.

index=java host=*myhost* "PLACEORDER_API_UNSUCCESSFUL" orderSourceId=7 OR orderSourceId=6 OR orderSourceId=5 | eval Channel=case(orderSourceId == "7", "Desktop", orderSourceId == "6", "Andriod", orderSourceId == "5", "iOS") | stats count AS Failure BY Channel

Option 2

index=java host=*myhost* "PLACEORDER_API_UNSUCCESSFUL" orderSourceId=7 OR orderSourceId=6 OR orderSourceId=5 | eval Channel=orderSourceId | replace "7" with "Desktop" in Channel|replace "6" with "Android" in Channel| replace "5" with "iOS" in Channel| stats count AS Failure BY Channel

PS:

Instead of eval Channel=orderSourceID you can also use rename orderSourceId as Channel.
While Using replace command numbers 5, 6 and 7 need not be in double quotes, but safety does not harm 🙂

Re: Why is my fillnull search with a BY clause not returning any results?

tkwaller — Tue, 15 Nov 2016 16:17:06 GMT

Yes the lookup table worked. Thank you.

Here is the reason I ask. I am trying to combine that search along with some other stuff to create a dashboard with. I got it working, just evaluating it now:

| inputlookup channel.csv| eval count=0, Channel=lower(Channel)| append [ search index=java host=*myhost* "PLACEORDER_API_UNSUCCESSFUL" orderSourceId=7 OR orderSourceId=6 OR orderSourceId=5 | eval Channel=lower(Channel) | stats count AS Failure BY Channel]| stats sum(count) AS Failure BY Channel | appendcols [search index=java host=*myhost* "PLACEORDER_API_SUCCESSFUL"  orderSourceId=7 OR orderSourceId=6 OR orderSourceId=5 | eval Channel=case(orderSourceId == 7, "Desktop", orderSourceId == 6, "Andriod", orderSourceId == 5, "iOS") |stats count as Success by Channel]| appendcols [search (index=java host=*myhost* "Request received for placeOrder")  OR (index=java host=*myhost* "PLACEORDER_API_REQUEST" orderSourceId=*) 
| transaction cartId maxspan=5sec| eval Channel=case(orderSourceId == 7, "Desktop", orderSourceId == 6, "Andriod", orderSourceId == 5, "iOS") | stats count by Channel] | eval Success_Percentage=(Success/(Success+Failure))*100 | table Channel Success Failure Success_Percentage

Re: Why is my fillnull search with a BY clause not returning any results?

tkwaller — Tue, 15 Nov 2016 16:53:30 GMT

the only thing I haven't figured out yet is how to get the description/Channel names included in the table.

In the channel.csv I have a field called description that names the channels:
"Desktop", "Andriod", "iOS"

Re: Why is my fillnull search with a BY clause not returning any results?

tkwaller — Tue, 15 Nov 2016 16:55:02 GMT

and the final result

| inputlookup channel2.csv| eval count=0, Channel=lower(Channel)| append [ search index=java host=*myhost* "PLACEORDER_API_UNSUCCESSFUL" orderSourceId=7 OR orderSourceId=6 OR orderSourceId=5 | eval Channel=lower(Channel) | stats count AS Failure BY Channel] |stats sum(count) AS Failure BY Channel | appendcols [search index=java host=*myhost* "PLACEORDER_API_SUCCESSFUL" orderSourceId=7 OR orderSourceId=6 OR orderSourceId=5 | eval Channel=case(orderSourceId == 7, "Desktop", orderSourceId == 6, "Andriod", orderSourceId == 5, "iOS") |stats count as Success by Channel]| appendcols [search (index=java host=*myhost* "Request received for placeOrder") OR (index=java host=*myhost* "PLACEORDER_API_REQUEST" orderSourceId=*) | transaction cartId maxspan=5sec| eval Channel=case(orderSourceId == 7, "Desktop", orderSourceId == 6, "Andriod", orderSourceId == 5, "iOS") | stats count by Channel] | eval Success_Percentage=(Success/(Success+Failure))*100 | table Channel Success Failure Success_Percentage

Had to update the lookup a bit to get it to return the description as the Channel name