topic Re: How to get the count of unique values for a field per event? in Splunk Search

How to get the count of unique values for a field per event?

Aaron_Fogarty — Mon, 25 Apr 2016 16:15:35 GMT

I have a field named Visit that can have several vales: "Order Entry", "Order Reference" and 5 others. I want to count every Visit per event only once per value.

What I mean by this is that in Example one, the Visit count for this event will be only 1
and in Example two, the Visit count for the event will be 2.

Event Example one:

Client - User:123 - Visit="Order Entry" - Apr 25, 2016, 9:34:58 AM EDT
Client - User:123 - New selection begin  - Apr 25, 2016, 9:34:58 AM EDT
Client - User:123 - New selection begin  - Apr 25, 2016, 9:34:58 AM EDT
Client - User:123 - Loading selection begin  - Apr 25, 2016, 9:34:58 AM EDT
Client - User:123 - Visit="Order Entry" - Apr 25, 2016, 9:34:58 AM EDT

Event Example two:

Client - User:456 - Visit="Order Entry" - Apr 25, 2016, 6:46:22 AM EDT
Client - User:456 - New order begin  - Apr 25, 2016, 6:46:22 AM EDT
Client - User:456 - Visit="Order Reference" - Apr 25, 2016, 6:46:22 AM EDT
Client - User:456 - Successful login.  Failed login attempts 0.

Thank you.

Re: How to get the count of unique values for a field per event?

javiergn — Mon, 25 Apr 2016 17:11:20 GMT

Hi,

If your events are separated as you described above, you can try this:

your search here
| rex max_match=0 "Visit=(?<visits>\"[^\"]+\")"
| mvexpand visits
| stats values(visits) as visits dc(visits) as visit_counts by _raw

Example 1:

| stats count
| eval _raw = "
 Client - User:123 - Visit=\"Order Entry\" - Apr 25, 2016, 9:34:58 AM EDT
 Client - User:123 - New selection begin  - Apr 25, 2016, 9:34:58 AM EDT
 Client - User:123 - New selection begin  - Apr 25, 2016, 9:34:58 AM EDT
 Client - User:123 - Loading selection begin  - Apr 25, 2016, 9:34:58 AM EDT
 Client - User:123 - Visit=\"Order Entry\" - Apr 25, 2016, 9:34:58 AM EDT
"
| rex max_match=0 "Visit=(?<visits>\"[^\"]+\")"
| mvexpand visits
| stats values(visits) as visits dc(visits) as visit_counts by _raw

# visit_counts = 1

Example 2:

| stats count
| eval _raw = "
 Client - User:456 - Visit=\"Order Entry\" - Apr 25, 2016, 6:46:22 AM EDT
 Client - User:456 - New order begin  - Apr 25, 2016, 6:46:22 AM EDT
 Client - User:456 - Visit=\"Order Reference\" - Apr 25, 2016, 6:46:22 AM EDT
 Client - User:456 - Successful login.  Failed login attempts 0.
"
| rex max_match=0 "Visit=(?<visits>\"[^\"]+\")"
| mvexpand visits
| stats values(visits) as visits dc(visits) as visit_counts by _raw


# visit_counts = 2

Re: How to get the count of unique values for a field per event?

Aaron_Fogarty — Tue, 26 Apr 2016 09:14:57 GMT

That is perfect javiergn. Exactly what I was looking to do. Cheers.