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!

Admin Your Splunk Cloud, Your Way

Join us to maximize different techniques to best tune Splunk Cloud. In this Tech Enablement, you will get ...

Cloud Platform | Discontinuing support for TLS version 1.0 and 1.1

Overview Transport Layer Security (TLS) is a security communications protocol that lets two computers, ...

New Customer Testimonials

Enterprises of all sizes and across different industries are accelerating cloud adoption by migrating ...