Problem Events DQL

RPbiaggio · ‎08 May 2024

Guys, can you help me with the case below?

I need to get only active (open) problems. This is happening, however, I am not able to bring the tags I need. As I'm getting takeMax, I think it's not returning the information. Can you help me adjust this query? I've done several tests and haven't been able to improve.

fetch events
| filter dt.system.bucket == "default_davis_events"
| filter event.kind == "DAVIS_PROBLEM"
| expand entity_tags
| parse entity_tags, """ "Jornada:" DATA:Jornada """
| parse entity_tags, """ "Serviço Negócio:" DATA:ServicoNegocio """
| parse entity_tags, """ "Canal:" DATA:Canal """
| parse entity_tags, """ "Etapa:" DATA:Etapa """
| parse entity_tags, """ "Fluxo:" DATA:Fluxo """
| fieldsAdd Tags = entity_tags
| fieldsAdd Jornada = if(isNull(Jornada), "Não Definido", else:Jornada)
| fieldsAdd Fluxo = if(isNull(Fluxo), "Não Definido", else:Fluxo)
| fieldsAdd Etapas = if(isNull(Etapas), "Não Definido", else:Etapas)
| fieldsAdd ServicoNegocio = if(isNull(ServicoNegocio), "Não Definido", else:ServicoNegocio)
| fieldsAdd Canal = if(isNull(Canal), "Não Definido", else:Canal)
| summarize {problem = takeMax(record(LastUpdate = timestamp,
                                      Inicio = event.start, 
                                      Fim = event.end,
                                      Duracao = resolved_problem_duration,
                                      Status = event.status,
                                      Evento = event.name,
                                      Severidade = event.category,
                                      Recorrencia = dt.davis.last_reopen_timestamp,
                                      UltimaReabertura = dt.davis.last_reopen_timestamp,
                                      dt.davis.is_duplicate = dt.davis.is_duplicate,
                                      Jornada, 
                                      ServicoNegocio,
                                      Canal,
                                      Fluxo,
                                      Etapas,
                                      TipoEntidade = affected_entity_types
                                      )
                                      )}, by:{ display_id }                                      
| fieldsFlatten problem
| FILTER problem.Status == "ACTIVE"

I have several tags, I want to specifically get the defined value.

Thank you

FranciscoGarcia · ‎09 May 2024

what are you trying to achieve by using takemax?

RPbiaggio · ‎09 May 2024

@FranciscoGarcia I'm trying to get the last recorded event. I've already used takeLast, takeFirst. takeMax was the one that best suited my needs.

FranciscoGarcia · ‎09 May 2024

I think the issue is in the parse command. Can you try chaning the double quotes to single? . Ie: Change

| parse entity_tags, """ "Jornada:" DATA:Jornada """

to

| parse entity_tags, """ 'Jornada:' DATA:Jornada """

RPbiaggio · ‎09 May 2024

@FranciscoGarcia

That's not it... I have my dashboard using these double quotes and the Parse is done correctly. What is happening is that in the code below, at times, especially when I increase the period in the timeframe, for example 24h, 6 hours, Dynatrace returns events with OPEN status when in reality they are no longer open. I'm trying to adjust this. I need Dynatrace to return only open problems, get the last event.

