use metric dimension as a value in a timeseries query

[dougwillis]

I have some metrics coming into Dynatrace from a Prometheus scrape.
The metric shows up as pg_meta_info and has a static value of 1 but many values setup as dimensions. One of these dimesnsions is version_num which contains an integer representation of the PGSQL engine version.

Is there a way to use the dimension value, which is in the format 140023, 160014, 160015, to show the dates that the postgresql database was upgraded?

I can see the changes of version using the query
timeseries { avg(pg_meta_info), value.A = avg(pg_meta_info, scalar: true) }, by: { ins, ver_num }
but that does not show the actual dates the database version changes as the metric value is always 1.

[Mohamed_Hamdy]

You can derive this from the first time each PostgreSQL version (ver_num) is observed for each instance (ins).

For example:

timeseries {
A = avg(pg_meta_info),
timestamp = start()
},
by: { ins, ver_num },
interval: 1h
| fieldsAdd first_seen =
arrayFirst(iCollectArray(if(isNotNull(A[]), timestamp[])))
| fields ins, ver_num, first_seen
| sort ins asc, first_seen asc

Since ver_num is a dimension, each PostgreSQL version creates a separate time series. The query finds the first non-null datapoint for each version and returns its timestamp.

first_seen should therefore indicate when Dynatrace first observed that PostgreSQL version.

Just keep in mind that this is the first observation within the selected timeframe, so the timeframe needs to start before the upgrade. The accuracy will also depend on the selected interval/scrape frequency.