How to make a new Timeseries by calculating data from 2 individual Timeseries?

[Hillman]

As the PostgreSQL extension does not ingest the Cache Hit Ratio metrics, we have to do manual calculation with the following formula:

100 * blocks_hit / ( blocks_hit + blocks_read )

blocks_hit and blocks_read are 2 Timeseries and I want to use the above formula to calculate and make a new Timeseries for Cache Hit Ratio. Is it possible to do so?

[dannemca]

Yes, it is possible.

For example, I will paste here a DQL that sums up the values from %CPU and %MemUsage, using the lookup command , https://docs.dynatrace.com/docs/platform/grail/dynatrace-query-language/commands/correlation-and-joi... 

timeseries { mem = avg(dt.host.memory.usage) }, by: { dt.entity.host }
| fieldsAdd dt.entity.host.name = entityName(dt.entity.host)
| lookup [timeseries { cpu = avg(dt.host.cpu.usage) }, by: { dt.entity.host }
| fieldsAdd dt.entity.host.name = entityName(dt.entity.host)], sourceField:dt.entity.host, lookupField:dt.entity.host
| fieldsAdd cpu_mem = mem[] + lookup.cpu[]

I am using the host name as matching field, so we calculate the right value for the right entity.

You can remove the original fields at the end, or configure it on visualization settings.

Try with your metrics and let us know.

[krzysztof_hoja · Accepted solution]

In this case it can be simplified and lookup is not needed. With timeseries command you can retrieve multiple metrics in single command:

timeseries { mem = avg(dt.host.memory.usage), cpu = avg(dt.host.cpu.usage) }, by: { dt.entity.host }, union: true
| fieldsAdd dt.entity.host.name = entityName(dt.entity.host)
| fieldsAdd cpu_mem = mem[] + cpu[]

[Hillman]

Thank you @krzysztof_hoja , @dannemca , I am able to create a timeseries with the cache hit ratio!

I have got one more question. When I try to create a Single Value chart with the following DQL.

timeseries { blocks_read = avg(postgres.blks_read.count), blocks_hit = avg(postgres.blks_hit.count) }, by: { device.name, database }, union: true
| fieldsAdd cache_hit_ratio = (100 * blocks_hit[] / (blocks_read[] + blocks_hit[]))
| fieldsAdd `Cache hit ratio` = arrayAvg(cache_hit_ratio)
| fieldsRemove interval

I have found the sparkline cannot be displayed if I assign the timeseries cache_hit_ratio as the sparkline. Do you have any idea why does that happen?

[dannemca]

That's because you removed the interval field. Comment/remove the last line and test again.