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
@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!
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
Try like this
| inputlookup movies.csv | stats dc(title) as count by location | eventstats sum(count) as total | 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.
Hi splunkrocks2014,
what is your question? your search seems to be correct.
Bye.
Giuseppe