DQL - Count records bigger than in a array of records

[m_zol]

Hello,

Is there any way to count records in a array if the values are bigger than a number?

The scenario is to get the records from a response time timeseries metric and check how many records are more than 1.5 seconds and return the count.

The function CountIf doesn't seems to work with arrays.

Anyone see a workaround?

Thanks

[m_zol]

Hi,

I was able to, partially, get what I need using expand command. 

// Total de requests Válidas Com Sucesso
timeseries { Total_Requests_Sucesso = sum(dt.service.request.count), start=start(), end=end()}, by: { endpoint.name }, interval: 1m,
filter: { matchesValue(entityAttr(dt.entity.service, "tags"), $Tags) AND
       ((http.response.status_code >= 200 AND http.response.status_code <= 299) OR http.response.status_code == 422)}

// Total de requests Válidas Com Erro
| join [timeseries {Total_Requests_Erros = sum(dt.service.request.count, default:0), start=start(), end=end()}, by: {endpoint.name}, interval: 1m,
filter: {matchesValue(entityAttr(dt.entity.service, "tags"), $Tags) AND
       ((http.response.status_code >= 500 AND http.response.status_code <= 599) OR http.response.status_code == 408)}
], on: {endpoint.name}, fields:{Total_Requests_Erros, endpoint.name}

| filter matchesPhrase(endpoint.name, "POST /open-banking/payments/v4/pix/payments")

//Cálculo da Disponibilidade Pontual
| fieldsAdd Disponibilidade_Pontual = Total_Requests_Sucesso[] / (Total_Requests_Erros[] + Total_Requests_Sucesso[]) * 100

// Quantidade de registros no array (Disponível + Indisponível)
| fieldsAdd QuantidadeRegistros = arraySize(arrayRemoveNulls(Disponibilidade_Pontual))

// remoção de campos não necessários
//| FieldsRemove Total_Requests_Sucesso, Total_Requests_Erros

// expando os valores em diferentes linhas
| expand Disponibilidade_Pontual
| filter Disponibilidade_Pontual < 95


| summarize {
    Total_Indisponivel = count(),
    Total_Registros = takeLast(QuantidadeRegistros)

}, by:{endpoint.name, timeframe, interval}

| fieldsAdd TotalDisponivel = Total_Registros - Total_Indisponivel
| fieldsAdd Disponibilidade_Diaria = toDouble(TotalDisponivel) / toDouble(Total_Registros) * 100

But this DQL gets the right result for one day, now I need to break the data for each day.. not sure how yet.

[krzysztof_hoja · Accepted solution]

To count array elements fulfilling specific conditions you can use this query as example:

timeseries rt=avg(dt.service.request.response_time)
| fieldsAdd v=arraySum(iCollectArray(if(rt[]>100000,1, else:0)))

with iterative expressions you can perform array transformation, by applying formula to every element. This way we produce array of 0s or 1s (or nulls when array element is null). As a alst step we just need to sum array elements to have counts.

Example timeseries looks like this

and when I apply threshold of 100ms, I get this count:

If you have multi day timeseries and want to have result grouped by days, here how to achieve it:

timeseries rt=avg(dt.service.request.response_time), timestamp=start()
| fieldsAdd d=record(rt=rt[], timestamp=timestamp[])
| expand d
| summarize v=countIf(d[rt]>90000), by: {timestamp=d[timestamp]@d}