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

[praddasg]

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

[manjunathmeti · Accepted solution]

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]

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 separately I can do that

[manjunathmeti]

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]

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

[manjunathmeti]

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

[praddasg]

🙂 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

[praddasg]

Hi There,

Is there anyway possible to include

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

[praddasg]

https://share.getcloudapp.com/E0uqGmpx

[praddasg]

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 50