topic Re: How to calculate availibility score with timeseries? in DQL

How to calculate availibility score with timeseries?

jegron — Mon, 15 Jul 2024 13:19:53 GMT

Hi,

I would like to calculate availability for Database :

It must be shown in the ratio when there is no data or Offline Database :

How can I do it with DQL?

Julien

Re: How to calculate availibility score with timeseries?

krzysztof_hoja — Mon, 15 Jul 2024 19:38:56 GMT

If I understand correctly the logic of this metric:

there is 0 (value) in interval for a specific database and state when database is in this state
there is nothing (visible as null in timeseries) for a specific database and state when database is in another state or is not monitored

using 0 makes it difficult to use metric value directly, but fortunately we have rollup:total (formerly known as rollup:count) which will have value of contributions or nothing when metric was not reported.

If we define availability as a % of moments when database was online vs. moments when it was in any state, this query should do the job:

timeseries state = sum(`sql-server.databases.state`, rollup: total), by: { database.state, database }, filter: database.state=="ONLINE" | lookup [ timeseries state_all = sum(`sql-server.databases.state`, rollup: total), by: { database.state, database } ], sourceField:database, lookupField:database, fields:{state_all} | fields interval, timeframe, database, availability=100*state[]/state_all[]

I also assumed that for intervals with no state, we should have no data on the chart (or null in timeseries)

Kris

Re: How to calculate availibility score with timeseries?

jegron — Tue, 16 Jul 2024 08:27:37 GMT

Thanks!

How can I also include "No Data" as "Failed" in the score?

Re: How to calculate availibility score with timeseries?

krzysztof_hoja — Tue, 16 Jul 2024 15:21:25 GMT

Yes. default: parameter is helpful here.

timeseries state = sum(`sql-server.databases.state`, rollup: total, default:0), by: { database.state, database }, filter: database.state=="ONLINE" | lookup [ timeseries state_all = sum(`sql-server.databases.state`, rollup: total, default:1), by: { database.state, database } ], sourceField:database, lookupField:database, fields:{state_all} | fields interval, timeframe, database, availability=100*state[]/state_all[]

Re: How to calculate availibility score with timeseries?

jegron — Thu, 18 Jul 2024 06:57:32 GMT

Thanks ! 👏👌