DQL examples for security data

The examples below illustrate how to build powerful and flexible security reports by using DQL queries to slice and dice historical security events.

Basic examples

Total number of open vulnerabilities

Get the total number of open, non-muted vulnerabilities in your environment.

Query steps:

Fetch the latest snapshot of the vulnerabilities per entity.
Filter the data by the resolution and mute status.
Count the vulnerabilities.

Query example:

fetch events
| filter dt.system.bucket=="default_security_events"
| filter event.provider=="Dynatrace"
| filter event.type=="VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level=="ENTITY"
// get latest snapshot
| sort timestamp, direction:"descending"
| summarize {
  vulnerability.risk.level = takeFirst(vulnerability.risk.level),
  vulnerability.parent.mute.status = takeFirst(vulnerability.parent.mute.status),
    vulnerability.mute.status = takeFirst(vulnerability.mute.status),
  vulnerability.resolution.status = takeFirst(vulnerability.resolution.status)
}, by: {vulnerability.id,affected_entity.id}
 // end of get latest snapshot
| filter vulnerability.resolution.status == "OPEN"
| filter vulnerability.parent.mute.status != "MUTED" AND vulnerability.mute.status != "MUTED"
| summarize total=arraySize(collectDistinct(vulnerability.id))
| fields `Open vulnerabilities` = coalesce(total, 0)

Query result:

Open non-muted vulnerabilities

Total number of critical open vulnerabilities

Get the total number of the critical open, non-muted vulnerabilities in your environment.

Query steps:

Fetch the latest snapshot of the vulnerabilities per entity.
Filter the data by resolution and mute status.
Filter the data by the vulnerabilities' risk level.
Count the vulnerabilities.

Query example:

fetch events
| filter dt.system.bucket=="default_security_events"
| filter event.provider=="Dynatrace"
| filter event.type=="VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level=="ENTITY"
// get latest snapshot
| sort timestamp, direction:"descending"
| summarize {
  vulnerability.risk.level = takeFirst(vulnerability.risk.level),
  vulnerability.parent.mute.status = takeFirst(vulnerability.parent.mute.status),
    vulnerability.mute.status = takeFirst(vulnerability.mute.status),
  vulnerability.resolution.status = takeFirst(vulnerability.resolution.status)
}, by: {vulnerability.id,affected_entity.id}
// end of get latest snapshot
| filter in(vulnerability.risk.level,{"CRITICAL"})
| filter vulnerability.resolution.status == "OPEN"
| filter vulnerability.parent.mute.status != "MUTED" AND vulnerability.mute.status != "MUTED"
| summarize total=arraySize(collectDistinct(vulnerability.id))
| fields `Critical open vulnerabilities` = coalesce(total, 0)

Query result:

Total number of critical open vulnerabilities

Total number of open vulnerabilities in a management zone

Get the total number of open, non-muted vulnerabilities in a specific management zone (in this example, AppSec: UNGUARD).

Query steps:

Fetch the latest snapshot of the vulnerabilities per entity.
Filter the data by resolution and mute status.
Filter the vulnerabilities by a specific management zone.
Count the vulnerabilities.

Query example:

fetch events
| filter dt.system.bucket=="default_security_events"
| filter event.provider=="Dynatrace"
| filter event.type=="VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level=="ENTITY"
// get latest snapshot
| sort timestamp, direction:"descending"
| summarize {
  vulnerability.risk.level = takeFirst(vulnerability.risk.level),
  vulnerability.parent.mute.status = takeFirst(vulnerability.parent.mute.status),
  vulnerability.mute.status = takeFirst(vulnerability.mute.status),
  vulnerability.resolution.status = takeFirst(vulnerability.resolution.status),
  affected_entity.management_zones.names = takeFirst(affected_entity.management_zones.names)
}, by: {vulnerability.id,affected_entity.id}
// end of get latest snapshot
| filter vulnerability.resolution.status == "OPEN"
| filter vulnerability.parent.mute.status != "MUTED" AND vulnerability.mute.status != "MUTED"
| filter in(affected_entity.management_zones.names,{"AppSec: Unguard"}) // filter for the management zone
| summarize total=arraySize(collectDistinct(vulnerability.id))
| fields `Open vulnerabilities (unguard)` = coalesce(total, 0)

Query result:

Open, non-muted vulnerabilities in a management zone

Total number of open vulnerabilities with internet exposure

Get the total number of open, non-muted vulnerabilities with public internet exposure in your environment.

Query steps:

Fetch the latest snapshot of the vulnerabilities per entity.
Filter the data by resolution and mute status.
Filters the data by the vulnerabilities' internet exposure level.
Count the vulnerabilities.

Query example:

fetch events
| filter dt.system.bucket=="default_security_events"
| filter event.provider=="Dynatrace"
| filter event.type=="VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level=="ENTITY"
// get latest snapshot
| sort timestamp, direction:"descending"
| summarize {
  vulnerability.risk.level = takeFirst(vulnerability.risk.level),
  vulnerability.parent.mute.status = takeFirst(vulnerability.parent.mute.status),
  vulnerability.mute.status = takeFirst(vulnerability.mute.status),
  vulnerability.resolution.status = takeFirst(vulnerability.resolution.status),
  vulnerability.davis_assessment.exposure_status=takeFirst(vulnerability.davis_assessment.exposure_status)
}, by: {vulnerability.display_id,affected_entity.id}
// end of get latest snapshot
| summarize {`Internet exposure`=countIf(vulnerability.davis_assessment.exposure_status=="PUBLIC_NETWORK")},
by: {vulnerability.display_id}
| summarize {`With internet exposure`=countIf(`Internet exposure`>0)}

Query result:

Total number of open vulnerabilities with public internet exposure

Total number of affected entities

Get the total number of affected entities in your environment.

Query steps:

