DQL to pull vulnerabilities for each host

[tijust1]

Hi,

Do we have DQL functionality available to pull vulnerabilities for each host?

Let me know if anyone has done it. I am looking for a DQL query to fetch vulnerabilities for each host along with other details.

Thanks,

Tijust1

[PacoPorro · Accepted solution]

fetch events
| filter event.kind == "SECURITY_EVENT"
| filter event.category == "VULNERABILITY_MANAGEMENT"
| filter event.type == "VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level == "ENTITY"
| fieldsAdd vulnerability.id, vulnerability.external_url, vulnerability.external_id, vulnerability.references.owasp, vulnerability.url, vulnerability.display_id, vulnerability.description, vulnerability.references.cve, vulnerability.title, related_entities.services.ids, vulnerability.parent.davis_assessment.exposure_status, vulnerability.davis_assessment.exploit_status, vulnerability.davis_assessment.exposure_status, affected_entities.vulnerable_components.ids, vulnerability.stack, related_entities.databases.ids, vulnerability.parent.davis_assessment.score, vulnerability.technology, vulnerability.references.cwe, related_entities.hosts.ids
| expand related_entities.hosts.ids
| lookup [
    fetch dt.entity.host
    | fieldsAdd entity.name
  ], sourceField:related_entities.hosts.ids, lookupField:id, prefix:"host."

[tijust1]

@PacoPorro Thanks for quick response. is there way to get in table format as I am getting result like belo

[tijust1]

@PacoPorro my bad.. i got it. Thanks a lot for providing dql.

[tijust1]

@PacoPorro is there way to get details for only one MZ. I mean if there is any filter option to choose certain MZ.

[c_schwarzbauer]

@tijust1 

the above mentioned query will show you a lot of duplicates, as it shows you all the events from all snapshots, whereas you probably only want the most recent information.

for that you'd need a query similar to the one explained here: https://docs.dynatrace.com/docs/platform-modules/application-security/security-data-on-grail/example...

slightly adjusted and adding the host information in the final table could e.g. look like this (changes in bold) :

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),
related_entities.hosts.names=takeFirst(related_entities.hosts.names),
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,
related_entities.hosts.names,
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

HTH,
Chris

[DamianG]

Do we have the option to get the same infomation (vulnerabilities per host) in Dynatrace Managed without DQL?

Thank you.

[c_schwarzbauer]

hi @DamianG,

did you try using the Environment API v2, as described here: https://docs.dynatrace.com/docs/dynatrace-api/environment-api/application-security/vulnerabilities/g... ?

using the 'relatedHostNames' (or similar) for filtering via the 'securityProblemSelector' should be able to address your use case, I think.

HTH, Chris