Splunk Search

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

lucasfbeinjamin
Path Finder

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.

0 Karma
1 Solution

Vijeta
Influencer

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

View solution in original post

493669
Super Champion

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)."%"

lucasfbeinjamin
Path Finder

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

0 Karma

Vijeta
Influencer

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

View solution in original post

lucasfbeinjamin
Path Finder

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?

0 Karma

Vijeta
Influencer

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
0 Karma

lucasfbeinjamin
Path Finder

Thankyou, you helped me a lot!!!!

GoSplunk

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!