timeseries result by tag

[Stephen1_Lee]

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

[dannemca]

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

[Stephen1_Lee]

Hi

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
}
}

[stefan_eggersto · Accepted solution]

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!

[Stephen1_Lee]

Hi

Yes this worked.  Thank you very much.

[dannemca]

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