Inspector - Add ability to have server level control over various global thresholds in the settings table

[Adedba]

Currently you can specify the following threshold values in the Settings table

FullBackupThreshold
DiffBackupThreshold
LogBackupThreshold
DaysUntilDriveFullThreshold
FreeSpaceRemainingPercent
DatabaseGrowthsAllowedPerDay
MAXDatabaseGrowthsAllowedPerDay
CPUHistoryRetentionInDays
CPUThresholdWarningHighlight
CPUThresholdAdvisoryHighlight
CPUThresholdInfoHighlight
BackupSpaceWeekdayOffset
BlitzWaitsTopXRows
BlitzWaitsHourlyBucketSize
BlitzWaitsAlwaysShowBreached
BlitzWaitsBucketColourOdd
BlitzWaitsBucketColourEven

These setting however can only be set at a global level with no way to tailor per server. This new change will allow server level granular control of these thresholds if you are centralising more than one servers' worth of information in your Inspector database.

A new table will be introduced:

CREATE TABLE [Inspector].[ServerSettingThresholds] (
ID INT IDENTITY(1,1),
Servername NVARCHAR(128) NOT NULL,
Modulename VARCHAR(50) NOT NULL,
ThresholdName VARCHAR(100) NOT NULL,
ThresholdInt INT NULL,
ThresholdString VARCHAR(255) NULL,
IsActive BIT NOT NULL
);

and a new function to get the Threshold value from the new table if it exists for the server otherwise default to the global value in the settings table.

Depending on the threshold you will need to specify either an Int value or a Varchar value. When the function is called the procedure calling the function will TRY_CAST attempt to the correct data type for that threshold , as long as this converts successfully the value is accepted otherwise a default value will be used.

[Adedba]

/* Global threshold for the DiffBackupThreshold */
SELECT [Value] 
FROM [Inspector].[Settings]
WHERE [Description] = 'LogBackupThreshold';

My value is 20

/* Override the Global setting for LogBackupThreshold for server DESTINY */
INSERT INTO [Inspector].[ServerSettingThresholds] ([Servername],[Modulename],[ThresholdName],[ThresholdInt],[ThresholdString],[IsActive])
VALUES('DESTINY','BackupsCheck','LogBackupThreshold',15,NULL,1);

/* The new function will first check for overrides, then the global setting */
SELECT [Inspector].[GetServerModuleThreshold]('DESTINY','BackupsCheck','LogBackupThreshold');

This returns 15 as I inserted an override for this server

/* Check a server that has no override */
SELECT [Inspector].[GetServerModuleThreshold]('DESTINY\SQL02','BackupsCheck','LogBackupThreshold');

No Override present so the global value 20 is used (the one found in Inspector.Settings)

When the new function [Inspector].[GetServerModuleThreshold] is used within the respective Report procs it is wrapped in a TRY_CAST to the expected data type
if this succeeds then the value is used, however if conversion fails then a default value is set so be sure to check the values you set are compatible.

Example:
(SELECT ISNULL(TRY_CAST([Inspector].[GetServerModuleThreshold] (@Servername,@Modulename,'LogBackupThreshold') AS INT),20));

Example with values:
SELECT ISNULL(TRY_CAST([Inspector].[GetServerModuleThreshold] ('DESTINY\SQL03','BackupsCheck','LogBackupThreshold') AS INT),20);

[Adedba]

Small bug found and fixed.

Bug was that if you specified an override of NULL it was not honoured instead the global setting was being used, to be honest this only actually affects DiffBackupThreshold because a NULL value for this setting ignores the threshold all together but this is unique behaviour and no other settings use a NULL .

[Adedba]

Further explanation of logic:

In the new table ServerSettingThresholds specifying an Int threshold will take precedence over a String threshold, if the int threshold is NULL then the string threshold will be used instead.

Each specified threshold row in the table can be enabled/disabled using the IsActive column, if the thrshold is disabled then the value for the global setting found in Inspector.Settings will be used instead.

Within the respective Report procs that use thresholds there will be individual logic applied to attempt a convert to the correct data type e.g

(SELECT ISNULL(TRY_CAST([Inspector].[GetServerModuleThreshold] (@Servername,@Modulename,'LogBackupThreshold') AS INT),20));

In the above example if the TRY_CAST to INT succeeds then the value will be used, otherwise it will return NULL and the ISNULL will handle this and return a default value which in this case is 20, these statements will differ depending on the Report proc and the required data type.