Query examples

This following sections provide examples of SQL query scripts that you can use to perform a variety of operations on your database.

Following queries are based on the public views and best for the dashboard/report developers to understand the public views. In these queries we have used the systemId,ApplicationId,workloadId which are unique parameters to filter the resultset.

Given a domain, find all systems currently in the domain

In the console, find the domain ID of any domain by moving the pointer on top of the domain symbol in the detail page of the domain. The domain ID is listed in the pop-up with the label "APP - id". In this example, we find its value is 2004.

We would like to list all of the systems that are currently children of this domain. For each of these, we would like to print its system ID, its user-assignable name, and its (printable) system type.

We will use the following two public views:

  • PV_APPL_SYS
  • PV_SYSTEM
SELECT SYS.SYSID, SYS.NAME, SYS.SYSTYPE
FROM
  PV_APPL_SYS A2S
 JOIN PV_SYSTEM SYS ON A2S.SYSID = SYS.SYSID
WHERE A2S.APPID = 2004

The result of the above query shows the following result.

SYSID,NAME,SYSTYPE
1067465,xenvm-rh52-bpa01,Virtual Machine - Xen
1067464,xenvm-w28-bpa01,Virtual Machine - Xen
1067463,xenvm-w23-bpa01,Virtual Machine - Xen
1067462,xenvm-w23-bpa02,Virtual Machine - Xen
1067461,xenvm-rh54-bpa02,Virtual Machine - Xen
1067460,Control domain on host isr-xen-bpa-01,Virtual Machine - Xen

Given a system and a time series metric, find daily values for the last 7 days.

Let us say that CPU_UTIL is the name of the metric we want. The unique metric names for all metrics are listed in the console under Administration, Data Warehouse, Datasets. This metric is a "global" metric, i.e., we expect to see only a single instance per system. So we expect the output to contain seven samples, one for each of the last seven days.

In the console, find the system ID of any system by moving pointer on the system symbol in the detail page of the system. The system ID is listed in the pop-up with the label "SYS - id". In this example, we find its value is 970889.

We will use the following two public views:

  • PV_SYS_METRIC
  • PV_SYS_DATA_D
SELECT VAL.TS, VAL.AVGVALUE, VAL.MINVALUE, VAL.MAXVALUE
FROM
  PV_SYS_METRIC MET
 JOIN PV_SYS_DATA_D VAL ON MET.SYSMETRICID = VAL.SYSMETRICID
WHERE
  MET.SYSID =  970889
 AND MET.METRIC = 'CPU_UTIL'
 AND VAL.TS >= TRUNC(SYSDATE - 7) AND VAL.TS < TRUNC(SYSDATE)
ORDER BY VAL.TS;

TRUNC function returns a date truncated to a specific unit of measure.

Syntax: trunc ( date, [ format ] )
date is the date to truncate.

format is the unit of measure to apply for truncating. If the format parameter is omitted, the trunc function will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off.

For Example

trunc(to_date('22-AUG-03'), 'YEAR')

would return '01-JAN-03'

trunc(to_date('22-AUG-03'), 'Q')

would return '01-JUL-03'

trunc(to_date('22-AUG-03'), 'MONTH')

would return '01-AUG-03'

trunc(to_date('22-AUG-03'), 'DDD')

would return '22-AUG-03'

trunc(to_date('22-AUG-03'), 'DAY')

would return '17-AUG-03'

The PV_SYS_DATA_D view contains all the daily values, including average, minimum, and maximum. We chose to extract all three for each day.

The results are shown below. Results are sorted by SQL date because of the ORDER BY clause. (The format of the date in the output will depend on your SQL client configuration.)

"TS","AVGVALUE","MINVALUE","MAXVALUE"
2012-10-27,0.5205622444786244633333333333333333333333,0.04644177800696617,0.909843240311659
2012-10-28,0.51523396303659872775,0.058212300957076496,0.9260009638056216
2012-10-29,0.5242105897258791719166666666666666666667,0.040380093025864566,0.998268535025455
2012-10-30,0.5167022143604880517125,0.0000992992295354711,0.8905891658829533
2012-10-31,0.5263632273780292395,0.004111277113975048,0.9406337832242007
2012-11-01,0.52791124013206826625,0.00826028292142783,0.9425309659299393
2012-11-02,0.536305469803727208625,0.006290689518372777,0.9660488590464125

It is implied that SUBMETRICNAME and LOCATIONID are set to defaults. This is usually true.

AND MET.SUBMETRICNAME = 'GLOBAL' AND MET.LOCATIONID = 0

Find the latest value of a configuration metric of a system

Given a system and a config metric, find the latest value of that metric for the system.

Let us say that CPU_MHZ is the name of the metric we want. The unique metric names for all metrics are listed in the console under Administration, Data Warehouse, Datasets. This metric is a "global" metric, i.e., we expect to see only a single instance per system. So we expect the output to contain one sample.

In the console, find the system ID of any system by moving pointer on the system symbol in the detail page of the system. The system ID is listed in the pop-up with the label "SYS - id". In this example, we find its value is 970889.

We will use the following two public views:

  • PV_SYS_METRIC
  • PV_SYS_CONF_DATA_LAST
SELECT VAL.VALUE AS CPU_MHZ
FROM
    PV_SYS_METRIC MET
   JOIN PV_SYS_CONF_LAST VAL ON MET.SYSMETRICID = VAL.SYSMETRICID
WHERE
  MET.SYSID = ? -- given a system
  AND MET.METRIC = 'CPU_MHZ'

Given a domain and a config metric, find the latest value for each system that is currently a child of that domain.

SELECT MET.SYSID, MET.NAME, VAL.VALUE AS CPU_MHZ
FROM  PV_APPL_SYS A2S
     JOIN PV_SYS_METRIC MET ON A2S.SYSID = MET.SYSID
     JOIN PV_SYS_CONF_LAST VAL ON MET.SYSMETRICID = VAL.SYSMETRICID
WHERE
  A2S.APPID = ?  -- given a domain
  AND MET.METRIC = 'CPU_MHZ' AND MET.SUBMETRICNAME = 'GLOBAL' AND MET.LOCATIONID = 0 -- given a metric
ORDER BY MET.SYSID;

Listing business drivers present in a domain

The following query returns the list of business drivers belonging to a particular domain. The same structure can be used to extract system data.

SELECT a.applname, w.NAME AS wkldname
FROM pv_workload w, pv_application a, pv_appl_wkld j
WHERE w.wkldid = j.wkldid AND a.appid = j.appid AND a.appid = 1

Retrieving business driver data

The following query returns the daily total and hourly average for a business driver in the last 30 days. The same structure can be used to extract system data.

SELECT d.wkldmetricid, TRUNC (ts) ts, SUM (sumvalue) sumvalue,
    AVG (sumvalue) avghvalue, MIN (m.NAME) NAME,
    MIN (m.metricname) metricname, MIN (m.submetricname) submetricname,
    MIN (locationname) locationname
FROM pv_wkld_metric m, pv_wkld_data_day d
WHERE m.wkldmetricid = d.wkldmetricid
    AND ts >= TRUNC (SYSDATE - 30)
    AND ts < TRUNC (SYSDATE)
    AND m.wkldid = 1
GROUP BY d.wkldmetricid, TRUNC (ts)

Note

This query does not return rows with NULL values for missing samples.

Retrieving business driver data (including missing samples)

The following query provides an example of how to use the get_date_list function in a larger query:

SELECT t_int.wkldmetricid, t_int.ts ts, SUM (sumvalue) sumvalue,
    AVG (sumvalue) avghvalue, MIN (t_int.NAME) NAME,
    MIN (t_int.metricname) metricname,
    MIN (t_int.submetricname) submetricname,
    MIN (t_int.locationname) locationname
FROM pv_wkld_data_day d, (
    SELECT m.wkldmetricid, m.metricname, m.submetricname,
          m.locationname, m.NAME, m.wkldid, t.COLUMN_VALUE ts
    FROM pv_wkld_metric m,
         TABLE(get_date_list(TRUNC(SYSDATE-30),TRUNC(SYSDATE),3600)) t
    WHERE m.wkldid = 1
     ) t_int
WHERE t_int.wkldmetricid = d.wkldmetricid(+)
    AND t_int.ts = d.ts(+)
GROUP BY t_int.wkldmetricid, t_int.ts
ORDER BY t_int.wkldmetricid, t_int.ts

In order to include missing samples on Enterprise Report, use the following SQL function:

get_date_list (p_sincedate, p_todate, p_period)

The parameters contained in this function are:

  • p_sincedate: Start of the time interval
  • p_todate: End of the time interval
  • p_period: Sample time period in seconds.

For example, to retrieve all data from last week with hourly resolution, you can use the following parameters:

get_date_list(trunc(sysdate-7),trunc(sysdate),3600)

Retrieving system metric values

The following query returns the current value of a certain system metric. The same structure can be used for business driver metrics.

SELECT SYSMETRICID, VALUE AS CURRENT_VALUE
FROM PV_SYS_CONF_VARIATION
WHERE trunc(sysdate) < todate
    AND trunc(sysdate) > sincedate and sysmetricid = 19088

In this query, the earliest value of the sincedate field assumed by a metric is "01-01-1970", while the value of the todate field is "31-12-9999".

In BMC TrueSight Capacity Optimization '31-12-9999' date indicates a  ‘to’ date of ‘forever’.  Normally only object relationships have that date, config and statistical metrics do not.

Retrieving top 10 systems based on CPU consumption

The following query returns a lit of the "top ten" generic systems within the specified domain that have the highest CPU consumption over the past 7 days.

SELECT *
FROM (
    SELECT m.sysmetricid, MIN (m.name) systemname,
          wavg (wvalue (d.avgvalue, d.duration)) avgvalue
    FROM pv_sys_data_d d JOIN pv_sys_metric m ON (m.sysmetricid = d.sysmetricid)
    WHERE m.metric = 'CPU_UTIL'
         AND m.submetricname = 'GLOBAL'
         AND m.locationid = 0
         AND d.ts >= trunc(SYSDATE - 7)
         AND d.ts < trunc(SYSDATE)
         AND m.systypeid = 0
         AND m.sysid IN (
              SELECT sysid
              FROM pv_appl_sys
              WHERE appid = 23439
               )
    GROUP BY m.sysmetricid
    ORDER BY avgvalue DESC
     )
WHERE ROWNUM <= 10

In this query, systems belonging to a specific domain are filtered using the following code:

AND m.sysid IN (
    SELECT sysid
    FROM pv_appl_sys
    WHERE appid = 23439
     )

The time filter is substantiated by the following code:

AND d.ts >= trunc(SYSDATE - 7)
AND d.ts < trunc(SYSDATE)

Given a system, find all systems that are children or descendants of that system currently

The following query returns a list of systems those are children's of an given system. Here idparent should be replaced with parent systemId to get the result.

   select NAME,sysid,systype from pv_system
        where sysid in (select idchild from PV_ENT_PARENTSHIP_CURRENT
              where structureidparent = 'SYS'
              AND idparent=?)

Given a domain, find all systems that are children or descendants of children of that domain currently

The following query returns a list of systems those are children's of an given domain as well as the systems from the child domain. Here APPID should be replaced with parent domainID to get the result.

 SELECT *
FROM PV_SYSTEM SYS JOIN (
   SELECT AS1.APPID, AS1.SYSID
   FROM PV_APPL_SYS AS1
   UNION ALL
   SELECT AP2.GROUPID AS APPID, SYSID
   FROM PV_APPLICATION AP2 JOIN PV_APPL_SYS AS2 ON AP2.APPID = AS2.APPID

) A2S ON A2S.SYSID = SYS.SYSID
WHERE A2S.APPID = ?

Given a domain, count the number of systems that are direct children of that domain.

The following query returns number of systems those are children's of an given system. Here idparent should be replaced with parent systemId to get the result.

 select count(*) from pv_system
 where sysid in (select idchild from PV_ENT_PARENTSHIP_CURRENT
                 where structureidparent = 'SYS'
                 AND idparent=?)

Given a domain, count the number of systems that are descendants of that domain.

 
 The following query returns number of systems those are children's of an given domain as well as the systems from the child domain. Here APPID should be replaced with parent domainID to get the result.

 SELECT count(*)
FROM PV_SYSTEM SYS JOIN (

   SELECT AS1.APPID, AS1.SYSID
   FROM PV_APPL_SYS AS1
   UNION ALL
   SELECT AP2.GROUPID AS APPID, SYSID
   FROM PV_APPLICATION AP2 JOIN PV_APPL_SYS AS2 ON AP2.APPID = AS2.APPID

) A2S ON A2S.SYSID = SYS.SYSID
WHERE A2S.APPID = ?


Given a domain, produce a monthly count of all the systems that were descendants of that domain at least once in that month, for the past 12 months.

The following query returns number of systems those are children's of an given application/domain.

SELECT cal.month_ts, VALUE as mkt_name, COUNT (DISTINCT appid) AS applicationcount

FROM PV_sys_conf_variation c

JOIN pv_sys_metric m

ON (m.sysmetricid = c.sysmetricid)

JOIN pv_appl_sys a

ON (m.sysid = a.sysid)

JOIN (SELECT COLUMN_VALUE AS month_ts

FROM TABLE (

get_date_list (

TRUNC (ADD_MONTHS (SYSDATE, -24), 'mm'),

TRUNC (ADD_MONTHS (SYSDATE, +1), 'mm'),

30 * 86400))) cal

ON ( TRUNC (c.sincedate, 'MM') <= cal.month_ts

AND c.todate > cal.month_ts)

WHERE metric = 'CMDB_MARKET_NAME_C' and value = ?

GROUP BY cal.month_ts, VALUE


Discover the results produced by a golden model (system, metric, stat)

SELECT DISTINCT
  PROD.MODELID, PROD.MODELNAME,
  PROD.TCID, PROD.TCNAME,
  MET.SYSID, MET.NAME SYSNAME,
  MET.SYSMETRICID, MET.METRIC, MET.METRICNAME,
  VAL.STATID, VAL.NAME STATNAME, VAL.DESCRIPTION STATDESCRIPTION
FROM
  PV_MODEL_STAT_PROD PROD
 JOIN PV_SYS_DATA_CUST_STAT VAL ON PROD.STATPRODID = VAL.STATPRODID
 JOIN PV_SYS_METRIC MET ON MET.SYSMETRICID = VAL.SYSOBJID
WHERE
  PROD.TCID    = 1096
ORDER BY
 1,3,5,7;

Golden_Value_Results.png

Explain how to get statistic with single value (such as days to crash)

SELECT
  MET.SYSID, MET.NAME SYSNAME,
  MET.SYSMETRICID, MET.METRIC, MET.SUBMETRICNAME,
  VAL1.TS,
  VAL1.VALUE EST_DAYS_TO_CRASH,
  VAL2.VALUE EST_DAYS_TO_CRASH_UB,
  VAL3.VALUE EST_D_TREND,
  VAL4.STRINGVALUE EST_OUTPUT_IMAGE
FROM
  PV_SYS_METRIC MET
 -- EST_DAYS_TO_CRASH
  JOIN PV_SYS_DATA_CUST_STAT VAL1 ON MET.SYSMETRICID = VAL1.SYSOBJID AND VAL1.STATID = 8  AND VAL1.TS = TRUNC(SYSDATE)
 -- EST_DAYS_TO_CRASH_UB
  JOIN PV_SYS_DATA_CUST_STAT VAL2 ON MET.SYSMETRICID = VAL2.SYSOBJID AND VAL2.STATID = 9  AND VAL2.TS = TRUNC(SYSDATE) AND VAL2.STATPRODID = VAL1.STATPRODID
 --EST_D_TREND
  JOIN PV_SYS_DATA_CUST_STAT VAL3 ON MET.SYSMETRICID = VAL3.SYSOBJID AND VAL3.STATID = 10 AND VAL3.TS = TRUNC(SYSDATE) AND VAL3.STATPRODID = VAL1.STATPRODID
  -- EST_OUTPUT_IMAGE
  JOIN PV_SYS_DATA_CUST_STAT VAL4 ON MET.SYSMETRICID = VAL4.SYSOBJID AND VAL4.STATID = 13 AND VAL4.TS = TRUNC(SYSDATE) AND VAL4.STATPRODID = VAL1.STATPRODID
WHERE EXISTS (
   SELECT NULL FROM PV_MODEL_STAT_PROD PROD WHERE PROD.TCID = 22704 AND PROD.STATPRODID = VAL1.STATPRODID
);

Single_Value_Stats.png

Explain how to work with a statistic that produces more than one value (estimated daily value)

SELECT
  MET.SYSID, MET.NAME SYSNAME,
  MET.SYSMETRICID, MET.METRIC, MET.METRICNAME,
  VAL.NAME STATNAME, VAL.DESCRIPTION STATDESCRIPTION,
  VAL.TS, VAL.VALUE EST_DAILY_VALUE
FROM
  PV_SYS_METRIC MET
 JOIN PV_SYS_DATA_CUST_STAT VAL ON MET.SYSMETRICID = VAL.SYSOBJID
 JOIN PV_MODEL_STAT_PROD PROD ON PROD.STATPRODID = VAL.STATPRODID
WHERE PROD.TCID    = 4738 AND MET.METRIC = 'CPU_UTILMHZ' AND VAL.STATID = 7;

Multiple_Value_Stats.png

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*