Installation

if field A is null, count field B instead

mmdacutanan
Explorer

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!

Tags (1)
0 Karma
1 Solution

493669
Super Champion

Hi @mmdacutanan,
Try this:

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

View solution in original post

0 Karma

493669
Super Champion

Hi @mmdacutanan,
Try this:

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

mmdacutanan
Explorer

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!

0 Karma

493669
Super Champion

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
0 Karma

mmdacutanan
Explorer

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

0 Karma

mmdacutanan
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Exporting Splunk Apps

Join us on Monday, October 21 at 11 am PT | 2 pm ET!With the app export functionality, app developers and ...

Cisco Use Cases, ITSI Best Practices, and More New Articles from Splunk Lantern

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Build Your First SPL2 App!

Watch the recording now!.Do you want to SPL™, too? SPL2, Splunk's next-generation data search and preparation ...