Page tree
Skip to end of metadata
Go to start of metadata

You can use the query examples in this topic as-is to generate chargeback report results in your infrastructure simply by specifying custom values for the modelid and date period (to_date) parameters.

These queries retrieve data from Chargeback related public views that are present in the BMC TrueSight Capacity Optimization database. Public views are produced by the Advanced Reporting module, and can be accessed using the CPIT_REP account.

To view query examples based on public views for various chargeback categories, refer to the following sections in this topic:

Chargeback by Target

Description: This query gives a summary of the charges for each of the targets on a daily basis for a specific model and period.

In this example:

  • modelid is 29
  • Period starts 01/11/2010 and ends 01/01/2011

Query

SELECT t0.*, md.currency
FROM (
SELECT targetid,
  MIN (targetname) AS targetname,
  ts,
  SUM (value) as cost
FROM
  (SELECT m.targetid,
    m.targetname,
    m.metric,
    md.ts,
    md.value
  FROM PV_CB_TARGET_DATA_DAY md,
    PV_CB_TARGET_METRIC m
  WHERE m.TARGETMETRICID = md.targetmetricid
  AND m.targetid        IN
    ( SELECT TARGETIDCHILD FROM PV_CB_TARGET_PARENTSHIP WHERE MODELID = 29
    )
  AND metric = 'BYCOSUBKEY_COST'
  )
where ts >= to_date ('01/11/2010','dd/mm/yyyy')
and ts < to_date ('01/01/2011','dd/mm/yyyy')
GROUP BY targetid, ts
order by ts ) t0, PV_CB_MODEL md
where md.modelid = 29

Result

TARGETID

TARGETNAME

TS

COST

CURRENCY

2318

ACME - INDUSTRIAL PRODUCTS

01-NOV-10

1.23

USD

2319

ACME - CONSUMER PRODUCTS

01-NOV-10

2.07

USD

2320

ACME - SALES & MARKETING

01-NOV-10

0.22

USD

2318

ACME - INDUSTRIAL PRODUCTS

02-NOV-10

1.23

USD

2319

ACME - CONSUMER PRODUCTS

02-NOV-10

2.07

USD

2320

ACME - SALES & MARKETING

02-NOV-10

0.22

USD

2318

ACME - INDUSTRIAL PRODUCTS

03-NOV-10

1.23

USD

2319

ACME - CONSUMER PRODUCTS

03-NOV-10

2.07

USD

2320

ACME - SALES & MARKETING

03-NOV-10

0.22

USD

...

...

...

...

...

Chargeback by Service and Cost Object

Description: This query gives a summary of the charges of the services associated with targets for a specific model and period.

In this example:

  • modelid is 29
  • Period starts 11/10/2009 and ends 17/10/2009

Query

SELECT t0.*, md.currency
FROM (
SELECT targetname,
co1.name as servicename,
co2.name as costobjname,
cost
FROM (SELECT targetid,
  MIN (targetname) AS targetname,
  COCOSTOBJKEY1,
  COSTOBJKEY,
  SUM (value) AS cost
FROM
  (SELECT m.targetid,
    m.targetname,
    m.metric,
    m.cocostobjkey1,
    m.costobjkey,
    md.ts,
    md.value
  FROM PV_CB_TARGET_DATA_DAY md,
    PV_CB_TARGET_METRIC m
  WHERE m.TARGETMETRICID = md.targetmetricid
  AND m.targetid        IN
    ( SELECT TARGETIDCHILD FROM PV_CB_TARGET_PARENTSHIP WHERE MODELID = 29
    )
  AND metric = 'BYCOSUBKEY_COST'
  AND ts    >= to_date ('11/10/2009','dd/mm/yyyy')
  AND ts     < to_date ('17/10/2009','dd/mm/yyyy')
  )
GROUP BY targetid, COCOSTOBJKEY1, COSTOBJKEY) dt,
  PV_CB_COST_OBJECT CO1,
  PV_CB_COST_OBJECT CO2
  where dt.COCOSTOBJKEY1 = co1.costobjkey
  and dt.COSTOBJKEY = co2.costobjkey ) t0, PV_CB_MODEL md
where md.modelid = 29
order by servicename, costobjname

Result

TARGETNAME

SERVICENAME

COSTOBJNAME

COST

CURRENCY

ACME - CONSUMER PRODUCTS

AFFID

Storage SAN

