Using filter macros with SQL queries

Filter macros are pieces of code that are replaced at execution time to produce dynamic results. They are replaced in the SQL query according to the parameters that come from the Dashboard.

The different types of filter macros that can be used in the SQL code of a View template are:

  • Simple filter macros: Enables you to specify the name of the parameter and the column (optional) that should be used for filtering.
  • Custom filter macros: Enables you to customize the filtering condition.

Additional template macros include:

Simple filter macros

Simple filter macros let you insert dynamic content in most SQL queries. Their generic format is:

${FILTER(<parameter-ID>[, <column-name>])}

The macro is substituted in the following way:

AND <parameter-ID> = <parameter-value>

Or, if the optional parameter <column-name> is specified, the macro is substituted as:

AND <column-name> = <parameter-value>

Example

If you want to write a query that is filtered for a specific system, you can use the following View template:

SELECT * FROM SYS_DEF WHERE 1=1 ${FILTER(SYSID)}

At execution time, all macros will be replaced according to the parameter values, dynamically changing the query. Assuming that the current value for the SYSID parameter is "109278", the resulting query will be:

SELECT * FROM SYS_DEF WHERE 1=1 AND SYSID=109278

Custom filter macros

Custom filter macros allow you to apply dynamic changes using a customized where condition. Their generic format is:

${CUST_FILTER(<parameter-id>, <replacement>)}

The macro is substituted in the following way:

AND <replacement>

Example

If you want to write a query that is filtered for a specific system, you can use the following View template:

SELECT * FROM SYS_DEF t0 WHERE 1=1 ${CUST_FILTER(SYSID, AND t0.SYSID = ?)}

At execution time, all macros will be replaced according to the parameter values, dynamically changing the query. Assuming that the current value for the SYSID parameter is "1000", the resulting query will be:

SELECT * FROM SYS_DEF t0 WHERE 1=1 AND t0.SYSID = 1000

Dashboard parameters

The following table lists all Dashboard parameters that can be used with filter macros:

Dashboard parameters

Parameter Name

Parameter ID

Domain

APPID

Location

LOCATIONID

Report ID

REPID

Report URL

REPURL

Resource

METRIC

Series ID

SERIESID

Subresource

SUBMETRICNAME or SUBOBJNAME

System

SYSID

System Series ID

SYSMETRICID or SYSOBJID

Tag IDTAGID

Business Driver

WKLDID

Business Driver Series ID

WKLDMETRICID or WKLDOBJID

For custom parameters, the parameter ID is simply the parameter name. For example, if you defined a custom filter named MY_CUSTOM_FILTER and you want to filter on a column named MY_COLUMN, you can use the following macro:

${CUST_FILTER(MY_CUSTOM_FILTER, AND MY_COLUMN = ?)}

Note

Filter macros are case sensitive: use the correct case for macros and parameter IDs. For standard parameters, use the parameter IDs as specified in the preceding table.

Time filter macros

Time filter macros allow you to add time filtering on the date column. Their generic format is:

${TIMEFILTER[(<column-name>)]}

Example

If you want to write a query that is filtered according to a specific
timefilter, you can use the following View template:

SELECT * FROM MYTABLE WHERE 1=1 ${TIMEFILTER}

At execution time, the time filter will be evaluated, dynamically
changing the query. The resulting query will be similar to the following:

SELECT * FROM MYTABLE WHERE 1=1 AND
ts between to_date('06/04/2009','DD/MM/YYYY')
and to_date('07/04/2009','DD/MM/YYYY')

You can also specify a column name; for instance:

SELECT * FROM MYTABLE WHERE 1=1 ${TIMEFILTER(myTable.myCustomTSColumn)}

In this case, the myTable.myCustomTSColumn column will be used
instead of the default ts, resulting in the following query:

SELECT * FROM MYTABLE WHERE 1=1 AND
myTable.myCustomTSColumn between
to_date('06/04/2009','DD/MM/YYYY')
and to_date('07/04/2009','DD/MM/YYYY')

Domain filter macros

DefinitionEnables you to display entities that belong to a specific domain and its sub-domains.
Syntax

${DOMAIN_FILTER(TYPE,ALIAS_COLUMN_NAME)}

Parameters
  • TYPE can be any of the following:
    • SYS: If the entities extracted by SQL are systems
    • WKLD: If the entities extracted by SQL are business drivers
    • APP: If entities extracted by SQL are domains
  • ALIAS_COLUMN_NAME (optional): Used to specify an alias for the column to be filtered if it is different from the default (ENTID for SYS and WKLD)
Example

Consider the following query:

Select * from MY_MART tab where
tab.entid in
      (select sysid
      from PV_APPL_SYS aps
      where 1 = 1 ${CUST_FILTER(APPID,  AND aps.appid IN 
      (select * from table (get_appid_child_list (?, 1))))}

With the use of the DOMAIN_FILTER macro, the where clause in the query above can simply be replaced with:

Select * from MY_MART tab where 1=1 ${DOMAIN_FILTER(SYS)}

Note

For the proper data mart column names, BMC recommends to use the Labels option in the Output Columns section instead of using table column aliases in the SQL query.


ACL filter macros

DefinitionDisplay only those entities belonging to domains that the user can access.
Syntax
${ ACL_FILTER(TYPE,ALIAS_COLUMN_NAME)}
Parameters
  • TYPE can be any of the following:
    • SYS: If entities extracted by SQL are systems
    • WKLD: If entities extracted by SQL are business drivers
    • APP: If entities extracted by SQL are domains
  • ALIAS_COLUMN_NAME (optional): Used to specify an alias for the column to be filtered if it is different from the default (ENTID for SYS; WKLD, APPID for APP)
Example
Select * from PV_SYSTEM t0 where 1=1 ${ACL_FILTER(SYS,t0.SYSID)}

View macros

View macros allow you to include the SQL code of a data mart inside another data mart. Their generic format is:

${VIEW(<identifier>[, <table-alias>]}

where <identifier> is the data mart identifier to include and <table-alias> is the alias to be used for the data mart template.

Example

SELECT * FROM sys_def t1 WHERE EXISTS
( SELECT null FROM ${VIEW(ER_V_MYDATAMART_11,mytable)} WHERE mytable.sysid=t1.sysid )

View template macros

The view template macro is deprecated in TrueSight Capacity Optimization version 11.5.01. BMC recommends that you use the VIEW macro that provides the same capabilities with a simplified workflow. See Deprecated and dropped features and components.

View template macros allow you to include the SQL code of a View template inside another View template. Their generic format is:

${VTEMPL(<erid>[, <table-alias>]}

where <erid> is the View template ID to include and <table-alias> is the alias to be used for the data mart template.

Example

SELECT * FROM sys_def t1 WHERE EXISTS
( SELECT null FROM ${VTEMPL(2448,mytable)} WHERE mytable.sysid=t1.sysid )
Was this page helpful? Yes No Submitting... Thank you

Comments