Splunk Search

How can I create a query where I can sum the total and then take the percentage and add them in a column?

Carolina
Engager

Hello,

I need your help for the following:
I need to add the Total row and then divide it by the column of funds.
Example total 559892+32398=592190/funds consecutive
and add the percentage in another column that is called% of total

My Query is the following

search........
| rex field=Cuenta (?<BIN6>\d{6}) 
| lookup  Bines_Lookup BIN as BIN6 OUTPUT DES as Descripcion_BIN TTARGETA as TTARGETA
| search Descripcion_BIN!="N/A" 
| bucket _time span=2h 
| chart limit=0 count by  BIN6 Respuesta
| addtotals fieldname=Approved Approved* Honor* Partially*
| addtotals fieldname=Rejected Refer* Allowable* Do* Error* Collect* No* Customer* Retry* Transaction* Bank* Partially* Expired* Suspicion* Call* Restricted* all*  Lost* Stolen* Not* Incorrect* Exceeds* Hard* Response* Issuer* Financial* Duplicate* PTLF* Bad* Pick* Invalid* Requirement* Suspicious* File* Format* It* Reserved*
| table BIN6 Approved Rejected Others "Not sufficient funds"
| eval "% Aceptation" = round(Approved/(Approved+Rejected)*100, 2)
| rename "Not sufficient funds" as fondos
| table BIN6 Approved Rejected "% Aceptation"  fondos
| addcoltotals label=Total Approved Rejected labelfield=BIN6
| addtotals  fieldname=total1 Total*
|  eval "% of total"=(Total/fondos)*10
| table BIN6 Approved Rejected "% Aceptation"  fondos "% of total"
0 Karma
1 Solution

KailA
Contributor

Hi,

Start by removing this :

 | addcoltotals label=Total Approved Rejected labelfield=BIN6
 | addtotals  fieldname=total1 Total*
 |  eval "% of total"=(Total/fondos)*10
 | table BIN6 Approved Rejected "% Aceptation"  fondos "% of total"

And replace it by this :

| eventstats sum(Approved) as tot_aproved sum(Rejected) as tot_rejected
| eval Total = tot_approved + tot_rejected
| eval "% of total" = (Total / fondos) * 10
| table ...

This sould work well !

View solution in original post

0 Karma

KailA
Contributor

Hi,

Start by removing this :

 | addcoltotals label=Total Approved Rejected labelfield=BIN6
 | addtotals  fieldname=total1 Total*
 |  eval "% of total"=(Total/fondos)*10
 | table BIN6 Approved Rejected "% Aceptation"  fondos "% of total"

And replace it by this :

| eventstats sum(Approved) as tot_aproved sum(Rejected) as tot_rejected
| eval Total = tot_approved + tot_rejected
| eval "% of total" = (Total / fondos) * 10
| table ...

This sould work well !

0 Karma

Carolina
Engager

Thank you for help. I worked the solution

0 Karma

493669
Super Champion

Try this:

...|appendpipe[|search BIN6=total|eval Sum=Approved + Rejected ]|eventstats sum(Sum) as summation|eval "% of total" =summation*100/test."%"

let me know if it helps!

0 Karma

p_gurav
Champion

Hi,

Are u talking about something similar to this solution provided in this question?

https://answers.splunk.com/answers/488926/how-to-get-a-total-count-and-count-by-specific-fie-1.html

0 Karma

Carolina
Engager

thank you !

0 Karma

493669
Super Champion

row of funds =5000 how you have get this?

0 Karma

Carolina
Engager

excuse me wrong, I need the total sum first and then divide it for each row of funds. Example
592190/2, 592190/47
to have a new column

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...