DQL for dt.entity.id and dt.entity.name with dt.host.availability as a list

[dwclogic]

I really thought this would be a breeze, but it's proving to be a Very difficult list to generate.  I was hoping to use in a Notebook or Dashboard

[dwclogic]

Working with the Davis AI Notebook widget, we eventually got a result.  However it seems to be missing information, probably because data has been lost over time, all items collecting the information all have exactly the same values of 121 values of 1, which averages to 1.   All other hosts seem to have null for those values, or its all 1s.  So, I doubt this data.

fetch dt.entity.host
| fieldsAdd lifetime, id, entity.name
| lookup [timeseries by:{dt.entity.host}, avg_availability = avg(dt.host.availability)], sourceField:id, lookupField:dt.entity.host, fields:{avg_availability}
| filter NOT isNull(avg_availability)
| fieldsAdd arrayAvg(avg_availability)
| sort entity.name

[p_devulapalli]

@dwclogic Here is what the host states actually mean

https://docs.dynatrace.com/docs/shortlink/host-availability#states

To make things simple and if you want to know percentage of time the host has been available instead of reporting on individual states , you can use a query like below

timeseries m1=max(dt.host.availability, rollup: sum),
filter:availability.state == "up" , by:{availability.state, dt.entity.host}
| parse replaceString(toString(m1),"null","0"), """'['ARRAY{ DOUBLE:i ', '}{1,10000}:m1Pop"""
| fieldsAdd intervalToMinutes = toLong(interval)/60000000000
| fieldsAdd normalizedM1 = if(m1Pop[]>intervalToMinutes, intervalToMinutes, else: m1Pop[])
| fieldsAdd availability= toDouble(arraySum(normalizedM1)/(arraySize(normalizedM1))*100)/intervalToMinutes
| fieldsAdd dt.entity.host.name = entityName(dt.entity.host)
| fieldsKeep availability,dt.entity.host,dt.entity.host.name, availability.state