Add Percentage Column next to the Count

[rakeshkumar19]

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

[niketn · Accepted solution]

[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

[rakeshkumar19]

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

[niketn]

@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.

[rakeshkumar19]

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 ?

[rakeshkumar19]

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

Appreciate your help

[niketn]

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

[vnravikumar]

hi

try the following to add %

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