The ability to enter your own queries into Foglight for SQL Server, Oracle and DB2 opens up a world of additional data that you can use with Foglight. These are called User-Defined Collections, or UDC's for short.
There are some nuances to using them however, and this post will show a couple best practices for using UDC's.
You need to start with an idea of what you need to collect, and it's a good idea to verify that Foglight isn't already collecting it. This example will retrieve a list of logins and server roles by directly querying the syslogins view in SQL Server.
It is a very good idea to explicitly list the column names in your Select statement versus using a Select *. If the underlying view or table changes, the data that Foglight maps to its return values will invalidate and the UDC will no longer work.
Once the query is verified, we need to map the results of the SQL query to Foglight topology objects. One or more columns need to be defined as a key with the "is key" property. This is similar conceptually to a primary key in a relational table.
The Foglight datatype also needs to be defined - these are string, integer, double and date. Note that if your SQL returns binary columns, it will not be valid for a UDC.
The next property that is very important to get right is the "frequently modified" value. Un-checking it will set the value as a "property" in Foglight. If you need to chart the value over time (and of course if it does frequently change), then you should leave it checked. We'll see the impact of both options below.
Once the UDC is saved, you can view the results by drilling down to the instance from the Databases dashboard, and going to User-Defined -> Collections. From the configuration step above, I specified that "hasaccess" does not change frequently and the remaining columns (sysadmin, etc.) do. You can notice the impact - "sysadmin" is clickable, meaning, we have a history of the metric value.
Ideally, we would want to work with the UDC results on a dashboard that spans other instances, and maybe includes other data. In the next post, we'll create a UI Query to expose the UDC data, and drop that onto a dashboard.
