Splunk Enterprise

Multivalue stats

me74fhfd
Path Finder

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

 

Labels (2)
Tags (2)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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"
---
If this reply helps you, Karma would be appreciated.

me74fhfd
Path Finder

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"

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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"
---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...