if field A is null, count field B instead

[mmdacutanan]

How do I go about the counting the following scenario:
1) CALLVARIABLE3 and CALLVARIABLE6 both empty, do not count
2) CALLVARIABLE3 is empty and CALLVARIABLE6 has value, count CALLVARIABLE6
3) CALLVARIABLE3 is not empty and CALLVARIABLE6 is empty, count CALLVARIABLE3
4) CALLVARIABLE3 and CALLVARIABLE6 both not empty and has the same value, count CALLVARIABLE6
5) CALLVARIABLE3 and CALLVARIABLE6 both not empty but does not have same value, count CALLVARIABLE6

Sample raw data below:

XXXXXXXYYYYYYYZZZZZZ|RemoteApplicationData|("CALLVARIABLE1"="","CALLVARIABLE2"="152574786091","CALLVARIABLE3"="2021212324,N,0000000,000,0","CALLVARIABLE4"="000,000,0,0000000,S,4,1,071011,N,0","CALLVARIABLE5"="Z1525740000786091","CALLVARIABLE6"="2021212324,8889991234,8889991234","CALLVARIABLE7"="L_SPEAK_FREELY","CALLVARIABLE8"="A,D,01,C,0,0,0,0,1,0,00,002,0,G,0,1,0,0","CALLVARIABLE9"="","CALLVARIABLE10"="N,,","APPLICATIONDATA"="10user.XfrReasonR10user.EndPtCodeX10user.LstPrmptPS139410user.ANIMatch`T")|2018-09-26 19:03:11.808

Thank you in advance!

[493669 · Accepted solution]

Hi @mmdacutanan,
Try this:

|eval CALLVARIABLE6 =if(CALLVARIABLE6 ="",null,CALLVARIABLE6 ), CALLVARIABLE3 =if(CALLVARIABLE3 ="",null,CALLVARIABLE3 )|eval testCount=coalesce(CALLVARIABLE6 ,CALLVARIABLE3 )

[mmdacutanan]

Thank you very much! This get me 90% of desired outcome. What I am missing is if CALLVARIABLE3 and CALLVARIABLE6 are both NOT empty AND does not contain the same value, count CALLVARIABLE6. In the query below, if CALLVARIABLE3 is not the same as CALLVARIABLE6, CALLVARIABLE3 is assigned to field Final. I tried doing "CALLVARIABLE3 != CALLVARIABLE6" but that doesn't seem to work. 😞

index=abc sourcetype=MainReportLog "|RemoteApplicationData|"
| eval CALLVARIABLE6=if(CALLVARIABLE6="",null, CALLVARIABLE6), CALLVARIABLE3=if(CALLVARIABLE3="",null, CALLVARIABLE3)
| eval Final=coalesce(CALLVARIABLE3, CALLVARIABLE6)| table CALLVARIABLE3 CALLVARIABLE6 Final

Thanks in advance!

[493669]

the query is working as expected for that scenario as well.
for ex. if CALLVARIABLE6=6 and CALLVARIABLE3=3 so these are not same and not empty .so you are expecting final result as 6 in this case and which is provided by this query.
Try this run anywhere search-

|makeresults |eval CALLVARIABLE6=6, CALLVARIABLE3=3
|eval CALLVARIABLE6 =if(CALLVARIABLE6 ="",null,CALLVARIABLE6 ), CALLVARIABLE3 =if(CALLVARIABLE3 ="",null,CALLVARIABLE3 )|eval Final=coalesce(CALLVARIABLE6 ,CALLVARIABLE3 )|table CALLVARIABLE6, CALLVARIABLE3, Final

[mmdacutanan]

Ahh, it was just the order of the fields within the eval. Thank you very much!=) Working now.

[mmdacutanan]

Thank you very much! This gets me 90% of the desired outcome. 😃 I am missing scenario when CALLVARIABLE3 and CALLVARIABLE6 is not null AND NOT EQUAL to each other, count CALLVARIABLE6 instead. Doesn't look like I can do straight CALLVARIABLE3 != CALLVARIABLE6. What would be the right syntax?

index=abc sourcetype=MainReportLog "|RemoteApplicationData|"
| eval CALLVARIABLE6=if(CALLVARIABLE6="",null, CALLVARIABLE6), CALLVARIABLE3=if(CALLVARIABLE3="",null, CALLVARIABLE3)
| eval Final=coalesce(CALLVARIABLE3, CALLVARIABLE6)| table CALLVARIABLE3 CALLVARIABLE6 Final

Thanks in advance!!