34077.27

USD

ACME - CONSUMER PRODUCTS

BILGP

Storage SAN

84159.81

USD

ACME - CONSUMER PRODUCTS

BILPW

Storage SAN

22786.081

USD

ACME - CONSUMER PRODUCTS

CPGAS

Server

3.03

USD

ACME - CONSUMER PRODUCTS

CPGAS

Storage SAN

41481.96

USD

ACME - CONSUMER PRODUCTS

DEMAG

Storage SAN

14343.43

USD

ACME - CONSUMER PRODUCTS

SITET

Server

10.77

USD

ACME - CORPORATE

CTRM

Storage SAN

54978.69

USD

ACME - CORPORATE

ECM

Storage SAN

16.00

USD

ACME - CORPORATE

ECM

Database-ENECMP1

0.0038

USD

ACME - CORPORATE

ECM

Database-OTHER

0.17

USD

ACME - CORPORATE

GFA

Storage SAN

149680.89

USD

ACME - CORPORATE

GHG

Storage SAN

5373.97

USD

ACME - SALES & MARKETING

VBM

Server

7.79

USD

ACME - SALES & MARKETING

VBM

Storage SAN

28854.803

USD

ACME - SERVICES

ERM

Database-ENERMP1

0.12

USD

ACME - SERVICES

ERM

Database-OTHER

0.17

USD

ACME - SERVICES

ERM

Storage SAN

16.008

USD

...

...

...

...

...

Chargeback by Cost Object

Description: This query gives a summary of the charges corresponding to each cost object associated with targets for a specific model and period.

In this example:

  • modelid is 29
  • Period starts 11/10/2009 and ends 17/10/2009

Query

SELECT t0.*,
  md.currency
FROM
  (SELECT TARGETNAME,
    co.name AS costobjname,
    cost
  FROM
    (SELECT TARGETID,
      MIN(TARGETNAME) AS TARGETNAME,
      COSTOBJKEY,
      SUM (value) AS cost
    FROM
      (SELECT m.targetid,
        m.targetname,
        m.metric,
        m.cocostobjkey1,
        m.costobjkey,
        md.ts,
        md.value
      FROM PV_CB_TARGET_DATA_DAY md,
        PV_CB_TARGET_METRIC m
      WHERE m.TARGETMETRICID = md.targetmetricid
      AND m.targetid        IN
        ( SELECT TARGETIDCHILD FROM PV_CB_TARGET_PARENTSHIP WHERE MODELID = 29
        )
      AND metric = 'BYCOSUBKEY_COST'
      AND ts    >= to_date ('11/10/2009','dd/mm/yyyy')
      AND ts     < to_date ('17/10/2009','dd/mm/yyyy')
      )
    GROUP BY TARGETID, COSTOBJKEY
    ) dt,
    PV_CB_COST_OBJECT CO
  WHERE dt.COSTOBJKEY = co.costobjkey
  ) t0 ,
  PV_CB_MODEL md
WHERE md.modelid = 29
ORDER BY TARGETNAME, costobjname

Result

TARGETNAME

COSTOBJNAME

COST

CURRENCY

ACME - CONSUMER PRODUCTS

Server

16.6417

USD

ACME - CONSUMER PRODUCTS

Storage SAN

417.881

USD

ACME - CORPORATE

Database-ENECMP1

0.0015

USD

ACME - CORPORATE

Database-OTHER

0.047

USD

ACME - CORPORATE

Storage SAN

863.353

USD

ACME - INDUSTRIAL PRODUCTS

Server

8.82

USD

ACME - INDUSTRIAL PRODUCTS

Storage SAN

113.344

USD

ACME - SALES & MARKETING

Server

1.576

USD

ACME - SALES & MARKETING

Storage SAN

629.85

USD

ACME - SERVICES

Database-ENERMP1

0.038

USD

ACME - SERVICES

Database-OTHER

0.047

USD

ACME - SERVICES

Storage SAN

4.564

USD

...

...

...

...

Chargeback by Consumption

Description: This query gives a summary of the consumption of resources like CPU utilization, memory utilization, by the cost object associated with the targets for a specific model and period.

In this example:

  • modelid is 29
  • Period starts 01/10/2009 and ends 01/11/2009

Query

SELECT
  t0.TARGETNAME,
  t0.costobjname,
  os.propvalue AS type,
  t0.metadata,
  t0.consumption,
  cr.consunit,
  t0.ts
