Splunk Search
Highlighted

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

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
Highlighted

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

Super Champion

row of funds =5000 how you have get this?

0 Karma
Highlighted

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

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
Highlighted

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

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
Highlighted

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

Engager

thank you !

0 Karma
Highlighted

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

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
Highlighted

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

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
Highlighted

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

Engager

Thank you for help. I worked the solution

0 Karma