topic Re: My case statement is putting events in the "other" category -- why? in Splunk Search

My case statement is putting events in the "other" category -- why?

Subrahmanyab — Tue, 29 Sep 2020 15:55:12 GMT

Hi guys,
So i have a user_agent and a url field for an elb log file. I am checking the user agent field for the values that contain Googlebot and Bingbot. If the useragent field has either of these values i want them to be displayed in the results as google_bot and bing_bot, otherwise the events that dont match either of these conditions should fall under the other category. My problem is when the search finalizes, it ends up sticking every event in the other category, but while it is running the search, it splits them by the proper category that i want (google_bot,bing_bot, other). I don' understand why my case statement default to putting every event in the other category. I have tried multiple commands, shown below, but they all end with the same results where every event is placed in the other category. Can anyone help me understand why it is doing this?

index=vgl | eval bot= coalesce(case(user_agent LIKE "%google%", "google_bot", user_agent LIKE "%bing%","bing_bot"), "other")|chart count(bot) AS count_bot by url, bot usenull=false

index=vgl | eval bot= case(user_agent LIKE "%google%", "google_bot", user_agent LIKE "%bing%","bing_bot", True(), "other")|chart count(bot) AS count_bot by url, bot usenull=false

index=vgl | eval bot= case(user_agent LIKE "%google%", "google_bot", user_agent LIKE "%bing%","bing_bot", 1=1, "other")|chart count(bot) AS count_bot by url, bot usenull=false

This is what how I want my results

This is by the time it finalizes the job

Re: My case statement is putting events in the "other" category -- why?

gcusello — Sat, 23 Sep 2017 06:55:16 GMT

HI Subrahmanyab,
did you tried with

index=vgl 
| eval bot=case(user_agent LIKE "%google%", "google_bot", user_agent LIKE "%bing%","bing_bot", NOT (user_agent LIKE "%google%" OR  user_agent LIKE "%bing%"), "other")
| chart count(bot) AS count_bot by url, bot usenull=false

Bye.
Giuseppe

Re: My case statement is putting events in the "other" category -- why?

Subrahmanyab — Mon, 25 Sep 2017 06:34:00 GMT

HI Gluseppe,

Thank you for the response I tried your command but the result is the same

Re: My case statement is putting events in the "other" category -- why?

gcusello — Mon, 25 Sep 2017 06:45:02 GMT

HI Subrahmanyab,
Sorry, I was sleeping yesterday morning, please try:

 index=vgl 
 | eval bot=case(like(user_agent,"%google%"), "google_bot",like(user_agent,"%bing%"),"bing_bot",1=1, "other")
 | chart count(bot) AS count_bot by url, bot usenull=false

Bye.
Giuseppe

Re: My case statement is putting events in the "other" category -- why?

Subrahmanyab — Mon, 25 Sep 2017 07:46:02 GMT

Sorry to say but it's the same result

Re: My case statement is putting events in the "other" category -- why?

gcusello — Tue, 29 Sep 2020 15:51:51 GMT

HI Subrahmanyab,
to debug problem see events (running search in verbose mode or without chart command) and see if bot field has values ("google_bot", "bing_bot", "other") for all events or not, in this way you can understand if the eval command is correct (100%) or not.

if it's correct, you have to debug chart command.

Eval command in my example is correctly valorized, try to modify chart command:

 index=vgl 
  | eval bot=case(like(user_agent,"%google%"), "google_bot",like(user_agent,"%bing%"),"bing_bot",1=1, "other")
  | chart count over url BY bot

Bye.
Giuseppe

Re: My case statement is putting events in the "other" category -- why?

lakromani — Mon, 25 Sep 2017 08:00:59 GMT

How come you marked this as accepted when it does not work?

Re: My case statement is putting events in the "other" category -- why?

Subrahmanyab — Mon, 25 Sep 2017 14:53:01 GMT

thank you, cusello, It is working now, I removed my index file and re loaded the index and now it is working