DQL timeseries filter by list of services

[PVThach]

I am trying to create a dashboard of external services health.   I am able to get list of 20 external services with this DQL, which I am thinking to use a variable input to the dashboard.

fetch dt.entity.service
| fieldsAdd entity.name, publicDomainName
| filter isExternalService
| filter isNotNull(publicDomainName)
| filterOut contains(publicDomainName, "my.com") // filter out internal or proxy
| filter serviceType == "WEB_REQUEST_SERVICE"
| fields entity.name // could be id

I am not sure how to filter timeseries.   My timeseries look like this:

timeseries {
latency_p75 = percentile(dt.service.request.response_time, 75),
latency_p90 = percentile(dt.service.request.response_time, 90),
Throughput = sum(dt.service.request.count),
errors = sum(dt.service.request.failure_count)
},
by: dt.entity.service,
// FILTER services as variable from previous DQL
//filter:{ in(dt.entity.service, classicEntitySelector(concat("type(service),entityName.equals(\"CashTransfer Service\")"))) }. //this works but only for one service
| fieldsAdd Service = entityName(dt.entity.service)
| fieldsAdd Latency_p75 = arrayAvg(latency_p75),
Latency_p90 = arrayAvg(latency_p90),
Throughput = arraySum(Throughput),
Errors = arraySum(errors),
dt.entity.service

| fieldsAdd Availability = 100 - (Errors / Throughput) * 100

| fields Service,

Status = if(Availability < 98.00, "🔴 Unhealthy", else: "✅ Healthy"),
StatusSort = if(Availability < 98.00, 0, else: 1),
Availability,
Throughput,
Errors,
Latency_p75,
Latency_p90,
dt.entity.service
| sort StatusSort asc

[t_pawlak]

Hi,
The reason your approach doesn’t work is that dashboard DQL cannot join fetch results with timeseries.
join only works when both sides produce the same type of records, and fetch dt.entity.* produces entity records, while timeseries produces metric records. That’s why filtering the second query based on the first one isn’t supported directly.

try this approach:

timeseries {
    latency_p75 = percentile(dt.service.request.response_time, 75),
    latency_p90 = percentile(dt.service.request.response_time, 90),
    Throughput  = sum(dt.service.request.count),
    Errors      = sum(dt.service.request.failure_count)
}, by: { dt.entity.service }
| filter isExternalService
| filter isNotNull(publicDomainName)
| filterOut contains(publicDomainName, "my.com")
| filter serviceType == "WEB_REQUEST_SERVICE"

| fieldsAdd
    Service = entityName(dt.entity.service),
    Latency_p75 = arrayAvg(latency_p75),
    Latency_p90 = arrayAvg(latency_p90),
    Throughput = arraySum(Throughput),
    Errors = arraySum(Errors)

| fieldsAdd Availability = 100 - (Errors / Throughput) * 100

| fieldsAdd
    Status = if(Availability < 98.0, ":red_circle: Unhealthy", else: ":white_heavy_check_mark: Healthy"),
    StatusSort = if(Availability < 98.0, 0, else: 1)

| fields
    Status,
    Service,
    Availability,
    Throughput,
    Errors,
    Latency_p75,
    Latency_p90,
    publicDomainName,
    dt.entity.service

| sort StatusSort asc, Availability asc

I tested it without variables, because I don't have it on my env, but this is result:

First use this query in a tile, then select Use as variable 
Then filter your timeseries using that variable
I hope it will work

[PVThach]

It didn't work for me.  I am getting:

The field isExternalService doesn't exist.

Any filter for field in dt.entity.service result in field doesn't exist.

| filter isExternalService
| filter isNotNull(publicDomainName)
| filterOut contains(publicDomainName, "my.com")
| filter serviceType == "WEB_REQUEST_SERVICE"

I tried running in dashboard and notebook, same result.

[t_pawlak · Accepted solution]

Hi, try this

timeseries {
    latency_p75 = percentile(dt.service.request.response_time, 75),
    latency_p90 = percentile(dt.service.request.response_time, 90),
    Throughput  = sum(dt.service.request.count),
    Errors      = sum(dt.service.request.failure_count)
}, by: { dt.entity.service }


| fieldsAdd
    isExternalService = entityAttr(dt.entity.service, "isExternalService"),
    publicDomainName  = entityAttr(dt.entity.service, "publicDomainName"),
    serviceType       = entityAttr(dt.entity.service, "serviceType")


| filter isExternalService
| filter isNotNull(publicDomainName)
| filterOut contains(publicDomainName, "my.com")
| filter serviceType == "WEB_REQUEST_SERVICE"

| fieldsAdd
    Service      = entityName(dt.entity.service),
    Latency_p75  = arrayAvg(latency_p75),
    Latency_p90  = arrayAvg(latency_p90),
    Throughput   = arraySum(Throughput),
    Errors       = arraySum(Errors)

| fieldsAdd Availability = 100 - (Errors / Throughput) * 100

| fieldsAdd
    Status     = if(Availability < 98.0, ":red_circle: Unhealthy", else: ":white_heavy_check_mark: Healthy"),
    StatusSort = if(Availability < 98.0, 0, else: 1)

| fields
    Status,
    StatusSort,
    Service,
    Availability,
    Throughput,
    Errors,
    Latency_p75,
    Latency_p90,
    publicDomainName,
    dt.entity.service

| sort StatusSort asc, Availability asc

[PVThach]

Perfect, thanks!