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.
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.
-
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.
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;
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;
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.
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;
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.
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;
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.