Data Explorer advance query mode

[Vijayt]

I want to extract the hostname that has exceeded 40% memory max in a 30-minute interval in the last 24 hours. How can we achieve this via a nested query, filter the series value to max (40)? 

[Emm4nuel]

To achieve this behavior in Data Explorer's advanced mode, you can use the metric selector syntax and transformations available in the Classic Metrics API. However, Data Explorer does not support all DQL features directly in advanced mode. Here's how you can approximate the behavior:

1. Metric Selector: Use the metric key builtin:host.memory.usage to query memory usage.
2. Split By: Use splitBy("dt.entity.host") to group by host.
3. Aggregation: Use max to calculate the maximum memory usage.
4. Filter: Unfortunately, advanced mode does not support filtering based on aggregated values like arrayMax. You would need to apply this filter manually after exporting the data or use DQL in Notebooks or Dashboards for this functionality.
5. Interval: Set the interval to 30 minutes in the visualization settings.
6. Timeframe: Set the timeframe to the last 24 hours in the Data Explorer UI.

Example Metric Selector:

builtin:host.memory.usage:splitBy("dt.entity.host"):max

This will give you the maximum memory usage per host. To filter and add the host name, you would need to use DQL in Notebooks or Dashboards.

So to accomplish this you would need to use DQL:

timeseries by:{dt.entity.host}, interval:30m, from:now() - 24h, maxMemory = max(dt.host.memory.usage)
| filter arrayMax(maxMemory) > 40
| fieldsAdd hostName = dt.entity.host

Execute this query on DQL and you will get what you are looking for, Does this satisfy your need? I will keep searching if we can accomplish this through Data Explorer.

[Vijayt]

unfortunately we have only access to dashboard classic. can it be achieved by nested query . i have seen already but forgot the syntax 

[Maheedhar_T]

Hi @Vijayt ,
You can control this using the min,max at the right side when you get that nested query in Data Explorer.
Query would still look like this only

builtin:host.mem.usage:splitBy("dt.entity.host"):sort(value(auto,descending))

Then from this we need to change the visualisation as highlighted in the below image.
Then you'll see only the hosts that are in that range.

Hope this helps.

Regards,
@Maheedhar_T 

[Julius_Loman · Accepted solution]

If I got your question right, with Data Explorer (no DQL), I'd suggest the following:

builtin:host.mem.usage
:max 
:partition("above40",value("yes",gt(40)),otherwise("no"))
:filter(eq("above40","yes"))
:splitBy("dt.entity.host")

First select the maximum in the bucket interval (:max), then add a dimension to compare if it's over 40% (:partition) and afterward filter only those series where it's over (:filter) and split by host.

If you need it in 30 minute buckets, you can select that option in Data Explorer.

This is link to the playground environment. 

A second option is filtering by series - in this case it returns hosts where maximum usage in the selected interval is over 40%:

builtin:host.mem.usage
:auto
:filter(series(max,gt(40)))
:splitBy("dt.entity.host")

[Vijayt · Accepted solution]

this works perfectly fine //builtin:host.cpu.user:max:partition("critical",value("1",gt(60)),otherwise("0"))
:filter(eq("above60","1")):splitBy("dt.entity.host"):count:sort(value(sum,descending))// 

This one still shows <40 data points not sure why //

builtin:host.mem.usage:auto:filter(series(max,gt(40))):splitBy("dt.entity.host")//