This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
FAQ
DQL
Questions about Dynatrace Query Language
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Counting Problems by ManagementZone

Go to solution
PVThach
Frequent Guest

‎23 Nov 2025 08:12 PM

I am able to get total Hosts, Services, Process Group, Custom Device, etcs by ManagementZone (MZ).
I am stuck trying to get total number problems associated by MZ (affected_entity_ids, and root_cause_entity_id).

Here is what I have:

fetch dt.entity.service
| expand managementZones
| summarize by:{managementZones}, service = if( isNull(countDistinct(id) ), 0, else: countDistinct(id))

 

| append [ fetch dt.entity.host | expand managementZones | filterOut isNull(managementZones) | summarize by:{managementZones}, Host = if( isNull(countDistinct(id) ), 0, else: countDistinct(id)) ]
| append [ fetch dt.entity.process_group | expand managementZones | filterOut isNull(managementZones) | summarize by:{managementZones}, ProcessGroup = if( isNull(countDistinct(id) ), 0, else: countDistinct(id)) ]
| append [ fetch dt.entity.application | expand managementZones | filterOut isNull(managementZones) | summarize by:{managementZones}, Application = if( isNull(countDistinct(id) ), 0, else: countDistinct(id)) ]
| append [ fetch dt.entity.aws_lambda_function | expand managementZones | filterOut isNull(managementZones) | summarize by:{managementZones}, AWS_lambda_function = if( isNull(countDistinct(id) ), 0, else: countDistinct(id)) ]
| append [ fetch dt.entity.aws_credentials | expand managementZones | filterOut isNull(managementZones) | summarize by:{managementZones}, AWS_credential = if( isNull(countDistinct(id) ), 0, else: countDistinct(id)) ]
| append [ fetch dt.entity.http_check | expand managementZones | filterOut isNull(managementZones) | summarize by:{managementZones}, http_check = if( isNull(countDistinct(id) ), 0, else: countDistinct(id)) ]
| append [ fetch dt.entity.custom_device | expand managementZones | filterOut isNull(managementZones) | summarize by:{managementZones}, CustomDevice = if( isNull(countDistinct(id) ), 0, else: countDistinct(id)) ]
| append [ fetch dt.entity.custom_device_group | expand managementZones | filterOut isNull(managementZones) | summarize by:{managementZones}, CustomDeviceGroup = if( isNull(countDistinct(id) ), 0, else: countDistinct(id)) ]
| append [ fetch dt.entity.relational_database_service | expand managementZones | filterOut isNull(managementZones) | summarize by:{managementZones}, relational_database = if( isNull(countDistinct(id) ), 0, else: countDistinct(id)) ]
| append [ fetch dt.entity.kubernetes_service | expand managementZones | filterOut isNull(managementZones) | summarize by:{managementZones}, kubernetes_service = if( isNull(countDistinct(id) ), 0, else: countDistinct(id)) ]

// This is where I am having issue: How do I count problems (affected_entity_ids, and root_cause_entity_id) by management zone.
//
//| append [ fetch dt.davis.problems | expand affected_entity_ids | expand root_cause_entity_id | summarize by:{display_id}, AFService = countDistinct(display_id) ]
//
// Perhaps append then lookup with sourceField:affected_entity_ids and root_cause_entity_id ?

| summarize by:{managementZones},
Hosts = if ( isNull(max(Host)), 0, else: max(Host) ),
Services = if ( isNull(max(service)), 0, else: max(service) ),
ProcessGrp = if ( isNull(max(ProcessGroup)), 0, else: max(ProcessGroup) ),
CustomDev = if ( isNull(max(CustomDevice)), 0, else: max(CustomDevice) ),
CustomDevGrp = if ( isNull(max(CustomDeviceGroup)), 0, else: max(CustomDeviceGroup) ),
Applications = if ( isNull(max(Application)), 0, else: max(Application) ),
AWS_lambda = if ( isNull(max(AWS_lambda_function)), 0, else: max(AWS_lambda_function) ),
AWS_credentials = if ( isNull(max(AWS_credential)), 0, else: max(AWS_credential) ),
k8s_services = if ( isNull(max(kubernetes_service)), 0, else: max(kubernetes_service) ),
RDB = if ( isNull(max(relational_database)), 0, else: max(relational_database) ),
http_checks = if ( isNull(max(http_check)), 0, else: max(http_check) )

| filterOut isNull(managementZones)
| sort upper(managementZones) asc

 

Labels:
0 Kudos
Reply
2 REPLIES 2

Go to solution
t_pawlak
Champion

‎24 Nov 2025 09:24 AM

Hi,
try this:

fetch dt.entity.service
| expand managementZones
| summarize by:{managementZones}, service = if( isNull(countDistinct(id) ), 0, else: countDistinct(id))

