topic Re: How do I count unique values in a multi value field? in Splunk Search

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

aarontmartin165 — Tue, 17 Apr 2018 13:38:33 GMT

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?

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

Sukisen1981 — Tue, 17 Apr 2018 13:44:35 GMT

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

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

damien_chillet — Tue, 17 Apr 2018 13:46:05 GMT

You should try adding a space in your split separator:

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

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

p_gurav — Tue, 17 Apr 2018 13:47:14 GMT

Can you try:

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

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

evsmt — Tue, 17 Apr 2018 14:07:15 GMT

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

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

aarontmartin165 — Tue, 17 Apr 2018 14:35:10 GMT

Why would I use the -1?

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

aarontmartin165 — Tue, 17 Apr 2018 14:37:11 GMT

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

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

damien_chillet — Tue, 17 Apr 2018 14:45:37 GMT

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.

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

aarontmartin165 — Tue, 17 Apr 2018 15:13:57 GMT

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.