topic Re: Display stats percentage in row instead of columns in Splunk Search

Display stats percentage in row instead of columns

new2splunk1 — Sat, 05 Jun 2021 04:43:45 GMT

Hi Splunk experts,

I'm generating stats from 3 indexes (System A, B, C) and the results look like this:

Table 1:

The totals, Success%, Failed% are calcuated based on the below commands:

| addcoltotals col=t labelfield=SystemA_TranName

| eval Success%=if(SystemA_TranName=="Total",round((Success/Total_Transaction_Counts)*100,2)."%","")
| eval Failed%=if(SystemA_TranName=="Total",round((Failed/Total_Transaction_Counts)*100,2)."%","")

My requirement is to show the Success% and Failed% columns highlighted in Table 1 by adding a Percentage row to the end and display the percentage values in Success column and Failed column as shown in Table 2 below.

Table2:

I'm trying to get the desired results as shown in Table 2 by writing the below query:

| addcoltotals col=t labelfield=SystemA_TranName
| eval Success%=if(SystemA_TranName=="Total",round((Success/Total_Transaction_Counts)*100,2)."%","")
| eval Failed%=if(SystemA_TranName=="Total",round((Failed/Total_Transaction_Counts)*100,2)."%","")
| fields SystemA_TranName, SystemA_TranStatus, SystemB_TranName, SystemB_TranStatus, SystemC_TranName, SystemC_TranStatus, Total_Transaction_Counts, Success, Failed
| append [| gentimes start=-1
| eval SystemA_TranName="Percentage"
| eval SystemA_TranStatus=""
| eval SystemB_TranName=""
| eval SystemB_TranStatus=""
| eval SystemC_TranName=""
| eval SystemC_TranStatus=""
| eval Total_Transaction_Counts=""
| eval Success=Success%
| eval Failed=Failed%
| table SystemA_TranName SystemA_TranStatus SystemB_TranName SystemB_TranStatus SystemC_TranName SystemC_TranStatus Total_Transaction_Counts Success Failed]

As shown in Table 3 below, I'm able to add a Percentage row to the end but the percentage values 80.00% and 25.00% are not displayed in Success and Failed column respectively. Any suggestions, please?

Table3:

Re: Display stats percentage in row instead of columns

ITWhisperer — Sat, 05 Jun 2021 05:15:59 GMT

Change the last part (from append onwards) to something like this

| append [| makeresults | eval SystemA_TranName="Percentage" | table SystemA_TranName] | filldown Success% Failed% | eval Success=if(SystemA_TranName="Percentage",Success%,Success) | eval Failed=if(SystemA_TranName="Percentage",Failed%,Failed) | table SystemA_TranName SystemA_TranStatus SystemB_TranName SystemB_TranStatus SystemC_TranName SystemC_TranStatus Total_Transaction_Counts Success Failed]

Re: Display stats percentage in row instead of columns

new2splunk1 — Sat, 05 Jun 2021 08:40:50 GMT

Thank you so much. This really helps. One last input needed. The percentage value is displayed only if I remove the blue text from the Success% and Failed% calculation.

| eval Success%=if(SystemA_TranName=="Total",round((Success/Total_Transaction_Counts)*100,2)."%","")
| eval Failed%=if(SystemA_TranName=="Total",round((Failed/Total_Transaction_Counts)*100,2)."%","")

I also tried adding ."%" to the below commands after the filldown command but it wouldn't work either.

| eval Success=if(SystemA_TranName=="Percentage",Success%."%",Success)
| eval Failed=if(SystemA_TranName=="Percentage",Failed%."%",Failed)

Is there anway to have the % symbol displayed after the percentage value?

Re: Display stats percentage in row instead of columns

ITWhisperer — Sat, 05 Jun 2021 09:06:47 GMT

You could try something like this

| eval Success%=if(SystemA_TranName=="Total",printf("%.2f%%",((Success/Total_Transaction_Counts)*100)),"") | eval Failed%=if(SystemA_TranName=="Total",printf("%.2f%%",((Failed/Total_Transaction_Counts)*100)),"")

Re: Display stats percentage in row instead of columns

new2splunk1 — Sat, 05 Jun 2021 10:48:24 GMT

Thanks so much. Works like a charm 🙂