Transform "There are no records" into 0 with DQL

[elenaperez]

Hi,

I am trying to transform the message "There are no records" in my tile to a single 0 (or an array of zeros).

I have tried using union:true, nonempty:true, and the coalesce function but none of them seem to work. I assume this is because the query uses 2 timeseries joined.

Is there a way to achieve this?

Here is my DQL:

timeseries count = sum(dt.service.request.count, default:0), by: { dt.entity.service }, filter: { failed == true }
| join [ timeseries count = sum(dt.service.request.count, default:0), by: { dt.entity.service } ], on: { dt.entity.service }, fields: { operand = count }
| filter { ( in(dt.entity.service, classicEntitySelector("type(service),tag(\"Kubernetes_ClusterName:payments\")"))) }
| fieldsAdd expression = count[] / operand[]
| fieldsRemove operand, count
| summarize { expression = avg(expression[] ) }, by: { interval, timeframe }
| sort arrayAvg(expression) desc

Thanks in advance!

[krzysztof_hoja · Accepted solution]

"There are no records" message appears instead of chart in 2 cases:

• Where result set is empty / no records returned
• All timeseries to display contain only nulls / no values

To avoid this we need to make sure that there is always a result and it contains data. Null is valid value of timeseries which means no data, but it this it may be caused as a result of division of 0 by 0

Additionally there is another problem with join in this query: left query (timeseries with "failed == true" condition) will not return a timeseries for a specific service when there is no failed requests and this cause that a service will not be present in the final data at all. 

Final query can look like this:

timeseries total = sum(dt.service.request.count, default:0), nonempty:true,  by: { dt.entity.service }
, filter: { ( in(dt.entity.service, classicEntitySelector("type(service),tag(\"Kubernetes_ClusterName:payments\")")))  }
| fieldsAdd key=record(dt.entity.service)

| join [ 
  timeseries failed = sum(dt.service.request.count, default:0), nonempty:true, by: { dt.entity.service } 
  , filter: { failed == true and ( in(dt.entity.service, classicEntitySelector("type(service),tag(\"Kubernetes_ClusterName:payments\")"))) }
  | fieldsAdd key=record(dt.entity.service)

], on: { key }, fields: { failed }, kind:leftOuter

| fieldsAdd failed = coalesce(failed, icollectArray(total[]*0) )

| fieldsAdd expression = failed[] / total[]

| summarize { expression = avg(expression[] ) }, by: { interval, timeframe }
| fieldsAdd expression = coalesce(expression[],0)
| sort arrayAvg(expression) desc

What are the components of it:

• Use of nonempty:true parameter ensures that there is always something returned by timeseries command. In case of query with by: record with null value of dimension will be provided.
• I included entity filter in timeseries command. Having it after would eliminate special record produced by use of nonempty:true parameter
• kind:leftOuter was used to ensure when there are no "failed" requests, "total" timeseries is not eliminated 
• join command works this way, so record keyed by null will not be connected. To avoid this behavior we can wrap "dt.entity.service" in a record and use it in join condition

| fieldsAdd key=record(dt.entity.service)

• In such case value of "failed" timeseries is null, so 

| fieldsAdd failed = coalesce(failed, icollectArray(total[]*0) )

makes sure we have array of zeroes instead

• To avoid nulls being result of 0/0 division, they can be replaced with 0s:

| fieldsAdd expression = coalesce(expression[],0)