Resolve DQL Error When Query Returns Multiple Metrics

[nilkanthj]

created below DQL to monitor the Disk with used.percent and disk.avail condition however we are getting below error message.

Could you please help to fix this issue ?

DQL
timeseries { averageDiskU = avg(dt.host.disk.used.percent), averageDiskA = avg(dt.host.disk.avail) }, by: { dt.entity.disk, dt.entity.host, host.name, dt.entity.host_group }, filter: {contains(entityName(dt.entity.disk), "sapdata") and dt.entity.host_group == "HOST_GROUP-B86DECA59DA2RM07" }
| lookup [fetch dt.entity.disk  | fieldsAdd component = arrayFirst(tags) ], lookupField:id , sourceField:dt.entity.disk
| fieldsAdd dt.entity.disk.name = entityName(dt.entity.disk)
| fieldsadd averageDiskUsed = toLong(arrayLast(averageDiskU))
| fieldsAdd averageDiskAvailable = toLong(arrayLast(averageDiskA))
| filter averageDiskUsed >= 85
| filter averageDiskAvailable <= 32212254720
| fieldsAdd Priority = if(averageDiskUsed >= 95 and averageDiskAvailable <= 10737418240 , "P1", else:(if(averageDiskUsed >= 90 and averageDiskAvailable <= 21474836480, "P2", else:(if(averageDiskUsed >= 85 and averageDiskAvailable <= 32212254720, "P3", else:"OK")))))

Error message -- The query results in multiple metrics, but anomaly detectors only support single metric

[IzabelaRokita]

Hey @nilkanthj ,
I just wanted to check in and see if you still need help with this. If so, I’d be happy to look into it for you! 😊
Please let me know what works best for you.

[danijela]

Hello @IzabelaRokita ,

I would need help with my query:

timeseries {abap_dumps = sum(log.sap.abap.st22.dumps_count, rollup:sum), default:0}, by:{log.source},
filter:{matchesValue(log.source, {"P02", "P03", "P13", "P15","P06"})}
| lookup [fetch `dt.entity.sap:abap:system`], sourceField:log.source,lookupField:entity.name, fields: {id}
| summarize { dumps=sum(abap_dumps[]) } , by: {timeframe, interval}
| fieldsadd sum=arrayMovingSum(dumps, 15)
| fieldsRemove dumps

[MaximilianoML]

Hello @danijela,

In this case the important point is that timeseries returns an array of datapoints. Because of that, you should apply arrayMovingSum() directly on the timeseries array.

The part below changes the shape of the data:

| summarize { dumps=sum(abap_dumps[]) } , by: {timeframe, interval}

After this, dumps is no longer the same timeseries array structure, so arrayMovingSum(dumps, 15) will not behave as expected.

You can try this version below:

timeseries {
    abap_dumps = sum(log.sap.abap.st22.dumps_count, rollup: sum),
    default: 0
},
by: { log.source },
filter: {
    matchesValue(log.source, {"P02", "P03", "P13", "P15", "P06"})
}
| lookup [
    fetch `dt.entity.sap:abap:system`
],
sourceField: log.source,
lookupField: entity.name,
fields: { id }
| fieldsAdd dumps_15_intervals = arrayMovingSum(abap_dumps, 15)
| fields log.source, id, timeframe, interval, abap_dumps, dumps_15_intervals

 I hope it helps you 😊