topic Re: Summing of row in a column on condition in Splunk Search

Summing of row in a column on condition

vinay_ks04 — Tue, 28 Jan 2014 06:30:04 GMT

I have three columns

ColumnA     ColumnB     ColumnC
vin          1            1
vin          1            2 
vin          2            3
kum          1            1
kum          1            2
kum          2            3

I wanted sum columnc and find out the percentage by ColumnA

ColumnA     ColumnB     ColumnC     "Totalof Columnc by Site" Percentage
vin          1            3              6                       50
             2            3              6                       50
kum          1            3              6                       50
             2            3              6                       50

Re: Summing of row in a column on condition

lguinn2 — Tue, 28 Jan 2014 06:43:46 GMT

Try this

yoursearchhere
| stats sum(ColumnC) as CTotal by ColumnA ColumnB  
| eventstats sum(CTotal) as SiteTotal by ColumnA
| eval Percentage = round(CTotal*100/SiteTotal,0)

Re: Summing of row in a column on condition

David — Tue, 28 Jan 2014 06:44:34 GMT

Based on your result, it looks like you want to add something like this to the end of your search:

| stats sum(ColumnC) as "Total of ColumnC by Site" by ColumnA, ColumnB | eventstats sum(ColumnC) as OverallColumnCTotal | eval Percentage = round("Total of ColumnC by Site" / OverallColumnCTotal * 100,0) | table ColumnA ColumnB ColumnC "Total of ColumnC by Site" Percentage

Does that match what you're looking for?

Re: Summing of row in a column on condition

vinay_ks04 — Mon, 28 Sep 2020 15:44:10 GMT

Thanks a lot it is working.......

I am adding one more column to just print the value which is there in the log, but it is not printing.
I am using "list(Config) As "Config Name"

index="autolab_prod" sourcetype="Percentage_Usage_Config" | stats sum(taskcount) as TOTAL_Task by Site ConfigID | eventstats sum(TOTAL_Task) as SiteTotal by Site | eval percent=round(100*TOTAL_Task/SiteTotal,0) | stats list(ConfigID) AS "CONFIG ID", list(CONFIG) AS "CONFIG NAME", list(TOTAL_Task) AS "TOTAL_Task", list(SiteTotal) AS SiteTotal, list(percent) as PERCENTAGE by Site

Re: Summing of row in a column on condition

vinay_ks04 — Tue, 28 Jan 2014 07:28:30 GMT

thank you,
yes, but I am not able to add one more column.

Re: Summing of row in a column on condition

vinay_ks04 — Mon, 28 Sep 2020 15:44:18 GMT

Thanks a lot 🙂
I have added extra columnname in stats, below is my query.

index="autolab_prod" sourcetype="Percentage_Usage_Config" | stats sum(taskcount) as TOTAL_Task by Site ConfigID CONFIG | eventstats sum(TOTAL_Task) as SiteTotal by Site | eval percent=round(100*TOTAL_Task/SiteTotal,0) | stats list(ConfigID) AS "CONFIG ID", list(CONFIG) AS CONFIGNAME, list(TOTAL_Task) AS "TASK_TOTAL", list(SiteTotal) AS TASK_TOTAL_COUNT, list(percent) as PERCENTAGE by Site

Re: Summing of row in a column on condition

lguinn2 — Thu, 30 Jan 2014 07:39:41 GMT

After the stats command, your search pipeline no longer has any fields except the ones that are the result of the command: TOTAL_Task Site and ConfigID

So if you want the CONFIG to be included, you will have to include it in the by clause of the stats command.