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

[Subrahmanyab]

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

[gcusello · Accepted solution]

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

[Subrahmanyab]

HI Gluseppe,

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

[Subrahmanyab]

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

[gcusello]

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

[Subrahmanyab]

Sorry to say but it's the same result

[lakromani]

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

[gcusello]

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

Bye.
Giuseppe