topic How to exclude NULL return fields from my search? in Splunk Search

How to exclude NULL return fields from my search?

rkaakaty — Wed, 31 May 2017 15:30:25 GMT

eventtype=qualys_vm_detection_event STATUS!="FIXED" 
| fillnull value=- PROTOCOL
| dedup 1 HOST_ID, QID, PROTOCOL, STATUS keepempty=true sortby -_time  
| stats list(HOST_ID) as HOST_ID, list(DNS) as Host_Name, list(OS), list(IP) as IP count(HOST_ID) by QID 
| rename count(HOST_ID) AS HOSTS
| lookup qualys_kb_lookup QID OUTPUT TITLE SEVERITY PATCHABLE  
| table TITLE, CATEGORY, PATCHABLE, QID, HOSTS
| sort - HOSTS
| head 10

Using TITLE=* or TITLE!="" is not returning any results at all...

Re: How to exclude NULL return fields from my search?

skoelpin — Wed, 31 May 2017 15:50:37 GMT

Use this to exclude null values on your stats command

usenull=f

Re: How to exclude NULL return fields from my search?

niketn — Wed, 31 May 2017 15:57:24 GMT

Since you are getting the TITLE field from lookup, you can add the following where clause after lookup:

   | lookup qualys_kb_lookup QID OUTPUT TITLE SEVERITY PATCHABLE 
   | where isnotnull(TITLE)

Re: How to exclude NULL return fields from my search?

woodcock — Wed, 31 May 2017 15:58:52 GMT

You should be able to use either of these:

| search TITLE="*"

Or:

| where isnotnull(TITLE)

Re: How to exclude NULL return fields from my search?

niketn — Thu, 01 Jun 2017 04:34:01 GMT

@rkaakaty Please accept the answer is it has resolved your issue.

Re: How to exclude NULL return fields from my search?

nit123 — Thu, 01 Jun 2017 05:57:55 GMT

Either try from the following

a. search | where isnull()

b. FieldName != ''

c. len(FieldName )> 0

Option (c) works pretty good.

if this solves your prolem, spare a moment to reward points.

Thanks.

Re: How to exclude NULL return fields from my search?

nit123 — Sat, 15 Jul 2017 13:43:04 GMT

Does my answer above solve your question ? If yes, spare a moment to accept the answer and vote for it. Thanks.