Installation

How to remove null values from search results?

Communicator

Hi,

My below given search 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.

Search:

index=internal source=*licenseusage.log type=usage | lookup indexname indexname AS idx OUTPUT baname | timechart span=1d sum(b) as totalusage 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

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

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. "

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

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 totalusage = round((totalusage/1024/1024/1024), 2) | sort -total_usage

Thanks

0 Karma

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

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 totalusage = round((totalusage/1024/1024/1024), 2) | sort -total_usage

0 Karma

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

SplunkTrust
SplunkTrust