SQL Sentry is a pretty cool monitoring system but there's a downside to having so many knobs to turn: it's next to impossible to find out which knobs have been turned. Let's look at an example.

First I built a five replica multi-subnet Availability Group using my PowerShell module OftenOn. Then I've manually installed SentryOne and watched all the replicas while splitting them between two sites; Seattle and Dallas. I could only get an evaluation license which covers one replica so the rest are not being actively monitored but that's not important at the moment.

Performance Monitor screen

Problem statement

  • Let's say I have a few SQL Sentry installations between different countries and so hosting different PII data. I can't consolidate them but I want to make sure they're configured the same.
  • Or if I have hundreds of servers being monitored and alarms often fire on one instance but not another, and I want to find out what's different in my monitoring configurations between the two.
  • Or I want to wipe my SentryOne database and start again, but don't want to accidentally lose any critical custom monitoring I've possibly forgotten about.

SQL Sentry has two knobs - Settings and Conditions - which I'm going to refer to collectively as a configuration. That's not an official SentryOne term though.

Settings and Conditions

  • Configurations can be set at different levels which then are inherited downwards, from All Targets to Sites to Computers, or through Object Groups (which I don't use but are like tags for components and/or sub-components).

    • Settings are split into object types (Windows, Amazon, Azure, SQL, etc), subcategories (Computers, Logs, Tasks, Tasks Source), and then finally the settings themselves. I'd estimate there's about 50 different screens to click through if you wanted to cover everything.

    • Conditions are split into General, Failsafe, Audit, and Advisory types.

      • They have actions assigned (Execute Job, Send Email, etc)
      • Advisory conditions might come out of the box or be custom ones you've created yourself.
      • Conditions can have filters attached (only trigger if some condition is met).
      • Conditions can also be disabled at any level.

(If all of this sounds complex or confusing you're not wrong but you could also learn a bit how SQL Sentry works by watching the free SentryOne Certified Training videos.)

I've fiddled one or more settings or conditions of each of these types in my lab so now we've got to go find them. How would you do that manually? You might start with the Reports functionality built into the client.

Reports

But you'll quickly find it has some problems and doesn't cover everything. Plus if I'm running this on multiple installations I don't want to click through all of the reports on each one, export it, then manipulate it further. I'd rather just get it straight from the database. So we will dig a little further.

Disclaimer

It should go without saying but none of the below is supported in any way. Please don't bother SentryOne about this stuff. This is for power users only who throw caution to the wind and have a job to do.

Settings

SQL Sentry stores its configuration (settings and conditions) in a readable database but how the above all ties together isn't documented or easliy discernable. However for the settings at least they have an undocumented 3,000 odd line procedure which will join it all together for you so we can start with that. This is likely used by the report above.

EXEC dbo.ReportProc_ActiveSettingsList @IsWatched = 0;

Default function

It found my first set of easter eggs at the bottom but they're buried with a lot of other defaults that we don't care about (because they're defaults). We can quickly extract those settings from an empty/dummy install of SQL Sentry and filter them out with an EXCEPT or similar. I've listed the defaults from a minor version ago below, these do change in minor ways but they don't update on your existing installs (so it's kind of interesting to look at what yours are set to):

WITH Defaults (ObjectName, AppliesToObjectType, SettingTypeName, SettingName, ConfiguredValue) AS (
        SELECT 'Global', 'Analysis Services', 'Analysis Services', 'Collect Memory by Category Data', 'No' UNION
        SELECT 'Global', 'Analysis Services', 'Analysis Services', 'Maintenance Window Enabled', 'No' UNION
        SELECT 'Global', 'Analysis Services', 'Analysis Services', 'Maintenance Window Name', 'None' UNION
        SELECT 'Global', 'Analysis Services', 'Analysis Services', 'Synchronization Threshold', '0.00:30:00' UNION
        SELECT 'Global', 'Analysis Services', 'Analysis Services', 'Synchronization Type', 'Don''t synchronize automatically.' UNION
        SELECT 'Global', 'Analysis Services', 'Top Commands Source', 'Auto-Watch New Objects', 'Yes' UNION
        SELECT 'Global', 'Analysis Services', 'Top Commands Source', 'Collect MDX/DMX/XMLA Events', 'Yes' UNION
        SELECT 'Global', 'Analysis Services', 'Top Commands Source', 'Maximum Rows To Synchronize', '5000' UNION
        SELECT 'Global', 'Analysis Services', 'Top Commands Source', 'Minimum Duration', '0.00:00:01' UNION
        SELECT 'Global', 'Analysis Services', 'Top Commands Source', 'Missed Run Wait Time', '0.00:05:00' UNION
        SELECT 'Global', 'Analytics Platform System', 'Analytics Platform System', 'Maintenance Window Enabled', 'No' UNION
        SELECT 'Global', 'Analytics Platform System', 'Analytics Platform System', 'Maintenance Window Name', 'None' UNION
        SELECT 'Global', 'APS', 'APS Distributed Queries Source', 'Auto-Watch New Objects', 'Yes' UNION
        SELECT 'Global', 'APS', 'APS Distributed Queries Source', 'Maximum Rows To Synchronize', '5000' UNION
        SELECT 'Global', 'APS', 'APS Distributed Queries Source', 'Missed Run Wait Time', '0.00:05:00' UNION
        SELECT 'Global', 'APS', 'APS Health Alerts Source', 'Auto-Watch New Objects', 'Yes' UNION
        SELECT 'Global', 'APS', 'APS Health Alerts Source', 'Maximum Rows To Synchronize', '5000' UNION
        SELECT 'Global', 'APS', 'APS Health Alerts Source', 'Missed Run Wait Time', '0.00:05:00' UNION
        SELECT 'Global', 'APS', 'APS Loader Backup Runs Source', 'Auto-Watch New Objects', 'Yes' UNION
        SELECT 'Global', 'APS', 'APS Loader Backup Runs Source', 'Maximum Rows To Synchronize', '5000' UNION
        SELECT 'Global', 'APS', 'APS Loader Backup Runs Source', 'Missed Run Wait Time', '0.00:05:00' UNION
        SELECT 'Global', 'APS Distributed Queries', 'APS Distributed Queries', 'Maximum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'APS Distributed Queries', 'APS Distributed Queries', 'Maximum Runtime Threshold Percent', '250%' UNION
        SELECT 'Global', 'APS Distributed Queries', 'APS Distributed Queries', 'Minimum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'APS Distributed Queries', 'APS Distributed Queries', 'Minimum Runtime Threshold Percent', '10%' UNION
        SELECT 'Global', 'APS Loader Backup Run', 'APS Loader Backup Run', 'Maximum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'APS Loader Backup Run', 'APS Loader Backup Run', 'Maximum Runtime Threshold Percent', '250%' UNION
        SELECT 'Global', 'APS Loader Backup Run', 'APS Loader Backup Run', 'Minimum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'APS Loader Backup Run', 'APS Loader Backup Run', 'Minimum Runtime Threshold Percent', '10%' UNION
        SELECT 'Global', 'Azure SQL Data Warehouse', 'Azure SQL Data Warehouse', 'Maintenance Window Enabled', 'No' UNION
        SELECT 'Global', 'Azure SQL Data Warehouse', 'Azure SQL Data Warehouse', 'Maintenance Window Name', 'None' UNION
        SELECT 'Global', 'Azure SQL Data Warehouse', 'Azure SQL Data Warehouse Distributed Queries Source', 'Auto-Watch New Objects', 'Yes' UNION
        SELECT 'Global', 'Azure SQL Data Warehouse', 'Azure SQL Data Warehouse Distributed Queries Source', 'Maximum Rows To Synchronize', '5000' UNION
        SELECT 'Global', 'Azure SQL Data Warehouse', 'Azure SQL Data Warehouse Distributed Queries Source', 'Missed Run Wait Time', '0.00:05:00' UNION
        SELECT 'Global', 'Azure SQL Data Warehouse', 'Azure SQL Data Warehouse Loader Backup Runs Source', 'Auto-Watch New Objects', 'Yes' UNION
        SELECT 'Global', 'Azure SQL Data Warehouse', 'Azure SQL Data Warehouse Loader Backup Runs Source', 'Maximum Rows To Synchronize', '5000' UNION
        SELECT 'Global', 'Azure SQL Data Warehouse', 'Azure SQL Data Warehouse Loader Backup Runs Source', 'Missed Run Wait Time', '0.00:05:00' UNION
        SELECT 'Global', 'Azure SQL Data Warehouse Distributed Queries', 'Azure SQL Data Warehouse Distributed Queries', 'Maximum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'Azure SQL Data Warehouse Distributed Queries', 'Azure SQL Data Warehouse Distributed Queries', 'Maximum Runtime Threshold Percent', '250%' UNION
        SELECT 'Global', 'Azure SQL Data Warehouse Distributed Queries', 'Azure SQL Data Warehouse Distributed Queries', 'Minimum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'Azure SQL Data Warehouse Distributed Queries', 'Azure SQL Data Warehouse Distributed Queries', 'Minimum Runtime Threshold Percent', '10%' UNION
        SELECT 'Global', 'Azure SQL Data Warehouse Loader Backup Run', 'Azure SQL Data Warehouse Loader Backup Run', 'Maximum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'Azure SQL Data Warehouse Loader Backup Run', 'Azure SQL Data Warehouse Loader Backup Run', 'Maximum Runtime Threshold Percent', '250%' UNION
        SELECT 'Global', 'Azure SQL Data Warehouse Loader Backup Run', 'Azure SQL Data Warehouse Loader Backup Run', 'Minimum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'Azure SQL Data Warehouse Loader Backup Run', 'Azure SQL Data Warehouse Loader Backup Run', 'Minimum Runtime Threshold Percent', '10%' UNION
        SELECT 'Global', 'Azure SQL Database', 'Azure SQL Database', 'Maintenance Window Enabled', 'No' UNION
        SELECT 'Global', 'Azure SQL Database', 'Azure SQL Database', 'Maintenance Window Name', 'None' UNION
        SELECT 'Global', 'Azure SQL Database', 'Top SQL Source', 'Auto-Watch New Objects', 'Yes' UNION
        SELECT 'Global', 'Azure SQL Database', 'Top SQL Source', 'Collect Batch and RPC Events', 'No' UNION
        SELECT 'Global', 'Azure SQL Database', 'Top SQL Source', 'Collect Statement Events', 'No' UNION
        SELECT 'Global', 'Azure SQL Database', 'Top SQL Source', 'Maximum Rows To Synchronize', '5000' UNION
        SELECT 'Global', 'Azure SQL Database', 'Top SQL Source', 'Minimum CPU', '0' UNION
        SELECT 'Global', 'Azure SQL Database', 'Top SQL Source', 'Minimum Duration', '0.00:00:05' UNION
        SELECT 'Global', 'Azure SQL Database', 'Top SQL Source', 'Minimum Reads', '0' UNION
        SELECT 'Global', 'Azure SQL Database', 'Top SQL Source', 'Minimum Writes', '0' UNION
        SELECT 'Global', 'Azure SQL Database', 'Top SQL Source', 'Missed Run Wait Time', '0.00:05:00' UNION
        SELECT 'Global', 'Blocking SQL', 'Blocking SQL', 'Maximum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'Blocking SQL', 'Blocking SQL', 'Maximum Runtime Threshold Percent', '250%' UNION
        SELECT 'Global', 'Blocking SQL', 'Blocking SQL', 'Minimum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'Blocking SQL', 'Blocking SQL', 'Minimum Runtime Threshold Percent', '10%' UNION
        SELECT 'Global', 'Database', 'Databases Source', 'Auto-Watch New Objects', 'Yes' UNION
        SELECT 'Global', 'Database', 'Databases Source', 'Collect Buffer Data when Buffer > 8GB', 'False' UNION
        SELECT 'Global', 'Database', 'Databases Source', 'Max Index Size (MB) to Collect Fragmentation Data', '50000' UNION
        SELECT 'Global', 'Database', 'Databases Source', 'Max Partitions to Collect per Database', '500' UNION
        SELECT 'Global', 'Database', 'Databases Source', 'Maximum Rows To Synchronize', '5000' UNION
        SELECT 'Global', 'Database', 'Databases Source', 'Min Index Size (MB) to Collect Fragmentation Data', '10' UNION
        SELECT 'Global', 'Database', 'Databases Source', 'Missed Run Wait Time', '0.00:05:00' UNION
        SELECT 'Global', 'Index', 'Index Defragmentation', 'Enable Index Defragmentation', 'False' UNION
        SELECT 'Global', 'Index', 'Index Defragmentation', 'Fragmentation Scan Mode', 'Limited' UNION
        SELECT 'Global', 'Index', 'Index Defragmentation', 'MAXDOP', '0' UNION
        SELECT 'Global', 'Index', 'Index Defragmentation', 'Maximum Concurrent Defrag Operations', '1' UNION
        SELECT 'Global', 'Index', 'Index Defragmentation', 'Maximum Duration', '0.02:00:00' UNION
        SELECT 'Global', 'Index', 'Index Defragmentation', 'Maximum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'Index', 'Index Defragmentation', 'Maximum Runtime Threshold Percent', '250%' UNION
        SELECT 'Global', 'Index', 'Index Defragmentation', 'Minimum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'Index', 'Index Defragmentation', 'Minimum Runtime Threshold Percent', '10%' UNION
        SELECT 'Global', 'Index', 'Index Defragmentation', 'Partitions', 'All' UNION
        SELECT 'Global', 'Index', 'Index Defragmentation', 'Rebuild Threshold %', '30' UNION
        SELECT 'Global', 'Index', 'Index Defragmentation', 'Reorg Threshold %', '10' UNION
        SELECT 'Global', 'Index', 'Index Defragmentation', 'Run Post-defrag Analysis', 'True' UNION
        SELECT 'Global', 'Index', 'Index Defragmentation', 'Schedule', '<not specified>' UNION
        SELECT 'Global', 'Index', 'Index Defragmentation', 'Sort in tempdb', 'False' UNION
        SELECT 'Global', 'Index', 'Index Defragmentation', 'Use Online Rebuild', 'True' UNION
        SELECT 'Global', 'Reporting Services Report', 'Reporting Services Report', 'Maximum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'Reporting Services Report', 'Reporting Services Report', 'Maximum Runtime Threshold Percent', '250%' UNION
        SELECT 'Global', 'Reporting Services Report', 'Reporting Services Report', 'Minimum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'Reporting Services Report', 'Reporting Services Report', 'Minimum Runtime Threshold Percent', '10%' UNION
        SELECT 'Global', 'SQL Server', 'Blocking SQL Source', 'Auto-Watch New Objects', 'Yes' UNION
        SELECT 'Global', 'SQL Server', 'Blocking SQL Source', 'Maximum Rows To Synchronize', '5000' UNION
        SELECT 'Global', 'SQL Server', 'Blocking SQL Source', 'Minimum Block Duration', '0.00:00:15' UNION
        SELECT 'Global', 'SQL Server', 'Blocking SQL Source', 'Missed Run Wait Time', '0.00:05:00' UNION
        SELECT 'Global', 'SQL Server', 'Deadlocks Source', 'Auto-Watch New Objects', 'Yes' UNION
        SELECT 'Global', 'SQL Server', 'Deadlocks Source', 'Collect Deadlock Events', 'Yes' UNION
        SELECT 'Global', 'SQL Server', 'Deadlocks Source', 'Maximum Rows To Synchronize', '5000' UNION
        SELECT 'Global', 'SQL Server', 'Deadlocks Source', 'Missed Run Wait Time', '0.00:05:00' UNION
        SELECT 'Global', 'SQL Server', 'Maintenance Plans Source', 'Auto-Watch New Objects', 'Yes' UNION
        SELECT 'Global', 'SQL Server', 'Maintenance Plans Source', 'Maximum Rows To Synchronize', '5000' UNION
        SELECT 'Global', 'SQL Server', 'Maintenance Plans Source', 'Missed Run Wait Time', '0.00:05:00' UNION
        SELECT 'Global', 'SQL Server', 'Reporting Services Reports Source', 'Auto-Watch New Objects', 'Yes' UNION
        SELECT 'Global', 'SQL Server', 'Reporting Services Reports Source', 'Maximum Rows To Synchronize', '5000' UNION
        SELECT 'Global', 'SQL Server', 'Reporting Services Reports Source', 'Missed Run Wait Time', '0.00:05:00' UNION
        SELECT 'Global', 'SQL Server', 'SQL Server', 'Auto-enable SQL Server Agent Tokens', 'Yes' UNION
        SELECT 'Global', 'SQL Server', 'SQL Server', 'Auto-recycle Large SQL Server Agent Logs', 'No' UNION
        SELECT 'Global', 'SQL Server', 'SQL Server', 'Maintenance Window Enabled', 'No' UNION
        SELECT 'Global', 'SQL Server', 'SQL Server', 'Maintenance Window Name', 'None' UNION
        SELECT 'Global', 'SQL Server', 'SQL Server', 'Max Databases to Synchronize (largest first)', '100' UNION
        SELECT 'Global', 'SQL Server', 'SQL Server', 'Maximum Queue Length', '5' UNION
        SELECT 'Global', 'SQL Server', 'SQL Server', 'Synchronization Threshold', '0.00:30:00' UNION
        SELECT 'Global', 'SQL Server', 'SQL Server', 'Synchronization Type', 'Don''t synchronize automatically.' UNION
        SELECT 'Global', 'SQL Server', 'SQL Server Agent Alerts Source', 'Auto-Watch New Objects', 'Yes' UNION
        SELECT 'Global', 'SQL Server', 'SQL Server Agent Alerts Source', 'Maximum Rows To Synchronize', '5000' UNION
        SELECT 'Global', 'SQL Server', 'SQL Server Agent Alerts Source', 'Missed Run Wait Time', '0.00:05:00' UNION
        SELECT 'Global', 'SQL Server', 'SQL Server Agent Jobs Source', 'Auto-Watch New Objects', 'Yes' UNION
        SELECT 'Global', 'SQL Server', 'SQL Server Agent Jobs Source', 'Maximum Rows To Synchronize', '5000' UNION
        SELECT 'Global', 'SQL Server', 'SQL Server Agent Jobs Source', 'Missed Run Wait Time', '0.00:05:00' UNION
        SELECT 'Global', 'SQL Server', 'SQL Server Agent Log Source', 'Auto-Watch New Objects', 'Yes' UNION
        SELECT 'Global', 'SQL Server', 'SQL Server Agent Log Source', 'Maximum Rows To Synchronize', '5000' UNION
        SELECT 'Global', 'SQL Server', 'SQL Server Agent Log Source', 'Missed Run Wait Time', '0.00:05:00' UNION
        SELECT 'Global', 'SQL Server', 'Top SQL Source', 'Auto-Watch New Objects', 'Yes' UNION
        SELECT 'Global', 'SQL Server', 'Top SQL Source', 'Collect Batch and RPC Events', 'Yes' UNION
        SELECT 'Global', 'SQL Server', 'Top SQL Source', 'Collect Statement Events', 'Yes' UNION
        SELECT 'Global', 'SQL Server', 'Top SQL Source', 'Maximum Rows To Synchronize', '5000' UNION
        SELECT 'Global', 'SQL Server', 'Top SQL Source', 'Minimum CPU', '0' UNION
        SELECT 'Global', 'SQL Server', 'Top SQL Source', 'Minimum Duration', '0.00:00:05' UNION
        SELECT 'Global', 'SQL Server', 'Top SQL Source', 'Minimum Reads', '0' UNION
        SELECT 'Global', 'SQL Server', 'Top SQL Source', 'Minimum Writes', '0' UNION
        SELECT 'Global', 'SQL Server', 'Top SQL Source', 'Missed Run Wait Time', '0.00:05:00' UNION
        SELECT 'Global', 'SQL Server Agent Job', 'SQL Server Agent Job', 'Auto-start Threshold', '0.04:00:00' UNION
        SELECT 'Global', 'SQL Server Agent Job', 'SQL Server Agent Job', 'Auto-start Threshold Enabled', 'No' UNION
        SELECT 'Global', 'SQL Server Agent Job', 'SQL Server Agent Job', 'Auto-start Type', 'Use default setting.' UNION
        SELECT 'Global', 'SQL Server Agent Job', 'SQL Server Agent Job', 'Behavior When Queued', 'Never Queue.' UNION
        SELECT 'Global', 'SQL Server Agent Job', 'SQL Server Agent Job', 'Behavior When Queuing', 'Never Queue.' UNION
        SELECT 'Global', 'SQL Server Agent Job', 'SQL Server Agent Job', 'Maximum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'SQL Server Agent Job', 'SQL Server Agent Job', 'Maximum Runtime Threshold Percent', '250%' UNION
        SELECT 'Global', 'SQL Server Agent Job', 'SQL Server Agent Job', 'Minimum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'SQL Server Agent Job', 'SQL Server Agent Job', 'Minimum Runtime Threshold Percent', '10%' UNION
        SELECT 'Global', 'SQL Server Agent Job', 'SQL Server Agent Job', 'Queue For', '0.00:30:00' UNION
        SELECT 'Global', 'SQL Server Agent Job', 'SQL Server Agent Job', 'Queue Others For', '0.00:30:00' UNION
        SELECT 'Global', 'Top Commands', 'Top Commands', 'Maximum Runtime Threshold', '0.01:00:00' UNION
        SELECT 'Global', 'Top Commands', 'Top Commands', 'Maximum Runtime Threshold Percent', '250%' UNION
        SELECT 'Global', 'Top Commands', 'Top Commands', 'Minimum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'Top Commands', 'Top Commands', 'Minimum Runtime Threshold Percent', '10%' UNION
        SELECT 'Global', 'Top SQL', 'Top SQL', 'Maximum Runtime Threshold', '0.01:00:00' UNION
        SELECT 'Global', 'Top SQL', 'Top SQL', 'Maximum Runtime Threshold Percent', '250%' UNION
        SELECT 'Global', 'Top SQL', 'Top SQL', 'Minimum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'Top SQL', 'Top SQL', 'Minimum Runtime Threshold Percent', '10%' UNION
        SELECT 'Global', 'Windows', 'Windows Event Logs Source', 'Auto-Watch New Objects', 'Yes' UNION
        SELECT 'Global', 'Windows', 'Windows Event Logs Source', 'Maximum Rows To Synchronize', '5000' UNION
        SELECT 'Global', 'Windows', 'Windows Event Logs Source', 'Missed Run Wait Time', '0.00:05:00' UNION
        SELECT 'Global', 'Windows', 'Windows Tasks Source', 'Auto-Watch New Objects', 'Yes' UNION
        SELECT 'Global', 'Windows', 'Windows Tasks Source', 'Maximum Rows To Synchronize', '5000' UNION
        SELECT 'Global', 'Windows', 'Windows Tasks Source', 'Missed Run Wait Time', '0.00:05:00' UNION
        SELECT 'Global', 'Windows Computer', 'Windows Computer', 'Maintenance Window Enabled', 'No' UNION
        SELECT 'Global', 'Windows Computer', 'Windows Computer', 'Maintenance Window Name', 'None' UNION
        SELECT 'Global', 'Windows Task', 'Windows Task', 'Maximum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'Windows Task', 'Windows Task', 'Maximum Runtime Threshold Percent', '250%' UNION
        SELECT 'Global', 'Windows Task', 'Windows Task', 'Minimum Runtime Threshold', '0.00:00:00' UNION
        SELECT 'Global', 'Windows Task', 'Windows Task', 'Minimum Runtime Threshold Percent', '10%'
        )

You'll notice there's an additional column above that isn't in the procedure's output. It's not obvious from the screenshot but some setting categories re-use the same type and setting name and so the detail becomes lost in the report. Sentry contains that information it's just not returning it so I modified their procedure to return an extra column and gave it a new name dbo.ReportProc_ActiveSettingsList_Alt.sql (each changed line is marked with an --X so you can go look).

In case you're wondering the numbers I've fiddled with are at the Global level, the Seattle site, the SEC1N1.LAB.COM computer object, and then the SEC1N1.lab.com instance object (which is why it seems to be repeating but it's not). It wouldn't hurt to have this made clear either but I haven't done that.

EXEC dbo.ReportProc_ActiveSettingsList_Alt @IsWatched = 0;

Alt output

Conditions

When it comes to Conditions you can't exactly change the existing conditions only remove them or "add things on top of them". For my own sanity I break those down as follows:

  • Actions can be set
  • Custom Conditions can be created
  • Filters can be added to the action
  • Overrides can be created

It turns out there's another undocumented stored procedure which might be used to figure out which actions are set.

EXEC dbo.ReportProc_ConfiguredActionsList @IsWatched = 0, @ShowNotificationsOnly = 0;

There is a ReportProc_ConfigurationNotificationsList but it seems to be a subset of this one.

Anyway it has caught another easter egg which is that I've added a Log To Database action on the SEC1N1.lab.com instance. Thankfully in this case the existing procedure already includes the ObjectTypeName field so we don't need to modify this procedure ourselves.

One thing about this output is that it includes "defaults" that are configured only during the install, like how all of the emails go to the licensee email address. It also doesn't report on default actions you've completely removed. It would be possible to work these out from a dummy install just like the settings output and do a left join, but I haven't done this here.

Notifications

Custom conditions are easy. Here I just want to know what exists, not any particular detail of what it applies to. This is more so I remember to sync them between installations, and once it's there then that other detail would show up in the above parts anyway.

SELECT  adcd.*
FROM    dbo.vwActiveDynamicConditionDefinitions adcd
WHERE   adcd.PublisherID <> 1;

Advisory condition

Filtered and overridden conditions are more complex. I had to build this the hard way and there are some odd NULLs I don't understand but it works well enough.

-- Condition filters and disabled conditions
SELECT  vo.ObjectName,
        ot.Name AS ObjectType,
        COALESCE(ct.Name, CONVERT(NVARCHAR(64), oca.ConditionTypeID)) AS ConditionType, -- Sometimes there's no match
        at.Name AS Action,
        oca.DoAction,
        oca.AppendAction,
        oca.LastModifiedUtc,
        cf.FilterString,
        cf.FilterStringFriendlyName,
        cf.Description AS FilterDescription
FROM    dbo.ObjectConditionAction oca
LEFT JOIN dbo.vwObjects vo
ON      oca.ObjectID = vo.ObjectID
LEFT JOIN dbo.ObjectType ot
ON      oca.ObjectTypeID = ot.ID
OUTER APPLY (
        SELECT  ct.Name, ct.Description
        FROM    dbo.ConditionType ct
        WHERE   oca.ConditionTypeID = ct.ID
        UNION
        SELECT  vadcd.Name, vadcd.Description
        FROM    dbo.vwActiveDynamicConditionDefinitions vadcd
        WHERE   oca.ConditionTypeID = vadcd.ConditionID
        ) ct
LEFT JOIN dbo.ActionType at
ON      oca.ActionTypeID = at.ID
LEFT JOIN vwActiveDynamicConditionDefinitions vadcd
ON      oca.ConditionTypeID = vadcd.ConditionID
LEFT JOIN dbo.ObjectActionConditionFilter oacf
ON      oca.ID = oacf.ObjectConditionActionID
LEFT JOIN dbo.ConditionFilter cf
ON      oacf.ConditionFilterID = cf.ID
WHERE   cf.FilterString IS NOT NULL
OR      oca.DoAction = 0;

It found the final easter eggs which are a global filter I added on the application names that might trigger a Top SQL email, and how I disabled the Check Constraints Not Trusted condition for replicas in the Dallas site.

Global filter

Final script

I've included my reporting script procedures here and here. It's not perfect but it's also the most there is publicly available on the planet right now.

EXEC dbo.GetSQLSentrySettings @IsWatched = 0;

Final output

Imagine though that you relied upon the internal reports OR went looking through every screen for these on your own. It would be impossible which is why I think a feature like this is important.

What I'd like to see from SentryOne is:

  • Adding the applies to object type column to the ActiveSettingsList procedure just like I have. Also having the object type itself would be beneficial.
  • Adding a set of defaults into the database so differences can be highlighted in the reports.
  • Adding a reporting procedure for the custom conditions, overrides and disabled conditions.
  • Covering anything else I've missed where settings or conditions can be stored.
  • And generally documenting an official solution to export configuration data from the database layer.

That's pretty much what is needed to close the loop on the problem statement.