PRO TIP: Alert on OneAgent running outdated version

[mosharref_hossn]

EDIT: 

Davis Anomaly Detected rule ended up generating too many alerts since it checks often. 

We moved this query to Workflow and run it once a week to send a notification via Send Email action. 

*****

Our team needed a way to alert when the OneAgent fails to upgrade to the latest available version and running an outdated version.

To address this, we created the following DQL query to be used with Davis Anomaly Detection.

Notifications can be configured using a Management Zone in combination with an Alert Profile.

Alternatively, tags can also be used to help route alerts to the appropriate team.

We also needed to exclude hosts that could not be upgraded due to unsupported OS version (lookup sub-query). 

You can exclude the subquery if that is not required. 

// Fetch hosts with installed agent versions and sort asc by Installer Version
// Sort order will help filter out any hosts that are not running the latest version in next sub-query
fetch dt.entity.host
| filter isNotNull(installerVersion) 
| fields id, entity.name, installerVersion, lifetime
// Coverting lifetime[end] value to "timestamp" enables the user to create a timeseries metric at the end to run in Davis Anomaly Detection
| fieldsAdd timestamp=toTimestamp(lifetime[end])
| fieldsRemove lifetime
// NOTE: sorted in ascending order to get oldest agent versions
| sort installerVersion asc
| limit 30
| summarize {installerVersion=max(installerVersion)}, by:{entity.name, id, timestamp}
 
// Fetch hosts with installed agent version, sort desc by Installer Version and return matching host IDs
| join [
fetch dt.entity.host
| filter isNotNull(installerVersion)
| fields  installerVersion, id
// NOTE: sorted in descending order to get latest agent versions
| sort installerVersion desc
| limit 30
], on:{id}, kind: leftOuter, fields:{latestAgentVersion=installerVersion}
// Filter out hosts matching the latest Agent version
| filter isNull(latestAgentVersion)
| fieldsRemove latestAgentVersion
 
// Fetch logs & lookup hosts with failed installation due to unsupported platform (dynamically)
// And exclude matched hosts.
| lookup [
    fetch logs, from:now()-48h
    | filter matchesValue(log.source, "Windows Application Log") and loglevel == "ERROR"
    | filter contains(content, "unsupported platform") and contains(content, "Dynatrace OneAgent")
    | dedup dt.entity.host
    | sort timestamp desc
| fields dt.entity.host
], sourceField: id, lookupField: dt.entity.host
 
// Exclude agents running in unsupported OS version
| filter isNull(lookup.dt.entity.host)
 
// Remove unnecessary fields
| fieldsRemove lookup.dt.entity.host

// Create timeseries metric
| maketimeseries Count=count(default:0), by:{dt.entity.host=entity.name, installerVersion}, interval:1m

Hope this helps the community. 

[AntonPineiro]

Thanks! 

[joyce-regions]

Can you help me with the output of this query to send email? Can we have the output table in the body message when it send to email?

[mosharref_hossn · Accepted solution]

We ended up add these additional lines of query after the timeseries. This creates a link to the Host OneAgent settings page and add the process name via lookup. 

Use the Send Email action in Workflow and add the email body markdown table to format all fields into tables. 

Query update:

// Look up One Agent System monitoring process group instance which can be used as dt.source_entity, if needed for management zone. 
| lookup [
    fetch dt.entity.process_group_instance
    | fields id, entity.name, belongs_to
    | fieldsAdd process.belongs_to.dt.entity.host=belongs_to[dt.entity.host]
    | filter contains(entity.name, "OneAgent system monitoring")
], sourceField: dt.entity.host, lookupField: process.belongs_to.dt.entity.host, fields:{dt.entity.process_group_instance=id} 
| fieldsAdd process.name = entityName(dt.entity.process_group_instance)
// Create a link to host OneAgent settings
| fieldsAdd OneAgentSettings = concat("https://{environmentid}.apps.dynatrace.com/ui/apps/dynatrace.classic.hosts/ui/settings/", dt.entity.host, "/builtin:deployment.oneagent.updates?gtf=-2h&gf=all")
| fields host.name, installerVersion, process.name, OneAgentSettings

 Email body markdown table:

|      host.name       |      process.name                   |     installerVersion              |    OneAgentSettings    |
|----------------------------|----------------------------------|-----------------------------------|----------------------------------------|
{% for e in result("get_outdated_oneagent")["records"] %}
|  {{e["host.name"][:35]}}     | {{e["process.name"][:35]}}  | {{e["installerVersion"][:30]}}       | {{e["OneAgentSettings"][:160]}} |
{% endfor %}

[joyce-regions]

I am back with an issue with email body again. 

I was success to create the email with query above, but when I apply for another query - simpler, the email was failed with error as no find out 

like I create query 

fetch dt.system.events
| filter event.kind == "BILLING_USAGE_EVENT" and event.type == "Full-Stack Monitoring"
|fieldsAdd event.type, event.billing.category
| summarize billed_gibibyte = sum(billed_gibibyte_hours),

by:{event.type, dt.entity.host, dt.cost.product, dt.cost.costcenter}
//join two tables details with id as same conditon to look up
| lookup [fetch dt.entity.host
| fields hosts = entity.name, id, hostGroupName, dt.security_context], lookupField:id,sourceField:dt.entity.host

| fieldsAdd cost = (billed_gibibyte * 0.0057)
| summarize cost_weekly = sum(cost), by:{ dt.cost.product, dt.cost.costcenter}
| fields App = dt.cost.product, Costcenter = dt.cost.costcenter, cost_weekly

 Then apply with the email body

| dt.cost.product | dt.cost.costcenter | Cost Weekly |
|----------------------------|-----------------------------------|----------------------------------------|
{% for e in result("full_stack_cost")["records"] %}

| {{e["App"][:30]}} | {{e["Costcenter"][:35]}} | {{e["cost_weekly"][:30]}} |
{% endfor %}

The error comes as We couldn't preview: Undefined variables: App.

Do you know any idea?

[mosharref_hossn]

@joyce-regions 

To preview the results of another workflow action using markdown code in the Email Action, you must provide a sample result to that previous action like this:

You can get the raw response data by running the previous action and viewing the action execution results or running the query in Notebook and looking at the "Raw response" visualization. 

[joyce-regions]

thank you for replying...I still cannot find out what issue is. 

I attached the raw response of previous DQL task and screenshot of error. Please help!

[mosharref_hossn · Accepted solution]

The results contains two data type: double, string

In the Jinja/markdown code,

{{e["cost_weekly"][:35]}} 

the slice operator like [:35] does not work on a float (double) data type in Jinja. That explains the error in the preview.

You can try something like this:

{{ "%-30s"|format(e["dt.cost.product"][:30]) }}  |   $ {{ "%-30s"|format(e["cost_weekly"]) }}

or 

{{ "%-30s"|format(e["dt.cost.product"][:30]) }}   |  $ {{ "%.2f"|format(e["cost_weekly"]) }}

[joyce-regions]

It is worked as expect. Thanks again so much. You mentioned that syntax using jinja, Can you please share with me any docs that I can learn about this? since I need to work a lot on flow to send out email with fully table info.

[mosharref_hossn]

Email Connector - Dynatrace Docs 

Expression reference — Dynatrace Docs

Jinja — Jinja Documentation (3.1.x)

[joyce-regions]

It is super super helpful....I can retrieve the table in the email. Thank you so much. 

[g_kat]

Very useful query, thank you!!