topic Re: timeseries result by tag in DQL

timeseries result by tag

Stephen1_Lee — Wed, 17 May 2023 06:57:21 GMT

Hi.

Using DQL how to retrieve timeseries cpu for hosts by tag and then get the max of those hosts.

My syntax isn't correct.

timeseries memory=avg(dt.host.cpu.usage),by:{dt.entity.host}, interval:1h
| lookup [fetch dt.entity.host fieldsAdd tags,
],sourceField:dt.entity.host, lookupField:id
| filter matchesValue(tags,"TG.ProcessGroup.DetectedName:Oracle Listeners")
| summarize MaxMem = avg(arrayMax(memory))

I'd like to use the final result as an objective in the site reliability guardian.

Thanks

Re: timeseries result by tag

dannemca — Wed, 17 May 2023 06:57:48 GMT

Try this:

timeseries memory=avg(dt.host.cpu.usage),by:{dt.entity.host}, interval:1h
| lookup [fetch dt.entity.host
| fieldsAdd tags],lookupField:id , sourceField:dt.entity.host
| filter matchesValue(lookup.tags,"TG.ProcessGroup.DetectedName:Oracle Listeners")
| summarize MaxMem = avg(arrayMax(memory))

and let us know

Re: timeseries result by tag

Stephen1_Lee — Wed, 17 May 2023 14:30:59 GMT

I got this error. Note: when I query the dt.entity.hosts alone with the tag value it gives me back 61 records so I assume I have 61 hosts with this tag value.

{
"error": {
"message": "LOOKUP_TABLE_SIZE",
"details": {
"exceptionType": "DQL-EXEC-EXECUTION",
"errorType": "LOOKUP_TABLE_SIZE",
"errorMessage": "The lookup command's subquery read too much data. Please continue to filter the lookup table or narrow the query time range.",
"arguments": [],
"queryString": "timeseries memory=avg(dt.host.cpu.usage),by:{dt.entity.host}, interval:1h\n| lookup [fetch dt.entity.host\n| fieldsAdd tags],lookupField:id , sourceField:dt.entity.host\n| filter matchesValue(lookup.tags,\"TG.App.DynamicAppName:app_pddf\")\n| summarize MaxMem = avg(arrayMax(memory))",
"errorMessageFormatSpecifierTypes": [
"GENERAL_MESSAGE"
],
"errorMessageFormat": "The lookup command's subquery read too much data. Please continue to filter the lookup table or narrow the query time range."
},
"code": 500
}
}

Re: timeseries result by tag

dannemca — Wed, 17 May 2023 16:43:18 GMT

Try to add another filter to the lookup and see if the data is present... if is there a real limit, let's wait for someone from Dynatrace to explain what it is, since I seen nothing in docs.

edit, only limit I am seeing is this:

the time interval would have exceeded the limit of elements per series (10,080).

Re: timeseries result by tag

stefan_eggersto — Mon, 22 May 2023 04:18:39 GMT

Hi @Stephen1_Lee,

to avoid The lookup command's subquery read too much data. Please continue to filter the lookup table or narrow the query time range, you can do following:

move the filter for tags into the lookup query and in the main query, filter for hosts matching the lookup query (filter isNotNull(lookup.id))
remove the fieldsAdd tags, since you dont need the tags as a result, only for filtering

timeseries memory = avg(dt.host.cpu.usage), by:{dt.entity.host}, interval:1h
| lookup [
  fetch dt.entity.host 
  | filter matchesValue(tags, "TG.ProcessGroup.DetectedName:Oracle Listeners")
], lookupField:id, sourceField:dt.entity.host
| filter isNotNull(lookup.id)
| summarize MaxMem = avg(arrayMax(memory))

Please give it a try and let me know if this query works!

Re: timeseries result by tag

Stephen1_Lee — Thu, 25 May 2023 00:39:47 GMT

Yes this worked. Thank you very much.

Re: timeseries result by tag

Manish_Kumar — Fri, 13 Jun 2025 11:27:50 GMT

This Condition works onthe dashboard, provided by extensions