FROM
  (SELECT TARGETNAME,
    co.name AS costobjname,
    co.costobjid,
    co.COSTOBJKEY,
    costobjsubkey,
    METADATA,
    ts,
    consumption
  FROM
    (SELECT TARGETID,
      MIN(TARGETNAME) AS TARGETNAME,
      COSTOBJKEY,
      costobjsubkey,
      METADATA,
      ts,
      SUM (value) AS consumption
    FROM
      (SELECT m.targetid,
        m.targetname,
        m.metric,
        m.costobjkey,
        m.costobjsubkey,
        M.METADATA,
        md.ts,
        md.value
      FROM PV_CB_TARGET_DATA_DAY md,
        PV_CB_TARGET_METRIC m
      WHERE m.TARGETMETRICID = md.targetmetricid
      AND m.targetid        IN
        ( SELECT TARGETIDCHILD FROM PV_CB_TARGET_PARENTSHIP WHERE MODELID = 29
        )
      AND metric = 'BYCOSUBKEY_CONSUMPTION'
      AND ts    >= to_date ('01/10/2009','dd/mm/yyyy')
      AND ts     < to_date ('01/11/2009','dd/mm/yyyy')
      )
    GROUP BY TARGETID,
      COSTOBJKEY,
      costobjsubkey,
      METADATA,
      ts
    ) dt,
    PV_CB_COST_OBJECT CO
  WHERE dt.COSTOBJKEY = co.costobjkey
  ) t0,
  pv_cb_cost_object_subkey os,
  pv_cb_cost_rate cr
WHERE os.costobjsubkey = t0.costobjsubkey
AND OS.COSTOBJID       = t0.costobjid
AND os.propname        = 'label'
AND cr.costobjkey      = t0.COSTOBJKEY
AND cr.costobjsubkey   = t0.costobjsubkey
AND cr.metadata        = t0.metadata
AND cr.sincedate      <= to_date ('01/10/2009','dd/mm/yyyy')
AND cr.todate          > to_date ('01/11/2009','dd/mm/yyyy')
ORDER BY TARGETNAME,
  ts,
  t0.costobjname,
  t0.costobjsubkey

Result

TARGETNAME

COSTOBJNAME

TYPE

METADATA

CONSUMPTION

CONSUNIT

TS

ACME - CONSUMER PRODUCTS

Server

CPU Allocation

WebFarm=BEA 8:ServerType=ENT

264

CPUHour

01-OCT-09

ACME - CONSUMER PRODUCTS

Server

CPU Allocation

WebFarm=BEA 8:ServerType=MR

108

CPUHour

01-OCT-09

ACME - CONSUMER PRODUCTS

Server

CPU Utilization

WebFarm=BEA 8:ServerType=ENT

7.25562

GHzHour

01-OCT-09

ACME - CONSUMER PRODUCTS

Server

CPU Utilization

WebFarm=BEA 8:ServerType=MR

9.459

GHzHour

01-OCT-09

ACME - CONSUMER PRODUCTS

Server

Memory Utilization

WebFarm=BEA 8:ServerType=ENT

85.804

GBHour

01-OCT-09

ACME - CONSUMER PRODUCTS

Server

Memory Utilization

WebFarm=BEA 8:ServerType=MR

37.83

GBHour

01-OCT-09

ACME - CONSUMER PRODUCTS

Server

CPU Allocation

WebFarm=BEA 8:ServerType=MR

108

CPUHour

02-OCT-09

ACME - CONSUMER PRODUCTS

Server

CPU Allocation

WebFarm=BEA 8:ServerType=ENT

264

CPUHour

02-OCT-09

ACME - CONSUMER PRODUCTS

Server

CPU Utilization

WebFarm=BEA 8:ServerType=ENT

6.317

GHzHour

02-OCT-09

ACME - CONSUMER PRODUCTS

Server

CPU Utilization

WebFarm=BEA 8:ServerType=MR

23.206

GHzHour

02-OCT-09

ACME - CONSUMER PRODUCTS

Server

Memory Utilization

WebFarm=BEA 8:ServerType=ENT

76.85

GBHour

02-OCT-09

ACME - CONSUMER PRODUCTS

Server

Memory Utilization

WebFarm=BEA 8:ServerType=MR

44.71

GBHour

02-OCT-09

Related topic

Accessing data using public views