Splunk Search

How to count number of values case-insensitive, but show the most popular case version in the results (ex: 2 "Apple" + 1 "apple" = 3 "Apple")?

eugenek
Path Finder

I would like to count ignoring case, which can be down with eval lower. However, when displaying the results, I would like to show the "most popular" version of the capitalization.

Example:
q=Apple
q=apple
q=Apple
q=PC

The count for apple would be 3 when ignoring case, but is there a way to use the most popular variant of the capitalizes (in this case "Apple") associated with the total count of 3?

Best I can come up with right now would be a rather manual approach:
1. Create a new field that’s all the same case using lower()
2. Calculate the counts for that field
3. Calculate the counts for the original, mixed-case field
4. Create a new field which takes the capitalization from the most popular version, based on step 3
5. As the result, use label values from step 4 and counts from step 2

If there is no built-in way, I'll mark implementation of the steps above as an accepted answer.

1 Solution

somesoni2
Revered Legend

Try something like this

Your base search giving field q | stats count by q | sort -count | eval qtemp=lower(q) | stats sum(count) as count list(q) as q list(count) as countq by qtemp | eval q=mvindex(q,0) | table q, count

A sample runanywhere query :

|gentimes start=-1 | eval q="Apple apple Apple PC apple Apple pc pc aPPLE aPPLE aPPLE aPPLE" | table q | makemv q | mvexpand q | stats count by q | sort -count | eval qtemp=lower(q) | stats sum(count) as count list(q) as q list(count) as countq by qtemp | eval q=mvindex(q,0) | table q, count

View solution in original post

somesoni2
Revered Legend

Try something like this

Your base search giving field q | stats count by q | sort -count | eval qtemp=lower(q) | stats sum(count) as count list(q) as q list(count) as countq by qtemp | eval q=mvindex(q,0) | table q, count

A sample runanywhere query :

|gentimes start=-1 | eval q="Apple apple Apple PC apple Apple pc pc aPPLE aPPLE aPPLE aPPLE" | table q | makemv q | mvexpand q | stats count by q | sort -count | eval qtemp=lower(q) | stats sum(count) as count list(q) as q list(count) as countq by qtemp | eval q=mvindex(q,0) | table q, count

eugenek
Path Finder

If I could give bonus points for "sample runanywhere query", I would. Well done!
... I guess I can give bonus points.

0 Karma

stephane_cyrill
Builder

That is really good. for your information you can give bonus to someone by clicking on award points

0 Karma

stephane_cyrill
Builder

Hi, eugenek

Assuming that the field contening how value is q , with the following you can have what you are describing easily.

1- Count of value of field q that start with lowercase (apple):

.... q=* | where like(q, "apple%")|stats count(q) AS Count_apple

2- Count of value of field q that start with uppercase (Apple):

.... q=* | where like(q, "Apple%")|stats count(q) AS Count_Apple

3- count of the original mixed field( only for values of apple & Apple) :

.... q=* | where like(q, "Apple%") OR where like(q, "apple%") |stats count(q) AS mixed_count

0 Karma

eugenek
Path Finder

That's a very specific case. See accepted answer for a generic solution

0 Karma
Get Updates on the Splunk Community!

Routing Data to Different Splunk Indexes in the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

Getting Started with AIOps: Event Correlation Basics and Alert Storm Detection in ...

Getting Started with AIOps:Event Correlation Basics and Alert Storm Detection in Splunk IT Service ...

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...