Splunk Search

How to calculate percentage of data which has two different values between these two values

praddasg
Path Finder

Here I have 3 fields "Status", merchantID & count. I am trying to find out the percentage of "CONFIRMED" and "REJECTED (these are values of "Status" for each merchantID. I mean calculation would be ((REJECTED-CONFIRMED)/CONFIRMED)*100, but this should be at a merchantID level. I am kind of new in Splunk and stuck. I could only come up with the below

index=apps
sourcetype="pos-generic:prod" Received request to change status CONFIRMED OR REJECTED
partner_account_name="Level Up"
| stats count by status, merchantId
0 Karma
1 Solution

manjunathmeti
SplunkTrust
SplunkTrust

Please try this:

index=apps
sourcetype="pos-generic:prod" Received request to change status CONFIRMED OR REJECTED
partner_account_name="Level Up"
| stats count by status, merchantId 
| xyseries merchantId, status, count 
| fillnull value=0 
| eval result = ((REJECTED-CONFIRMED)/CONFIRMED)*100

View solution in original post

manjunathmeti
SplunkTrust
SplunkTrust

Please try this:

index=apps
sourcetype="pos-generic:prod" Received request to change status CONFIRMED OR REJECTED
partner_account_name="Level Up"
| stats count by status, merchantId 
| xyseries merchantId, status, count 
| fillnull value=0 
| eval result = ((REJECTED-CONFIRMED)/CONFIRMED)*100

praddasg
Path Finder

Hey thanks, this works, just a thing, I wanted the percentage to be positive if CONFIRMED status is more than REJECTED. So modified little bit

index=apps
sourcetype="pos-generic:prod" Received request to change status CONFIRMED OR REJECTED
partner_account_name="Level Up"
| stats count by status, merchantId
| xyseries merchantId, status, count
| fillnull value=0
| eval result = ((CONFIRMED-REJECTED)/CONFIRMED)*100

Few more things:
1. There are other status which are also coming up in the result, this is strange since I have scoped the search to only CONFIRMED OR REJECTED. Right now it is showing me only 1 extra but if I increase the timescope, it might give me few more
2. In cases where REJECT is all and nothing is in CONFIRMED, there is no value in result, understandable since this mathematical but was wondering if there is any way around (one idea is to use your calculation than mine but I wanted to have the correct depiction of the result)
3. This is probably an extension of the query, I wanted to have this result only shown up if the result value is more than certain value let say 30% and if CONFIRMED+REJECTED is more than 10

If the last bit (point 3) need to be asked separatelyalt text I can do that

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

hi @praddasg

  1. You can filter results based on status values. You need to modify the query.

    index=apps sourcetype="pos-generic:prod" status=CONFIRMED OR status = REJECTED partner_account_name="Level Up"

  2. If you are calculating % of CONFIRMED and REJECTED then you logic should be result1 = (CONFIRMED/(CONFIRMED+REJECTED))*100 and result2 = (REJECTED/(CONFIRMED+REJECTED))*100. I might be wrong here, still if you are getting blank fields in result you can use fillnull (ex: | fillnull value=0 result).

  3. Try below query:

    index=apps sourcetype="pos-generic:prod" status=CONFIRMED OR status = REJECTED partner_account_name="Level Up"
    | stats count by status, merchantId
    | xyseries merchantId, status, count
    | fillnull value = 0
    | eval count = CONFIRMED + REJECTED
    | where count > 10

praddasg
Path Finder

ok this is what I am using now and i guess this is working the way I want

index=apps
sourcetype="pos-generic:prod" Received request to change status=CONFIRMED OR status=REJECTED
partner_account_name="Level Up"
| stats count by status, merchantId
| xyseries merchantId, status, count
| eval result = ((CONFIRMED-REJECTED)/CONFIRMED)*100
| fillnull value=100 result
| eval count = CONFIRMED + REJECTED
| where count >= 10
| where result >= 50

Thanks a ton for your help. If I do face anything more, will let you know

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

That's great. Please accept and vote the answer and comment if you need anything 🙂

0 Karma

praddasg
Path Finder

🙂 done

I made some little bit more changes to show properly

index=apps
sourcetype="pos-generic:prod" Received request to change status=CONFIRMED OR status=REJECTED
partner_account_name="Level Up"
| stats count by status, merchantId
| xyseries merchantId, status, count
| eval result = (REJECTED)/((CONFIRMED+REJECTED))*100
| fillnull value=100 result
| eval count = CONFIRMED + REJECTED
| where count >= 10
| where result >= 20

I may not require the | fillnull value=100 result but still keeping it as of now

0 Karma

praddasg
Path Finder

Hi There,

Is there anyway possible to includealt text

Basically I am trying to have the error messages to also reflect against each merchantID along with the above. Right now I have to do separately by using this

REJECTED sourcetype="pos-generic:prod" partner_account_name="Level Up"
| table partner_account_name, merchantId, orderId, message
| stats count by partner_account_name, merchantId, message

0 Karma

praddasg
Path Finder
0 Karma

praddasg
Path Finder

Hey thanks a lot. This helped but few things. I modified that query a little bit to show the result in positive when Confirmed is more than reject

eval result = ((CONFIRMED-REJECTED)/CONFIRMED)*100 instead of eval result = ((REJECTED-CONFIRMED)/CONFIRMED)*100

few more things:
1. I scoped the search to show only 2 status (CONFIRMED & REJECTED) but another status is being reflected. This does not really impacts a lot but kind of confusing, is there any way that can be removed?
2. Cases where everything is REJECT and nothing is CONFIRMED, the result is blank (this is expected due to Mathematical reason) but was wondering if there is anything that can be done to avoid this?
3. This might a be overall new question but wanted to check, I wanted this query to be little bit more intuitive in terms of only showing when CONFIRMED+REJECTED total is more than equal to, lets say 15 and result is more than equal to 50alt text

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

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 ...