How to remove null values from search results?

[sunnyparmar]

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.

[ddrillic]

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]

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]

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

[renjith_nair · Accepted solution]

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

[sunnyparmar]

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

[renjith_nair]

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

[javiergn]

Use the fillnull command:

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