topic Re: mvcount and stats count give different results in Splunk Search

mvcount and stats count give different results

viggor — Thu, 18 Jan 2018 18:41:53 GMT

I have a log file where each line has an itemId and a clusterId.
When I run the following sort of queries

| stats count(itemId) as clusterSize by clusterId 
| sort - clusterSize

| stats list(itemId) AS items BY clusterId 
| eval clusterSize=mvcount(items) 
| sort -clusterSize

and get different results. I don't know if it's a coincidence but the second command results in largest clusterSizes of exactly 100.

Does anybody have an idea?

Re: mvcount and stats count give different results

mayurr98 — Thu, 18 Jan 2018 18:58:07 GMT

hey

list(X)
Returns a list of up to 100 values of the field X as a multivalue entry. The order of the values reflects the order of input events.

have a look in this official doc http://docs.splunk.com/Documentation/Splunk/7.0.1/SearchReference/Multivaluefunctions#list.28X.29

so your first query output is correct while your second query results in largest clusterSizes of exactly 100 because of its limit (gives wrong output) and that is why there is a mismatch.

let me know if this helps !

Re: mvcount and stats count give different results

mporath_splunk — Thu, 18 Jan 2018 18:59:41 GMT

Per the Splunk documentation, list() Returns a list of up to 100 values of the field X as a multivalue entry.

Re: mvcount and stats count give different results

cmerriman — Thu, 18 Jan 2018 19:01:36 GMT

the list command only returns 100 field values. if there are more than 100 values of itemId, this is why there is that problem in the second query.
http://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/CommonStatsFunctions#Supported_functions_and_syntax

if you're looking for a total count of itemIds by clusterId, the first query works great, if you want to know how many unique itemIds are in each clusterId, try |stats dc(itemId) as clusterSize by clusterId