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!
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
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:
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
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`
Hi tkwaller,
Sorry but I didn't understand your need:
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
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
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"
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