One of the powerful features of Foglight for Oracle and Foglight for SQL Server is the ability to enter your own database queries (SQL) and have the Foglight collector execute these and return data. Trent Mera wrote a great blog on this topic called User-Defined Collections (UDC).
Once the data "hits" Foglight, it can be used like any other metric. For example, you could add the results to a drag 'n drop dashboard or create a rule to evaluate and alert on the values. The latter is what this post will explore.
Coincidentally, this approach can be used to create any new rule in Foglight. The TL;DR version is to find the data in (or get it into) Foglight, and then go through the steps to create a rule.
The first step is to confirm that the data we wish to alert on is actually available in Foglight.
For a user-defined collection, the "User-Defined" drilldown on the Databases dashboard can be used. This SQL text was created to check if there are any enterprise-level features being used in any SQL Server database. It then returns a number. Perfect for an introduction to writing custom rules! The end result will be an alert (alarm) whenever the use of an enterprise feature is detected.
Here is the T-SQL text for reference:
DECLARE @db_name nvarchar(1024)
IF OBJECT_ID('tempdb..#udc_results_entfeatures') IS NOT NULL DROP TABLE #udc_results_entfeatures
CREATE TABLE #udc_results_entfeatures (db_name nvarchar(1024), num_features int)
DECLARE c_db_names CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN('master', 'msdb', 'model') AND state = 0 --might need to exclude more dbs
OPEN c_db_names
FETCH c_db_names INTO @db_name
WHILE @@Fetch_Status = 0
BEGIN
EXEC('
INSERT INTO #udc_results_entfeatures
SELECT
''' + @db_name + '''
, COUNT(*)
FROM ' + @db_name + '.sys.dm_db_persisted_sku_features
')
FETCH c_db_names INTO @db_name
END
CLOSE c_db_names
DEALLOCATE c_db_names
SELECT sum(num_features) as num_features FROM #udc_results_entfeatures
Next we need to find where the data from the UDC "lives". Navigate to Configuration -> Data and expand the Databases node followed by either Oracle or SQL Server. Next expand the Instances node followed by the instance name that has a UDC. We are looking for "Custom Query Root." Find that and expand it.
Next, expand All Queries. A list of queries from the Databases -> UDC dashboard, prefixed with either DBSS or DBO is shown. Finally, expand "Query Results."
Note the "num_features" metric which matched what was on the UDC dashboard. If your view doesn't look like the one below, you'll want to expand the right-side drawer, and pick the Property Viewer.
Next select the name that matches your UDC. In this case, it's SQL-Server Cust NumEnterpriseFeatures.
In the Property Viewer pane, look for and select the value of "topology TypeName".
Next, navigate to Administration -> Rules & Notifications -> Create Rule. Complete the following steps:
1- Enter a rule name. A good suggestion is to prefix it with your initials so you can easily find it later.
2- Decide on the rule type.
3- Choose the rule triggering mechanism.
4- Enter a description of the rule (optional).
5- In the Rule Scope -> Topology Type pane, paste in the value you copied previously.
6- Click the green check mark to validate it. A list of matching topology types will show in green above the pane.
7- Click Next to proceed.
On the Conditions, Alarms & Actions tab, perform the following:
1- Expand the severity level that you want the alarm to appear at.
2- Enter the alarm condition. We use the #metric# notation to get the latest value of the metric. In this use case, if an enterprise feature is detected, the value will be greater than 0. You can validate that the condition works in a couple ways. Syntax validation can be done with the green check mark.
3- Enter a message that will appear when the alarm fires.
4- Click the checkbox to activate the severity level.
Note: Severity level variables and alarm actions can be added here as well. For other use cases, registry variables can be created so that condition values aren't hard-coded. In this case, it's either 0 or > 0 to evaluate, so that step was skipped.
Back in this post, the "Run Condition Query" function was shown. It can be used here to validate our condition logic. Simply copy the condition and execute the query. It evaluates to true for the Scoped Object shown, which means the rule would fire once saved.
There are 3 additional tabs for further control over the rule behaviour. Clicking Finish will save the rule.
A summary of the rule is shown.
Depending on the collection frequency of the UDC, it may take some time... but.. wait for it.. an alarm will eventually appear!
Since the parent object of the topology type the rule was scoped to is a database instance, these UDC rules will also appear for the instance on the Databases dashboard.
Rinse & repeat for remaining UDC's that you wish to alarm on.
For more information, Tim Fritz wrote a great article on how to modify your rule to add alerts by email.
