Splunk Search

Calculation of percentage based on the results from 3 different searches with a common value

klchandrakanth
Explorer

I have calculated % from 3 different searches and i am getting the result perfectly fine.

source="log-ura" "Flag Finalizacao" NOT Finalizacao=3 AND NOT  Finalizacao=4 AND NOT Finalizacao=6 AND NOT Finalizacao=7 AND NOT Finalizacao=11 AND NOT Finalizacao=13  | stats count(Finalizacao) as Ignored | 
           join date [search source="log-ura" "Flag Finalizacao" Finalizacao=*  | stats count(Finalizacao) as TotalFlagCount] | table Ignored TotalFlagCount |
           join date [search source="log-ura" "Flag Finalizacao" Finalizacao=3 OR Finalizacao=4  | stats count(Finalizacao) as Together]
           |  eval ATHlíquido=(Together/(TotalFlagCount-Ignored))*100 | table ATHlíquido

Now I need to calculate the % based on the telephone dialing codes. I am trying to extract dialing code(DDD) and Counts from 3 different searches and need to apply a math formula for each DDD code.

source="log-ura" "Flag Finalizacao" NOT Finalizacao=3 AND NOT  Finalizacao=4 AND NOT Finalizacao=6 AND NOT Finalizacao=7 AND NOT Finalizacao=11 AND NOT Finalizacao=13  | dedup _raw | eval Ignored.PhoneDDD = substr(PhNumber, 1, 2) | stats count(Finalizacao) as Ignored by Ignored.PhoneDDD | 
           join date [search source="log-ura" "Flag Finalizacao" Finalizacao=*  | dedup _raw | eval TotalFlagCount.PhoneDDD = substr(PhNumber, 1, 2)| stats count(Finalizacao) as TotalFlagCount by TotalFlagCount.PhoneDDD ] |
       join date [search source="log-ura" "Flag Finalizacao" Finalizacao=3 OR Finalizacao=4  | dedup _raw | eval Together.PhoneDDD = substr(PhNumber, 1, 2)| stats count(Finalizacao) as Together by Together.PhoneDDD ] | table Ignored.PhoneDDD Ignored TotalFlagCount.PhoneDDD TotalFlagCount Together.PhoneDDD Together

However its not working.

in other way,
search 1 --> DDD, countA
search 2 --> DDD, countB
search 3 --> DDD, countC

I need to do a formula = (countA-CountB)/CountC for each DDD. Can someone help me where i am doing wrong ?

0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try (with little optimization)

source="log-ura" "Flag Finalizacao" Finalizacao=* 
| eval together=if(Finalizacao=3 OR Finalizacao=4,1,0)
| eval ignored=if(NOT (Finalizacao=3 OR Finalizacao=4 OR Finalizacao=6 OR Finalizacao=7 OR Finalizacao=11 OR Finalizacao=13),1,0)
| eval PhoneDDD = substr(PhNumber, 1, 2)
| stats count as TotalFlagCount sum(ignored) as Ignored sum(together) as Together by PhoneDDD

This will give a Total, Ignored and Together value for each PhoneDDD value. You can then apply your formula with available fields.

View solution in original post

somesoni2
Revered Legend

Give this a try (with little optimization)

source="log-ura" "Flag Finalizacao" Finalizacao=* 
| eval together=if(Finalizacao=3 OR Finalizacao=4,1,0)
| eval ignored=if(NOT (Finalizacao=3 OR Finalizacao=4 OR Finalizacao=6 OR Finalizacao=7 OR Finalizacao=11 OR Finalizacao=13),1,0)
| eval PhoneDDD = substr(PhNumber, 1, 2)
| stats count as TotalFlagCount sum(ignored) as Ignored sum(together) as Together by PhoneDDD

This will give a Total, Ignored and Together value for each PhoneDDD value. You can then apply your formula with available fields.

klchandrakanth
Explorer

Thank you for the quick response. I have posted on Friday that it was working fine. I dint notice that my internet was disconnected.

0 Karma

tiagofbmm
Influencer

Hi

Could you please provide a sample of your data (even masked)?

It is difficult to understand what went wrong without it.

0 Karma

klchandrakanth
Explorer

Raw data is below.

3/5/18
8:59:59.424 AM  
05/03/2018 08:59:59.424||APL|STOP.jsp|152369|151674|93981111213|93981111213||MSG-AP-STATUS-FINANCEIRO|Flag Finalizacao = 2|
3/5/18
8:59:59.420 AM  
05/03/2018 08:59:59.420||APL|STOP.jsp|152369|151674|93981111213|93981111213||MSG-AP-STATUS-FINANCEIRO|Flag Finalizacao = 2|
3/5/18
8:59:58.583 AM  
05/03/2018 08:59:58.583||APL|STOP.jsp|152369|151110|31991374053|31991374053||MSG-A-RECONTRATACAO-UPSELL|Flag Finalizacao = 1|
3/5/18
8:59:57.698 AM  
05/03/2018 08:59:57.698||APL|STOP.jsp|152369|152720|2131012407|2131012407||SAUDACAO_10341|Flag Finalizacao = 2|
3/5/18
8:59:57.694 AM  
05/03/2018 08:59:57.694||APL|STOP.jsp|152369|152720|2131012407|2131012407||SAUDACAO_10341|Flag Finalizacao = 2|

Simple query to extract Flag count and dialing code(DDD) is below.
source="log-ura" "Flag Finalizacao" NOT Finalizacao=3 AND NOT Finalizacao=4 AND NOT Finalizacao=6 AND NOT Finalizacao=7 AND NOT Finalizacao=11 AND NOT Finalizacao=13 | dedup _raw | eval aPhoneDDD = substr(PhNumber, 1, 2) | stats count(Finalizacao) as Ignored by aPhoneDDD

Sample result is below.
aPhoneDDD Ignored
11 20
15 2
19 1
21 309
Let me know if you need any other information.

0 Karma
Get Updates on the Splunk Community!

Sending Metrics to Splunk Enterprise With the OpenTelemetry Collector

This blog post is part of an ongoing series on OpenTelemetry. The OpenTelemetry project is the second largest ...

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...

Want a chance to win $500 to the Splunk shop? Take our IT Incident Management Survey!

  Top Trends & Best Practices in Incident ManagementSplunk is partnering up with Constellation Research to ...