Advanced analytics with Notebooks

Latest Dynatrace
Reference
6-min read

You can use Notebooks to analyze your carbon footprint data.

Example 1

Gets all the business events from your OneAgent-monitored environment. The results of this query display a list of metrics reported by OneAgent instances for the selected timeframe in your Notebooks query.

fetch bizevents
 | filter contains (event.provider, "dynatrace.biz.carbon")
 | filter contains (event.type, "carbon.measurement")

The event.type == “carbon.report” is no longer supported after the app version 0.28.0, but old data can be explored, blending new and old event.types, with an alternative query:

fetch bizevents
 | filter contains (event.provider, "dynatrace.biz.carbon")
 | filter in(event.type,"carbon.report", "carbon.measurement")

Example 2

Gets the energy and CO2 consumption per host.

fetch bizevents
| filter contains (event.provider, "dynatrace.biz.carbon")
| filter contains (event.type, "carbon.measurement")
| summarize totalEnergy = sum(energy.measurement.computing), totalCO2e = sum(carbon.emissions), by:dt.entity.host

The attributes emissions and totalElectricConsumption are deprecated from the data model since the app version 0.28.0. If needed, the following alternative query makes calculations with the old and new schemas:

fetch bizevents
| filter contains (event.provider, "dynatrace.biz.carbon")
| filter contains (event.type, "carbon.measurement")
| fieldsAdd energy.measurement.computing=if(isNotNull(totalElectricConsumption), totalElectricConsumption, else:energy.measurement.computing)
| fieldsAdd carbon.emissions=if(isNotNull(emissions), emissions, else:carbon.emissions)
| summarize totalEnergy = sum(energy.measurement.computing), totalCO2e = sum(carbon.emissions), by:dt.entity.host

The following table lists sample results of this query, where totalCO2e is in grams and totalEnergy is in watt-hours.

dt.entity.host

totalEnergy

totalCO2e

HOST-1

14.02

7.55

HOST-2

21.65

11.67

HOST-3

48.79

26.30

Example 3

Gets the emissions from the top ten hosts that are generating more CO2-eq in grams taking advantage of the lookup command to include the names of the hosts.

fetch bizevents
| filter event.provider == "dynatrace.biz.carbon"
| filter contains (event.type, "carbon.measurement")
| fields dt.entity.host, carbon.emissions
| lookup sourceField:dt.entity.host, lookupField:id, prefix:"host.",[fetch dt.entity.host ]
| summarize totalEmissions = sum(carbon.emissions), by:{host.entity.name, dt.entity.host}
| sort (totalEmissions) desc
| limit 10

Example 4

Distributes carbon emissions by process group using CPU usage.

fetch dt.entity.process_group_instance
| fields entity.name, dt.entity.host=belongs_to[dt.entity.host],
  dt.entity.process_group= instance_of[dt.entity.process_group],
  dt.entity.process_group_instance = id
| lookup sourceField:dt.entity.host, lookupField:dt.entity.host, [fetch bizevents
  | filter event.type=="carbon.measurement"
  | summarize sum = sum(carbon.emissions), by:{dt.entity.host}], prefix:"carbon."
| lookup [timeseries ts=avg(dt.process.cpu.usage),
  by:{dt.entity.process_group_instance}
  | fields cpu_sum= arraySum(ts), dt.entity.process_group_instance],sourceField:dt.entity.process_group_instance,
  lookupField:dt.entity.process_group_instance
| fieldsAdd cpu_sum=lookup.cpu_sum
| fieldsRemove "lookup*"
| fieldsRename carbon_per_host=carbon.sum
| lookup [timeseries ts=avg(dt.process.cpu.usage),  by:{dt.entity.process_group_instance}
  | lookup [fetch dt.entity.process_group_instance], sourceField:dt.entity.process_group_instance, lookupField:id
  | fields ts, dt.entity.process_group_instance, dt.entity.host = lookup.belongs_to[dt.entity.host]
  | fieldsAdd cpusum = arraySum(ts)
  | summarize cpuHostSum = sum(cpusum), by:{dt.entity.host}],
  sourceField:dt.entity.host, lookupField:dt.entity.host
| fieldsAdd carbon_per_process = (cpu_sum/lookup.cpuHostSum) * carbon_per_host
| fields dt.entity.process_group, dt.entity.process_group_instance,
  dt.entity.host, carbon_per_process
| sort carbon_per_process desc

Query steps explained

Line 1
```
fetch dt.entity.process_group_instance
```
Fetches the table for process_group_instance.
Line 2
```
| fields entity.name, dt.entity.host=belongs_to[dt.entity.host],
  dt.entity.process_group= instance_of[dt.entity.process_group],
  dt.entity.process_group_instance = id
```
Collects from topology the hosts that are running those process group instances and the process group where those instances belong. Filter by hostId if you want to get data only from that particular host.

Line 3

| lookup sourceField:dt.entity.host, lookupField:dt.entity.host, [fetch bizevents
| filter event.type=="carbon.measurement"
| summarize sum = sum(carbon.emissions), by:{dt.entity.host}], prefix:"carbon."

The lookup command collects all emissions from the hosts, matching with the hosts that are running processes. The fields that are collected from the lookup command will come with a carbon prefix.

Line 4

| lookup [timeseries ts=avg(dt.process.cpu.usage),
by:{dt.entity.process_group_instance}
| fields cpu_sum= arraySum(ts), dt.entity.process_group_instance],sourceField:dt.entity.process_group_instance,
lookupField:dt.entity.process_group_instance

The lookup command collects the average values of the metrics CPU usage from each process instance in the system.

Line 5
```
| fieldsAdd cpu_sum=lookup.cpu_sum
```
Creates a new field for the CPU usage of the process instance.
Line 6
```
| fieldsRemove "lookup*"
```
Removes all the fields that came from the last lookup (the default prefix is lookup).
Line 7
```
| fieldsRename carbon_per_host=carbon.sum
```
Renames a field for a better understanding of the data contained.

Line 8

| lookup [timeseries ts=avg(dt.process.cpu.usage),  by:{dt.entity.process_group_instance}
| lookup [fetch dt.entity.process_group_instance], sourceField:dt.entity.process_group_instance, lookupField:id
| fields ts, dt.entity.process_group_instance, dt.entity.host = lookup.belongs_to[dt.entity.host]
| fieldsAdd cpusum = arraySum(ts)
| summarize cpuHostSum = sum(cpusum), by:{dt.entity.host}],
sourceField:dt.entity.host, lookupField:dt.entity.host

The first lookup extracts host CPU usage by Process Group Instance enriching the result with the dt.entity.host within the second lookup.

Inside the first lookup, you can see a timeseries command that extracts the average CPU usage for each process. The result of timeseries is enriched with the other lookup to collect the hosts and summarize the total CPU usage of each host coming from each process instance running on it.

Line 9
```
| fieldsAdd carbon_per_process = (cpu_sum/lookup.cpuHostSum) * carbon_per_host
```
A new field is calculated to distribute carbon emissions per process, multiplying the total emissions generated on a host, by the proportion of CPU usage of the process versus the total CPU usage of the host.

All of these parameters have been calculated in the previous steps of the DQL query.

Line 10

| fields dt.entity.process_group, dt.entity.process_group_instance,
dt.entity.host, carbon_per_process

Selects only the relevant fields as an output of the DQL query.

Line 11
```
| sort carbon_per_process desc
```
Sorts the results by the processes by most to least emissions generated.

Example 5

Gets the public price list costs of your cloud vendors.

fetch bizevents
| filter event.provider == "dynatrace.biz.carbon"
| filter event.type == "cost.list.price"
| limit 1

Example 6

Gets the hourly price list costs of the instrumented hosts with OneAgent and with the configured cloud vendor extension.

fetch bizevents
| filter event.provider == "dynatrace.biz.carbon"
| filter event.type == "cost.list.spend"
| filterOut isNull(cloud.provider)

Example 7

Gets the total costs per cloud provider from instrumented hosts based on the public price list.

