Splunk Search

Display stats percentage in row instead of columns

new2splunk1
Engager

Hi Splunk experts, 

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

Table 1:

new2splunk1_3-1622866839549.png

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:

new2splunk1_2-1622866798787.png

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:

new2splunk1_4-1622867169782.png

 

 

 

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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]

View solution in original post

0 Karma

new2splunk1
Engager

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?

 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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)),"")

 

0 Karma

new2splunk1
Engager

Thanks so much. Works like a charm :slightly_smiling_face:

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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]
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...