Fetch the latest snapshot of the vulnerabilities per entity.
Filter the data by resolution and mute status.
Counts the affected entities.

Query example:

fetch events
| filter dt.system.bucket=="default_security_events"
| filter event.provider=="Dynatrace"
| filter event.type=="VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level=="ENTITY"
// get latest snapshot
| sort timestamp, direction:"descending"
| summarize {
  vulnerability.risk.level = takeFirst(vulnerability.risk.level),
  vulnerability.parent.mute.status = takeFirst(vulnerability.parent.mute.status),
  vulnerability.mute.status = takeFirst(vulnerability.mute.status),
  vulnerability.resolution.status = takeFirst(vulnerability.resolution.status),
  affected_entity.type=takeFirst(affected_entity.type)
}, by: {vulnerability.id,affected_entity.id}
// end of get latest snapshot
| filter vulnerability.resolution.status == "OPEN"
| filter vulnerability.parent.mute.status != "MUTED" AND vulnerability.mute.status != "MUTED"
| summarize entities=arraySize(collectDistinct(affected_entity.id))
| fields `Affected entities`=coalesce(entities, 0)

Query result:

Total number of affected entities

Total number of affected process groups

Get the total number of affected process groups in your environment.

Query steps:

Fetch the latest snapshot of the vulnerabilities per entity.
Filter the data by resolution and mute status.
Filter the data by the type of affected entity.
Counts the affected entities.

Query example:

fetch events
| filter dt.system.bucket=="default_security_events"
| filter event.provider=="Dynatrace"
| filter event.type=="VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level=="ENTITY"
// get latest snapshot
| sort timestamp, direction:"descending"
| summarize {
  vulnerability.risk.level = takeFirst(vulnerability.risk.level),
  vulnerability.parent.mute.status = takeFirst(vulnerability.parent.mute.status),
  vulnerability.mute.status = takeFirst(vulnerability.mute.status),
  vulnerability.resolution.status = takeFirst(vulnerability.resolution.status),
  affected_entity.type=takeFirst(affected_entity.type)
}, by: {vulnerability.id,affected_entity.id}
// end of get latest snapshot
| filter vulnerability.resolution.status == "OPEN"
| filter vulnerability.parent.mute.status != "MUTED" AND vulnerability.mute.status != "MUTED"
| summarize affected_entity.type=takeAny(affected_entity.type), by: {affected_entity.id}
| summarize entities=countIf(affected_entity.type=="PROCESS_GROUP") // filter for the type of an affected entity
| fields `Affected process groups`=coalesce(entities, 0)

Query result:

Total number of affected process groups

Total number of affected entities over time

Get the total number of affected, non-muted entities over time (in three-hour buckets).

Query steps:

Fetch the vulnerabilities per entity.
Filter the data by resolution and mute status.
Count the entities for each timestamp.
Aggregate the data over time into three-hour buckets.

Query example:

fetch events
| filter dt.system.bucket=="default_security_events"
| filter event.provider=="Dynatrace"
| filter event.type=="VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level=="ENTITY"
| filter vulnerability.resolution.status == "OPEN"
| filter vulnerability.parent.mute.status != "MUTED" AND vulnerability.mute.status != "MUTED"
| summarize count(), by:{affected_entity.id, timestamp=bin(timestamp,3h)}
| makeTimeseries {`Affected entities`=count()}, time: timestamp

Query result:

Total number of affected entities over time

Get the total number of hosts that are indirectly affected by open vulnerabilities in your environment.

Query steps:

Fetch the vulnerabilities per entity.
Filter the data by resolution and mute status.
Count the hosts that relate to a vulnerability.

Query example:

fetch events
| filter dt.system.bucket=="default_security_events"
| filter event.provider=="Dynatrace"
| filter event.type=="VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level=="ENTITY"
// get latest snapshot
| sort timestamp, direction:"descending"
| summarize {
  vulnerability.risk.level = takeFirst(vulnerability.risk.level),
  vulnerability.parent.mute.status = takeFirst(vulnerability.parent.mute.status),
  vulnerability.mute.status = takeFirst(vulnerability.mute.status),
  vulnerability.resolution.status = takeFirst(vulnerability.resolution.status),
  related_entities.hosts.ids=takeFirst(related_entities.hosts.ids)
}, by: {vulnerability.id,affected_entity.id}
// end of get latest snapshot
| filter vulnerability.resolution.status == "OPEN"
| filter vulnerability.parent.mute.status != "MUTED" AND vulnerability.mute.status != "MUTED"
| expand entities=related_entities.hosts.ids
| summarize entities=arraySize(arrayRemoveNulls(collectDistinct(entities)))
| fields `Related hosts`=coalesce(entities, 0)

Query result:

Total number of hosts related to vulnerabilities

Open vulnerabilities by risk level

Get a count of open vulnerabilities split by risk levels.

Query steps:

Fetch the latest snapshot of the vulnerabilities per entity.
Filter the data by resolution and mute status.
Count the entities for each known risk level and calculate the maximal risk score.
Count the vulnerabilities for each risk level sorted by the maximal risk score.

Query example:

