Splunk Search

How to edit my search to find the top 5 distinct count and the total?

splunkrocks2014
Communicator

Assuming I have a lookup table with movie title and location, and I got the top 5 location based on distinct title count from the following search. What is the best way to get the top 5 distinct title count and the total from a search? Thanks.

| inputlookup movies.csv | stats dc(title) as count by location | sort limit=5 -count 
0 Karma

aaraneta_splunk
Splunk Employee
Splunk Employee

@splunkrocks2014 - Looks like you have a few possible solutions to your question. If one of them provided a working solution, please don't forget to click "Accept" below the best answer to resolve this post. If you still need help, please leave a comment. Don’t forget to upvote anything that was helpful too. Thanks!

woodcock
Esteemed Legend

Like this:

| inputlookup movies.csv | eventstats dc(title) AS TotalTitles | stats first(TotalTitles) AS TotalTitles dc(title) AS count BY location | sort 5 -count | eval count = count . "/" . TotalTitles | fields - TotalTitles
0 Karma

somesoni2
Revered Legend

Try like this

| inputlookup movies.csv | stats dc(title) as count by location | eventstats sum(count) as total | sort limit=5 -count 
0 Karma

adayton20
Contributor

Are you simply trying to sum a total count from all the distinct title counts? Ie, adding together all counts in the column? You could try using addcoltotals: https://docs.splunk.com/Documentation/Splunk/6.5.2/SearchReference/Addcoltotals

If that doesn't answer your question, could you describe a little further what you're looking for?

0 Karma

splunkrocks2014
Communicator

If the query doesn't calculate the unique count, "addcoltotals" does all the work, for instance,

| inputlookup movies.csv | stats count(title) as count by location | sort limit=5 -count | addcoltotals

However, when used in the distinct query, "addcoltotals" adds up all numbers from dc(Title) which is incorrect.

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi splunkrocks2014,
what is your question? your search seems to be correct.
Bye.
Giuseppe

0 Karma
Get Updates on the Splunk Community!

CX Day is Coming!

Customer Experience (CX) Day is on October 7th!! We're so excited to bring back another day full of wonderful ...

Strengthen Your Future: A Look Back at Splunk 10 Innovations and .conf25 Highlights!

The Big One: Splunk 10 is Here!  The moment many of you have been waiting for has arrived! We are thrilled to ...

Now Offering the AI Assistant Usage Dashboard in Cloud Monitoring Console

Today, we’re excited to announce the release of a brand new AI assistant usage dashboard in Cloud Monitoring ...