How to filter row if some fields are empty?

[LearningGuy]

Hello,
How to filter all row if some fields are empty, but do not filter if one of the field has value?   
I appreciate your help. Thank you

I want to filter out row, if vuln, score and company fields are empty/NULL   
(All 3 fields are empty: Row 2 and 6 in the table below)

If vuln OR company fields have values(NOT EMPTY), do not filter 
Row 4: vuln=Empty                            company=company D(NOT empty)
Row 9: vuln=vuln9(NOT empty)    company=empty

If I use the search below, it will filter out row with vuln OR company that are empty (Row 4 and Row 9)
index=testindex  vuln=* AND score=* AND company=*

Current data

no	ip	vuln	score	company
1	1.1.1.1	vuln1	9	company A
2	1.1.1.2
3	1.1.1.3	vuln3	9	company C
4	1.1.1.4			company D
5	1.1.1.5	vuln5	7	company E
6	1.1.1.6
7	1.1.1.7	vuln7	5	company G
8	1.1.1.8	vuln8	5	company H
9	1.1.1.9	vuln9
10	1.1.1.10	vuln10	4	company J

Expected Result: ***NEED CORRECTION***

no	ip	vuln	score	company
1	1.1.1.1	vuln1	9	company A
2	FILTERED	FILTERED	FILTERED	FILTERED
3	1.1.1.3	vuln3	9	company C
4	1.1.1.4			company D
5	1.1.1.5	vuln5	7	company E
6	FILTERED	FILTERED	FILTERED	FILTERED
7	1.1.1.7	vuln7	5	company G
8	1.1.1.8	vuln8	5	company H
9	1.1.1.9	vuln9
10	1.1.1.10	vuln10	4	company J

Sorry, This is what I mean by FILTERED

no	ip	vuln	score	company
1	1.1.1.1	vuln1	9	company A
3	1.1.1.3	vuln3	9	company C
4	1.1.1.4			company D
5	1.1.1.5	vuln5	7	company E
7	1.1.1.7	vuln7	5	company G
8	1.1.1.8	vuln8	5	company H
9	1.1.1.9	vuln9

[gcusello]

Hi @LearningGuy ,

the solution to your "Expected Result" is the one hinted by @ITWhisperer .

Instead you can have to the last table simply adding 

(vuln=* OR company=*)

to you main search.

Ciao.

Giuseppe

[LearningGuy]

I found solution for this:

index=testindex  (vuln=* AND score=* AND company=*) OR (vuln=*) OR NOT (company="")

(vuln=* AND score=* AND company=*)   ==>   condition for vuln, score, company exists
(vuln=*)  ==> condition for only vuln exists
NOT (company="") ==> condition for only company exists         

company=*   "is equivalent with"  NOT (company="")     "is equivalent with"    isnull(company)

any idea why company=*  or isnull(company) does not work?
Thank you

[bowesmana]

Empty and null are different things. If the field is "" then it is not null, so I use

| where len(company)>0

[LearningGuy]

Hello,
Thank you for your help.

When I use one condition, it worked

| where len(company)>0

1) but when I combined "len", it didn't work - "The search job has failed due to an error. "

| where isnotnull(vuln) AND isnotnull(score) AND len(company>0) 

2) Why can't I use  len function without "where"?     
3) Can I use company=* to include "exist/non empty"?          It looks like * also didn't work

Please suggest. Thanks

[ITWhisperer]

You have a typo

| where isnotnull(vuln) AND isnotnull(score) AND len(company) > 0

[LearningGuy]

Hello,
Thanks for your correction and your help.
So this is what I am looking for:   

| where (isnotnull(vuln) AND isnotnull(score) AND len(company)>0)) OR (isnotnull(vuln)) OR len(company>0)

 Any idea why * didn't work?

It seems like "where" is faster than "search" 
Thank you

[ITWhisperer]

* doesn't work (as a wildcard) for where only search

[LearningGuy]

Hello,
Thank you for your help.
Your answer is correct, the output literally put "FILTERED".    
Sorry if my original post is not clear. I corrected my post.

What I meant by "filtered" , completely removed like shown below:

no	ip	vuln	score	company
1	1.1.1.1	vuln1	9	company A
3	1.1.1.3	vuln3	9	company C
4	1.1.1.4			company D
5	1.1.1.5	vuln5	7	company E
7	1.1.1.7	vuln7	5	company G
8	1.1.1.8	vuln8	5	company H
9	1.1.1.9	vuln9

I think I figured it out
index=testindex  (vuln=* AND score=* AND company=*) OR (vuln=*) OR NOT (company="")
It's just weird that company=* does not work and I had to use NOT (company="") to filter out empty 
NOT isnull(company) also doesn't work

Please suggest.
Thanks

[ITWhisperer]

| where isnotnull(vuln) OR isnotnull(score) OR isnotnull(company)

[ITWhisperer]

| eval ip=if(isnull(vuln) AND isnull(score) AND isnull(company),"FILTERED",ip)
| eval vuln=if(ip="FILTERED",ip,vuln)
| eval score=if(ip="FILTERED",ip,score)
| eval company=if(ip="FILTERED",ip,company)