Transform "There are no records" into 0 with DQL

elenaperez — Wed, 22 Jan 2025 09:01:24 GMT

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!

Re: Transform "There are no records" into 0 with DQL

krzysztof_hoja — Thu, 23 Jan 2025 11:11:59 GMT

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

Re: Transform "There are no records" into 0 with DQL

RPbiaggio — Thu, 20 Mar 2025 17:01:32 GMT

@krzysztof_hoja, is everything ok? I'm using a fetch events. It's not possible to use "nonempty:true", right?

topic Re: Transform "There are no records" into 0 with DQL in DQL

Transform "There are no records" into 0 with DQL

Re: Transform "There are no records" into 0 with DQL

Re: Transform "There are no records" into 0 with DQL