Hi, this is Darren Mallette from Quest Software. In this post I'm going to show how to create a custom rule using 2 different topology types in Foglight. Specifically, I'm going to use the user defined metric capability as of the 5.5.2 version of the Foglight SQL Server cartridge and combine that with a native metric from the agent.
As of the Foglight 5.5.2 release of the SQL Server cartridge, the ability to pull custom SQL and WMI metrics into the Foglight data model has been included. On the agent administration page, you can specify the SQL to execute (must return a single value) and the data type that Foglight will recognize it as (ie. count, kilobyte, etc.).
The returned data resides in the DBSS_DB_Udf_Stats topology with a subtype for the data type you specified on setup (ie. DBSS_DB_Udf_Stats_Count for counts).
Finding the Scope
The hardest thing I struggled with when defining rules of this type is in selecting the proper topology type on which to scope the rule. The Script Editor is your friend here. I’ve selected DBSS_Instance here as my scoping query. This is the topology type under which metrics coming in from the 5.5.2+ SQL Server agent will reside.
Once I found the correct topology to base the rule upon, I went to Manage Rules, added a rule and gave it a name. I also picked the scope to be DBSS_Instance.
Next I went to the Conditions, Alarms and Actions tab to specify the condition that will trigger the alarm. Since this is a data driven rule it must reference some metric value. Hmm, wait a second. My colleague, Brian Wheeldon, brought up a very good point. Since I am using metrics from different topologies in my rule, there is a possibility that each has a different collection frequency. So we will actually want to change the Rule Triggering above to “Time Driven”, and specify a time interval.
I entered the rule condition (below) and then clicked the “Validate Condition” checkbox to check the syntax.
Initial Condition:
history_Web = #value from DBSS_DB_Udf_Stats_Count where name= "WebServerLongDBCalls" for 5 min#
mean_Web = avg(history_Web)
history_block = #sql_block_cnt from DBSS_SQL_Server_Connections_Summary for 5 min#
mean_block = avg(history_block)
if ((mean_Web >= 60000) && (mean_block >= 0))
return true;
else
return false;
To explain the above, I am setting a value of “history_Web” equal to the value field from the DBSS_DB_Udf_Stats_Count topology, where the metric name is “WebServerLongDBCalls” for a 5 minute period. “WebServerLongDBCalls” is the name that I gave our metric in the SQL Server setup screen when the SQL call was created. I then set a variable called mean_Web to the average of the returned observations. Same thing goes or the “sql_block_cnt” which comes from a different topology (DBSS_SQL_Server_Connections_Summary). Finally in the IF block I have the comparison logic – if web calls are equal or greater than 60000 and block count is greater than or equal to 0 then the condition is true.
Once the rule condition is saved, it can be tested in the “Run Condition Query” window. Clicking the icon next to the Condition Query allows copying of the condition for a specified severity level. Clicking on execute query brings back the result (hopefully) or an error. In this case, I selected values in my rule that I know would be found in the Foglight metrics, so that I can test for a true condition.
Bonus Points
Notice in my condition that I hardcoded the values to evaluate against (60000 and 0). A best practice would be to create registry variables for these. This allows the registry value to be re-used in other rules, makes it easier to change the value, the value can be scoped to specific instances and these values can be used on a schedule. I created two registry variables to be used in the rule condition and message.
The updated condition in the rule looks like this:
history_Web = #value from DBSS_DB_Udf_Stats_Count where name= "WebServerLongDBCalls" for 5 min#
mean_Web = avg(history_Web)
history_block = #sql_block_cnt from DBSS_SQL_Server_Connections_Summary for 5 min#
mean_block = avg(history_block)
if ((mean_Web >= registry("Custom_WebServerLongDBCalls_Warning")) && (mean_block >=
registry("Custom_sql_block_cnt_Warning")))
return true;
else
return false;
To get the registry variable value, I just called the registry(“ “) function. Remember the double-quotes and brackets!
You should also put in a meaningful message when the condition is true. This will appear in the alarm. Back to the Script Editor, I noticed some interesting attributes like “mon_host_name” and “mon_instance_name” under the scope of DBSS_Instance. These look useful so we just need a way to get them into our alarm message.
On the Rule Variables tab, I added in 6 variables that can be used in the message. All of these are “expressions”. The expression is the text after the “:”.
mean_Web: history_Web = #value from DBSS_DB_Udf_Stats_Count where name= "WebServerLongDBCalls" for 5 min#
mean_Web = avg(history_Web)
return mean_Web;
mean_block: history_block = #sql_block_cnt from DBSS_SQL_Server_Connections_Summary for 5 min#
mean_block = avg(history_block)
return mean_block;
Custom_WebServerLongDBCalls_Warning: registry("Custom_WebServerLongDBCalls_Warning")
Custom_sql_block_cnt_Warning: registry("Custom_sql_block_cnt_Warning")
HostName : scope.mon_host_name
Instance : scope.mon_instance_name
I used the Rule Variables tab in case I want to add in other severity conditions in the future. This way I can re-use these variables.
Finally, I updated the Alarm Message field on the Conditions tab with the following:
DB Long Calls Alert: There are @mean_Web DB Long Calls and @mean_block blocked users on SQL Instance @Instance on host @HostName.
The thresholds used are: DB Long Calls = @Custom_WebServerLongDBCalls_Warning and Blocked Users = @Custom_sql_block_cnt_Warning.
Notice that you just need to prefix your rule variables with the “@” sign. No special syntax is needed for plain text.
I then created some test cases in my SQL Server database (blocked a user, and added an extra row to my table that the custom SQL metric is based off of) and observed the Alarms dashboard in Foglight.
You can go further, by defining an email action on the rule, or using an event driven rule to perform some other action (like an integration action via snmp).