fetch events
| filter event.kind == "DAVIS_PROBLEM"
| fieldsAdd entity_tags
| expand entity_tags
| parse entity_tags, """ "Jornada:" DATA:Jornada """
| parse entity_tags, """ "Serviço Negócio:" DATA:ServicoNegocio """
| parse entity_tags, """ "Canal:" DATA:Canal """
| parse entity_tags, """ "Etapa:" DATA:Etapa """
| parse entity_tags, """ "Fluxo:" DATA:Fluxo """
| fieldsAdd affected_entity_types
//| fieldsAdd dt.entity.queue.name, dt.entity.queue
//=============== 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.queue], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.queue"
| lookup [fetch dt.entity.service_method], sourceField:affected_entity_ids, lookupField: id, prefix:"lookup.affected.entity.service_method"
| 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 {startTime = takeMax(event.start),
            endTime = takeMax(event.end),
            Duration = takeMax(resolved_problem_duration),
            Status = takeMax(event.status),
            event.name = takeMax(event.name),
            severityLevel = takeFirst(event.category),
            Recorrencia = countDistinct(dt.davis.last_reopen_timestamp),
            UltimaReabertura = takeFirst(dt.davis.last_reopen_timestamp),
            dt.davis.is_duplicate = takeMax(dt.davis.is_duplicate),
            Jornada = takeLast(Jornada), 
            ServicoNegocio = takeLast(ServicoNegocio),
            Canal = takeLast(Canal),
            Fluxo = collectDistinct(Fluxo),
            Etapas = collectDistinct(Etapa),
            TipoEntidade = takeFirst(affected_entity_types),
            affected = collectDistinct(affected_entity_ids),
            rootCause = takeFirst(root_cause_entity_id),
            affectedServices = collectDistinct(lookup.affected.entity.servicesentity.name),
            affectedQueues = collectDistinct(lookup.affected.entity.queueentity.name),
            affectedMethod = collectDistinct(lookup.affected.entity.service_methodentity.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),
            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"))
| filter ("ALL" == $Status OR $Status == Status)
| filter ("ALL" == $Severity OR $Severity == severityLevel)
| fields Status = if((Status == "OPEN"),"⭕ OPEN",else:if((Status == "CLOSED"),"✅ CLOSED")),
          Problem = (display_id),
          StartTime = startTime,EndTime =  if((Status == "OPEN"),"In Progress",
            else:if((Status == "CLOSED"),endTime)),`Duration (min)` = if((Status == "CLOSED"),Duration,
            else:if((Status == "OPEN"), toLong(currentTime-startTime)/60000000000)),
          Evento = event.name,
          Recorrencia = if((Recorrencia == 0),"Não",else:"Sim"),
          //Reaberto = if((Recorrencias == 0),"Não",else:concat("Qtde Reaberturas: ",Recorrencias)),
          UltimaReabertura = if(isNull(UltimaReabertura), "Não foi reaberto", else:UltimaReabertura),
          Severity = severityLevel,
          TipoEntidade,
          Affected = arrayRemoveNulls(arrayConcat(affectedMethod,affectedQueues,affectedApplications,affectedMobile,affectedCustomApplication,affectedCloudApplication,affectedSyntheticTest,affectedHttpCheck,affectedServices,affectedPGI,affectedKubernetesCluster,affectedHosts,affectedHypervisor,affectedCustomDevices,affectedEnvironment)),
          RootCause = arrayRemoveNulls(arrayConcat(rootCauseCustomDevices,rootCauseHosts,rootCauseHttpCheck,rootCauseServices,rootCausePGI,rootCauseApplications,rootCauseMobile,rootCauseCustomApplication,rootCauseSyntheticTest)),
          Jornada,          
          ServicoNegocio,
          Canal,
          Fluxo= arrayRemoveNulls(Fluxo),
          Etapas,  
          event.id
| fieldsAdd ServicoNegocio = if(isNull(ServicoNegocio), "Não Definido", else:ServicoNegocio)
| fieldsAdd Jornada = if(isNull(Jornada), "Não Definido", else:Jornada)
| fieldsAdd Fluxo = if(isNull(Fluxo), "Não Definido", else:Fluxo)
| fieldsAdd Etapas = if(isNull(Etapas), "Não Definido", else:Etapas)
| fieldsAdd Canal = if(isNull(Canal), "Não Definido", else:Canal)
| fieldsAdd Canal = if(isNull(Canal), "Não Definido", else:Canal)
| filter in(Canal,array($Canal))
| filter in(ServicoNegocio,array($ServicoNegocio))
| filter ("ALL" == $Jornada OR $Jornada == Jornada)
| filter ("ALL" == $Recorrencia OR $Recorrencia == Recorrencia)

FranciscoGarcia · ‎09 May 2024

You are right. The quotes are OK. Just and idea, not sure if it will help you. Usually to get the latest open problem I use something like:

fetch events
| filter event.kind=="DAVIS_PROBLEM"
| dedup display_id, sort:{timestamp desc} // only select most recent problem event
| filterout event.status=="CLOSED" // keep open problems