How-To: Handling Empty or Null Values

Occasionally you may want to search for and display values, including those summarized with a function like groupBy() with a set of values that can include an empty (or null) value. This can cause problems, particularly if the query is being used with a visualization or graph.

This cannot be handled automatically. Instead, the easiest solution is to filter the missing values from the output. There is no concept of the empty or null value within LogScale. Instead of explicitly filtering the null or empty values out of the event results, you include a filter selection that explicitly looks for any value for a given field.

For example, when processing HTTP access logs with the query:

method=GET
| groupBy(field=[method, statuscode], function=count(as=method_total))
| sort([method, statuscode], order=asc)

It's possible that the statuscode field has no value, but this is not automatically excluded and so results in the empty value being included in the output:

method  statuscode      method_total
GET     <no value>      10
GET     200             32492
GET     301             1
GET     304             113
GET     403             9
GET     404             132

In a table this is OK, but when translated to a Bar Chart it results in a poorly formatted graph.

To resolve this, rather than filtering out the empty string, include a positive filter that displays only entries with a matching value for the corresponding field by performing a wildcard search for that field:

statuscode=*

This will only include events that have a value for the corresponding field, filtering out any empty results. Including this into the original query:

method=GET
| statuscode=*
| groupBy(field=[method, statuscode], function=count(as=method_total))
| sort([method, statuscode], order=asc)

The results now show events that only include a non-empty value for statuscode

method  statuscode      method_total
GET     200             32492
GET     301             1
GET     304             113
GET     403             9
GET     404             132