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")?

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.

Tags (3)
1 Solution
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
``````
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
``````
Path Finder

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

Builder

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

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

Path Finder

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

Get Updates on the Splunk Community!

#### Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

#### Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...

#### DevSecOps: Why You Should Care and How To Get Started

WATCH NOW In this Tech Talk we will talk about what people mean by DevSecOps and deep dive into the different ...