topic Re: How to filter row if some fields are empty? in Splunk Search

How to filter row if some fields are empty?

LearningGuy — Sat, 11 Nov 2023 02:17:21 GMT

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

Re: How to filter row if some fields are empty?

ITWhisperer — Fri, 10 Nov 2023 23:10:55 GMT

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

Re: How to filter row if some fields are empty?

LearningGuy — Sat, 11 Nov 2023 02:25:37 GMT

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

Re: How to filter row if some fields are empty?

gcusello — Sat, 11 Nov 2023 06:56:54 GMT

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

Re: How to filter row if some fields are empty?

ITWhisperer — Sat, 11 Nov 2023 10:23:13 GMT

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

Re: How to filter row if some fields are empty?

LearningGuy — Mon, 13 Nov 2023 00:08:33 GMT

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

Re: How to filter row if some fields are empty?

bowesmana — Mon, 13 Nov 2023 01:30:05 GMT

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

| where len(company)>0

Re: How to filter row if some fields are empty?

LearningGuy — Mon, 13 Nov 2023 14:45:07 GMT

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

Re: How to filter row if some fields are empty?

ITWhisperer — Mon, 13 Nov 2023 14:47:22 GMT

You have a typo

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

Re: How to filter row if some fields are empty?

LearningGuy — Mon, 13 Nov 2023 15:28:03 GMT

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

Re: How to filter row if some fields are empty?

ITWhisperer — Mon, 13 Nov 2023 15:46:08 GMT

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