topic Re: How to remove null field after using "where isnotnull" command? in Splunk Search

How to remove null field after using "where isnotnull" command?

ECovell — Tue, 27 Dec 2016 18:49:44 GMT

I am getting a little frustrated with this search... I have a field that just does not want to release the NULL value.

| eval src_ip=if(isnull(src_ip),"No IP",src_ip) 
| search Username="*-a" 
| convert ctime(_time) as datetime 
| replace "-" WITH "" IN Username
| where isnotnull (Username) 
| stats values(datetime) by src_ip, Username, ComputerName 
| rename src_ip as "Client Address" Username as User_ID ComputerName as "Reporting Server" count as "Number of Successful Login Attempts" percent as "Percent"


Client Address  User_ID                   Reporting Server            values(datetime)
xx.x.xxx.x                                          xxx-xxx.ctg.com            12/27/2016 09:10:00
xx.x.xxx.x       xxxxxx-a                   xxx-xxx.ctg.com            12/27/2016 09:10:00

I have tried multiple variations to get rid of the null value such as the where isnotnull, search Username!=,.. and others.
Does anyone else have a suggestion for me to try?

Thanks,
Ernie

Re: How to remove null field after using "where isnotnull" command?

somesoni2 — Tue, 27 Dec 2016 21:57:26 GMT

Try this (just replace your where command with this, rest all same)

| where isnotnull(Username) AND trim(Username)!=""

Re: How to remove null field after using "where isnotnull" command?

ECovell — Wed, 28 Dec 2016 12:51:00 GMT

No luck, I get zero results found by adding trim.

Re: How to remove null field after using "where isnotnull" command?

gordo32 — Tue, 01 May 2018 16:58:26 GMT

I ran into the same problem.

You can't use trim without use eval (e.g. | eval Username=trim(Username))
I found this worked for me without needing to trim: | where isnotnull(Username) AND Username!=""