fetch events
| filter dt.system.bucket=="default_security_events"
| filter event.provider=="Dynatrace"
| filter event.type=="VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level=="ENTITY"
// get latest snapshot
| sort timestamp, direction:"descending"
| summarize {
  vulnerability.risk.level = takeFirst(vulnerability.risk.level),
  vulnerability.risk.score = takeFirst(vulnerability.risk.score),
  vulnerability.parent.mute.status = takeFirst(vulnerability.parent.mute.status),
  vulnerability.mute.status = takeFirst(vulnerability.mute.status),
  vulnerability.resolution.status = takeFirst(vulnerability.resolution.status),
  affected_entity.management_zones.names = takeFirst(affected_entity.management_zones.names),
  vulnerability.type = takeFirst(vulnerability.type)
}, by: {vulnerability.id,affected_entity.id}
// end of get latest snapshot
| filter vulnerability.resolution.status == "OPEN"
| filter vulnerability.parent.mute.status != "MUTED" AND vulnerability.mute.status != "MUTED"
| summarize{
Critical=countIf(vulnerability.risk.level=="CRITICAL"),
High=countIf(vulnerability.risk.level=="HIGH"),
Medium=countIf(vulnerability.risk.level=="MEDIUM"),
Low=countIf(vulnerability.risk.level=="LOW"),
None=countIf(vulnerability.risk.level=="NONE"),
maxScore=takeMax(vulnerability.risk.score)
}, by: {vulnerability.id}
| fieldsAdd riskLevel=if(Critical>0,"CRITICAL", else:
                         if(High>0,"HIGH", else:
                         if(Medium>0,"MEDIUM", else:
                         if(Low>0,"LOW", else:
                         if(None>0,"NONE")))))
| summarize {
Vulnerabilities=count(),
maxScore=takeMax(maxScore)
}, by:{riskLevel}
| sort maxScore, direction:"descending"

Query result:

Open vulnerabilities by risk level

Open vulnerabilities by type

Get a count of open vulnerabilities split by type.

Query steps:

Fetch the latest snapshot per vulnerability.
Filter the data by resolution and mute status.
Count the vulnerabilities per type.
Lists the types sorted by the counts.

Query example:

fetch events
| filter dt.system.bucket=="default_security_events"
| filter event.provider=="Dynatrace"
| filter event.type=="VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level=="ENTITY"
// get latest snapshot
| sort timestamp, direction:"descending"
| summarize {
  vulnerability.risk.level = takeFirst(vulnerability.risk.level),
  vulnerability.risk.score = takeFirst(vulnerability.risk.score),
  vulnerability.parent.mute.status = takeFirst(vulnerability.parent.mute.status),
  vulnerability.mute.status = takeFirst(vulnerability.mute.status),
  vulnerability.resolution.status = takeFirst(vulnerability.resolution.status),
  affected_entity.management_zones.names = takeFirst(affected_entity.management_zones.names),
  vulnerability.type = takeFirst(vulnerability.type)
}, by: {vulnerability.display_id,affected_entity.id}
// end of get latest snapshot
| filter vulnerability.resolution.status == "OPEN"
| filter vulnerability.parent.mute.status != "MUTED" AND vulnerability.mute.status != "MUTED"
| summarize
{vulnerability.type=takeFirst(vulnerability.type)},
by: {vulnerability.display_id}
| summarize
vulnerabilities=count(), by:{vulnerability.type}
| sort vulnerabilities,direction:"descending"

Query result:

Open vulnerabilities by type

Vulnerability count over time by status

Get the vulnerability count over time split by status, in three-hour buckets.

Query steps:

Fetch the last snapshot of the vulnerabilities per entity for each three-hour timestamp bucket.
Count the entities per vulnerability split by each combined status (resolution and mute status).
Count the vulnerabilities over time split by the aggregated combined status.

Query example:

fetch events
| filter dt.system.bucket=="default_security_events"
| filter event.provider=="Dynatrace"
| filter event.type=="VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level=="ENTITY"
| sort timestamp, direction:"descending"
| summarize {
  vulnerability.resolution.status=takeFirst(vulnerability.resolution.status),
  vulnerability.mute.status=takeFirst(vulnerability.mute.status),
  vulnerability.parent.mute.status=takeFirst(vulnerability.parent.mute.status)
}, by: {vulnerability.display_id, affected_entity.id, timestamp=bin(timestamp,3h)}
| summarize {
OpenEntities=countIf(vulnerability.resolution.status=="OPEN" AND vulnerability.mute.status=="NOT_MUTED" AND vulnerability.parent.mute.status=="NOT_MUTED"),
ResolvedEntities=countIf(vulnerability.resolution.status=="RESOLVED" AND vulnerability.mute.status=="NOT_MUTED" AND vulnerability.parent.mute.status=="NOT_MUTED"),
MutedEntities=countIf(vulnerability.mute.status=="MUTED" OR vulnerability.parent.mute.status=="MUTED")
}, by: {vulnerability.display_id, timestamp}
| makeTimeseries {
Muted=countIf(OpenEntities==0 AND ResolvedEntities==0 AND MutedEntities>0),
Resolved=countIf(OpenEntities==0 AND ResolvedEntities>0),
Open=countIf(OpenEntities>0)
}, time: timestamp

Query result:

Vulnerability count over time by status

Advanced examples

Vulnerabilities on a library

Get the open vulnerabilities on a specific library (in this example, log4j).

Query steps:

Fetch the latest snapshot of the vulnerabilities per entity.
Filter the data by resolution and mute status.
Filter the data by the software component name.
Aggregate the data per vulnerability.
List prioritized vulnerabilities with the most relevant information, sorted by various criteria, including risk, status, risk assessments, affected entities count, and the timestamp of when the vulnerabilities were first seen.

Query example:

