Splunk Search

How do I count unique values in a multi value field?

aarontmartin165
Explorer

I have a field cat which may display multiple fields of varying count FFIEC, GLBA, PPI or just PPI so there is no set count to the multivalue fields. I am attempting to count the number of times each unique value appears and graph it over time. My query is as follows:

my query | eval Policies=split(cat,";") | timechart span=1h count by Policies

My problem is that when the line chart is displayed there can be multiple lines for a Policy value. For example if the multivalue field returns 3 instances as follows

FFIEC; GLBA; PPI
PPI
PPI
FFIEC; GLBA; PPI

My line values would display PPI = 2 FFIEC = 2 GLBA= 2 PPI = 2

What I am hoping to achieve is PPI=4 FFIEC=2 GLBA=2

Can anyone identify the part of my query I have wrong?

0 Karma
1 Solution

damien_chillet
Builder

You should try adding a space in your split separator:

my query | eval Policies=split(cat,"; ") | timechart span=1h count by Policies

View solution in original post

evsmt
Explorer

Use mvexpand to create an event for each multi value value. You'll be able create a timechart with a line for each distict policy:

my query 
| eval Policies=split(cat,";") 
| mvexpand Policies
| timechart span=1h count by Policies
0 Karma

p_gurav
Champion

Can you try:

my query | eval To_count=mvcount(split(cat,"; "))-1  | timechart span=1h values(To_count) by Policies
0 Karma

aarontmartin165
Explorer

Why would I use the -1?

0 Karma

damien_chillet
Builder

You should try adding a space in your split separator:

my query | eval Policies=split(cat,"; ") | timechart span=1h count by Policies

aarontmartin165
Explorer

That appears to have worked but why?
If
cat= FFIEC; PPI
and
cat= PPI

There is no ; or " " on the second value so why would the "; " be any different than ";"?

0 Karma

damien_chillet
Builder

split function will create a value for the multivalve field overtime it meets the splitter.
So, in first case "cat=FFIEC; PPI" it will return "FFIEC" and " PPI" if you use ";"
In second case it will just return "PPI" because nothing to split.

0 Karma

aarontmartin165
Explorer

Got it, I was thinking that leading spaces would be dropped but as I read your explanation I realize I had no reason I should have expected that.

0 Karma

Sukisen1981
Champion

what happens if you just mvexpand on policies before the timechart?

0 Karma
Get Updates on the Splunk Community!

Thanks for the Memories! Splunk University, .conf24, and Community Connections

Thank you to everyone in the Splunk Community who joined us for .conf24 – starting with Splunk University and ...

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...