makeTimeseries in DQL with lookupField

[scholcia]

How to create makeTimeseries  in this DQL to be able to create alert from it :

timeseries { uptime = avg(com.dynatrace.extension.network_device.sysuptime), by: { `dt.entity.network:device` }, interval:1m }
| fieldsAdd status = if(isNotNull(uptime[-5]), 0, else: 1)| fieldsRename device=`dt.entity.network:device`
| lookup [
fetch `dt.entity.network:device`
| expand tags
], lookupField:id, sourceField:device
| filter isNotNull(lookup.id)
| fields timeframe, interval, status, device, lookup.tags

[t_pawlak]

Hi,
To create a DQL-based custom alert, the query must output a numeric timeseries column array. 
I think that in your case status is currently computed not as a array because you use uptime[-5].
Try this:

timeseries {
  uptime = avg(com.dynatrace.extension.network_device.sysuptime),
  by: { `dt.entity.network:device` },
  interval: 1m
}
| fieldsAdd status = if(isNotNull(uptime[]), 0, else: 1)
| fieldsRename device = `dt.entity.network:device`
| lookup [
    fetch `dt.entity.network:device`
    | expand tags
  ], lookupField: id, sourceField: device
| filter isNotNull(lookup.id)
| fields timeframe, interval, status, device, lookup.tags

status as a 0/1 timeseries.
Then in alert select status

[scholcia]

Thanks a lot it works however I need to exclude last value from array 'uptime' because in each case is "null" so that my alert will be useless.

[t_pawlak · Accepted solution]

Yeah, you can drop the last (incomplete) bucket using to:-1m, but in this case you need to use from too.
This is what I mean:

timeseries {
  uptime = avg(com.dynatrace.extension.network_device.sysuptime),
  by: { `dt.entity.network:device` },
  interval: 1m,
  from: -60m,
  to: -1m
}
| fieldsAdd status = if(isNotNull(uptime[]), 0, else: 1)
| fieldsRename device = `dt.entity.network:device`
| lookup [
    fetch `dt.entity.network:device`
    | expand tags
  ], lookupField: id, sourceField: device
| filter isNotNull(lookup.id)
| fields timeframe, interval, status, device, lookup.tags

thanks to that you will drop the trailing null bucket

[t_pawlak]

In documentation I found that you can use arrayLast(uptime) - it returns the last non-null element
Array functions 

[scholcia]

thanks, that's perfect solution !