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

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
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...