Hi, this is Darren Mallette from Quest Software. Happy New Year and welcome to the first blog post of 2010.
In this post I'm going to show how to create custom SQL Server metrics in Foglight 5.5.2(+). Specifically, I'm going to use the user defined metric capability in the Foglight 5.5.2 cartridge.
In the latest release of the SQL Server cartridge (version 5.5.2), 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.). This returned data then 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).
Adding Custom SQL Metrics
Start on the Databases dashboard, and drill in to the SQL Server instance that you want to add metrics for, and click the User Metrics icon.
This takes you to a drilldown where you can view existing metrics and add new ones. You have the option of adding either a metric returned by a SQL statement, or a WMI counter from the machine.
We are going to add a custom SQL metric so we start from the “User Defined SQL Metrics” tab and click “Create/Manage User-defined Metrics”.
The User-Defined Metrics page opens and we can add new SQL metrics and edit or delete existing metrics. We can also get to this page directly by going to the Global Administration page from the main Databases dashboard. You’ll notice on the left hand side of this dashboard that you also have the option to manage Performance Counters.
Since we want to add a new SQL metric, click the Add button in the lower right of the dashboard.
We need to give the metric a name and provide a SQL query. The query needs to return a single value. The unit of measurement is the Foglight unit that the metric will be known as (ie. count, KB, millisecond, etc.) You also have the option for Foglight to aggregate the metric.
Once you’ve done that, click Verify to check that the SQL returns a value. You’ll have the option to pick which agent to test run the SQL with.
If the query is verified successfully, click Add to continue. Remember to click the “Apply” button in the lower right of the screen after you add your metrics. I’ve learnt that from experience.
Following the “breadcrumbs” in the upper left back to the User Defined drilldown, we can see our new metric and it should start displaying data shortly.
In a future posting, I’ll show how to use these metrics in conjunction with other SQL Server agent metrics in a Foglight rule.