fetch events
| filter dt.system.bucket=="default_security_events"
| filter event.provider=="Dynatrace"
| filter event.type=="VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level=="ENTITY"
// get latest snapshot
| sort timestamp, direction:"descending"
| summarize {
vulnerability.parent.first_seen=takeFirst(vulnerability.parent.first_seen),
vulnerability.resolution.status=takeFirst(vulnerability.resolution.status),
vulnerability.mute.status=takeFirst(vulnerability.mute.status),
vulnerability.parent.resolution.status=takeFirst(vulnerability.parent.resolution.status),
vulnerability.parent.resolution.change_date=takeFirst(vulnerability.parent.resolution.change_date),
vulnerability.parent.mute.status=takeFirst(vulnerability.parent.mute.status),
vulnerability.parent.risk.score=takeFirst(vulnerability.parent.risk.score),
vulnerability.risk.score=takeFirst(vulnerability.risk.score),
vulnerability.parent.risk.level=takeFirst(vulnerability.parent.risk.level),
vulnerability.risk.level=takeFirst(vulnerability.risk.level),
vulnerability.stack=takeFirst(vulnerability.stack),
vulnerability.type=takeFirst(vulnerability.type),
vulnerability.external_id=takeFirst(vulnerability.external_id),
vulnerability.references.cve=takeFirst(vulnerability.references.cve),
vulnerability.technology=takeFirst(vulnerability.technology),
vulnerability.resolution.change_date=takeFirst(vulnerability.resolution.change_date),
vulnerability.mute.change_date=takeFirst(vulnerability.mute.change_date),
vulnerability.title=takeFirst(vulnerability.title),
vulnerability.davis_assessment.exposure_status=takeFirst(vulnerability.davis_assessment.exposure_status),
vulnerability.davis_assessment.exploit_status=takeFirst(vulnerability.davis_assessment.exploit_status),
vulnerability.davis_assessment.vulnerable_function_status=takeFirst(vulnerability.davis_assessment.vulnerable_function_status),
vulnerability.davis_assessment.data_assets_status=takeFirst(vulnerability.davis_assessment.data_assets_status),
affected_entity.vulnerable_component.name=takeFirst(affected_entity.vulnerable_component.name),
affected_entity.management_zones.names=takeFirst(affected_entity.management_zones.names),
affected_entity.name=takeFirst(affected_entity.name),
related_entities.hosts.names=takeFirst(related_entities.hosts.names),
related_entities.kubernetes_workloads.names=takeFirst(related_entities.kubernetes_workloads.names),
related_entities.kubernetes_clusters.names=takeFirst(related_entities.kubernetes_clusters.names),
related_entities.databases.count=takeFirst(related_entities.databases.count),
timestamp=takeFirst(timestamp)
}, by: {vulnerability.display_id, affected_entity.id}
// end of get latest snapshot
| filter contains(affected_entity.vulnerable_component.name,"log4j") // filter by the vulnerable library/component name
// now summarize on the vulnerability level
| summarize{
vulnerability.parent.first_seen=takeFirst(vulnerability.parent.first_seen),
vulnerability.parent.resolution.status=takeFirst(vulnerability.parent.resolution.status),
vulnerability.parent.resolution.change_date=takeFirst(vulnerability.parent.resolution.change_date),
vulnerability.parent.mute.status=takeFirst(vulnerability.parent.mute.status),
vulnerability.title=takeFirst(vulnerability.title),
vulnerability.references.cve=takeFirst(vulnerability.references.cve),
affected_entity.vulnerable_component.names=collectDistinct(affected_entity.vulnerable_component.name),
Critical=countIf(vulnerability.risk.level=="CRITICAL"),
High=countIf(vulnerability.risk.level=="HIGH"),
Medium=countIf(vulnerability.risk.level=="MEDIUM"),
Low=countIf(vulnerability.risk.level=="LOW"),
vulnerability.risk.score=round(takeMax(vulnerability.risk.score),decimals:1),
`Affected entities`=arraySize(collectDistinct(affected_entity.id)),
`# Non-muted entities`=countIf(vulnerability.mute.status=="NOT_MUTED"),
`# Afftected entities`=countIf(vulnerability.resolution.status=="OPEN"),
`# Function in use`=countIf(vulnerability.davis_assessment.vulnerable_function_status=="IN_USE"),
`# Exposure to internet`=countIf(vulnerability.davis_assessment.exposure_status=="PUBLIC_NETWORK"),
`# Exploit published`=countIf(vulnerability.davis_assessment.exploit_status=="AVAILABLE"),
`# Reachable databases`=countIf(vulnerability.davis_assessment.data_assets_status=="REACHABLE")
}, by: {vulnerability.display_id}
| fieldsAdd vulnerability.risk.level=if(Critical>0,"CRITICAL",
                                     else:if(High>0,"HIGH",
                                     else:if(Medium>0,"MEDIUM",
                                     else:"LOW")))
| fields
vulnerability.display_id,
vulnerability.risk.level,
vulnerability.risk.score,
vulnerability.title,
vulnerability.references.cve,
vulnerability.parent.resolution.status,
vulnerability.parent.mute.status,
vulnerability.parent.first_seen,
status_sort=if(vulnerability.parent.resolution.status=="RESOLVED",3,else:if( vulnerability.parent.mute.status=="MUTED" OR `# Non-muted entities`==0,2,else:1)),
`# Function in use`,
`# Afftected entities`,
`# Exposure to internet`,
`# Exploit published`,
`# Reachable databases`
| sort   status_sort, {vulnerability.risk.score, direction:"descending"}, {`# Function in use`, direction:"descending"},
{`# Exposure to internet`, direction:"descending"}, {`# Exploit published`, direction:"descending"},
{`# Reachable databases`, direction:"descending"}, {`# Afftected entities`,direction:"descending"}, {vulnerability.parent.first_seen,direction:"descending"}
| fieldsRemove status_sort

Query result:

List vulnerabilities on a library

Vulnerabilities on a host

Get the open vulnerabilities directly or indirectly affecting a specific host (in this example, i-05f1305a50721e04d).

Query steps:

Fetch the latest snapshot of the vulnerabilities per entity.
Filter the data by resolution and mute status.
Filter the data by host name (as a related or an affected entity).
Aggregate the data per vulnerability.
List prioritized vulnerabilities with the most relevant information, sorted by various criteria, including risk, status, risk assessments, affected entities count, and the timestamp of when the vulnerabilities were first seen.

Query example:

fetch events
| filter dt.system.bucket=="default_security_events"
| filter event.provider=="Dynatrace"
| filter event.type=="VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level=="ENTITY"
// get latest snapshot
| sort timestamp, direction:"descending"
| summarize {
vulnerability.parent.first_seen=takeFirst(vulnerability.parent.first_seen),
vulnerability.resolution.status=takeFirst(vulnerability.resolution.status),
vulnerability.mute.status=takeFirst(vulnerability.mute.status),
vulnerability.parent.resolution.status=takeFirst(vulnerability.parent.resolution.status),
vulnerability.parent.resolution.change_date=takeFirst(vulnerability.parent.resolution.change_date),
vulnerability.parent.mute.status=takeFirst(vulnerability.parent.mute.status),
vulnerability.parent.risk.score=takeFirst(vulnerability.parent.risk.score),
vulnerability.risk.score=takeFirst(vulnerability.risk.score),
vulnerability.parent.risk.level=takeFirst(vulnerability.parent.risk.level),
vulnerability.risk.level=takeFirst(vulnerability.risk.level),
vulnerability.stack=takeFirst(vulnerability.stack),
vulnerability.type=takeFirst(vulnerability.type),
vulnerability.external_id=takeFirst(vulnerability.external_id),
vulnerability.references.cve=takeFirst(vulnerability.references.cve),
vulnerability.technology=takeFirst(vulnerability.technology),
vulnerability.resolution.change_date=takeFirst(vulnerability.resolution.change_date),
vulnerability.mute.change_date=takeFirst(vulnerability.mute.change_date),
vulnerability.title=takeFirst(vulnerability.title),
vulnerability.davis_assessment.exposure_status=takeFirst(vulnerability.davis_assessment.exposure_status),
vulnerability.davis_assessment.exploit_status=takeFirst(vulnerability.davis_assessment.exploit_status),
vulnerability.davis_assessment.vulnerable_function_status=takeFirst(vulnerability.davis_assessment.vulnerable_function_status),
vulnerability.davis_assessment.data_assets_status=takeFirst(vulnerability.davis_assessment.data_assets_status),
affected_entity.vulnerable_component.name=takeFirst(affected_entity.vulnerable_component.name),
affected_entity.management_zones.names=takeFirst(affected_entity.management_zones.names),
affected_entity.name=takeFirst(affected_entity.name),
related_entities.hosts.names=takeFirst(related_entities.hosts.names),
related_entities.kubernetes_workloads.names=takeFirst(related_entities.kubernetes_workloads.names),
related_entities.kubernetes_clusters.names=takeFirst(related_entities.kubernetes_clusters.names),
related_entities.databases.count=takeFirst(related_entities.databases.count),
timestamp=takeFirst(timestamp)
}, by: {vulnerability.display_id, affected_entity.id}
// end of get latest snapshot
| filter iAny(in("i-05f1305a50721e04d",related_entities.hosts.names[])) OR affected_entity.name=="i-05f1305a50721e04d" // filter by name of the related/affected host
// now summarize on the vulnerability level
| summarize{
vulnerability.parent.first_seen=takeFirst(vulnerability.parent.first_seen),
vulnerability.parent.resolution.status=takeFirst(vulnerability.parent.resolution.status),
vulnerability.parent.resolution.change_date=takeFirst(vulnerability.parent.resolution.change_date),
vulnerability.parent.mute.status=takeFirst(vulnerability.parent.mute.status),
vulnerability.title=takeFirst(vulnerability.title),
vulnerability.references.cve=takeFirst(vulnerability.references.cve),
affected_entity.vulnerable_component.names=collectDistinct(affected_entity.vulnerable_component.name),
Critical=countIf(vulnerability.risk.level=="CRITICAL"),
High=countIf(vulnerability.risk.level=="HIGH"),
Medium=countIf(vulnerability.risk.level=="MEDIUM"),
Low=countIf(vulnerability.risk.level=="LOW"),
vulnerability.risk.score=round(takeMax(vulnerability.risk.score),decimals:1),
`Affected entities`=arraySize(collectDistinct(affected_entity.id)),
`# Non-muted entities`=countIf(vulnerability.mute.status=="NOT_MUTED"),
`# Afftected entities`=countIf(vulnerability.resolution.status=="OPEN"),
`# Function in use`=countIf(vulnerability.davis_assessment.vulnerable_function_status=="IN_USE"),
`# Exposure to internet`=countIf(vulnerability.davis_assessment.exposure_status=="PUBLIC_NETWORK"),
`# Exploit published`=countIf(vulnerability.davis_assessment.exploit_status=="AVAILABLE"),
`# Reachable databases`=countIf(vulnerability.davis_assessment.data_assets_status=="REACHABLE")
}, by: {vulnerability.display_id}
| fieldsAdd vulnerability.risk.level=if(Critical>0,"CRITICAL",
                                     else:if(High>0,"HIGH",
                                     else:if(Medium>0,"MEDIUM",
                                     else:"LOW")))
| fields
vulnerability.display_id,
vulnerability.risk.level,
vulnerability.risk.score,
vulnerability.title,
vulnerability.references.cve,
vulnerability.parent.resolution.status,
vulnerability.parent.mute.status,
vulnerability.parent.first_seen,
status_sort=if(vulnerability.parent.resolution.status=="RESOLVED",3,else:if( vulnerability.parent.mute.status=="MUTED" OR `# Non-muted entities`==0,2,else:1)),
`# Function in use`,
`# Afftected entities`,
`# Exposure to internet`,
`# Exploit published`,
`# Reachable databases`
| sort   status_sort, {vulnerability.risk.score, direction:"descending"}, {`# Function in use`, direction:"descending"},
{`# Exposure to internet`, direction:"descending"}, {`# Exploit published`, direction:"descending"},
{`# Reachable databases`, direction:"descending"}, {`# Afftected entities`,direction:"descending"}, {vulnerability.parent.first_seen,direction:"descending"}
| fieldsRemove status_sort

Query result:

Vulnerabilities on a host

Vulnerabilities on an application

Get the open vulnerabilities affecting a specific application (in this example, www.easytravel.com).

