topic How do I pull a stats table where there are blank fields in event data? in Splunk Search

How do I pull a stats table where there are blank fields in event data?

reneedeleon — Tue, 29 Sep 2020 21:23:18 GMT

This is the event data:
ls1=INFO ls1Label=Severity ls2=MS SQL SERVER ls2Label=ServerType ls3=Command List ls3Label= cat=Audit sproc=ubuntu user=billy uid=DOMAIN\billybob dest= lhost=abrokenserver ohost=serverconnectedto CMD=su apt install *

index=rootCMDs
| rex field=_raw "^[^ \n]* (?P[^ ]+)"
| rex field=_raw "^(?:[^|\n]|){5}(?P[^|]+)"
| rex field=_raw "ls3label=(?.)\scat="
| eval ls3label=case(isnull(ls3label),"NULL",1=1,dst)
| where isnotnull(ls3label)
| search dst=" "
| stats count by lhost, ls3label, sproc. user, uid
| sort 0 count desc

When I pull the stats count I get no data but the even data lists everything and has hundreds of events where *="no data". How do I specifically search for the blank data only? Or is my search improperly formatted?

Re: How do I pull a stats table where there are blank fields in event data?

kamlesh_vaghela — Wed, 26 Sep 2018 18:28:37 GMT

@reneedeleon

Have you tried `fillnull' command to assigned default value instead of keeping null value?

http://docs.splunk.com/Documentation/Splunk/7.1.3/SearchReference/Fillnull

| fillnull value="NA" lhost, ls3label, sproc. user, uid
| stats count by lhost, ls3label, sproc. user, uid

Re: How do I pull a stats table where there are blank fields in event data?

DalJeanis — Thu, 27 Sep 2018 04:39:51 GMT

Converted comment to answer because that's the answer.

Re: How do I pull a stats table where there are blank fields in event data?

reneedeleon — Thu, 27 Sep 2018 11:36:25 GMT

Thank you Dal,

 Let me ask another question to the answer. Is it plausible to search multiple fields where there is data and NULL values.

maybe:

| search *=NULL OR | where *=NULL