topic Counting Problems by ManagementZone in DQL

Counting Problems by ManagementZone

PVThach — Sun, 23 Nov 2025 20:12:42 GMT

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

Re: Counting Problems by ManagementZone

t_pawlak — Mon, 24 Nov 2025 09:24:44 GMT

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

Re: Counting Problems by ManagementZone

PVThach — Tue, 25 Nov 2025 14:24:12 GMT

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

| 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.