Query steps:

Fetch the latest snapshot of the vulnerabilities per entity.
Filter the data by resolution and mute status.
Filter the data by application name.
Aggregate the data per vulnerability.
List prioritized vulnerabilities with the most relevant information, sorted by various criteria, including risk, status, risk assessments, affected entities count, and the timestamp of when the vulnerabilities were first seen.

Query example:

fetch events
| filter dt.system.bucket=="default_security_events"
| filter event.provider=="Dynatrace"
| filter event.type=="VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level=="ENTITY"
// get latest snapshot
| sort timestamp, direction:"descending"
| summarize {
vulnerability.parent.first_seen=takeFirst(vulnerability.parent.first_seen),
vulnerability.resolution.status=takeFirst(vulnerability.resolution.status),
vulnerability.mute.status=takeFirst(vulnerability.mute.status),
vulnerability.parent.resolution.status=takeFirst(vulnerability.parent.resolution.status),
vulnerability.parent.resolution.change_date=takeFirst(vulnerability.parent.resolution.change_date),
vulnerability.parent.mute.status=takeFirst(vulnerability.parent.mute.status),
vulnerability.parent.risk.score=takeFirst(vulnerability.parent.risk.score),
vulnerability.risk.score=takeFirst(vulnerability.risk.score),
vulnerability.parent.risk.level=takeFirst(vulnerability.parent.risk.level),
vulnerability.risk.level=takeFirst(vulnerability.risk.level),
vulnerability.references.cve=takeFirst(vulnerability.references.cve),
vulnerability.resolution.change_date=takeFirst(vulnerability.resolution.change_date),
vulnerability.mute.change_date=takeFirst(vulnerability.mute.change_date),
vulnerability.title=takeFirst(vulnerability.title),
vulnerability.davis_assessment.exposure_status=takeFirst(vulnerability.davis_assessment.exposure_status),
vulnerability.davis_assessment.exploit_status=takeFirst(vulnerability.davis_assessment.exploit_status),
vulnerability.davis_assessment.vulnerable_function_status=takeFirst(vulnerability.davis_assessment.vulnerable_function_status),
vulnerability.davis_assessment.data_assets_status=takeFirst(vulnerability.davis_assessment.data_assets_status),
affected_entity.name=takeFirst(affected_entity.name),
related_entities.applications.names=takeFirst(related_entities.applications.names),
timestamp=takeFirst(timestamp)
}, by: {vulnerability.display_id, affected_entity.id}
// end of get latest snapshot
| filter in("www.easytravel.com",related_entities.applications.names) // filter by name of the related/affected host
// now summarize on the vulnerability level
| summarize{
vulnerability.parent.first_seen=takeFirst(vulnerability.parent.first_seen),
vulnerability.parent.resolution.status=takeFirst(vulnerability.parent.resolution.status),
vulnerability.parent.resolution.change_date=takeFirst(vulnerability.parent.resolution.change_date),
vulnerability.parent.mute.status=takeFirst(vulnerability.parent.mute.status),
vulnerability.title=takeFirst(vulnerability.title),
vulnerability.references.cve=takeFirst(vulnerability.references.cve),
Critical=countIf(vulnerability.risk.level=="CRITICAL"),
High=countIf(vulnerability.risk.level=="HIGH"),
Medium=countIf(vulnerability.risk.level=="MEDIUM"),
Low=countIf(vulnerability.risk.level=="LOW"),
vulnerability.risk.score=round(takeMax(vulnerability.risk.score),decimals:1),
`# Affected entities`=countIf(vulnerability.mute.status=="NOT_MUTED" and vulnerability.resolution.status=="OPEN"),
`# Non-muted entities`=countIf(vulnerability.mute.status=="NOT_MUTED"),
`# Function in use`=countIf(vulnerability.davis_assessment.vulnerable_function_status=="IN_USE"),
`# Exposure to internet`=countIf(vulnerability.davis_assessment.exposure_status=="PUBLIC_NETWORK"),
`# Exploit published`=countIf(vulnerability.davis_assessment.exploit_status=="AVAILABLE"),
`# Reachable databases`=countIf(vulnerability.davis_assessment.data_assets_status=="REACHABLE")
}, by: {vulnerability.display_id}
| fieldsAdd vulnerability.risk.level=if(Critical>0,"CRITICAL",
                                     else:if(High>0,"HIGH",
                                     else:if(Medium>0,"MEDIUM",
                                     else:if(Low>0,"LOW",
                                     else:"NONE"))))
| fields
vulnerability.display_id,
vulnerability.risk.level,
vulnerability.risk.score,
vulnerability.title,
vulnerability.references.cve,
vulnerability.parent.resolution.status,
vulnerability.parent.mute.status,
`# Affected entities`,
vulnerability.parent.first_seen,
status_sort=if(vulnerability.parent.resolution.status=="RESOLVED",3,else:if( vulnerability.parent.mute.status=="MUTED" OR `# Non-muted entities`==0,2,else:1)),
`# Function in use`,
`# Exposure to internet`,
`# Exploit published`,
`# Reachable databases`
| sort   status_sort, {vulnerability.risk.score, direction:"descending"}, {`# Function in use`, direction:"descending"},
{`# Exposure to internet`, direction:"descending"}, {`# Exploit published`, direction:"descending"},
{`# Reachable databases`, direction:"descending"}, {`# Affected entities`,direction:"descending"}, {vulnerability.parent.first_seen,direction:"descending"}
| fieldsRemove status_sort

Query result:

Vulnerabilities on an application

Top five affected entities by vulnerability count

Get the top five affected entities by the number of open vulnerabilities.

Query steps:

Fetch the latest snapshot of the vulnerabilities per entity.
Filter the data by resolution and mute status.
Aggregate the data by the affected entity.
List the entities sorted by the total count of open vulnerabilities.

Query example:

fetch events
| filter event.provider == "Dynatrace"                    // events generated by Dynatrace
| filter event.type == "VULNERABILITY_STATE_REPORT_EVENT" // historical vulnerability data
| filter event.level == "ENTITY"                          // vulnerability per entity
// get latest snapshot
| sort timestamp, direction:"descending"
| summarize {
affected_entity.type = takeFirst(affected_entity.type),
affected_entity.name = takeFirst(affected_entity.name),
dt.entity.process_group = takeFirst(dt.entity.process_group),
dt.entity.host = takeFirst(dt.entity.host),
dt.entity.kubernetes_node = takeFirst(dt.entity.kubernetes_node),
vulnerability.resolution.status=takeFirst(vulnerability.resolution.status),
vulnerability.mute.status=takeFirst(vulnerability.mute.status),
vulnerability.parent.mute.status=takeFirst(vulnerability.parent.mute.status)
}, by: {affected_entity.id, vulnerability.display_id}
// end of get latest snapshot
| filter vulnerability.resolution.status == "OPEN"
| filter vulnerability.parent.mute.status != "MUTED" AND vulnerability.mute.status != "MUTED"
| summarize {
`Affected entity name` = takeFirst(affected_entity.name),
Type = takeFirst(affected_entity.type),
Vulnerabilities = countDistinct(vulnerability.display_id),
dt.entity.process_group = takeFirst(dt.entity.process_group),
dt.entity.host = takeFirst(dt.entity.host),
dt.entity.kubernetes_node = takeFirst(dt.entity.kubernetes_node)
}, by: {affected_entity.id}
| fieldsRemove affected_entity.id
| sort {Vulnerabilities, direction:"descending"}
| fields dt.source_entity = if(isNotNull(dt.entity.process_group),
                               dt.entity.process_group,
                               else:if(isNotNull(dt.entity.host),dt.entity.host,else:dt.entity.kubernetes_node)),
         Name = `Affected entity name`,
         Type,
         Vulnerabilities
| limit 5

Query result:

Top five affected entities by vulnerability count

Top five process groups with owners

Get the top five process groups by the count of open vulnerabilities, with their respective owners.

Query steps:

Fetch the latest snapshot of the vulnerabilities per entity.
Filter the data by resolution and mute status.
Filter by affected entity type.
Enrich the data with ownership information.
List the entities sorted by vulnerability count.

Query example:

fetch events
| filter event.provider == "Dynatrace"                    // events generated by Dynatrace
| filter event.type == "VULNERABILITY_STATE_REPORT_EVENT" // historical vulnerability data
| filter event.level == "ENTITY"                          // vulnerability per entity
// get latest snapshot
| sort timestamp, direction:"descending"
| summarize {
affected_entity.type = takeFirst(affected_entity.type),
affected_entity.name = takeFirst(affected_entity.name),
dt.entity.process_group = takeFirst(dt.entity.process_group),
dt.entity.host = takeFirst(dt.entity.host),
dt.entity.kubernetes_node = takeFirst(dt.entity.kubernetes_node),
vulnerability.resolution.status=takeFirst(vulnerability.resolution.status),
vulnerability.mute.status=takeFirst(vulnerability.mute.status),
vulnerability.parent.mute.status=takeFirst(vulnerability.parent.mute.status)
}, by: {affected_entity.id, vulnerability.display_id}
// end of get latest snapshot
| filter vulnerability.resolution.status == "OPEN"
| filter vulnerability.parent.mute.status != "MUTED" AND vulnerability.mute.status != "MUTED"
| summarize {
affected_entity.name = takeFirst(affected_entity.name),
affected_entity.type = takeFirst(affected_entity.type),
Vulnerabilities = countDistinct(vulnerability.display_id),
dt.entity.process_group = takeFirst(dt.entity.process_group),
dt.entity.host = takeFirst(dt.entity.host),
dt.entity.kubernetes_node = takeFirst(dt.entity.kubernetes_node)},
by: {affected_entity.id}
| sort {Vulnerabilities, direction:"descending"}
| filter affected_entity.type=="PROCESS_GROUP"
| fields dt.entity.process_group,
         affected_entity.name,
         Vulnerabilities
// add ownership information
| lookup [
fetch dt.entity.process_group
| fieldsAdd tags
| parse toString(tags), "LD ('owner:'|'owner\\\\:') (SPACE)? LD:Team ('\"')"
], sourceField:dt.entity.process_group, lookupField:id, fields:{Team}
| fields
dt.entity.process_group,
affected_entity.name,
`Owner team`=if(isNotNull(Team),Team,else:"-"),
Vulnerabilities
| sort Vulnerabilities, direction:"descending"
// end of add ownership information
| limit 5

Query result:

Top 5 process groups with owner teams

Hosts related to vulnerabilities on a library with owners

Get the hosts that are indirectly related to open vulnerabilities on a specific library (in this example, tomcat), with their respective owners.

Query steps:

Fetch the latest snapshot of the vulnerabilities per entity.
Filter the data by resolution and mute status.
Filter by software component name.
Aggregate the vulnerabilities per related host.
Enrich the data with ownership information.
List the hosts sorted by vulnerability count.

Query example:

fetch events
| filter dt.system.bucket=="default_security_events"
| filter event.provider=="Dynatrace"
| filter event.type=="VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level=="ENTITY"
// get latest snapshot
| sort timestamp, direction:"descending"
| summarize {
related_entities.hosts.count=takeFirst(related_entities.hosts.count),
related_entities.hosts.ids=takeFirst(related_entities.hosts.ids),
affected_entity.vulnerable_component.name=takeFirst(affected_entity.vulnerable_component.name),
vulnerability.resolution.status=takeFirst(vulnerability.resolution.status),
vulnerability.mute.status=takeFirst(vulnerability.mute.status),
vulnerability.parent.mute.status=takeFirst(vulnerability.parent.mute.status)
}, by: {vulnerability.display_id, affected_entity.id}
// end of get latest snapshot
| filter contains(affected_entity.vulnerable_component.name,"tomcat") // filter by the vulnerable component name
| filter vulnerability.resolution.status=="OPEN"
| filter vulnerability.parent.mute.status != "MUTED" AND vulnerability.mute.status != "MUTED"
| filter related_entities.hosts.count>0
| expand entity_id=related_entities.hosts.ids
| summarize Vulnerabilities=countDistinct(vulnerability.display_id), by: {entity_id}
//add ownership information
| lookup [
fetch dt.entity.host
| parse toString(tags), "LD ('owner:'|'owner\\\\:') (SPACE)? LD:Team ('\"')"
| fieldsAdd tags
], sourceField:entity_id, lookupField:id, fields: {Team, entity.name}
// end of add ownership information
| fields
dt.entity.host=entity_id,
Name=entity.name,
`Owner team`=if(isNotNull(Team),Team,else:"-"),
Vulnerabilities
| sort Vulnerabilities, direction:"descending"

Query result:

Hosts related to vulnerabilities on a library with owners

Vulnerable software components of a host with owners

Get the vulnerable components of a specific host (in this example, HOST-4CF0F659B8823D74) with owners.

Query steps:

Fetch the latest snapshot of the vulnerabilities per entity.
Filter the data by resolution and mute status.
Filter by host ID.
Enrich the data with ownership information.
Aggregate data by software component.
Enrich the data with the corresponding file name.
List the components sorted by total affected entities count.

Query example:

fetch events
| filter dt.system.bucket=="default_security_events"
| filter event.provider=="Dynatrace"
| filter event.type=="VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level=="ENTITY"
// get latest snapshot
| sort timestamp, direction:"descending"
| summarize {
affected_entity.vulnerable_component.name=takeFirst(affected_entity.vulnerable_component.name),
affected_entity.vulnerable_component.id=takeFirst(affected_entity.vulnerable_component.id),
affected_entity.vulnerable_functions=takeFirst(affected_entity.vulnerable_functions),
affected_entity.type=takefirst(affected_entity.type),
related_entities.hosts.ids=takeFirst(related_entities.hosts.ids)
}, by: {vulnerability.display_id, affected_entity.id}
// end of get latest snapshot
| filter iAny(in("HOST-4CF0F659B8823D74",related_entities.hosts.ids[])) or affected_entity.id=="HOST-4CF0F659B8823D74" // filter by ID of the related or affected host
//add ownership information
| lookup [
fetch dt.entity.process_group
| fieldsAdd tags
| parse toString(tags), "LD ('owner:'|'owner\\\\:') (SPACE)? LD:Team ('\"')"
], sourceField:affected_entity.id, lookupField:id, fields:{Team}
// end of add ownership info
| expand affected_entity.vulnerable_functions
| summarize{
`# Affected entities`=arraySize(collectDistinct(affected_entity.id)),
affected_entity.vulnerable_functions=collectDistinct(affected_entity.vulnerable_functions),
Teams=arrayRemoveNulls(collectDistinct(Team)),
affected_entity.vulnerable_component.name=takeAny(affected_entity.vulnerable_component.name)
}, by: {affected_entity.vulnerable_component.id, alias:`Component id`}
| filterOut isNull(`Component id`)
// add component information
| lookup [
fetch dt.entity.software_component
| fieldsAdd softwareComponentFileName
], sourceField:`Component id`, lookupField:id, fields:{softwareComponentFileName}
// end of add component information
| fields
dt.entity.software_component=`Component id`,
affected_entity.vulnerable_component.name,
softwareComponentFileName=coalesce(softwareComponentFileName,"-"),
`Owner team`=if(arraySize(Teams)>0,Teams,else:"-"),
`Affected entities`=`# Affected entities`,
`Vulnerable functions`=arraySize(arrayRemoveNulls(affected_entity.vulnerable_functions))
| sort {`Affected entities`, direction:"descending"}, {`Vulnerable functions`, direction:"descending"}

Query result:

Vulnerable software components of a host with owners

Vulnerable functions of a software component

Get the vulnerable functions of a specific software component (in this example, SOFTWARE_COMPONENT-1D466FB7ADEBF92E).

Query steps:

Fetch the latest snapshot of the vulnerabilities per entity.
Filter the data by resolution and mute status.
Filter by software component ID.
Summarize the data per vulnerable function.
List the functions sorted by count of usages.

Query example:

fetch events
| filter dt.system.bucket=="default_security_events"
| filter event.provider=="Dynatrace"
| filter event.type=="VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level=="ENTITY"
// get latest snapshot
| sort timestamp, direction:"descending"
| summarize {
affected_entity.vulnerable_component.name=takeFirst(affected_entity.vulnerable_component.name),
affected_entity.vulnerable_component.id=takeFirst(affected_entity.vulnerable_component.id),
affected_entity.vulnerable_functions=takeFirst(affected_entity.vulnerable_functions),
affected_entity.type=takefirst(affected_entity.type)
}, by: {vulnerability.display_id, affected_entity.id}
// end of get latest snapshot
| filter affected_entity.vulnerable_component.id=="SOFTWARE_COMPONENT-1D466FB7ADEBF92E"  // filter for the software component ID
| filter isNotNull(affected_entity.vulnerable_functions)
| expand affected_entity.vulnerable_functions
| summarize{
affected_entity.vulnerable_component.name=collectDistinct(affected_entity.vulnerable_component.name),
`# Function usages`=countIf(in(affected_entity.vulnerable_functions,affected_entity.vulnerable_functions))
}, by: {affected_entity.vulnerable_functions}
| fields
affected_entity.vulnerable_functions,
`# Function usages`
| sort {`# Function usages`, direction:"descending"}

Query result:

Vulnerable functions of a software component