fetch bizevents
| filter event.provider == "dynatrace.biz.carbon"
| filter event.type == "cost.list.spend"
| filterOut isNull(cloud.provider)
| summarize {totalCost = sum(cost.total), totalCpuCores = sum(resource.instance.cpu_cores)}, by:{cloud.provider}

Example 8

Gets the total public price cost from instrumented hosts per cloud provider over time for the last 30 days.

fetch bizevents, from:now()-30d, to:now()
| filter event.provider == "dynatrace.biz.carbon"
| filter event.type == "cost.list.spend"
| filterOut isNull(cloud.provider)
| makeTimeseries totalCost= sum(cost.total), interval:24h, by:{cloud.provider}

Example 9

Gets costs allocated by teams defined in the dt.owner attribute.

fetch bizevents
| filter event.provider == "dynatrace.biz.carbon"
| filter event.type == "cost.list.spend"

| lookup [
  fetch dt.entity.host
  | fields id,entity.name,entity.type,tags,osVersion
  | expand tags
  | filter contains(tags,"dt.owner")
  | parse tags, """'dt.owner:'LD:owner"""
  ], sourceField:dt.entity.host,lookupField:id,prefix:"lup.host."

| fieldsRename lup.host.owner, owner
| fieldsAdd team = if(isNotNull(owner),owner,else:"Unallocated costs")
| summarize {`USD spent by team` = sum(cost.total),  `number of instances` = count()}, by:{team}

Example 10

Lists the carbon emissions and energy consumption of all Kubernetes clusters.

fetch bizevents
| filter event.type == "carbon.measurement.k8s.cluster"
| summarize {carbon=SUM(carbon.emissions), energy=SUM(energy.measurement.total) }, by:{k8s.cluster.name}
| sort carbon desc

Example 11

Calculates a timeseries of cluster Kubernetes data for use in a line or area chart visualization.

fetch bizevents
| filter event.type == "carbon.measurement.k8s.cluster"
| makeTimeseries sum(carbon.emissions), interval:1d, by:{k8s.cluster.name}

Example 12

Leverages the namespace bizevent to summarize carbon and energy by cluster and namespace.

fetch bizevents
| filter event.type == "carbon.measurement.k8s.namespace"
| summarize {carbon=sum(carbon.emissions),energy=sum(energy.measurement.total)}, by:{k8s.cluster.name, k8s.namespace.name}

Example 13

Reports on energy and carbon information from Cost & Carbon Optimization Cost & Carbon Optimization with Kubernetes utilization information from Kubernetes (new) Kubernetes.

Joining energy/carbon information with Kubernetes CPU and memory slack identifies the most costly and underutilized Kubernetes resources. Leverage this information with your application teams to reconsider optimizing their requests/limits to reduce slack and optimize carbon and costs.

This example is at the Kubernetes cluster only and can be extended to provide namespace visibility. Depending on the size of your Kubernetes infrastructure, you may wish to include this level of information in a dashboard and filter on a user-selected cluster.

The DQL for this example was built using the notebook intent in Cost & Carbon Optimization Cost & Carbon Optimization and Kubernetes (new) Kubernetes, joining the two information sources using the lookup DQL command.

fetch bizevents
| filter event.type == "carbon.measurement.k8s.cluster"
| fieldsAdd id = dt.entity.kubernetes_cluster

// now aggregate carbon/energy by cluster
| summarize {Carbon=SUM(carbon.emissions),  Energy=SUM(energy.measurement.total)}, by:{id, k8s.cluster.name}

// now let's get some CPU and memory utilization statistics
// CPU statistics first
| lookup [
  timeseries {
      valuesOp1 = sum(dt.kubernetes.container.requests_cpu),
      valuesOp2 = sum(dt.kubernetes.container.cpu_usage)
    }, by:{dt.entity.kubernetes_cluster}, from: -2m
    | fieldsAdd cpu_slack = valuesOp1[] - valuesOp2[]
    | fieldsAdd cpu_slack = arrayFirst(cpu_slack)
], sourceField:id, lookupField:dt.entity.kubernetes_cluster, fields:{cpu_slack}, executionOrder:leftFirst
| fieldsAdd cpuSlack =todouble( cpu_slack/1000 )

| lookup [
  timeseries {
      valuesOp1 = sum(dt.kubernetes.container.cpu_usage),
      valuesOp2 = sum(dt.kubernetes.node.cpu_allocatable)
    }, by:{dt.entity.kubernetes_cluster}, from: -2m
    | fieldsAdd cpu_usage_percent = valuesOp1[] / valuesOp2[]
    | fieldsAdd cpu_usage_percent = record(usedPercentageOfMax=arrayFirst(cpu_usage_percent)*100,cpuCore=arrayFirst(valuesOp2)/1000)
], sourceField:id, lookupField:dt.entity.kubernetes_cluster, fields:{cpu_usage_percent}, executionOrder:leftFirst

| lookup [
  timeseries {
      valuesOp1 = sum(dt.kubernetes.container.requests_cpu),
      valuesOp2 = sum(dt.kubernetes.node.cpu_allocatable)
    }, by:{dt.entity.kubernetes_cluster}, from: -2m
    | fieldsAdd requests_cpu_percent = valuesOp1[] / valuesOp2[]
    | fieldsAdd requests_cpu_percent = record(usedPercentageOfMax=arrayFirst(requests_cpu_percent)*100,cpuCore=arrayFirst(valuesOp2))
], sourceField:id, lookupField:dt.entity.kubernetes_cluster, fields:{requests_cpu_percent}, executionOrder:leftFirst
| lookup [
  timeseries {
      valuesOp1 = sum(dt.kubernetes.container.limits_cpu),
      valuesOp2 = sum(dt.kubernetes.node.cpu_allocatable)
    }, by:{dt.entity.kubernetes_cluster}, from: -2m
    | fieldsAdd limits_cpu_percent = valuesOp1[] / valuesOp2[]
    | fieldsAdd limits_cpu_percent = record(usedPercentageOfMax=arrayFirst(limits_cpu_percent),cpuCore=arrayFirst(valuesOp2))
], sourceField:id, lookupField:dt.entity.kubernetes_cluster, fields:{limits_cpu_percent}, executionOrder:leftFirst

// now memory statistics
| lookup [
  timeseries {
      valuesOp1 = sum(dt.kubernetes.container.memory_working_set),
      valuesOp2 = sum(dt.kubernetes.node.memory_allocatable)
    }, by:{dt.entity.kubernetes_cluster}, from: -2m
    | fieldsAdd memory_usage_percent = valuesOp1[] / valuesOp2[]
    | fieldsAdd memory_usage_percent = record(usedPercentageOfMax=arrayFirst(memory_usage_percent),memoryAllocatable=arrayFirst(valuesOp2))
], sourceField:id, lookupField:dt.entity.kubernetes_cluster, fields:{memory_usage_percent}, executionOrder:leftFirst
| lookup [
  timeseries {
      valuesOp1 = sum(dt.kubernetes.container.requests_memory),
      valuesOp2 = sum(dt.kubernetes.node.memory_allocatable)
    }, by:{dt.entity.kubernetes_cluster}, from: -2m
    | fieldsAdd requests_memory_percent = valuesOp1[] / valuesOp2[]
    | fieldsAdd requests_memory_percent = record(usedPercentageOfMax=arrayFirst(requests_memory_percent),memoryAllocatable=arrayFirst(valuesOp2))
], sourceField:id, lookupField:dt.entity.kubernetes_cluster, fields:{requests_memory_percent}, executionOrder:leftFirst
| lookup [
  timeseries {
      valuesOp1 = sum(dt.kubernetes.container.limits_memory),
      valuesOp2 = sum(dt.kubernetes.node.memory_allocatable)
    }, by:{dt.entity.kubernetes_cluster}, from: -2m
    | fieldsAdd limits_memory_percent = valuesOp1[] / valuesOp2[]
    | fieldsAdd limits_memory_percent = record(usedPercentageOfMax=arrayFirst(limits_memory_percent),memoryAllocatable=arrayFirst(valuesOp2))
], sourceField:id, lookupField:dt.entity.kubernetes_cluster, fields:{limits_memory_percent}, executionOrder:leftFirst
| lookup [
  timeseries {
      valuesOp1 = sum(dt.kubernetes.container.requests_memory),
      valuesOp2 = sum(dt.kubernetes.container.memory_working_set)
    }, by:{dt.entity.kubernetes_cluster}, from: -2m
    | fieldsAdd memory_slack = valuesOp1[] - valuesOp2[]
    | fieldsAdd memory_slack = arrayFirst(memory_slack)
], sourceField:id, lookupField:dt.entity.kubernetes_cluster, fields:{memory_slack}, executionOrder:leftFirst
// and get the pods allocatable data
| lookup [
  timeseries {
      valuesOp1 = sum(dt.kubernetes.pods),
      valuesOp2 = sum(dt.kubernetes.node.pods_allocatable)
    }, by:{dt.entity.kubernetes_cluster}, from: -2m
    | fieldsAdd pods_percent = valuesOp1[] / valuesOp2[]
    | fieldsAdd pods_percent = record(usedPercentageOfMax=arrayFirst(pods_percent),rawMaxValue=arrayFirst(valuesOp2))
], sourceField:id, lookupField:dt.entity.kubernetes_cluster, fields:{pods_percent}, executionOrder:leftFirst

