Archive

How to get a count of stats list that contains a specific data?

Path Finder

Hi all,

How to get a count of stats list that contains a specific data? Data is populated using stats and list() command.
Boundary: date and user. There are at least 1000 data. Sample example below.

Date User list(data)
3/31/17 user1 1, 2, 4

3/31/17 user2 1, 3

3/31/17 user3 8

Let say I want to count user who have list(data) that contains number bigger than "1". Then, the user count answer should be "3".
I tried using "| where 'list(data)' >1 | chart count(user) by date" , but it gives me a userCount of "1" for this case, as it ignores the list that have 3 or 2 data.

0 Karma
1 Solution

Revered Legend

Try like this

your current search giving Date User list(data)
| where isnotnull(mvfilter('list(data)'>1))
| chart count(user) by date

View solution in original post

Esteemed Legend

It REALLY helps to have the whole search.

0 Karma

Revered Legend

Try like this

your current search giving Date User list(data)
| where isnotnull(mvfilter('list(data)'>1))
| chart count(user) by date

View solution in original post

SplunkTrust
SplunkTrust

That one works by ignoring all values of the multivalue field list(data) that do not match your search criteria, in this case, >1. Those values are retained in the data, which is useful if you want to, for example, see what other values are present in records that have a particular value.

There's a less efficient method available as well, but a method that might seem more approachable to some beginners, and which would eliminate all the values that did not match. This method uses mvexpand to break up the single record with multiple values in the mv field into one record per mv value in the field. Remember, though, that if more than one of the multiple values might survive the filter, then you would be counting the number of VALUES, not the number of Users, so you use dc (distinct count) rather than count.

your current search giving Date User list(data)
| rename list(data) as mvdata
| mvexpand mvdata
| where (mvdata>1)
| chart dc(User) by Date
0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!