cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Filter by Management Zone using DQL

badgerfifteen
Contributor

I have a widget on the new Dynatrace dashboard which displays the disk usage of servers. In our estate, we have filtered out servers through to their management zones. In the old dashboard format, you could filter servers by the management zone easily. However, in the newest one, there isn't such an option available so the next best thing is filtering through DQL. However, I cannot find such a command to do this. Any ideas?

13 REPLIES 13

cesarsaravia
Dynatrace Pro
Dynatrace Pro

Hi @badgerfifteen 

On the dashboard 3rd Gen, you can use variable as filters. And the DQL you can use could be this:

Variable:

fetch dt.entity.host
| fields managementZones

Dashboard:

timeseries usage=avg(dt.host.disk.used), by:{dt.entity.host}
| lookup [ 
      fetch dt.entity.host
      | expand managementZones
    ], sourceField:dt.entity.host, lookupField:id, prefix:"mzone."
| filter mzone.managementZones == $VARIABLE_NAME

 Example:

cesarsaravia_0-1702923128967.png

cesarsaravia_1-1702923475834.png

 

 

-César S. -

How do you use the query you've drafted above but for process group filtering by management zone? Below is the DQL query I have without filtering by management zone but would like the variable that I've created based on your above example to help filter out the process groups on each host in a specific mz:

Variable:

fetch dt.entity.process_group
| fields managementZones
| sort managementZones asc


DQL Query:

timeseries cpuUsage = avg(dt.process.cpu.usage), by:{host.name, dt.entity.process_group} 
| fieldsAdd pgname = lookup([fetch dt.entity.process_group], lookupField:id, sourceField:dt.entity.process_group)[entity.name]
| sort arrayAvg(cpuUsage) desc
| limit 5




Hey @tonicbenn2023 
You still can use your DQL for Mzone variables, but the DQL you should use for this scenario is:

timeseries cpuUsage = avg(dt.process.cpu.usage), by:{host.name, dt.entity.process_group} 
| lookup [fetch dt.entity.process_group], lookupField:id, sourceField:dt.entity.process_group,prefix:"lookup.pg" //[entity.name]
| fieldsAdd lookup.pgentity.name,mzone= lookup.pgmanagementZones
| expand mzone
| filter toString(mzone)==$VARIABLE_MZONE
| sort arrayAvg(cpuUsage) desc
| limit 5

Result:

cesarsaravia_0-1711735381600.png

Regards,

-César S. -

Amazing! This worked, thank you :-)! 

RPbiaggio
Helper

@cesarsaravia Can I do something similar to filter my open issues by management zone?

Hi @RPbiaggio 
Here you have a list of dashboards created for GEN3
GitHub - TechShady/Dynatrace-Dashboards-Gen3: This repo provides Business Grade Dashboards for Dynat...
Also, here you have a Zip with the DQL for the problem analysis. You should have to modify it in order to get the MZone filter.

cesarsaravia_0-1708725580983.png

 

-César S. -

This repo is not working

 

RPbiaggio
Helper

@cesarsaravia Hi!! 

Thanks for the response. I was already looking at this same dashboard. I still haven't been able to get data collection to work when I include management zones.

RPbiaggio
Helper

@cesarsaraviaAn example of what I mentioned above. I got a ready-made template and I'm just trying to include the management zone, to be shown, but it returns null.

RPbiaggio_1-1708964568456.png

 

fetch events
| fieldsAdd tags = entity_tags, zonas = affected_entities.management_zones.names
| expand tags
| expand zonas
| filter event.kind == "DAVIS_PROBLEM" //and zonas == "FS: Aquisição Digital"
| sort timestamp desc

