Advanced analytics with Notebooks

You can use Notebooks Notebooks to analyze your carbon footprint data.

Example 1

Get 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

Get 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

Get 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

Distribute 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

    The table for process_group_instance is fetched.

  • 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

Get 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

Get 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

Get 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

Get 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

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