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

[klchandrakanth]

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 ?

[somesoni2 · Accepted solution]

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]

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

[tiagofbmm]

Hi

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

It is difficult to understand what went wrong without it.

[klchandrakanth]

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.