topic Re: how to remove null values from result in Installation

How to remove null values from search results?

sunnyparmar — Tue, 29 Sep 2020 08:44:52 GMT

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.

Re: how to remove null values from result

javiergn — Sun, 14 Feb 2016 14:11:29 GMT

Use the fillnull command:

http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/fillnull

Re: how to remove null values from result

renjith_nair — Sun, 14 Feb 2016 14:16:37 GMT

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

Re: how to remove null values from result

gyslainlatsa — Sun, 14 Feb 2016 16:59:13 GMT

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

Re: how to remove null values from result

ddrillic — Sun, 14 Feb 2016 21:37:25 GMT

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

Re: how to remove null values from result

sunnyparmar — Tue, 29 Sep 2020 08:48:29 GMT

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

Re: how to remove null values from result

sunnyparmar — Tue, 29 Sep 2020 08:48:32 GMT

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

Re: how to remove null values from result

renjith_nair — Mon, 15 Feb 2016 03:42:51 GMT

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