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.
Query Dynatrace events
Total number of open vulnerabilities
Get the total number of open, non-muted vulnerabilities in your environment.
Query example:
fetch events| filter dt.system.bucket=="default_security_events"AND event.provider=="Dynatrace"AND event.type=="VULNERABILITY_STATE_REPORT_EVENT"AND event.level=="ENTITY"// filter for the latest snapshot per entity| dedup {vulnerability.display_id, affected_entity.id}, sort:{timestamp desc}// filter for open non-muted vulnerabilities| filter vulnerability.resolution.status=="OPEN"AND vulnerability.parent.mute.status!="MUTED"AND vulnerability.mute.status!="MUTED"// count unique vulnerabilities| summarize {`Open vulnerabilities`=countDistinctExact(vulnerability.display_id)}
Query result:
Total number of critical open vulnerabilities
Get the total number of critical open, non-muted vulnerabilities in your environment.
Query example:
fetch events| filter dt.system.bucket=="default_security_events"AND event.provider=="Dynatrace"AND event.type=="VULNERABILITY_STATE_REPORT_EVENT"AND event.level=="ENTITY"// filter for the latest snapshot per entity| dedup {vulnerability.display_id, affected_entity.id}, sort:{timestamp desc}// filter for critical open non-muted vulnerabilities| filter vulnerability.resolution.status=="OPEN"AND vulnerability.parent.mute.status!="MUTED"AND vulnerability.mute.status!="MUTED"AND vulnerability.risk.level=="CRITICAL"// count unique vulnerabilities| summarize {`Critical open vulnerabilities`=countDistinctExact(vulnerability.display_id)}
Query result:
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 example:
fetch events| filter dt.system.bucket=="default_security_events"AND event.provider=="Dynatrace"AND event.type=="VULNERABILITY_STATE_REPORT_EVENT"AND event.level=="ENTITY"// filter for the latest snapshot per entity| dedup {vulnerability.display_id, affected_entity.id}, sort:{timestamp desc}// filter for open non-muted vulnerabilities in a specific management zone| filter vulnerability.resolution.status == "OPEN"AND vulnerability.parent.mute.status != "MUTED"AND vulnerability.mute.status != "MUTED"AND in("AppSec: Unguard", affected_entity.management_zones.names)// count unique vulnerabilities| summarize {`Open vulnerabilities (unguard)`=countDistinctExact(vulnerability.display_id)}
Query result:
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 example:
fetch events| filter dt.system.bucket=="default_security_events"AND event.provider=="Dynatrace"AND event.type=="VULNERABILITY_STATE_REPORT_EVENT"AND event.level=="ENTITY"// filter for the latest snapshot per entity| dedup {vulnerability.display_id, affected_entity.id}, sort:{timestamp desc}// filter for open non-muted vulnerabilities with public internet exposure| filter vulnerability.resolution.status == "OPEN"AND vulnerability.parent.mute.status != "MUTED"AND vulnerability.mute.status != "MUTED"AND vulnerability.davis_assessment.exposure_status=="PUBLIC_NETWORK"// count unique vulnerabilities| summarize {`With internet exposure`=countDistinctExact(vulnerability.display_id)}
Query result:
Total number of affected entities
Get the total number of affected entities in your environment.
Query example:
fetch events| filter dt.system.bucket=="default_security_events"AND event.provider=="Dynatrace"AND event.type=="VULNERABILITY_STATE_REPORT_EVENT"AND event.level=="ENTITY"// filter for the latest snapshot per entity| dedup {vulnerability.display_id, affected_entity.id}, sort:{timestamp desc}// filter for open non-muted vulnerabilities| filter vulnerability.resolution.status == "OPEN"AND vulnerability.parent.mute.status != "MUTED"AND vulnerability.mute.status != "MUTED"// count unique entities| summarize {`Affected entities`=countDistinctExact(affected_entity.id)}
Query result:
Total number of affected process groups
Get the total number of affected process groups in your environment.
Query example:
fetch events| filter dt.system.bucket=="default_security_events"AND event.provider=="Dynatrace"AND event.type=="VULNERABILITY_STATE_REPORT_EVENT"AND event.level=="ENTITY"// filter for the latest snapshot per entity| dedup {vulnerability.display_id, affected_entity.id}, sort:{timestamp desc}// filter for open non-muted vulnerabilities detected in running processes| filter vulnerability.resolution.status == "OPEN"AND vulnerability.parent.mute.status != "MUTED"AND vulnerability.mute.status != "MUTED"AND affected_entity.type=="PROCESS_GROUP"// count unique entities| summarize {`Affected process groups`=countDistinctExact(affected_entity.id)}
Query result:
Total number of affected entities over time
Get the total number of affected, non-muted entities over time (in three-hour buckets).
Query example:
fetch events| filter dt.system.bucket=="default_security_events"AND event.provider=="Dynatrace"AND event.type=="VULNERABILITY_STATE_REPORT_EVENT"AND event.level=="ENTITY"// filter for open non-muted vulnerabilitiesAND vulnerability.resolution.status == "OPEN"AND vulnerability.parent.mute.status != "MUTED"AND vulnerability.mute.status != "MUTED"// count unique entities for each timestamp bucket of 3h| sort timestamp desc| summarize {entities=countDistinctExact(affected_entity.id)}, by: {timestamp=bin(timestamp, 3h)}
Query result:
Total number of hosts related to vulnerabilities
Get the total number of hosts that are indirectly affected by open vulnerabilities in your environment.
Query example:
fetch events| filter dt.system.bucket=="default_security_events"AND event.provider=="Dynatrace"AND event.type=="VULNERABILITY_STATE_REPORT_EVENT"AND event.level=="ENTITY"// filter for the latest snapshot per entity| dedup {vulnerability.display_id, affected_entity.id}, sort:{timestamp desc}// filter for open non-muted vulnerabilities|filter vulnerability.resolution.status == "OPEN"AND vulnerability.parent.mute.status != "MUTED"AND vulnerability.mute.status != "MUTED"// count hosts| summarize {`Related hosts`=arraySize(collectDistinct(related_entities.hosts.ids, expand:true))}
Query result:
Open vulnerabilities by risk level
Get a count of open vulnerabilities split by risk levels.
Query example:
fetch events| filter dt.system.bucket=="default_security_events"AND event.provider=="Dynatrace"AND event.type=="VULNERABILITY_STATE_REPORT_EVENT"AND event.level=="ENTITY"// filter for the latest snapshot per entity| dedup {vulnerability.display_id, affected_entity.id}, sort:{timestamp desc}// filter for open non-muted vulnerabilities| filter vulnerability.resolution.status == "OPEN"AND vulnerability.parent.mute.status != "MUTED"AND vulnerability.mute.status != "MUTED"// summarize score per vulnerability| summarize {vulnerability.risk.score=takeMax(vulnerability.risk.score)}, by: {vulnerability.display_id}// map the risk level| fieldsAdd vulnerability.risk.level=if(vulnerability.risk.score>=9,"CRITICAL",else:if(vulnerability.risk.score>=7,"HIGH",else:if(vulnerability.risk.score>=4,"MEDIUM",else:if(vulnerability.risk.score>=0.1,"LOW",else:"NONE"))))// count vulnerabilities per risk level| summarize { Vulnerabilities=count(), maxScore=takeMax(vulnerability.risk.score) }, by:{vulnerability.risk.level}| sort maxScore, direction:"descending"
Query result:
Open vulnerabilities by type
Get a count of open vulnerabilities split by type.
Query example:
fetch events| filter dt.system.bucket=="default_security_events"AND event.provider=="Dynatrace"AND event.type=="VULNERABILITY_STATE_REPORT_EVENT"AND event.level=="ENTITY"// filter for the latest snapshot per entity| dedup {vulnerability.display_id, affected_entity.id}, sort:{timestamp desc}// filter for open non-muted vulnerabilities| filter vulnerability.resolution.status == "OPEN"AND vulnerability.parent.mute.status != "MUTED"AND vulnerability.mute.status != "MUTED"// count vulnerabilities per type| summarize { Vulnerabilities=countDistinctExact(vulnerability.display_id) }, by:{vulnerability.type}| sort Vulnerabilities, direction:"descending"| limit 10
Query result:
Open vulnerabilities over time
Get the open vulnerability count over time, in three-hour buckets.
Query example:
fetch events| filter dt.system.bucket=="default_security_events"AND event.provider=="Dynatrace"AND event.type=="VULNERABILITY_STATE_REPORT_EVENT"AND event.level=="ENTITY"// filter for open non-muted vulnerabilitiesAND vulnerability.resolution.status == "OPEN"AND vulnerability.parent.mute.status != "MUTED"AND vulnerability.mute.status != "MUTED"| sort timestamp desc| summarize {Open=countDistinctExact(vulnerability.display_id)}, by: {timestamp=bin(timestamp,3h)}
Query result:
Vulnerabilities on a library
Get the open vulnerabilities on a specific library (in this example, log4j
).
Query example:
fetch events| filter dt.system.bucket=="default_security_events"AND event.provider=="Dynatrace"AND event.type=="VULNERABILITY_STATE_REPORT_EVENT"AND event.level=="ENTITY"// filter for the latest snapshot per entity| dedup {vulnerability.display_id, affected_entity.id}, sort:{timestamp desc}// filter for open non-muted vulnerabilities| filter vulnerability.resolution.status == "OPEN"AND vulnerability.parent.mute.status != "MUTED"AND vulnerability.mute.status != "MUTED"// filter by the vulnerable library/component nameAND contains(affected_entity.vulnerable_component.name,"log4j",caseSensitive:false)// now summarize on the vulnerability level| summarize{vulnerability.risk.score=round(takeMax(vulnerability.risk.score),decimals:1),vulnerability.title=takeFirst(vulnerability.title),vulnerability.references.cve=takeFirst(vulnerability.references.cve),last_detected=coalesce(takeMax(vulnerability.resolution.change_date),takeMax(vulnerability.parent.first_seen)),affected_entities=countDistinctExact(affected_entity.id),vulnerable_function_in_use=if(in("IN_USE",collectArray(vulnerability.davis_assessment.vulnerable_function_status)),true, else:false),public_internet_exposure=if(in("PUBLIC_NETWORK",collectArray(vulnerability.davis_assessment.exposure_status)),true,else:false),public_exploit_available=if(in("AVAILABLE",collectArray(vulnerability.davis_assessment.exploit_status)),true,else:false),data_assets_within_reach=if(in("REACHABLE",collectArray(vulnerability.davis_assessment.data_assets_status)),true,else:false)}, by: {vulnerability.display_id}// map the risk level| fieldsAdd vulnerability.risk.level=if(vulnerability.risk.score>=9,"CRITICAL",else:if(vulnerability.risk.score>=7,"HIGH",else:if(vulnerability.risk.score>=4,"MEDIUM",else:if(vulnerability.risk.score>=0.1,"LOW",else:"NONE"))))| sort {vulnerability.risk.score, direction:"descending"}, {affected_entities, direction:"descending"}
Query result:
Vulnerabilities on a host
Get the open vulnerabilities directly or indirectly affecting a specific host (in this example, i-05f1305a50721e04d
).
Query example:
fetch events| filter dt.system.bucket=="default_security_events"AND event.provider=="Dynatrace"AND event.type=="VULNERABILITY_STATE_REPORT_EVENT"AND event.level=="ENTITY"// filter for the latest snapshot per entity| dedup {vulnerability.display_id, affected_entity.id}, sort:{timestamp desc}// filter for open non-muted vulnerabilities| filter vulnerability.resolution.status == "OPEN"AND vulnerability.parent.mute.status != "MUTED"AND vulnerability.mute.status != "MUTED"// filter by the host name of the related/affected hostAND in("easytravel-demo2",related_entities.hosts.names) OR affected_entity.name=="easytravel-demo2"// now summarize on the vulnerability level| summarize{vulnerability.risk.score=round(takeMax(vulnerability.risk.score),decimals:1),vulnerability.title=takeFirst(vulnerability.title),vulnerability.references.cve=takeFirst(vulnerability.references.cve),last_detected=coalesce(takeMax(vulnerability.resolution.change_date),takeMax(vulnerability.parent.first_seen)),affected_entities=countDistinctExact(affected_entity.id),vulnerable_function_in_use=if(in("IN_USE",collectArray(vulnerability.davis_assessment.vulnerable_function_status)),true, else:false),public_internet_exposure=if(in("PUBLIC_NETWORK",collectArray(vulnerability.davis_assessment.exposure_status)),true,else:false),public_exploit_available=if(in("AVAILABLE",collectArray(vulnerability.davis_assessment.exploit_status)),true,else:false),data_assets_within_reach=if(in("REACHABLE",collectArray(vulnerability.davis_assessment.data_assets_status)),true,else:false)}, by: {vulnerability.display_id}// map the risk level| fieldsAdd vulnerability.risk.level=if(vulnerability.risk.score>=9,"CRITICAL",else:if(vulnerability.risk.score>=7,"HIGH",else:if(vulnerability.risk.score>=4,"MEDIUM",else:if(vulnerability.risk.score>=0.1,"LOW",else:"NONE"))))| sort {vulnerability.risk.score, direction:"descending"}, {affected_entities, direction:"descending"}
Query result:
Vulnerabilities on an application
Get the open vulnerabilities affecting a specific application (in this example, www.easytravel.com
).
Query example:
| dedup {vulnerability.display_id, affected_entity.id}, sort:{timestamp desc}// filter for open non-muted vulnerabilities| filter vulnerability.resolution.status == "OPEN"AND vulnerability.parent.mute.status != "MUTED"AND vulnerability.mute.status != "MUTED"// filter by name of the related applicationsAND in("www.easytravel.com",related_entities.applications.names)// now summarize on the vulnerability level| summarize{vulnerability.risk.score=round(takeMax(vulnerability.risk.score),decimals:1),vulnerability.title=takeFirst(vulnerability.title),vulnerability.references.cve=takeFirst(vulnerability.references.cve),last_detected=coalesce(takeMax(vulnerability.resolution.change_date),takeMax(vulnerability.parent.first_seen)),affected_entities=countDistinctExact(affected_entity.id),vulnerable_function_in_use=if(in("IN_USE",collectArray(vulnerability.davis_assessment.vulnerable_function_status)),true, else:false),public_internet_exposure=if(in("PUBLIC_NETWORK",collectArray(vulnerability.davis_assessment.exposure_status)),true,else:false),public_exploit_available=if(in("AVAILABLE",collectArray(vulnerability.davis_assessment.exploit_status)),true,else:false),data_assets_within_reach=if(in("REACHABLE",collectArray(vulnerability.davis_assessment.data_assets_status)),true,else:false)}, by: {vulnerability.display_id}// map the risk level| fieldsAdd vulnerability.risk.level=if(vulnerability.risk.score>=9,"CRITICAL",else:if(vulnerability.risk.score>=7,"HIGH",else:if(vulnerability.risk.score>=4,"MEDIUM",else:if(vulnerability.risk.score>=0.1,"LOW",else:"NONE"))))| sort {vulnerability.risk.score, direction:"descending"}, {affected_entities, direction:"descending"}
Query result:
Top 10 affected entities by vulnerability count
Get the top 10 affected entities by the number of open vulnerabilities.
Query example:
fetch events| filter dt.system.bucket=="default_security_events"AND event.provider=="Dynatrace"AND event.type=="VULNERABILITY_STATE_REPORT_EVENT"AND event.level=="ENTITY"// filter for the latest snapshot per entity| dedup {vulnerability.display_id, affected_entity.id}, sort:{timestamp desc}// filter for open non-muted vulnerabilities| filter vulnerability.resolution.status == "OPEN"AND vulnerability.parent.mute.status != "MUTED"AND vulnerability.mute.status != "MUTED"| summarize {`Affected entity name` = takeFirst(affected_entity.name),Type = takeFirst(affected_entity.type),Vulnerabilities = countDistinctExact(vulnerability.display_id)}, by: {dt.source_entity=affected_entity.id}| sort {Vulnerabilities, direction:"descending"}| limit 10
Query result:
Top 10 process groups with owners
Get the top five process groups by the count of open vulnerabilities, with their respective owners.
Query example:
fetch events| filter dt.system.bucket=="default_security_events"AND event.provider=="Dynatrace"AND event.type=="VULNERABILITY_STATE_REPORT_EVENT"AND event.level=="ENTITY"// filter for the latest snapshot per entity| dedup {vulnerability.display_id, affected_entity.id}, sort:{timestamp desc}// filter for open non-muted vulnerabilities| filter vulnerability.resolution.status == "OPEN"AND vulnerability.parent.mute.status != "MUTED"AND vulnerability.mute.status != "MUTED"AND affected_entity.type=="PROCESS_GROUP"// summarize per process group| summarize {`Affected entity name` = takeFirst(affected_entity.name),Type = takeFirst(affected_entity.type),Vulnerabilities = countDistinctExact(vulnerability.display_id)}, by: {dt.source_entity=affected_entity.id}| sort {Vulnerabilities, direction:"descending"}| limit 10// add ownership information| lookup [fetch dt.entity.process_group| parse toString(tags), "LD ('owner:'|'owner\\\\:') (SPACE)? LD:Team ('\"')"| fields id, Team=coalesce(Team, "-")], sourceField:dt.source_entity, lookupField:id, fields:{Team}| sort Vulnerabilities, direction:"descending"
Query result:
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 example:
fetch events| filter dt.system.bucket=="default_security_events"AND event.provider=="Dynatrace"AND event.type=="VULNERABILITY_STATE_REPORT_EVENT"AND event.level=="ENTITY"// filter for the latest snapshot per entity| dedup {vulnerability.display_id, affected_entity.id}, sort:{timestamp desc}// filter for open non-muted vulnerabilities| filter vulnerability.resolution.status == "OPEN"AND vulnerability.parent.mute.status != "MUTED"AND vulnerability.mute.status != "MUTED"AND related_entities.hosts.count > 0// filter by the vulnerable component nameAND contains(affected_entity.vulnerable_component.name,"tomcat")| expand entity_id=related_entities.hosts.ids| summarize Vulnerabilities=countDistinctExact(vulnerability.display_id), by: {entity_id}//add ownership information| lookup [fetch dt.entity.host| parse toString(tags), "LD ('owner:'|'owner\\\\:') (SPACE)? LD:Team ('\"')"| fields id, Host=entity.name, Team=coalesce(Team, "-")], sourceField:entity_id, lookupField:id, fields: {Host,Team}| sort Vulnerabilities, direction:"descending"
Query result:
Vulnerable software components of a host with owners
Get the vulnerable components of a specific host (in this example, HOST-4CF0F659B8823D74
) with owners.
Query example:
fetch events| filter dt.system.bucket=="default_security_events"AND event.provider=="Dynatrace"AND event.type=="VULNERABILITY_STATE_REPORT_EVENT"AND event.level=="ENTITY"// filter for the latest snapshot per entity| dedup {vulnerability.display_id, affected_entity.id}, sort:{timestamp desc}// filter for open non-muted vulnerabilities| filter vulnerability.resolution.status == "OPEN"AND vulnerability.parent.mute.status != "MUTED"AND vulnerability.mute.status != "MUTED"// filter by ID of the related or affected hostAND in("HOST-DBF63A01C27E4B50",related_entities.hosts.ids) or affected_entity.id=="HOST-DBF63A01C27E4B50"| summarize{entities=countDistinctExact(affected_entity.id),vulnerable_functions=arraySize(collectDistinct(affected_entity.vulnerable_functions, expand:true)),vulnerable_component.name=takeAny(affected_entity.vulnerable_component.name)}, by: {dt.entity.software_component=affected_entity.vulnerable_component.id}| filterOut isNull(dt.entity.software_component)// add component information| lookup [fetch dt.entity.software_component| fieldsAdd softwareComponentFileName], sourceField:dt.entity.software_component, lookupField:id, fields:{softwareComponentFileName}| fields dt.entity.software_component, vulnerable_component.name, softwareComponentFileName, entities, vulnerable_functions| sort {entities, direction:"descending"}, {vulnerable_functions, direction:"descending"}
Query result:
Vulnerable functions of a software component
Get the vulnerable functions of a specific software component (in this example, SOFTWARE_COMPONENT-1D466FB7ADEBF92E
).
Query example:
fetch events| filter dt.system.bucket=="default_security_events"AND event.provider=="Dynatrace"AND event.type=="VULNERABILITY_STATE_REPORT_EVENT"AND event.level=="ENTITY"// filter for the latest snapshot per entity| dedup {vulnerability.display_id, affected_entity.id}, sort:{timestamp desc}// filter for open non-muted vulnerabilities| filter vulnerability.resolution.status == "OPEN"AND vulnerability.parent.mute.status != "MUTED"AND vulnerability.mute.status != "MUTED"// filter for the software component IDAND affected_entity.vulnerable_component.id=="SOFTWARE_COMPONENT-1D466FB7ADEBF92E"| expand vulnerable_function=affected_entity.vulnerable_functions| filter isNotNull(vulnerable_function)| summarize{Usages=countIf(in(vulnerable_function,affected_entity.vulnerable_functions))}, by: {vulnerable_function}| sort {Usages, direction:"descending"}
Query result:
Query ingested events
Total number of critical vulnerability findings
Get the total number of critical vulnerability findings ingested into Dynatrace.
Query example:
fetch events// data access| filter dt.system.bucket == "default_security_custom_events"AND event.type == "VULNERABILITY_FINDING_EVENT"AND isNotNull(vulnerable_component.name)| dedup {affected_entity.id, vulnerability.external_id, vulnerable_component.name}, sort: {timestamp desc}// aggregation and custom filtering| filter vulnerability.risk.level=="CRITICAL"| summarize {Vulnerabilities=countDistinctExact(vulnerability.external_id)}
Query result:
Total number of vulnerable container images
Get the total number of container images containing vulnerability findings ingested into Dynatrace.
Query example:
fetch events// data access| filter dt.system.bucket == "default_security_custom_events"AND event.type == "VULNERABILITY_FINDING_EVENT"AND affected_entity.type=="CONTAINER_IMAGE"AND isNotNull(vulnerable_component.name)| dedup {affected_entity.id, vulnerability.external_id, vulnerable_component.name,container_image.registry, container_image.repository, container_image.tags}, sort: {timestamp desc}// aggregation and custom filtering| summarize {containerImages=countDistinctExact(container_image.digest)}
Query result:
Total number of vulnerable components
Get the total number of vulnerable components in the container images containing vulnerability findings ingested into Dynatrace.
Query example:
fetch events// data access| filter dt.system.bucket == "default_security_custom_events"AND event.type == "VULNERABILITY_FINDING_EVENT"AND isNotNull(vulnerable_component.name)| dedup {affected_entity.id, vulnerability.external_id, vulnerable_component.name}, sort: {timestamp desc}// aggregation and custom filtering| summarize {components=countDistinctExact(vulnerable_component.name)}
Query result:
Most recent vulnerability findings
Get the most recent vulnerability findings ingested into Dynatrace.
Query example:
fetch events// data access| filter dt.system.bucket == "default_security_custom_events"AND event.type == "VULNERABILITY_FINDING_EVENT"AND isNotNull(vulnerable_component.name)| dedup {affected_entity.id, vulnerability.external_id, vulnerable_component.name}, sort: {timestamp desc}// aggregation and custom filtering| sort timestamp desc
Query result:
Number of scanned container images
Get the total number of ingested container images that have been scanned.
Query example:
fetch events| filter dt.system.bucket == "default_security_custom_events"| filter event.kind == "SECURITY_EVENT"AND object.type == "CONTAINER_IMAGE" // includes both SCAN_EVENTS and VULNERABILITY_FINDINGS without scan events| dedup {container_image.digest}, sort: {timestamp desc}| summarize {containerImages=count()}
Query result:
Number of container image scan events
Get the total number of scan events from ingested container images.
Query example:
fetch events| filter dt.system.bucket == "default_security_custom_events"| filter event.kind == "SECURITY_EVENT"AND event.type == "VULNERABILITY_SCAN"AND object.type == "CONTAINER_IMAGE"| summarize {scanEvents=count()}
Query result: