# In CO, what is the best way to import a metric that can be calculated via a formula using other metrics imported by an ETL?

Version 3

This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.

PRODUCT:

COMPONENT:

APPLIES TO:

## In TSCO, is it possible to define a formula that will calculate an output value within CO using an arbitrary set of metrics as the input into that formula?For example, say that 'Metric A' is something like "Bytes transferred" by network interface 0, 1, and 2 and 'Metric B' is something like "Packets sent" by network interface 0, 1, 2 so the resulting calculation requested is average bytes per packet over the all network interfaces (A[0] + A[1] + A[2]) / B[0] + B[1] + B[2]).I would like to take the values of two metrics at a given time and apply math functions to create a new value. How would I do that in either the console or dashboard?For exampleOct 30, 2:00 pm Metric A sum iteration type 1Oct 30, 2:00 pm Metric A sum iteration type 2Oct 30, 2:00 pm Metric A sum iteration type 3Oct 30, 2:00 pm Metric B count iteration type 1Oct 30, 2:00 pm Metric B count iteration type 2Oct 30, 2:00 pm Metric B count iteration type 3I would like to create a new metric (or result I can graph) called Metric C which is the (sum of all Metric A Type 1-3) / (sum of all Metric B type 1-3) thus producing an average iteration time for the Oct 30, 2:00 pm iteration data points. Then I will graph this value over time.

Legacy ID:KA359617

This is the mechanism we provide to calculate this (basically it's an how to build a derived metric via the ETL import):

The idea is that the fastest way to calculate this is modifying the custom ETL that already imports that data to calculate the metric and import it as a custom one.
The necessary condition for doing this is that all metrics to be "aggregated" (A,B and C in the issue) are imported from the same ETL.

The other option would be creating an auto ETL extractor, basing on a "generic - database extractor" template (a custom ETL extractor that extracts data from CO DB and puts them back into CO DB after some kind of manipulation).

You can use this extractor provided with TSCO, composing the appropriate query.

For example, the query below gets values from MEM_PAGE_IN metric and multiplies them by 256, to load them back into the custom metric 'MEM_PAGE_IN_RATE_MULTIPLE_C'.

This is an example of a full autoetl configuration.

name=Default
environ [PROD, DEV, TEST]=PROD
etl.batch.directory=\${BASE_DIR}/etl
etl.batch.name=\${ETL_RUN}
extract.database.url=dbi:Oracle:bmcprd.mandg.co.uk2
extract.database.user=cpit_app2
extract.lastcounter.column=TS
extract.module=ETL::extractor::DMSQLE
extract.sql.param.number=0
extract.sql.query=select dh.ts as TS, 300 as DURATION, 'ENTID#'%pipe%pipe d.sysid as DS_SYSNM,
'MEM_PAGE_IN_RATE_MULTIPLE_C' as OBJNM, dh.avgvalue*256 as VALUE from pv_sys_data_detail dh, pv_sys_metric d where
dh.sysmetricid=d.sysmetricid and dh.sysmetricid in (select distinct(sysmetricid) from pv_sys_metric where metricname='MEM_PAGE_IN_RATE')
and insertTS < '2011-10-26 17:00:00'

general.dataset.idlist=7
general.log.level=1
general.options.onemptydataset=ABORT
general.simulation=false

This config uses only PUBLIC VIEWS, the query with the syntax above will work from CO UI. If you need to use a sql client to test it you need to replace the %PIPE with the "|"   character, so it will be:

select dh.ts as TS, 300 as DURATION, 'ENTID#'||d.sysid as DS_SYSNM,
'MEM_PAGE_IN_RATE_MULTIPLE_C' as OBJNM, dh.avgvalue*256 as VALUE from pv_sys_data_detail dh, pv_sys_metric d where
dh.sysmetricid=d.sysmetricid and dh.sysmetricid in (select distinct(sysmetricid) from pv_sys_metric where metric='MEM_PAGE_IN_RATE') and TS <TO_DATE('2012-10-22 00:00:00','YYYY-MM-DD HH24:MI:SS')

Article Number:

## 000029653

Article Type:

## FAQ/Procedural

Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles