topic Re: Add Percentage Column next to the Count in Splunk Search

Add Percentage Column next to the Count

rakeshkumar19 — Wed, 17 Apr 2019 20:56:19 GMT

Please help me to add percentage column

SourceName, Count, %
ABC , 20, 5%
XYZ, 10, 2%

index=prod_sum
| dedup SourceName,filestotal
| stats count(filestotal) as Count by SourceName
| sort - Count limit=10

Re: Add Percentage Column next to the Count

niketn — Wed, 17 Apr 2019 21:35:11 GMT

[UPDATED]

Query corrected below....

@rakeshkumar19 rather than using stats followed by sort you should ideally use the top command which is meant for this use case. Following is run anywhere example based on Splunk's _internal index.

index=_internal sourcetype=splunkd
| top 10 component

However, since you seem to have duplicate records in your index (you should figure out why rather than using dedup). You should try the following

index=prod_sum
| stats count as Count by SourceName,filestotal 
| stats sum(Count) as Count by SourceName 
| eventstats sum(Count) as Total
| eval perc=round((Count/Total)*100,2)
| fields - Total
| sort 0 - Count 
| head 10

Following stats command also gets you unique records by SourceName and filestotal | stats count as Count by SourceName,filestotal. Since stats uses map-reduce it may perform better than dedup (depending on total volume of records). So please performance test and use this approach. If there is no performance improvement as per Job Inspector, your existing query can be modified as:

index=prod_sum
| dedup SourceName,filestotal 
| top 10 SourceName

Re: Add Percentage Column next to the Count

rakeshkumar19 — Wed, 17 Apr 2019 21:44:58 GMT

Thank you for help. Please let me know how to add Percentage next to the Counts.

Appreciate your help

Re: Add Percentage Column next to the Count

rakeshkumar19 — Wed, 17 Apr 2019 21:56:12 GMT

This code worked perfectly and there was no performance issue

index=prod_sum
| stats count as Count by SourceName,filestotal
| stats sum(Count) as Count by SourceName
| sort 0 - Count
| head 10

How to add percentage column ?

Re: Add Percentage Column next to the Count

niketn — Thu, 18 Apr 2019 00:40:33 GMT

@rakeshkumar19 sorry I missed percent calculation with stats. I have updated the query please check!

Re: Add Percentage Column next to the Count

vnravikumar — Thu, 18 Apr 2019 02:27:42 GMT

try the following to add %

.... | eval perc=round((Count/Total)*100,2)."%"
|rename perc as % | fields - Total

Re: Add Percentage Column next to the Count

rakeshkumar19 — Thu, 18 Apr 2019 13:48:38 GMT

Thank you for your help. The query is running as expected.
Any place where I can explore the Splunk functions and usage.

Re: Add Percentage Column next to the Count

niketn — Thu, 18 Apr 2019 21:14:34 GMT

@rakeshkumar19 Splunk Docs is the best place with list of all commands and examples of their usage. You can check out Search Reference Documentation link.