| append [
    fetch dt.entity.host
    | expand managementZones
    | filterOut isNull(managementZones)
    | summarize by:{managementZones}, Host = if( isNull(countDistinct(id) ), 0, else: countDistinct(id))
]
| append [
    fetch dt.entity.process_group
    | expand managementZones
    | filterOut isNull(managementZones)
    | summarize by:{managementZones}, ProcessGroup = if( isNull(countDistinct(id) ), 0, else: countDistinct(id))
]
| append [
    fetch dt.entity.application
    | expand managementZones
    | filterOut isNull(managementZones)
    | summarize by:{managementZones}, Application = if( isNull(countDistinct(id) ), 0, else: countDistinct(id))
]
| append [
    fetch dt.entity.aws_lambda_function
    | expand managementZones
    | filterOut isNull(managementZones)
    | summarize by:{managementZones}, AWS_lambda_function = if( isNull(countDistinct(id) ), 0, else: countDistinct(id))
]
| append [
    fetch dt.entity.aws_credentials
    | expand managementZones
    | filterOut isNull(managementZones)
    | summarize by:{managementZones}, AWS_credential = if( isNull(countDistinct(id) ), 0, else: countDistinct(id))
]
| append [
    fetch dt.entity.http_check
    | expand managementZones
    | filterOut isNull(managementZones)
    | summarize by:{managementZones}, http_check = if( isNull(countDistinct(id) ), 0, else: countDistinct(id))
]
| append [
    fetch dt.entity.custom_device
    | expand managementZones
    | filterOut isNull(managementZones)
    | summarize by:{managementZones}, CustomDevice = if( isNull(countDistinct(id) ), 0, else: countDistinct(id))
]
| append [
    fetch dt.entity.custom_device_group
    | expand managementZones
    | filterOut isNull(managementZones)
    | summarize by:{managementZones}, CustomDeviceGroup = if( isNull(countDistinct(id) ), 0, else: countDistinct(id))
]
| append [
    fetch dt.entity.relational_database_service
    | expand managementZones
    | filterOut isNull(managementZones)
    | summarize by:{managementZones}, relational_database = if( isNull(countDistinct(id) ), 0, else: countDistinct(id))
]
| append [
    fetch dt.entity.kubernetes_service
    | expand managementZones
    | filterOut isNull(managementZones)
    | summarize by:{managementZones}, kubernetes_service = if( isNull(countDistinct(id) ), 0, else: countDistinct(id))
]

//
// PROBLEMY – wg affected_entity_ids
//
| append [
    fetch dt.davis.problems
    | expand affected_entity_ids
    // podpinamy encje (tu: SERVICE) żeby z nich wyciągnąć managementZones
    | lookup sourceField:affected_entity_ids, lookupField:id, [
        fetch dt.entity.service
        | expand managementZones
        | filterOut isNull(managementZones)
    ]
    // lookup.managementZones -> normalne pole managementZones
    | expand `lookup.managementZones`, alias:managementZones
    | summarize by:{managementZones}, Problems_affected = countDistinct(display_id)
]

//
// PROBLEMY – wg root_cause_entity_id
//
| append [
    fetch dt.davis.problems
    | filter isNotNull(root_cause_entity_id) and root_cause_entity_id != ""
    | lookup sourceField:root_cause_entity_id, lookupField:id, [
        fetch dt.entity.service
        | expand managementZones
        | filterOut isNull(managementZones)
    ]
    | expand `lookup.managementZones`, alias:managementZones
    | summarize by:{managementZones}, Problems_root_cause = countDistinct(display_id)
]

| summarize by:{managementZones},
    Hosts              = if ( isNull(max(Host)), 0, else: max(Host) ),
    Services           = if ( isNull(max(service)), 0, else: max(service) ),
    ProcessGrp         = if ( isNull(max(ProcessGroup)), 0, else: max(ProcessGroup) ),
    CustomDev          = if ( isNull(max(CustomDevice)), 0, else: max(CustomDevice) ),
    CustomDevGrp       = if ( isNull(max(CustomDeviceGroup)), 0, else: max(CustomDeviceGroup) ),
    Applications       = if ( isNull(max(Application)), 0, else: max(Application) ),
    AWS_lambda         = if ( isNull(max(AWS_lambda_function)), 0, else: max(AWS_lambda_function) ),
    AWS_credentials    = if ( isNull(max(AWS_credential)), 0, else: max(AWS_credential) ),
    k8s_services       = if ( isNull(max(kubernetes_service)), 0, else: max(kubernetes_service) ),
    RDB                = if ( isNull(max(relational_database)), 0, else: max(relational_database) ),
    http_checks        = if ( isNull(max(http_check)), 0, else: max(http_check) ),
    Problems_affected  = if ( isNull(max(Problems_affected)), 0, else: max(Problems_affected) ),
    Problems_root_cause= if ( isNull(max(Problems_root_cause)), 0, else: max(Problems_root_cause) )

| filterOut isNull(managementZones)
| sort upper(managementZones) asc

 

t_pawlak_0-1763976208961.png

 

Reply

Go to solution
PVThach
Frequent Guest
In response to t_pawlak

‎25 Nov 2025 02:24 PM

Awesome.  Thank you.  The solution did give me the answer I was looking for.
I had made a minor change because of IDE warning:

    | expand `lookup.managementZones`, alias:managementZones

 to

    | fieldsAdd lookup.managementZones, alias:managementZones

 
The IDE warning:
Applying the expand command with an alias on an expression that's not of type array has the same effect as a `fieldsAdd` command. You can replace it with this.

0 Kudos
Reply
Ask a question

Featured Posts