// Lookup for affected_entity_ids and root_cause_entity_id Start
| expand affected_entity_ids
| expand root_cause_entity_id
| lookup [fetch dt.entity.service], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.services"
| lookup [fetch dt.entity.service], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.services"
| lookup [fetch dt.entity.process_group_instance], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.pgi"
| lookup [fetch dt.entity.process_group_instance], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.pgi"
| lookup [fetch dt.entity.application], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.applications"
| lookup [fetch dt.entity.application], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.applications"
| lookup [fetch dt.entity.mobile_application], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.mobile"
| lookup [fetch dt.entity.mobile_application], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.mobile"
| lookup [fetch dt.entity.custom_application], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.customapplication"
| lookup [fetch dt.entity.custom_application], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.customapplication"
| lookup [fetch dt.entity.cloud_application], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.cloudapplication"
| lookup [fetch dt.entity.cloud_application], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.cloudapplication"
| lookup [fetch dt.entity.synthetic_test], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.synthetictest"
| lookup [fetch dt.entity.synthetic_test], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.synthetictest"
| lookup [fetch dt.entity.http_check], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.httpcheck"
| lookup [fetch dt.entity.http_check], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.httpcheck"
| lookup [fetch dt.entity.kubernetes_cluster], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.kubernetescluster"
| lookup [fetch dt.entity.kubernetes_cluster], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.kubernetescluster"
| lookup [fetch dt.entity.host], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.hosts"
| lookup [fetch dt.entity.host], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.hosts"
| lookup [fetch dt.entity.custom_device], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.customdevices"
| lookup [fetch dt.entity.custom_device], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.customdevices"
| lookup [fetch dt.entity.hypervisor], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.hypervisor"
| lookup [fetch dt.entity.hypervisor], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.hypervisor"
| lookup [fetch dt.entity.environment], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.environment"
// Lookup for affected_entity_ids and root_cause_entity_id End


| summarize {zonas = takeFirst(zonas),startTime = takeFirst(event.start),
            endTime = takeFirst(event.end),
            problemClosedDuration = takeFirst(resolved_problem_duration),
            status = takeFirst(event.status),
            event.name = takeFirst(event.name),
            severityLevel = takeFirst(event.category),
            affected = takeFirst(affected_entity_ids),
            rootCause = takeFirst(root_cause_entity_id),
            dt.davis.is_duplicate = takeFirst(dt.davis.is_duplicate),
            affectedServices = collectDistinct(lookup.affected.entity.servicesentity.name),
            affectedPGI = collectDistinct(lookup.affected.entity.pgientity.name),
            affectedApplications = collectDistinct(lookup.affected.entity.applicationsentity.name),
            affectedMobile = collectDistinct(lookup.affected.entity.mobileentity.name),
            affectedCustomApplication = collectDistinct(lookup.affected.entity.customapplicationentity.name),
            affectedCloudApplication = collectDistinct(lookup.affected.entity.cloudapplicationentity.name),
            affectedSyntheticTest = collectDistinct(lookup.affected.entity.synthetictestentity.name),
            affectedEntityZone = takeFirst(affected_entity.management_zones.names),
            affectedHttpCheck = collectDistinct(lookup.affected.entity.httpcheckentity.name),
            affectedKubernetesCluster = collectDistinct(lookup.affected.entity.kubernetesclusterentity.name),
            affectedHosts = collectDistinct(lookup.affected.entity.hostsentity.name),
            affectedCustomDevices = collectDistinct(lookup.affected.entity.customdevicesentity.name),
            affectedHypervisor = collectDistinct(lookup.affected.entity.hypervisorentity.name),
            affectedEnvironment = collectDistinct(lookup.affected.entity.environmententity.name),
            rootCauseServices = collectDistinct(lookup.rootcause.entity.servicesentity.name),
            rootCausePGI = collectDistinct(lookup.rootcause.entity.pgientity.name),
            rootCauseApplications = collectDistinct(lookup.rootcause.entity.applicationsentity.name),
            rootCauseMobile = collectDistinct(lookup.rootcause.entity.mobileentity.name),
            rootCauseCustomApplication = collectDistinct(lookup.rootcause.entity.customapplicationentity.name),
            rootCauseSyntheticTest = collectDistinct(lookup.rootcause.entity.synthetictestentity.name),
            rootCauseHttpCheck = collectDistinct(lookup.rootcause.entity.httpcheckentity.name),
            rootCauseHosts = collectDistinct(lookup.rootcause.entity.hostsentity.name),
            rootCauseCustomDevices = collectDistinct(lookup.rootcause.entity.customdevicesentity.name),
            event.id = takeFirst(event.id)}, 
            by:{display_id}
| filter `dt.davis.is_duplicate` == false
| fieldsAdd currentTime = toTimestamp(now())
| fieldsAdd status = if((status == "ACTIVE"),"OPEN", 
                else:if((status == "CLOSED"), "CLOSED"))

