Splunk Search

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

Subrahmanyab
New Member

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
alt text

This is by the time it finalizes the job
alt text

Tags (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

Subrahmanyab
New Member

HI Gluseppe,

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

0 Karma

Subrahmanyab
New Member

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

Subrahmanyab
New Member

Sorry to say but it's the same result

0 Karma

lakromani
Builder

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

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

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...