Splunk Search

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

tkwaller
Builder

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!

0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

niketn
Legend

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:

  1. Instead of eval Channel=orderSourceID you can also use rename orderSourceId as Channel.
  2. While Using replace command numbers 5, 6 and 7 need not be in double quotes, but safety does not harm 🙂
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

gcusello
SplunkTrust
SplunkTrust

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

tkwaller
Builder

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`
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

tkwaller
Builder

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
0 Karma

tkwaller
Builder

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"

0 Karma

tkwaller
Builder

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

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