cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

How to calculate availibility score with timeseries?

jegron
DynaMight Champion
DynaMight Champion

Hi,

I would like to calculate availability for Database :

jegron_0-1721045906194.png

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

jegron_2-1721047995458.png

How can I do it with DQL?

Julien

 

 

Observability Engineer at Phenisys - Dynatrace Professional
4 REPLIES 4

krzysztof_hoja
Dynatrace Champion
Dynatrace Champion

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

krzysztof_hoja_0-1721072218123.png

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

Kris

jegron
DynaMight Champion
DynaMight Champion

Thanks!

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

Observability Engineer at Phenisys - Dynatrace Professional

krzysztof_hoja
Dynatrace Champion
Dynatrace Champion

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

krzysztof_hoja_0-1721143258371.png

 

jegron
DynaMight Champion
DynaMight Champion

Thanks ! 👏👌

Observability Engineer at Phenisys - Dynatrace Professional

Featured Posts