Splunk Search

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

ECovell
Path Finder

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

0 Karma

gordo32
Communicator

I ran into the same problem.

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

somesoni2
Revered Legend

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

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

ECovell
Path Finder

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

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!