topic Re: Using if condition in summarize in DQL

Using if condition in summarize

shaima — Mon, 26 Jan 2026 13:10:15 GMT

Hello,

I’ve been asked to aggregate metric dimensions for a dashboard.

The metric has over 100 dimensions. Each dimension represents a numeric value ranging from 0 to 150. The customer provided predefined groups, and I need to aggregate the dimensions according to those ranges, for example:

1–3

4–18

18–32

…

100+

I have a few questions regarding the best way to approach this.

1. Complexity of my current solution

I managed to create a working solution, but it's overly complex. I’m wondering if there’s a simpler or more elegant way to achieve this.

Solution 1:

timeseries { value.A = sum(metric.count.numbers, scalar: true) }, by: { Dimension }

| summarize sum = sum(value.A), by: {

Dimension == "0",

(Dimension == "1" OR Dimension == "2" OR Dimension == "3")

// etc ..

}

Output:

2. Issue with an alternative approach using asLong()

I also tried a second approach, which feels more logical to me, but it doesn’t work as expected. Specifically, using the asLong() function returns null.

Why does asLong(Dimension) return null in this case? Is there a different or recommended way to implement this kind of numeric range grouping?

Solution 2:

timeseries { value.A = sum(metric.count.numbers, scalar: true) }, by: { Dimension }

| summarize sum = sum(value.A), by: {

asLong(Dimension) == 0,

(asLong(Dimension) >= 1 AND asLong(Dimension) <= 3),

(asLong(Dimension) >= 4)

// etc..

}

Output:

Thanks in advance for any guidance.

Re: Using if condition in summarize

t_pawlak — Tue, 27 Jan 2026 12:27:59 GMT

Hi,

You’re right — Solution 1 works, but it becomes hard to maintain because you’re grouping by many boolean expressions (true/false), which doesn’t scale when you have 100+ dimension values.
You can try this:

convert the dimension to a number,
build a single bucket label field,
summarize by that bucket.

I couldn’t properly validate this against your specific metric (metric.count.numbers) in your environment, so I used a built-in CPU metric as an example.

timeseries { v = avg(dt.host.cpu.usage, scalar: true) }, by: { Dimension } | fieldsAdd d = toLong(Dimension) | fieldsAdd bucket = if(isNull(d), "NON_NUMERIC", else: if(d == 0, "0", else: if(d >= 1 and d <= 3, "1–3", else: if(d >= 4 and d <= 18, "4–18", else: if(d > 18 and d <= 32, "19–32", else: if(d >= 100, "100+", else: "33–99" ) ) ) ) ) ) | summarize sum = sum(v), by: { bucket } | sort bucket asc

For real usage, replace the metric and Dimension with the actual metric key and dimension name from your dataset.

And your second question, why does asLong(Dimension) return null.
Because asLong() typically does not perform a string-to-long conversion. It behaves more like a cast/interpretation and returns a value only if the input is already compatible with a numeric type.
Since your dimension values are strings ("0", "1", …), asLong(Dimension) can evaluate to null.
Use toLong(Dimension) instead — it performs the actual conversion and allows numeric range comparisons (>=, <=) for bucketing.

Re: Using if condition in summarize

krzysztof_hoja — Tue, 27 Jan 2026 16:09:58 GMT

I usually use coalesce() funtion to construct these (switch/case)/(case/when) statements. Less brackets to type, no need for else: and easier to maintain:

| fieldsAdd bucket = coalesce( if(d == 0, "0"), if(d >= 1 and d <= 3, "1–3"), if(d >= 4 and d <= 18, "4–18"), if(d > 18 and d <= 32, "19–32"), if(d > 33 and d <= 99, "33–99"), if(d >= 100, "100+"), "NONUMERIC" )

In case set of possible vales are not numbers which can be organized in ranges, instead of OR operator in() function will provide much shorter syntax:

if(in(Dimension, {"dog","cat","frog"}), "pets"),

Re: Using if condition in summarize

shaima — Thu, 05 Mar 2026 16:08:14 GMT

I believe the main issue was the use of asLong() instead of toLong(), and ended up implementing the second solution thanks to your clarification.

Thank you 🙂