Installation

How to remove null values from search results?

sunnyparmar
Communicator

Hi,

My below given query for License usage logs showing me data but there is "NULL" column is also coming in that with some data so how to get rid of this NULL column? When I am clicking on NULL column to see the events it contains nothing. Any suggestions would be appreciated.

Query -
index=_internal source=*license_usage.log type=usage | lookup index_name indexname AS idx OUTPUT baname | timechart span=1d sum(b) as total_usage by baname

I have used already below given parameters but still null values are showing-

| stats values() as * by Id
| streamstats count as Id | stats values(
) as * by Id

Thanks.

Labels (1)
1 Solution

renjith_nair
Legend

A NULL series is created for events that do not contain the split-by field. In your case, it might be some events where baname is not present.

You can remove NULL from timechart by adding the option usenull=f

index=_internal source=*license_usage.log type=usage | lookup index_name indexname AS idx OUTPUT baname  | timechart usenull=f span=1d sum(b) as total_usage by baname
---
What goes around comes around. If it helps, hit it with Karma 🙂

View solution in original post

ddrillic
Ultra Champion

From the official documentation at http://docs.splunk.com/Documentation/Splunk/6.1/SearchReference/chart
"the search uses the usenull=f argument to exclude fields that don't have a value. "

gyslainlatsa
Motivator

hi sunnyparmar,

try like this, use the command fields - NULL

index=_internal source=*license_usage.log type=usage | lookup index_name indexname AS idx OUTPUT baname | timechart span=1d sum(b) as total_usage by baname |fields - NULL

sunnyparmar
Communicator

Thanks for the help. it works but i have one more query related to the same. Currently it is showing me values in bytes for daily basis. If I have to convert it into GB's so how could I do that with the same query?

I am using parameter something like this but didn't give me the exact result-

| eval total_usage = round((total_usage/1024/1024/1024), 2) | sort -total_usage

Thanks

0 Karma

renjith_nair
Legend

A NULL series is created for events that do not contain the split-by field. In your case, it might be some events where baname is not present.

You can remove NULL from timechart by adding the option usenull=f

index=_internal source=*license_usage.log type=usage | lookup index_name indexname AS idx OUTPUT baname  | timechart usenull=f span=1d sum(b) as total_usage by baname
---
What goes around comes around. If it helps, hit it with Karma 🙂

sunnyparmar
Communicator

Thanks for the answer. it works but i have one more query related to the same. Currently it is showing me values in bytes for daily basis. If I have to convert it into GB's so how could I do that with the same query?

I am using parameter something like this but didn't give me the exact result-

| eval total_usage = round((total_usage/1024/1024/1024), 2) | sort -total_usage

0 Karma

renjith_nair
Legend

Try this

         index=_internal source=*license_usage.log type=usage |eval gb=round((b/1024/1024/1024), 2) | lookup index_name indexname AS idx OUTPUT baname  | timechart usenull=f span=1d sum(gb) as total_usage by baname

Above will give you the usage in GB spread across time window. If you still want this to be sorted on total_usage, then try below

             index=_internal source=*license_usage.log type=usage |eval gb=round((b/1024/1024/1024), 2) | lookup index_name indexname AS idx OUTPUT baname  | timechart usenull=f span=1d sum(gb) as total_usage by baname|untable _time baname total_usage|sort - total_usage
---
What goes around comes around. If it helps, hit it with Karma 🙂
0 Karma

javiergn
Super Champion
Get Updates on the Splunk Community!

Access Tokens Page - New & Improved

Splunk Observability Cloud recently launched an improved design for the access tokens page for better ...

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...