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
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?
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.
Try like this
| inputlookup movies.csv | stats dc(title) as count by location | eventstats sum(count) as total | sort limit=5 -count
| 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
@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!