// counts
| lookup [
  fetch dt.entity.kubernetes_node | fields id, matchedId = clustered_by[dt.entity.kubernetes_cluster]| lookup [fetch events, from: -370m
  | filter event.kind == "DAVIS_PROBLEM"
  | filter dt.system.bucket == "default_davis_events"
  | sort timestamp
  | summarize {
              event.status = takeLast(event.status),
              dt.davis.is_duplicate = takeLast(dt.davis.is_duplicate),
              affected_entity_ids = takeLast(affected_entity_ids)
        }, by: {event.id}
  | expand affected_entity_ids
  | filter isNull(dt.davis.is_duplicate) OR not(dt.davis.is_duplicate)
  | filter event.status == "ACTIVE"
  | summarize node.problem.count = count(),
  by:{affected_entity_ids}], sourceField:id, lookupField:affected_entity_ids, fields:{node.problem.count}| fieldsAdd unhealthy = if(node.problem.count > 0, 1, else: 0)| summarize raw_node.count = count(), raw_node.problem.count = sum(unhealthy), by:{matchedId}
], sourceField:id, lookupField:matchedId, fields:{raw_node.count,raw_node.problem.count}, executionOrder:leftFirst
| fieldsAdd node.count = raw_node.count, node.problem.count = raw_node.problem.count

| lookup [
  fetch dt.entity.cloud_application_namespace | fields id, matchedId = clustered_by[dt.entity.kubernetes_cluster]| lookup [fetch events, from: -370m
  | filter event.kind == "DAVIS_PROBLEM"
  | filter dt.system.bucket == "default_davis_events"
  | sort timestamp
  | summarize {
              event.status = takeLast(event.status),
              dt.davis.is_duplicate = takeLast(dt.davis.is_duplicate),
              affected_entity_ids = takeLast(affected_entity_ids)
        }, by: {event.id}
  | expand affected_entity_ids
  | filter isNull(dt.davis.is_duplicate) OR not(dt.davis.is_duplicate)
  | filter event.status == "ACTIVE"
  | summarize namespace.problem.count = count(),
  by:{affected_entity_ids}], sourceField:id, lookupField:affected_entity_ids, fields:{namespace.problem.count}| fieldsAdd unhealthy = if(namespace.problem.count > 0, 1, else: 0)| summarize raw_namespace.count = count(), raw_namespace.problem.count = sum(unhealthy), by:{matchedId}
], sourceField:id, lookupField:matchedId, fields:{raw_namespace.count,raw_namespace.problem.count}, executionOrder:leftFirst
| fieldsAdd namespace.count = raw_namespace.count, namespace.problem.count = raw_namespace.problem.count

// now let's add the utilization fields and display them with the carbon data by cluster
| fields k8s.cluster.name, Carbon, Energy, node.count, namespace.count, cpu_usage_percent, requests_cpu_percent, limits_cpu_percent, cpuSlack,
    memory_usage_percent, requests_memory_percent, limits_memory_percent, memory_slack,
    pods_percent