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

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...