| fields zonas,Status = if((status == "OPEN"),"🔴 OPEN", 
                  else:if((status == "CLOSED"),"🟢 CLOSED")),
         Problem = concat(display_id," - ",event.name),         
         Severity = severityLevel,
         Type = (event.name),
         AffectedCount = arraySize(arrayRemoveNulls(arrayConcat(affectedApplications,affectedMobile,affectedCustomApplication,affectedCloudApplication,affectedSyntheticTest,affectedHttpCheck,affectedServices,affectedPGI,affectedKubernetesCluster,affectedHosts,affectedHypervisor,affectedCustomDevices,affectedEnvironment))),
         Affected = arrayRemoveNulls(arrayConcat(zonas,affectedApplications,affectedMobile,affectedCustomApplication,affectedCloudApplication,affectedSyntheticTest,affectedHttpCheck,affectedServices,affectedPGI,affectedKubernetesCluster,affectedHosts,affectedHypervisor,affectedCustomDevices,affectedEnvironment)),
         RootCause = arrayRemoveNulls(arrayConcat(rootCauseServices,rootCauseHosts)),
         StartTime = startTime,
         EndTime =  if((status == "OPEN"),"In Progress", 
                    else:if((status == "CLOSED"),endTime)),   
         `Duration (min)` = if((status == "CLOSED"),problemClosedDuration/60000000000,
                   else:if((status == "OPEN"), toLong(currentTime-startTime)/60000000000)),         
         event.id           
| sort StartTime, direction:"descending"
| sort Status, direction:"ascending"

 

Hi @RPbiaggio 
After all the lookups, you must add this code and then get the right lookup.affected.entity.... 

| fieldsAdd zonas = coalesce(lookup.affected.entity.hostsmanagementZones, lookup.affected.entity.hypervisormanagementZones,lookup.affected.entity.servicesmanagementZones)

 Example:

cesarsaravia_0-1709074680370.png

 

-César S. -

Thanks for the help @cesarsaravia , if I don't bother you too much, I'd like to ask you how to filter the data in the dashboard above for a specific Management zone (in my case the Management Zone is a dynamic variable), couldn't figured it out where to add the filter statement. 

 

