topic How do you make a division with 2 numbers from the same field filtered by a condition from another field? in Splunk Search

How do you make a division with 2 numbers from the same field filtered by a condition from another field?

lucasfbeinjamin — Fri, 19 Oct 2018 17:18:07 GMT

Hi everyone,

I need to make a division with 2 numbers from the same field, but they are filtered from another field.
Example:

field1  i have :  "Accepted", "Not Accepted", "TOTAL"
field2 i have in the same order: 100, 200, 500

The sum (Accepted + Not Accepted) is not necessarily the TOTAL. There may be reprocessed records that enter the total.

The records/events are so confused because they are coming from a database query.

I need to get the percentage of the Number of "Not Accepted" over the grand total(TOTAL), and the same to "Accepted".

Anyone have a good idea?

Ty.

Re: How do you make a division with 2 numbers from the same field filtered by a condition from another field?

Vijeta — Fri, 19 Oct 2018 17:33:15 GMT

If the order in your field2 is always going to be as Accepted, Not accepted, Total then you can extract the numbers and do calculation
as below

index=<yourindex>| | rex field=field2 "(?<Accepted_count>\d+),\d+,\d+"|rex field=field2 "\d+,(?<UnAccepted_count>\d+),\d+"|rex field=field2 "\d+,\d+,(?<Total_count>\d+)"| eval Accept_per=Accepted_count/Total_count * 100

Re: How do you make a division with 2 numbers from the same field filtered by a condition from another field?

lucasfbeinjamin — Fri, 19 Oct 2018 18:23:59 GMT

Hi @Vijeta

I think you misunderstood how the events listed, the fields are already regex of an event, I put them that way, but actually they are in columns in my Statistics tab, you see?

Re: How do you make a division with 2 numbers from the same field filtered by a condition from another field?

Vijeta — Fri, 19 Oct 2018 18:35:35 GMT

My bad...you mean it is like this?

field1 field2

Accepted 100
Not Accepted 200
Total 500

In that case you can use below logic and calculate percentage

index=<>| eval A=if(field1="Accepted",field2)| eval NA=if(field1="Not Accepted",field2)| eval Tot=if(field1="Total",field2)| stats sum(A) as A, sum(NA) as NA, sum(Tot) as Tot

Re: How do you make a division with 2 numbers from the same field filtered by a condition from another field?

493669 — Fri, 19 Oct 2018 18:36:32 GMT

Does this makes sense-

|eventstats max(field2) as total|eval percentage=((field2/total)*100)."%"

below is sample run anywhere search-

| makeresults |eval field1="Accepted", field2=100| append [| makeresults |eval field1="Not Accepted", field2=200]| append [| makeresults |eval field1="TOTAL", field2=500]|eventstats max(field2) as total|eval percentage=((field2/total)*100)."%"

Re: How do you make a division with 2 numbers from the same field filtered by a condition from another field?

lucasfbeinjamin — Fri, 19 Oct 2018 19:30:44 GMT

Thankyou, you helped me a lot!!!!

GoSplunk

Re: How do you make a division with 2 numbers from the same field filtered by a condition from another field?

lucasfbeinjamin — Fri, 19 Oct 2018 19:31:52 GMT

Thank you for wasting your time trying to help me! I'm very grateful for that!