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
    Share:|

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


    PRODUCT:

    TrueSight Capacity Optimization


    COMPONENT:

    Capacity Optimization


    APPLIES TO:

    All TSCO versions



    QUESTION:

    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 example
    Oct 30, 2:00 pm Metric A sum iteration type 1
    Oct 30, 2:00 pm Metric A sum iteration type 2
    Oct 30, 2:00 pm Metric A sum iteration type 3
    Oct 30, 2:00 pm Metric B count iteration type 1
    Oct 30, 2:00 pm Metric B count iteration type 2
    Oct 30, 2:00 pm Metric B count iteration type 3


    I 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.





     


    ANSWER:

     

    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.password=******
    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.grouping.object.golevels.loadifempty=false
    general.log.filename=%BASE/log/%AYEAR%AMONTH%ADAY%AHOUR%MINUTE%TASKID
    general.log.level=1
    general.options.onemptydataset=ABORT
    general.simulation=false
    load.1.filename=%BASE/output/%AYEAR%AMONTH%ADAY%AHOUR%ZPROG%DSID%TASKID
    load.1.module=ETL::loader::CSVL
    load.2.filename=%BASE/output/%AYEAR%AMONTH%ADAY%AHOUR%ZPROG%DSID%TASKID
    load.2.module=ETL::loader::Caplan2CTLL
    load.2.oracle.location=INTERNAL
    load.module.number=2
    mask.ignorevalidation=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