
1. Re: Resource monitor Rule in TCO
Davide Salerno Nov 10, 2017 3:08 AM (in response to Swati Sharma)5 of 5 people found this helpfulHello Swati,
a solution for your request could be the following.
Solution summary
The solution proposed consists of creating an alert rule based on a SQL Data Mart containing the following 3 columns:
 METRIC value
 STATISTIC value
 DELTA value (a vaue that is the ration in percentage between METRIC and STATISTIC only if a centain condition it is verified )
And adding a condition on the DELTA column value to be greater than a desired threshold.
SQL Data Mart must be based on an Analysis Data Mart previously created based on an Analysis containing the desired metric and statistic.
Below are described deeply the steps.
Analysis Creation
The first step is simply creating a Performance vs. Time analysis analysis on the desired metric and with the desired statistic.
For example suppose that you will select the template “Utilization on Logical Volume” belonging to the template group “System” because I'm interested to the metric BYLDISK_USED_SPACE_PCT and its 95th percentile
The statistic must be selected from the Analysis editor in the “Statistic selection” section clicking on the option “Show custom statistic” and selecting the statistic from the “Available items” box.
In my example I will select the 95^{th} Percentile of BYLDISK_USED_SPACE_PCT that in the example is DISK_95_PERC
Analysis Data Mart Creation
From the Administration > Advanced Reporting > Data Marts > Analysis/Model data mart section create a new data mart based on the analysis previously created to materialize when using the Data Mart Materializer task and with the column mapping as per screenshots below (using STATISTIC as column name for the custom statistic column and METRIC for the metric column).
Materialize the newly created Data Mart clicking on the button “Materialize Now” and wait for the materialization.
SQL Data Mart Creation
Starting from the physical name of the Analysis data mart just created (in the example ER_V_METRIC_VS_STATISTIC_2058 as per screenshot) create a new SQL Data Mart from the Section Administration > Advanced Reporting > Data Marts > SQL data marts with the following SQL Code:
SELECT
ERV.*,
CASE WHEN STATISTIC >0.99 THEN (METRIC/STATISTIC)*100
CASE WHEN STATISTIC <=0.99 THEN 1
END AS DELTA
FROM ERV_<NAME_OF_THE_MATERIALIZED_MART_CREATED_BEFORE> ERV
If do you want only the cases in which the metric is greater than 0 you can simply add a condition to the SQL code:
SELECT
ERV.*,
CASE WHEN STATISTIC >0.99 AND METRIC > 0 THEN (METRIC/STATISTIC)*100
CASE WHEN STATISTIC <=0.99 THEN 1
END AS DELTA
FROM ERV_<NAME_OF_THE_MATERIALIZED_MART_CREATED_BEFORE> ERV
If you want a greater threshold (for example the utilizaiton metric greater than the 50%):
SELECT
ERV.*,
CASE WHEN STATISTIC >0.99 AND METRIC > 50 THEN (METRIC/STATISTIC)*100
CASE WHEN STATISTIC <=0.99 THEN 1
END AS DELTA
FROM ERV_<NAME_OF_THE_MATERIALIZED_MART_CREATED_BEFORE> ERV
If the metric is saved with values between 0 and 100, instead if the metric is stored with values between 0 and 1
SELECT
ERV.*,
CASE WHEN STATISTIC >0.99 AND METRIC > 0.5 THEN (METRIC/STATISTIC)*100
CASE WHEN STATISTIC <=0.99 THEN 1
END AS DELTA
FROM ERV_<NAME_OF_THE_MATERIALIZED_MART_CREATED_BEFORE> ERV
Alert Rule Data Mart Based Creation
The last step is to create an Alert Rule based on the SQL Data Mart previously created SQL Data Mart with 1 condition (in the screenshots below the mart name is METRIC_STAT_DELTA):
 On the DELTA column that it isn’t a metric with a greater than a threshold value of 150