This is the query in DQL (same Davis Problems's Dashboard as @RPbiaggio )

 

fetch events
| fieldsAdd tags = entity_tags, zone = affected_entities.management_zones.names
| expand tags
| expand zone
| filter event.kind == "DAVIS_PROBLEM" //and zonas == "FS: Aquisição Digital"
| sort timestamp desc

// Lookup for affected_entity_ids and root_cause_entity_id Start
| expand affected_entity_ids
| expand root_cause_entity_id
| lookup [fetch dt.entity.service], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.services"
| lookup [fetch dt.entity.service], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.services"
| lookup [fetch dt.entity.process_group_instance], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.pgi"
| lookup [fetch dt.entity.process_group_instance], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.pgi"
| lookup [fetch dt.entity.application], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.applications"
| lookup [fetch dt.entity.application], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.applications"
| lookup [fetch dt.entity.mobile_application], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.mobile"
| lookup [fetch dt.entity.mobile_application], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.mobile"
| lookup [fetch dt.entity.custom_application], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.customapplication"
| lookup [fetch dt.entity.custom_application], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.customapplication"
| lookup [fetch dt.entity.cloud_application], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.cloudapplication"
| lookup [fetch dt.entity.cloud_application], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.cloudapplication"
| lookup [fetch dt.entity.synthetic_test], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.synthetictest"
| lookup [fetch dt.entity.synthetic_test], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.synthetictest"
| lookup [fetch dt.entity.http_check], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.httpcheck"
| lookup [fetch dt.entity.http_check], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.httpcheck"
| lookup [fetch dt.entity.kubernetes_cluster], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.kubernetescluster"
| lookup [fetch dt.entity.kubernetes_cluster], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.kubernetescluster"
| lookup [fetch dt.entity.host], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.hosts"
| lookup [fetch dt.entity.host], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.hosts"
| lookup [fetch dt.entity.custom_device], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.customdevices"
| lookup [fetch dt.entity.custom_device], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.customdevices"
| lookup [fetch dt.entity.hypervisor], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.hypervisor"
| lookup [fetch dt.entity.hypervisor], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.hypervisor"
| lookup [fetch dt.entity.environment], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.environment"
// Lookup for affected_entity_ids and root_cause_entity_id End

| fieldsAdd zone = coalesce(lookup.affected.entity.hostsmanagementZones, lookup.affected.entity.hypervisormanagementZones,lookup.affected.entity.servicesmanagementZones)


// | summarize {zone = takeFirst(zone),startTime = takeFirst(event.start),
| summarize {zone = takeFirst(zone),startTime = takeFirst(event.start),
endTime = takeFirst(event.end),
problemClosedDuration = takeFirst(resolved_problem_duration),
status = takeFirst(event.status),
event.name = takeFirst(event.name),
severityLevel = takeFirst(event.category),
affected = takeFirst(affected_entity_ids),
rootCause = takeFirst(root_cause_entity_id),
dt.davis.is_duplicate = takeFirst(dt.davis.is_duplicate),
affectedServices = collectDistinct(lookup.affected.entity.servicesentity.name),
affectedPGI = collectDistinct(lookup.affected.entity.pgientity.name),
affectedApplications = collectDistinct(lookup.affected.entity.applicationsentity.name),
affectedMobile = collectDistinct(lookup.affected.entity.mobileentity.name),
affectedCustomApplication = collectDistinct(lookup.affected.entity.customapplicationentity.name),
affectedCloudApplication = collectDistinct(lookup.affected.entity.cloudapplicationentity.name),
affectedSyntheticTest = collectDistinct(lookup.affected.entity.synthetictestentity.name),
affectedEntityZone = takeFirst(affected_entity.management_zones.names),
affectedHttpCheck = collectDistinct(lookup.affected.entity.httpcheckentity.name),
affectedKubernetesCluster = collectDistinct(lookup.affected.entity.kubernetesclusterentity.name),
affectedHosts = collectDistinct(lookup.affected.entity.hostsentity.name),
affectedCustomDevices = collectDistinct(lookup.affected.entity.customdevicesentity.name),
affectedHypervisor = collectDistinct(lookup.affected.entity.hypervisorentity.name),
affectedEnvironment = collectDistinct(lookup.affected.entity.environmententity.name),
rootCauseServices = collectDistinct(lookup.rootcause.entity.servicesentity.name),
rootCausePGI = collectDistinct(lookup.rootcause.entity.pgientity.name),
rootCauseApplications = collectDistinct(lookup.rootcause.entity.applicationsentity.name),
rootCauseMobile = collectDistinct(lookup.rootcause.entity.mobileentity.name),
rootCauseCustomApplication = collectDistinct(lookup.rootcause.entity.customapplicationentity.name),
rootCauseSyntheticTest = collectDistinct(lookup.rootcause.entity.synthetictestentity.name),
rootCauseHttpCheck = collectDistinct(lookup.rootcause.entity.httpcheckentity.name),
rootCauseHosts = collectDistinct(lookup.rootcause.entity.hostsentity.name),
rootCauseCustomDevices = collectDistinct(lookup.rootcause.entity.customdevicesentity.name),
event.id = takeFirst(event.id)},
by:{display_id}
| filter `dt.davis.is_duplicate` == false
| fieldsAdd currentTime = toTimestamp(now())
| fieldsAdd status = if((status == "ACTIVE"),"OPEN",
else:if((status == "CLOSED"), "CLOSED"))

| fields zone,Status = if((status == "OPEN")," OPEN",
else:if((status == "CLOSED"),"🟢 CLOSED")),
Problem = concat(display_id," - ",event.name),
Severity = severityLevel,
Type = (event.name),
AffectedCount = arraySize(arrayRemoveNulls(arrayConcat(affectedApplications,affectedMobile,affectedCustomApplication,affectedCloudApplication,affectedSyntheticTest,affectedHttpCheck,affectedServices,affectedPGI,affectedKubernetesCluster,affectedHosts,affectedHypervisor,affectedCustomDevices,affectedEnvironment))),
Affected = arrayRemoveNulls(arrayConcat(zone,affectedApplications,affectedMobile,affectedCustomApplication,affectedCloudApplication,affectedSyntheticTest,affectedHttpCheck,affectedServices,affectedPGI,affectedKubernetesCluster,affectedHosts,affectedHypervisor,affectedCustomDevices,affectedEnvironment)),
RootCause = arrayRemoveNulls(arrayConcat(rootCauseServices,rootCauseHosts)),
StartTime = startTime,
EndTime = if((status == "OPEN"),"In Progress",
else:if((status == "CLOSED"),endTime)),
`Duration (min)` = if((status == "CLOSED"),problemClosedDuration/60000000000,
else:if((status == "OPEN"), toLong(currentTime-startTime)/60000000000)),
event.id
| sort StartTime, direction:"descending"
| sort Status, direction:"ascending"

 

My dynamic variable name is called  MZ and the query is this one

 

fetch dt.entity.host
| expand managementZones
| fields managementZones

Thanks in advance 

 

 

 

 

 

 

 

 

 

Hi @cesarsaravia  sorry to bother you, I'd like to ask you another thing on the same dashboard you were looking with @RPbiaggio  , how can I manage to filter data inside the Dashboard only for the Management Zone specified in a dynamic variable?

 

This is my variable (called MZ)

fetch dt.entity.host
| expand managementZones
| fields managementZones

 

This is my dashboard, the same as RPBiaggio:

 

fetch events
| fieldsAdd tags = entity_tags, zone = affected_entities.management_zones.names
| expand tags
| expand zone
| filter event.kind == "DAVIS_PROBLEM" //and zonas == "FS: Aquisição Digital"
| sort timestamp desc

// Lookup for affected_entity_ids and root_cause_entity_id Start
| expand affected_entity_ids
| expand root_cause_entity_id
| lookup [fetch dt.entity.service], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.services"
| lookup [fetch dt.entity.service], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.services"
| lookup [fetch dt.entity.process_group_instance], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.pgi"
| lookup [fetch dt.entity.process_group_instance], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.pgi"
| lookup [fetch dt.entity.application], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.applications"
| lookup [fetch dt.entity.application], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.applications"
| lookup [fetch dt.entity.mobile_application], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.mobile"
| lookup [fetch dt.entity.mobile_application], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.mobile"
| lookup [fetch dt.entity.custom_application], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.customapplication"
| lookup [fetch dt.entity.custom_application], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.customapplication"
| lookup [fetch dt.entity.cloud_application], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.cloudapplication"
| lookup [fetch dt.entity.cloud_application], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.cloudapplication"
| lookup [fetch dt.entity.synthetic_test], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.synthetictest"
| lookup [fetch dt.entity.synthetic_test], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.synthetictest"
| lookup [fetch dt.entity.http_check], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.httpcheck"
| lookup [fetch dt.entity.http_check], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.httpcheck"
| lookup [fetch dt.entity.kubernetes_cluster], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.kubernetescluster"
| lookup [fetch dt.entity.kubernetes_cluster], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.kubernetescluster"
| lookup [fetch dt.entity.host], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.hosts"
| lookup [fetch dt.entity.host], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.hosts"
| lookup [fetch dt.entity.custom_device], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.customdevices"
| lookup [fetch dt.entity.custom_device], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.customdevices"
| lookup [fetch dt.entity.hypervisor], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.hypervisor"
| lookup [fetch dt.entity.hypervisor], sourceField:root_cause_entity_id, lookupField: id, prefix:"lookup.rootcause.entity.hypervisor"
| lookup [fetch dt.entity.environment], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.environment"
// Lookup for affected_entity_ids and root_cause_entity_id End

| fieldsAdd zone = coalesce(lookup.affected.entity.hostsmanagementZones, lookup.affected.entity.hypervisormanagementZones,lookup.affected.entity.servicesmanagementZones)


// | summarize {zone = takeFirst(zone),startTime = takeFirst(event.start),
| summarize {zone = takeFirst(zone),startTime = takeFirst(event.start),
endTime = takeFirst(event.end),
problemClosedDuration = takeFirst(resolved_problem_duration),
status = takeFirst(event.status),
event.name = takeFirst(event.name),
severityLevel = takeFirst(event.category),
affected = takeFirst(affected_entity_ids),
rootCause = takeFirst(root_cause_entity_id),
dt.davis.is_duplicate = takeFirst(dt.davis.is_duplicate),
affectedServices = collectDistinct(lookup.affected.entity.servicesentity.name),
affectedPGI = collectDistinct(lookup.affected.entity.pgientity.name),
affectedApplications = collectDistinct(lookup.affected.entity.applicationsentity.name),
affectedMobile = collectDistinct(lookup.affected.entity.mobileentity.name),
affectedCustomApplication = collectDistinct(lookup.affected.entity.customapplicationentity.name),
affectedCloudApplication = collectDistinct(lookup.affected.entity.cloudapplicationentity.name),
affectedSyntheticTest = collectDistinct(lookup.affected.entity.synthetictestentity.name),
affectedEntityZone = takeFirst(affected_entity.management_zones.names),
affectedHttpCheck = collectDistinct(lookup.affected.entity.httpcheckentity.name),
affectedKubernetesCluster = collectDistinct(lookup.affected.entity.kubernetesclusterentity.name),
affectedHosts = collectDistinct(lookup.affected.entity.hostsentity.name),
affectedCustomDevices = collectDistinct(lookup.affected.entity.customdevicesentity.name),
affectedHypervisor = collectDistinct(lookup.affected.entity.hypervisorentity.name),
affectedEnvironment = collectDistinct(lookup.affected.entity.environmententity.name),
rootCauseServices = collectDistinct(lookup.rootcause.entity.servicesentity.name),
rootCausePGI = collectDistinct(lookup.rootcause.entity.pgientity.name),
rootCauseApplications = collectDistinct(lookup.rootcause.entity.applicationsentity.name),
rootCauseMobile = collectDistinct(lookup.rootcause.entity.mobileentity.name),
rootCauseCustomApplication = collectDistinct(lookup.rootcause.entity.customapplicationentity.name),
rootCauseSyntheticTest = collectDistinct(lookup.rootcause.entity.synthetictestentity.name),
rootCauseHttpCheck = collectDistinct(lookup.rootcause.entity.httpcheckentity.name),
rootCauseHosts = collectDistinct(lookup.rootcause.entity.hostsentity.name),
rootCauseCustomDevices = collectDistinct(lookup.rootcause.entity.customdevicesentity.name),
event.id = takeFirst(event.id)},
by:{display_id}
| filter `dt.davis.is_duplicate` == false
| fieldsAdd currentTime = toTimestamp(now())
| fieldsAdd status = if((status == "ACTIVE"),"OPEN",
else:if((status == "CLOSED"), "CLOSED"))

| fields zone,Status = if((status == "OPEN")," OPEN",
else:if((status == "CLOSED"),"🟢 CLOSED")),
Problem = concat(display_id," - ",event.name),
Severity = severityLevel,
Type = (event.name),
AffectedCount = arraySize(arrayRemoveNulls(arrayConcat(affectedApplications,affectedMobile,affectedCustomApplication,affectedCloudApplication,affectedSyntheticTest,affectedHttpCheck,affectedServices,affectedPGI,affectedKubernetesCluster,affectedHosts,affectedHypervisor,affectedCustomDevices,affectedEnvironment))),
Affected = arrayRemoveNulls(arrayConcat(zone,affectedApplications,affectedMobile,affectedCustomApplication,affectedCloudApplication,affectedSyntheticTest,affectedHttpCheck,affectedServices,affectedPGI,affectedKubernetesCluster,affectedHosts,affectedHypervisor,affectedCustomDevices,affectedEnvironment)),
RootCause = arrayRemoveNulls(arrayConcat(rootCauseServices,rootCauseHosts)),
StartTime = startTime,
EndTime = if((status == "OPEN"),"In Progress",
else:if((status == "CLOSED"),endTime)),
`Duration (min)` = if((status == "CLOSED"),problemClosedDuration/60000000000,
else:if((status == "OPEN"), toLong(currentTime-startTime)/60000000000)),
event.id
| sort StartTime, direction:"descending"
| sort Status, direction:"ascending"

 

 

 

Where should I add the additional filter for my variable MZ?  Couldn't figured it out, thanks in advance 

 

 

 

 

Oops, how are you? I don't know if I understand the question, but have you tried using this instruction to filter with your variable?

| filter in(zone ,array($MZ))

Featured Posts