How to calculate availibility score with timeseries?

[jegron]

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

[krzysztof_hoja · Accepted solution]

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

[jegron]

Thanks!

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

[krzysztof_hoja]

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[]

[jegron]

Thanks ! 👏👌