Multivalue stats

[me74fhfd]

Hi all, 

I have got this SPL to perform what I was looking for but want to know if there is any more elegant way of achieving the same, possibly using just one stats command:

index=index_1
| stats count(App) AS ACNT by Department
| stats sum(ACNT) as "Application Count" by Department | sort Department
| appendcols [search index=index_1 | dedup App | stats list(App) as Applications by Department | sort Department]
| appendcols [search index=index_1 | dedup Developer | stats count(Developer) as "Developer Count" by Department | sort Department]
| rename Department as Department
| table Department "Application Count" Applications "Developer Count"

Expected output in attach
Thanks

[richgalloway]

I don't understand why you are summing a count.  The sum should be the same as the count from the first stats.

I recommend avoiding the appendcols command unless you are guaranteed to get the same number of results as on the main search.

The stats command can perform multiple calculations so there's no need for appendcols.

index=index_1
| stats count(App) AS "Application Count", list(App) as Applications, count(Developer) as "Developer Count" by Department
| table Department "Application Count" Applications "Developer Count"

[me74fhfd]

Thats a good call for SUM, thanks for that. Overall, provided SPL does not work because before doing "stats list(App)" dedup has to be applied but only for that calculation not for the whole data set, or atleast duplicates in multivalue field to be removed later. Same for Developer field with exception that performing dedup before stats line wont work because its applies to this field only. Basically fact that I have to perform dedup to these 2 calculations and not affecting the whole set is stopping to execute spl you have provided.

Updated SPL from my side looks like this now:

index=index_1 | stats count(Apps) AS "Application Count" by Department | sort Department | appendcols [search index=index_1 | dedup Apps | stats list(Apps) as Applications by Department | sort Department] | appendcols [search index=index_1 | dedup Developer | stats count(Developer) as "Developer Count" by Department | sort Department] | table Department "Application Count" Applications "Developer Count"

[richgalloway]

To avoid duplicates, use values(foo) instead of list(foo) and distinct_count(foo) instead of count(foo) in the stats command.

index=index_1
| stats count(App) AS "Application Count", values(App) as Applications, dc(Developer) as "Developer Count" by Department
| table Department "Application Count" Applications "Developer Count"