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

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

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

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

View solution in original post

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

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
0 Karma

javiergn
SplunkTrust
SplunkTrust
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!