DQL query syntax while using in API calls

[harinhs]

I am trying to use this query to fetch remote environment data, but not working for remote environment through API call. But this works in local environment to get the host availability, 

fetch dt.entity.host
| filterOut isMonitoringCandidate == true or standalone == true
| lookup [
timeseries m1=max(dt.host.availability, rollup: sum),
filter: availability.state == "up",
by: {availability.state,dt.entity.host},
from: toTimestamp($dt_timeframe_from),
to: toTimestamp($dt_timeframe_to) - 2m
| parse replaceString(toString(m1),"null","0"), """'['ARRAY{ DOUBLE:i ', '}{1,10000}:m1Pop"""
| fieldsAdd intervalToMinutes = toLong(interval) / 60 / power(10, 9)
| fieldsAdd availability = toDouble(arraySum(m1Pop) / arraySize(m1Pop) * 100) / intervalToMinutes
], sourceField: id, lookupField: dt.entity.host, fields: {availability}
| fields id, availability = coalesce(availability, 0)
| summarize avg(availability)

How to parse this query in the API call section, I tried giving this like other queries but getting 400 Bad request error. 

const credentialId = "CREDENTIALS_VAULT-XXXXXXXX"; // Replace with your credential vault ID.
const url = "https://{environmentid}.apps.dynatrace.com/platform/storage/query/v1/query:execute"; // Replace with API URL.
const query = "<how to give the above DQL query as string here> "; // Replace with your query.

Could you please help? Thanks in advance. 

[t_pawlak]

Hi,
try this: 

fetch dt.entity.host
| filterOut isMonitoringCandidate == true or standalone == true
| lookup [
    timeseries m1 = max(dt.host.availability, rollup: sum),
      filter: availability.state == "up",
      by: { availability.state, dt.entity.host },
      from: now() - 24h,
      to: now() - 2m
    | parse replaceString(toString(m1), "null", "0"), "'['ARRAY{ DOUBLE:i ', '}{1,10000}:m1Pop"
    | fieldsAdd intervalToMinutes = toLong(interval) / 60 / power(10, 9)
    | fieldsAdd availability = toDouble(arraySum(m1Pop) / arraySize(m1Pop) * 100) / intervalToMinutes
  ],
  sourceField: id,
  lookupField: dt.entity.host,
  fields: { availability }
| fields id, availability = coalesce(availability, 0)
| summarize avg(availability)

The query works locally because $dt_timeframe_from and $dt_timeframe_to are dashboard-only variables. They are not resolved when executing DQL via the Query API, which results in a 400 Bad Request.

Remove $dt_timeframe_from / $dt_timeframe_to from the DQL

Use now() - … in the query or pass the timeframe via the query:execute request body

Replace the parse """ ... """ with a normal quoted string ("...") to avoid JSON parsing issues

After these changes, the query executes correctly via the API.