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!

Financial Services Industry Use Cases, ITSI Best Practices, and More New Articles ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Splunk Federated Analytics for Amazon Security Lake

Thursday, November 21, 2024  |  11AM PT / 2PM ET Register Now Join our session to see the technical ...

Splunk With AppDynamics - Meet the New IT (And Engineering) Couple

Wednesday, November 20, 2024  |  10AM PT / 1PM ET Register Now Join us in this session to learn all about ...