''; END IF (@Debug = 1) BEGIN SELECT OBJECT_NAME(@@PROCID) AS ''Procname'', @Servername AS ''@Servername'', @Modulename AS ''@Modulename'', @TableHeaderColour AS ''@TableHeaderColour'', @WarningHighlight AS ''@WarningHighlight'', @AdvisoryHighlight AS ''@AdvisoryHighlight'', @InfoHighlight AS ''@InfoHighlight'', @ModuleConfig AS ''@ModuleConfig'', @WarningLevel AS ''@WarningLevel'', @NoClutter AS ''@NoClutter'', @TableTail AS ''@TableTail'', @HtmlOutput AS ''@HtmlOutput'', @HtmlTableHead AS ''@HtmlTableHead'', @SQLtext AS ''@SQLtext'', @CollectionOutOfDate AS ''@CollectionOutOfDate'', @PSCollection AS ''@PSCollection'' END END'; IF OBJECT_ID('Inspector.ExecutionLogInsert') IS NULL EXEC('CREATE PROCEDURE [Inspector].[ExecutionLogInsert] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[ExecutionLogInsert] ( @RunDatetime DATETIME, @Servername NVARCHAR(128), @ModuleConfigDesc VARCHAR(20), @Procname NVARCHAR(128), @Frequency SMALLINT = NULL, @ErrorMessage NVARCHAR(128) = NULL, @Duration MONEY, @PSCollection BIT ) AS --Revision Date: 13/05/2021 INSERT INTO [Inspector].[ExecutionLog] (ExecutionDate,Servername,ModuleConfig_Desc,Procname,Frequency,Duration,PSCollection,ErrorMessage) VALUES(@RunDatetime,@Servername,@ModuleConfigDesc,@Procname,@Frequency,@Duration,@PSCollection,@ErrorMessage); '; IF OBJECT_ID('Inspector.ResetHtmlColors') IS NULL EXEC('CREATE PROCEDURE [Inspector].[ResetHtmlColors] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[ResetHtmlColors] AS BEGIN UPDATE [Inspector].[ModuleWarnings] SET [HighlightHtmlColor] = CASE WHEN [WarningLevel] IS NULL THEN NULL WHEN [WarningLevel] = 1 THEN ''#fc5858'' WHEN [WarningLevel] = 2 THEN ''#FAFCA4'' WHEN [WarningLevel] = 3 THEN ''#FEFFFF'' END, [GradientLeftHtmlColor] = CASE WHEN [WarningLevel] IS NULL THEN NULL WHEN [WarningLevel] IN (1,2,3) THEN ''#000000'' END, [GradientRightHtmlColor] = CASE WHEN [WarningLevel] IS NULL THEN NULL WHEN [WarningLevel] = 1 THEN ''#fc5858'' WHEN [WarningLevel] = 2 THEN ''#FAFCA4'' WHEN [WarningLevel] = 3 THEN ''#FEFFFF'' END END'; IF OBJECT_ID('Inspector.DriveCapacityHistory') IS NULL EXEC('CREATE PROCEDURE [Inspector].[DriveCapacityHistory] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[DriveCapacityHistory] ( @Servername NVARCHAR(128), @Drive VARCHAR(20) ) AS BEGIN --Revision date 25/03/2019 SELECT Servername, Log_Date, Drive, Capacity_GB, AvailableSpace_GB, DATEDIFF(DAY,LEAD(Log_Date,1,Log_Date) OVER(Partition by Drive ORDER BY Log_Date DESC),Log_Date) AS DaysSinceCapacityChange, Capacity_GB-LEAD(Capacity_GB,1,Capacity_GB) OVER(Partition by Drive ORDER BY Log_Date DESC) AS CapacityChange FROM ( SELECT Servername, Log_Date, Drive, Capacity_GB, AvailableSpace_GB, ROW_NUMBER() OVER(Partition by Capacity_GB,Drive ORDER BY Log_Date DESC) as CapacityChange FROM [Inspector].[DriveSpace] WHERE Drive = @Drive AND Servername = @Servername ) CapacityChanges WHERE CapacityChange = 1 ORDER BY Log_Date DESC END'; IF OBJECT_ID('Inspector.SuppressAdHocDatabase') IS NULL EXEC('CREATE PROCEDURE [Inspector].[SuppressAdHocDatabase] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[SuppressAdHocDatabase] ( @Databasename NVARCHAR(128), @Servername NVARCHAR(128) ) AS BEGIN --Revision date: 05/04/2019 SET NOCOUNT ON; UPDATE [Inspector].[ADHocDatabaseSupression] SET [Suppress] = 1 WHERE [Databasename] = @Databasename AND [Servername] = @Servername; END'; IF OBJECT_ID('Inspector.SuppressAGDatabase') IS NULL EXEC('CREATE PROCEDURE [Inspector].[SuppressAGDatabase] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[SuppressAGDatabase] ( @Databasename NVARCHAR(128), @Servername NVARCHAR(128) ) AS BEGIN --Revision date: 05/04/2019 SET NOCOUNT ON; UPDATE [Inspector].[AGDatabases] SET [Is_AG] = 0 WHERE [Databasename] = @Databasename AND [Servername] = @Servername; END'; IF OBJECT_ID('Inspector.DatabaseGrowthFilenameSync') IS NULL EXEC('CREATE PROCEDURE [Inspector].[DatabaseGrowthFilenameSync] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[DatabaseGrowthFilenameSync] AS BEGIN --Revision date: 01/05/2019 SET NOCOUNT ON; UPDATE Growths SET [Drive] = [ServerDrives].[Drive] --SELECT --[Growths].GrowthID, --[Growths].[Database_name], --[Growths].[FileName], --[DatabaseFileSizes].[Filename], --[ServerDrives].[Drive], --[Growths].[Drive] FROM [Inspector].[DatabaseFileSizeHistory] Growths INNER JOIN [Inspector].[DatabaseFileSizes] ON [DatabaseFileSizes].Servername = [Growths].Servername AND [DatabaseFileSizes].[Database_name] = [Growths].[Database_name] AND [DatabaseFileSizes].Database_id = [Growths].Database_id AND [DatabaseFileSizes].[Filename] LIKE ''%''+[Growths].[FileName] INNER JOIN (SELECT [Servername],[Drive] FROM [Inspector].[DriveSpace] GROUP BY [Servername],[Drive] ) [ServerDrives] ON [DatabaseFileSizes].Servername = [ServerDrives].[Servername] AND [DatabaseFileSizes].[Filename] LIKE [ServerDrives].[Drive]+''%'' WHERE [Growths].[Drive] IS NULL; UPDATE [Inspector].[Settings] SET [Value] = NULL WHERE [Description] = ''InspectorUpgradeFilenameSync''; END'; IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Inspector].[TempDBInsert]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [Inspector].[TempDBInsert] AS' END EXEC sp_executesql N'ALTER PROCEDURE [Inspector].[TempDBInsert] AS BEGIN SET NOCOUNT ON; DECLARE @SessionID INT; DECLARE @TransactionStart DATETIME; DECLARE @DurationMins DECIMAL(18,2); DECLARE @TempDBPercentUsed DECIMAL(5,2); SET @TempDBPercentUsed = (SELECT ISNULL(TRY_CAST([Inspector].[GetServerModuleThreshold](@@SERVERNAME, ''TempDB'', ''TempDBPercentUsed'') AS DECIMAL(5,2)), 75.00)); IF (@TempDBPercentUsed IS NULL) BEGIN SET @TempDBPercentUsed = 75.00; END /* we need to remove old records based on retention per server not just the global retention */ DELETE tdb FROM [Inspector].[CurrentServers] cs INNER JOIN [Inspector].[TempDB] tdb ON cs.Servername = tdb.Servername WHERE [Log_Date] < DATEADD(DAY,ISNULL(TRY_CAST([Inspector].[GetServerModuleThreshold](cs.[Servername], ''TempDB'', ''TempDBDataRetentionDays'') AS INT), 7)*-1,GETDATE()) AND cs.[IsActive] = 1; /* oldest transaction */ SELECT @SessionID = SessionTrans.session_id, @TransactionStart = MIN(ActiveTrans.transaction_begin_time) FROM tempdb.sys.dm_tran_session_transactions SessionTrans JOIN tempdb.sys.dm_tran_active_transactions ActiveTrans ON SessionTrans.transaction_id = ActiveTrans.transaction_id JOIN tempdb.sys.dm_exec_sessions ExecSessions ON ExecSessions.session_id = SessionTrans.session_id JOIN tempdb.sys.dm_exec_connections Connections ON Connections.session_id = ExecSessions.session_id GROUP BY SessionTrans.session_id; /* Calculate the duraion in mins for the oldest transaction */ SET @DurationMins = CAST(DATEDIFF(SECOND,MIN(@TransactionStart),GETDATE())/60.00 AS DECIMAL(18,2)); /* TempDB File utilisation */ INSERT INTO [Inspector].[TempDB] ([Servername],[Log_Date],[DatabaseFilename], [Reserved_MB], [Unallocated_MB], [Internal_object_reserved_MB], [User_object_reserved_MB], [Version_store_reserved_MB], [UsedPct], [OldestTransactionSessionId], [OldestTransactionDurationMins], [TransactionStartTime]) SELECT [Servername], [Log_Date], [DatabaseFilename], [Reserved_MB], [Unallocated_MB], [Internal_object_reserved_MB], [User_object_reserved_MB], [Version_store_reserved_MB], [UsedPct], [OldestTransactionSessionId], [OldestTransactionDurationMins], [TransactionStartTime] FROM ( SELECT @@SERVERNAME AS Servername, GETDATE() AS Log_Date, master_files.name AS DatabaseFilename, CAST((unallocated_extent_page_count+version_store_reserved_page_count+user_object_reserved_page_count+internal_object_reserved_page_count+mixed_extent_page_count)*8/1024.00 AS DECIMAL(18,2)) AS Reserved_MB, CAST(unallocated_extent_page_count*8/1024.00 AS DECIMAL(18,2)) AS Unallocated_MB, CAST((internal_object_reserved_page_count*8)/1024.00 AS DECIMAL(18,2)) AS Internal_object_reserved_MB, CAST((user_object_reserved_page_count*8)/1024.00 AS DECIMAL(18,2)) AS User_object_reserved_MB, CAST(version_store_reserved_page_count*8/1024.00 AS DECIMAL(18,2)) AS Version_store_reserved_MB, CAST( ( ( CAST((internal_object_reserved_page_count*8)/1024.00 AS DECIMAL(18,2))+ CAST((user_object_reserved_page_count*8)/1024.00 AS DECIMAL(18,2))+ CAST(version_store_reserved_page_count*8/1024.00 AS DECIMAL(18,2)) ) /CAST((unallocated_extent_page_count+version_store_reserved_page_count+user_object_reserved_page_count+internal_object_reserved_page_count+mixed_extent_page_count)*8/1024.00 AS DECIMAL(18,2)) )*100.00 AS DECIMAL(18,2) ) AS UsedPct, @SessionID AS OldestTransactionSessionId, @DurationMins AS OldestTransactionDurationMins, @TransactionStart AS TransactionStartTime FROM tempdb.sys.dm_db_file_space_usage INNER JOIN tempdb.sys.master_files ON dm_db_file_space_usage.[file_id] = master_files.[file_id] AND dm_db_file_space_usage.[database_id] = master_files.[database_id] ) AS TempDBUsage WHERE UsedPct >= @TempDBPercentUsed; END'; IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Inspector].[TempDBReport]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [Inspector].[TempDBReport] AS' END EXEC sp_executesql N'ALTER PROCEDURE [Inspector].[TempDBReport] ( @Servername NVARCHAR(128), @Modulename VARCHAR(50), @TableHeaderColour VARCHAR(7) = ''#E6E6FA'', @WarningHighlight VARCHAR(7), @AdvisoryHighlight VARCHAR(7), @InfoHighlight VARCHAR(7), @ModuleConfig VARCHAR(20), @WarningLevel TINYINT, @ServerSpecific BIT, @NoClutter BIT, @TableTail VARCHAR(256), @HtmlOutput VARCHAR(MAX) OUTPUT, @CollectionOutOfDate BIT OUTPUT, @PSCollection BIT, @Debug BIT = 0 ) AS BEGIN --Revision date: 14/06/2021 DECLARE @TempDBPercentUsed DECIMAL(5,2); DECLARE @HtmlTableHead VARCHAR(2000); DECLARE @AgentJobOwnerExclusions VARCHAR(255); DECLARE @LastCollection DATETIME; DECLARE @ReportFrequency INT; DECLARE @MonitorHourStart INT; DECLARE @MonitorHourEnd INT; SET @MonitorHourStart = (SELECT [MonitorHourStart] FROM [Inspector].[MonitorHours] WHERE [Servername] = @Servername AND [Modulename] = @Modulename); SET @MonitorHourEnd = (SELECT [MonitorHourEnd] FROM [Inspector].[MonitorHours] WHERE [Servername] = @Servername AND [Modulename] = @Modulename); SET @TempDBPercentUsed = (SELECT ISNULL(TRY_CAST([Inspector].[GetServerModuleThreshold](@@SERVERNAME, ''TempDB'', ''TempDBPercentUsed'') AS DECIMAL(5,2)), 75.00)); SET @LastCollection = [Inspector].[GetLastCollectionDateTime] (@Modulename); EXEC [Inspector].[GetModuleConfigFrequency] @ModuleConfig, @Frequency = @ReportFrequency OUTPUT; SET @ReportFrequency *= -1; SET @Debug = [Inspector].[GetDebugFlag](@Debug,@ModuleConfig,@Modulename); IF @MonitorHourStart IS NULL BEGIN SET @MonitorHourStart = 0 END; IF @MonitorHourEnd IS NULL BEGIN SET @MonitorHourEnd = 23 END; --Set columns names for the Html table SET @HtmlTableHead = (SELECT [Inspector].[GenerateHtmlTableheader] ( @Servername, @Modulename, @ServerSpecific, ''TempDB file Usage above ''+CAST(@TempDBPercentUsed AS VARCHAR(10))+''%'' +'' in the last '' +CAST(ABS(@ReportFrequency) AS VARCHAR(6)) +''mins between the hours of '' +CAST(@MonitorHourStart AS VARCHAR(10)) +'' and '' +CAST(@MonitorHourEnd AS VARCHAR(10)), @TableHeaderColour, ''Servername,Log_Date,DatabaseFilename,Reserved_MB,Unallocated_MB,Internal_object_reserved_MB,User_object_reserved_MB,Version_store_reserved_MB,UsedPct,OldestTransactionSessionId,OldestTransactionDurationMins,TransactionStartTime'' ) ); /* if there has been a data collection since the last report frequency minutes ago then run the report */ IF(@LastCollection >= DATEADD(MINUTE,@ReportFrequency,GETDATE())) BEGIN SET @HtmlOutput = (SELECT CASE WHEN @WarningLevel IS NULL THEN @AdvisoryHighlight WHEN @WarningLevel = 1 THEN @WarningHighlight WHEN @WarningLevel = 2 THEN @AdvisoryHighlight WHEN @WarningLevel = 3 THEN @InfoHighlight END AS [@bgcolor], [Servername] AS ''td'','''',+ CONVERT(VARCHAR(17),[Log_Date],113) AS ''td'','''',+ [DatabaseFilename] AS ''td'','''',+ [Reserved_MB] AS ''td'','''',+ [Unallocated_MB] AS ''td'','''',+ [Internal_object_reserved_MB] AS ''td'','''',+ [User_object_reserved_MB] AS ''td'','''',+ [Version_store_reserved_MB] AS ''td'','''',+ [UsedPct] AS ''td'','''',+ ISNULL(CAST([OldestTransactionSessionId] AS VARCHAR(10)),''N/A'') AS ''td'','''',+ ISNULL(CAST([OldestTransactionDurationMins] AS VARCHAR(10)),''N/A'') AS ''td'','''',+ ISNULL(CONVERT(VARCHAR(24),[TransactionStartTime],113),''N/A'') AS ''td'','''' FROM [Inspector].[TempDB] WHERE Servername = @Servername AND Log_Date >= DATEADD(MINUTE,@ReportFrequency,GETDATE()) AND [DateHour] BETWEEN @MonitorHourStart AND @MonitorHourEnd FOR XML PATH(''tr''),ELEMENTS); END ELSE BEGIN SET @HtmlOutput = (SELECT CASE WHEN @WarningLevel IS NULL THEN @AdvisoryHighlight WHEN @WarningLevel = 1 THEN @WarningHighlight WHEN @WarningLevel = 2 THEN @AdvisoryHighlight WHEN @WarningLevel = 3 THEN @InfoHighlight END AS [@bgcolor], @Servername AS ''td'','''',+ ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''' FOR XML PATH(''tr''),ELEMENTS); --Mark Collection as out of date SET @CollectionOutOfDate = 1; END SET @HtmlOutput = @HtmlTableHead + @HtmlOutput + @TableTail +''
''
IF (@Debug = 1)
BEGIN
SELECT
OBJECT_NAME(@@PROCID) AS ''Procname'',
@Servername AS ''@Servername'',
@Modulename AS ''@Modulename'',
@TableHeaderColour AS ''@TableHeaderColour'',
@WarningHighlight AS ''@WarningHighlight'',
@AdvisoryHighlight AS ''@AdvisoryHighlight'',
@InfoHighlight AS ''@InfoHighlight'',
@ModuleConfig AS ''@ModuleConfig'',
@WarningLevel AS ''@WarningLevel'',
@NoClutter AS ''@NoClutter'',
@TableTail AS ''@TableTail'',
@HtmlOutput AS ''@HtmlOutput'',
@HtmlTableHead AS ''@HtmlTableHead'',
@CollectionOutOfDate AS ''@CollectionOutOfDate'',
@PSCollection AS ''@PSCollection''
END
END';
IF OBJECT_ID('Inspector.PSGetColumns') IS NULL
EXEC('CREATE PROCEDURE [Inspector].[PSGetColumns] AS;');
EXEC sp_executesql N'
ALTER PROCEDURE [Inspector].[PSGetColumns]
(
@Tablename NVARCHAR(128)
)
AS
BEGIN
SET NOCOUNT ON;
--Revision date: 23/09/2019
SELECT CAST(STUFF(Columnname,1,1,'''') AS VARCHAR(4000)) AS Columnnames
FROM
(
SELECT '',''+QUOTENAME(columns.name)
FROM sys.tables
INNER JOIN sys.columns ON tables.object_id = columns.object_id
WHERE tables.name = @Tablename
AND [schema_id] = SCHEMA_ID(N''Inspector'')
AND [is_computed] = 0
AND [is_ms_shipped] = 0
ORDER BY tables.name ASC,columns.column_id ASC
FOR XML PATH('''')
) AS ColumnList (Columnname)
END';
IF OBJECT_ID('Inspector.PSGetInspectorBuild') IS NULL
EXEC('CREATE PROCEDURE [Inspector].[PSGetInspectorBuild] AS;');
EXEC sp_executesql N'
ALTER PROCEDURE [Inspector].[PSGetInspectorBuild]
AS
BEGIN
--Revision date: 14/09/2018
SELECT
@@SERVERNAME AS Servername,
CAST([Value] AS DECIMAL(4,2)) AS Build
FROM [Inspector].[Settings]
WHERE [Description] = ''InspectorBuild''
END';
IF OBJECT_ID('Inspector.PSGetConfig') IS NULL
EXEC('CREATE PROCEDURE [Inspector].[PSGetConfig] AS;')
EXEC sp_executesql N'
ALTER PROCEDURE [Inspector].[PSGetConfig]
(
@Servername NVARCHAR(128),
@ModuleConfig VARCHAR(20) = NULL,
@PSExecModules BIT = 0
)
AS
BEGIN
--Revision date: 01/02/2020
--TableAction: 1 delete, 2 delete with retention, 3 Stage/merge
--InsertAction: 1 ALL, 2 Todays'' data only, 3 Frequency based
DECLARE @DriveSpaceRetentionPeriodInDays VARCHAR(6) = (SELECT ISNULL(TRY_CAST([Inspector].[GetServerModuleThreshold](@@SERVERNAME, ''DriveSpace'', ''DriveSpaceRetentionPeriodInDays'') AS INT), 90));
IF EXISTS (SELECT 1 FROM [Inspector].[CurrentServers] WHERE [Servername] = @Servername)
BEGIN
SELECT DISTINCT
[PSConfig].Servername,
[PSConfig].ModuleConfig_Desc,
[PSConfig].Modulename,
[PSConfig].Procedurename,
[PSConfig].Tablename,
[PSConfig].StageTablename,
[PSConfig].StageProcname,
[PSConfig].TableAction,
[PSConfig].InsertAction,
[PSConfig].RetentionInDays,
(SELECT [Frequency] FROM [Inspector].[Modules] WHERE [Modules].[ModuleConfig_Desc] = [PSConfig].[ModuleConfig_Desc] AND [Modules].[Modulename] = [PSConfig].[Modulename]) AS [Frequency]
FROM
(
SELECT
[Servername],
COALESCE(@ModuleConfig,[ModuleConfig_Desc], ''Default'') AS [ModuleConfig_Desc]
FROM [Inspector].[CurrentServers]
WHERE Servername = @Servername
AND IsActive = 1
) AS ActiveServers
INNER JOIN [Inspector].[PSConfig] ON [ActiveServers].ModuleConfig_Desc = [PSConfig].[ModuleConfig_Desc]
AND [ActiveServers].[Servername] = [PSConfig].[Servername]
WHERE [PSConfig].[IsActive] = 1
AND (EXISTS(SELECT 1 FROM [Inspector].[ModuleSchedulesDue] WHERE ([PSConfig].ModuleConfig_Desc = [ModuleSchedulesDue].[ModuleConfig_Desc] AND [PSConfig].[Modulename] = [ModuleSchedulesDue].[Modulename]))
OR @PSExecModules = 1)
UNION
SELECT
@Servername,
[ActiveServers].[ModuleConfig_Desc],
[NonModuleColection].[Module],
[NonModuleColection].[Module]+''Insert'' AS Procedurename,
CASE
WHEN [NonModuleColection].[Module] = ''InstanceVersion'' THEN ''InstanceVersion,InstanceVersionHistory''
ELSE [NonModuleColection].[Module]
END AS Tablename,
CASE
WHEN [NonModuleColection].[Module] = ''InstanceVersion'' THEN ''N/A,PSInstanceVersionHistoryStage''
ELSE NULL
END AS StageTablename,
CASE
WHEN [NonModuleColection].[Module] = ''InstanceVersion'' THEN ''N/A,PSGetInstanceVersionHistoryStage''
ELSE NULL
END AS StageProcname,
CASE
WHEN [NonModuleColection].[Module] = ''InstanceVersion'' THEN ''1,3''
ELSE ''1''
END AS TableAction,
CASE
WHEN [NonModuleColection].[Module] = ''InstanceVersion'' THEN ''1,1''
ELSE ''2''
END AS InsertAction,
NULL AS RetentionInDays,
1 AS [Frequency]
FROM
(
SELECT
[Servername],
COALESCE(@ModuleConfig,[ModuleConfig_Desc], ''Default'') AS [ModuleConfig_Desc]
FROM [Inspector].[CurrentServers]
WHERE Servername = @Servername
AND IsActive = 1
) AS ActiveServers
CROSS APPLY(VALUES(''InstanceStart''), (''InstanceVersion'')) AS NonModuleColection([Module])
ORDER BY [Modulename] ASC;
END
END';
IF OBJECT_ID('Inspector.PSGetServers') IS NULL
EXEC('CREATE PROCEDURE [Inspector].[PSGetServers] AS;');
EXEC sp_executesql N'
ALTER PROCEDURE [Inspector].[PSGetServers]
AS
BEGIN
--Revision date: 20/01/2020
SELECT
[Servername]
FROM [Inspector].[CurrentServers]
WHERE [IsActive] = 1
ORDER BY
CASE
WHEN [Servername] = @@SERVERNAME THEN 2
ELSE 1
END
END';
IF OBJECT_ID('Inspector.PSGetADHocDatabaseCreationsStage') IS NULL
EXEC('CREATE PROCEDURE [Inspector].[PSGetADHocDatabaseCreationsStage] AS;');
EXEC sp_executesql N'
ALTER PROCEDURE [Inspector].[PSGetADHocDatabaseCreationsStage]
(
@Servername NVARCHAR(128)
)
AS
BEGIN
--Revision date: 14/09/2018
SET NOCOUNT ON;
--Delete previous data for server
DELETE
FROM [Inspector].[ADHocDatabaseCreations]
WHERE Servername = @Servername;
--Insert new data for recent collection
INSERT INTO [Inspector].[ADHocDatabaseCreations] ([Servername], [Log_Date], [Databasename], [Create_Date])
SELECT [Servername], [Log_Date], [Databasename], [Create_Date]
FROM [Inspector].[PSADHocDatabaseCreationsStage] Stage
WHERE Servername = @Servername
AND NOT EXISTS (SELECT 1
FROM [Inspector].[ADHocDatabaseCreations] Base
WHERE Base.Servername = Stage.Servername
AND Base.Log_Date = Stage.Log_Date
AND Base.Databasename = Stage.Databasename)
--Insert if not exists
INSERT INTO [Inspector].[ADHocDatabaseSupression] (Servername, Log_Date, Databasename, Suppress)
SELECT
@Servername,
GETDATE(),
Databasename,
0
FROM [Inspector].[ADHocDatabaseCreations] Creations
WHERE Servername = @Servername
AND Databasename != ''No Ad hoc database creations present''
AND NOT EXISTS (SELECT Databasename
FROM [Inspector].[ADHocDatabaseSupression] SuppressList
WHERE SuppressList.Servername = @Servername AND SuppressList.Databasename = Creations.Databasename);
END';
IF OBJECT_ID('Inspector.PSGetAGDatabasesStage') IS NULL
EXEC('CREATE PROCEDURE [Inspector].[PSGetAGDatabasesStage] AS;');
EXEC sp_executesql N'
ALTER PROCEDURE [Inspector].[PSGetAGDatabasesStage]
(
@Servername NVARCHAR(128)
)
AS
BEGIN
--Revision date: 05/04/2019
SET NOCOUNT ON;
--Insert new data for recent collection
INSERT INTO [Inspector].[AGDatabases] ([Servername], [Log_Date], [LastUpdated], [Databasename], [Is_AG], [Is_AGJoined])
SELECT [Servername], [Log_Date], [LastUpdated], [Databasename], [Is_AG], [Is_AGJoined]
FROM [Inspector].[PSAGDatabasesStage] Stage
WHERE Servername = @Servername
AND NOT EXISTS (SELECT 1
FROM [Inspector].[AGDatabases] Base
WHERE Base.Servername = Stage.Servername
AND Base.Databasename = Stage.Databasename)
--Update any changes and set LastUpdated Datetime (Is_AG is not updated , this is controlled within the Central Table)
UPDATE Base
SET
[Is_AGJoined] = Stage.[Is_AGJoined],
[LastUpdated] = GETDATE()
FROM [Inspector].[PSAGDatabasesStage] Stage
INNER JOIN [Inspector].[AGDatabases] Base ON Base.Servername = Stage.Servername AND Base.Databasename = Stage.Databasename
WHERE Stage.Servername = @Servername;
END';
IF OBJECT_ID('Inspector.PSGetDriveSpaceStage') IS NULL
EXEC('CREATE PROCEDURE [Inspector].[PSGetDriveSpaceStage] AS;');
EXEC sp_executesql N'
ALTER PROCEDURE [Inspector].[PSGetDriveSpaceStage]
(
@Servername NVARCHAR(128)
)
AS
BEGIN
DECLARE @DriveSpaceRetentionPeriodInDays INT = (SELECT ISNULL(TRY_CAST([Inspector].[GetServerModuleThreshold](@@SERVERNAME, ''DriveSpace'', ''DriveSpaceRetentionPeriodInDays'') AS INT), 90));
--Remove old data for the server
DELETE
FROM [Inspector].[DriveSpace]
WHERE Servername = @Servername
AND Log_Date < DATEADD(DAY,-@DriveSpaceRetentionPeriodInDays,DATEADD(DAY,1,CAST(GETDATE() AS DATE)))
--Insert new data for recent collection
INSERT INTO [Inspector].[DriveSpace] ([Servername], [Log_Date], [Drive], [Capacity_GB], [AvailableSpace_GB])
SELECT [Servername], [Log_Date], [Drive], [Capacity_GB], [AvailableSpace_GB]
FROM [Inspector].[PSDriveSpaceStage] Stage
WHERE Servername = @Servername
AND NOT EXISTS (SELECT 1
FROM [Inspector].[DriveSpace] Base
WHERE Base.Servername = Stage.Servername
AND Base.Log_Date = Stage.Log_Date)
END';
IF OBJECT_ID('Inspector.PSGetDatabaseGrowthsStage') IS NULL
EXEC('CREATE PROCEDURE [Inspector].[PSGetDatabaseGrowthsStage] AS;');
EXEC sp_executesql N'
ALTER PROCEDURE [Inspector].[PSGetDatabaseGrowthsStage]
(
@Servername NVARCHAR(128)
)
AS
BEGIN
--Revision date: 08/04/2019
--Update existing records
UPDATE Base
SET [Database_name] = [PSStage].[Database_name],
[OriginalSize_MB] = [PSStage].[OriginalSize_MB],
[Type_desc] = [PSStage].[Type_desc],
[File_id] = [PSStage].[File_id],
[Filename] = [PSStage].[Filename],
[PostGrowthSize_MB] = [PSStage].[PostGrowthSize_MB],
[GrowthRate] = [PSStage].[GrowthRate],
[Is_percent_growth] = [PSStage].[Is_percent_growth],
[NextGrowth] = [PSStage].[NextGrowth],
[LastUpdated] = [PSStage].[LastUpdated]
FROM [Inspector].[PSDatabaseFileSizesStage] PSStage
INNER JOIN [Inspector].[DatabaseFileSizes] Base ON PSStage.Database_id = Base.Database_id AND PSStage.[File_id] = Base.[File_id] AND Base.[Servername] = @Servername
WHERE PSStage.Servername = @Servername
AND ((PSStage.LastUpdated > Base.LastUpdated OR PSStage.LastUpdated IS NOT NULL AND Base.LastUpdated IS NULL)
OR [Base].[Filename] != [PSStage].[Filename])
--Insert missing rows in base from stage table
INSERT INTO [Inspector].[DatabaseFileSizes] ([Servername], [Database_id], [Database_name], [OriginalDateLogged], [OriginalSize_MB], [Type_desc], [File_id], [Filename], [PostGrowthSize_MB], [GrowthRate], [Is_percent_growth], [NextGrowth], [LastUpdated])
SELECT
[Servername],
[Database_id],
[Database_name],
[OriginalDateLogged],
[OriginalSize_MB],
[Type_desc],
[File_id],
[Filename],
[PostGrowthSize_MB],
[GrowthRate],
[Is_percent_growth],
[NextGrowth],
[LastUpdated]
FROM [Inspector].[PSDatabaseFileSizesStage] PSStage
WHERE PSStage.Servername = @Servername
AND NOT EXISTS (SELECT 1
FROM [Inspector].[DatabaseFileSizes] Base
WHERE PSStage.Database_id = Base.Database_id
AND PSStage.[File_id] = Base.[File_id]
AND Base.Servername = @Servername)
--Insert growth events
INSERT INTO [Inspector].[DatabaseFileSizeHistory] ([Servername], [Database_id], [Database_name], [Log_Date], [Type_Desc], [File_id], [Drive], [FileName], [PreGrowthSize_MB], [GrowthRate_MB], [GrowthIncrements], [PostGrowthSize_MB])
SELECT [Servername], [Database_id], [Database_name], [Log_Date], [Type_Desc], [File_id], [Drive], [FileName], [PreGrowthSize_MB], [GrowthRate_MB], [GrowthIncrements], [PostGrowthSize_MB]
FROM [Inspector].[PSDatabaseFileSizeHistoryStage] PSStage
WHERE NOT EXISTS (SELECT 1
FROM [Inspector].[DatabaseFileSizeHistory] Base
WHERE PSStage.Database_id = Base.Database_id
AND PSStage.[File_id] = Base.[File_id]
AND Base.Servername = PSStage.Servername
AND PSStage.Log_Date = Base.Log_Date)
END';
IF OBJECT_ID('Inspector.PSGetInstanceVersionHistoryStage') IS NULL
EXEC('CREATE PROCEDURE [Inspector].[PSGetInstanceVersionHistoryStage] AS;');
EXEC sp_executesql N'
ALTER PROCEDURE [Inspector].[PSGetInstanceVersionHistoryStage]
(
@Servername NVARCHAR(128)
)
AS
BEGIN
--Revision date: 11/01/2019
SET NOCOUNT ON;
--Insert new data for recent collection
INSERT INTO [Inspector].[InstanceVersionHistory] ([Servername], [Log_Date], [CollectionDatetime], [VersionNo], [Edition])
SELECT [Servername], [Log_Date], [CollectionDatetime], [VersionNo], [Edition]
FROM [Inspector].[PSInstanceVersionHistoryStage] Stage
WHERE Servername = @Servername
AND NOT EXISTS (SELECT 1
FROM [Inspector].[InstanceVersionHistory] Base
WHERE Base.Servername = Stage.Servername
AND Base.Log_Date = Stage.Log_Date
AND CAST(Base.Log_Date AS DATE) = CAST(Stage.Log_Date AS DATE)
);
END';
IF OBJECT_ID('Inspector.PSGetAGPrimaryHistoryStage') IS NULL
EXEC('CREATE PROCEDURE [Inspector].[PSGetAGPrimaryHistoryStage] AS;');
EXEC sp_executesql N'
ALTER PROCEDURE [Inspector].[PSGetAGPrimaryHistoryStage]
(
@Servername NVARCHAR(128)
)
AS
BEGIN
--Revision date: 08/05/2019
SET NOCOUNT ON;
--Insert growth events
INSERT INTO [Inspector].[AGPrimaryHistory] ([Log_Date], [CollectionDateTime], [Servername], [AGname])
SELECT [Log_Date], [CollectionDateTime], [Servername], [AGname]
FROM [Inspector].[PSAGPrimaryHistoryStage] PSStage
WHERE NOT EXISTS (SELECT 1
FROM [Inspector].[AGPrimaryHistory] Base
WHERE PSStage.[AGname] = Base.[AGname]
AND Base.[Servername] = PSStage.[Servername]
AND PSStage.[CollectionDateTime] = Base.[CollectionDateTime])
END';
IF OBJECT_ID('Inspector.PSHistCleanup') IS NULL
EXEC('CREATE PROCEDURE [Inspector].[PSHistCleanup] AS;');
EXEC sp_executesql N'
ALTER PROCEDURE [Inspector].[PSHistCleanup]
AS
BEGIN
--Revision date: 07/07/2021
DECLARE @Retention INT = (SELECT ISNULL(TRY_CAST([Inspector].[GetServerModuleThreshold](@@SERVERNAME, ''DriveSpace'', ''DriveSpaceRetentionPeriodInDays'') AS INT), 90));
--Clean up Drivespace table for history older than @Retention in days
DELETE FROM [Inspector].[DriveSpace]
WHERE Log_Date < DATEADD(DAY,-@Retention,DATEADD(DAY,1,CAST(GETDATE() AS DATE)));
--Clean up the history for growths older than @Retention in days
DELETE FROM [Inspector].[DatabaseFileSizeHistory]
WHERE [Log_Date] < DATEADD(DAY,-@Retention,GETDATE());
END';
IF OBJECT_ID('Inspector.PopulatePSConfig') IS NULL
EXEC('CREATE PROCEDURE [Inspector].[PopulatePSConfig] AS;');
EXEC sp_executesql N'
ALTER PROCEDURE [Inspector].[PopulatePSConfig]
AS
BEGIN
--Revision date: 27/01/2020
--TableAction: 1 delete, 2 delete with retention, 3 Stage/merge
--InsertAction: 1 ALL, 2 Todays'' data only
DECLARE @DriveSpaceRetentionPeriodInDays VARCHAR(6);
SET @DriveSpaceRetentionPeriodInDays = (SELECT ISNULL(TRY_CAST([Inspector].[GetServerModuleThreshold](@@SERVERNAME, ''DriveSpace'', ''DriveSpaceRetentionPeriodInDays'') AS INT), 90));
INSERT INTO [Inspector].[PSConfig] ([Servername], [ModuleConfig_Desc], [Modulename], [Procedurename], [Tablename], [StageTablename], [StageProcname], [TableAction], [InsertAction], [RetentionInDays], [IsActive])
SELECT
Servername,
ModuleConfig_Desc,
Modulename,
Procedurename,
Tablename,
StageTablename,
StageProcname,
TableAction,
InsertAction,
RetentionInDays,
1
FROM
(
SELECT
[ActiveServers].[Servername],
[ActiveServers].[ModuleConfig_Desc],
[PSEnabledModules].[Modulename],
[CollectionProcedurename] AS Procedurename,
CASE
WHEN [PSEnabledModules].[Modulename] = ''DatabaseGrowths''
THEN ''DatabaseFileSizes,DatabaseFileSizeHistory''
WHEN [PSEnabledModules].[Modulename] = ''ADHocDatabaseCreations''
THEN ''ADHocDatabaseCreations,ADHocDatabaseSupression''
WHEN [PSEnabledModules].[Modulename] = ''AGCheck''
THEN ''AGCheck,AGPrimaryHistory''
WHEN [CollectionProcedurename] IS NULL THEN NULL
ELSE [PSEnabledModules].[Modulename]
END AS Tablename,
CASE
WHEN [PSEnabledModules].[Modulename] IN (''AGDatabases'',''DriveSpace'')
THEN ''PS''+[PSEnabledModules].[Modulename]+''Stage''
WHEN [PSEnabledModules].[Modulename] = ''ADHocDatabaseCreations''
THEN ''PSADHocDatabaseCreationsStage,PSADHocDatabaseSupressionStage''
WHEN [PSEnabledModules].[Modulename] = ''DatabaseGrowths''
THEN ''PSDatabaseFileSizesStage,PSDatabaseFileSizeHistoryStage''
WHEN [PSEnabledModules].[Modulename] = ''AGCheck''
THEN ''N/A,PSAGPrimaryHistoryStage''
ELSE NULL
END AS StageTablename,
CASE
WHEN [PSEnabledModules].[Modulename] IN (''AGDatabases'', ''DriveSpace'', ''DatabaseGrowths'', ''ADHocDatabaseCreations'')
THEN ''PSGet''+[PSEnabledModules].[Modulename]+''Stage''
WHEN [PSEnabledModules].[Modulename] = ''AGCheck''
THEN ''N/A,PSGetAGPrimaryHistoryStage''
ELSE NULL
END AS StageProcname,
CASE
WHEN [PSEnabledModules].[Modulename] IN (''AGDatabases'',''DriveSpace'')
THEN ''3''
WHEN [PSEnabledModules].[Modulename] IN (''ADHocDatabaseCreations'',''DatabaseGrowths'')
THEN ''3,3''
WHEN [PSEnabledModules].[Modulename] = ''AGCheck''
THEN ''1,3''
ELSE ''1''
END AS TableAction, --1 delete, 2 delete with retention, 3 Stage/merge
CASE
WHEN [PSEnabledModules].[Modulename] IN (''AGDatabases'',''BackupSizesByDay'')
THEN ''1''
WHEN [PSEnabledModules].[Modulename] IN (''ADHocDatabaseCreations'',''AGCheck'')
THEN ''1,1''
WHEN [PSEnabledModules].[Modulename] = ''DatabaseGrowths''
THEN ''1,2''
ELSE ''2''
END AS InsertAction, --1 ALL, 2 Todays'' data only
CASE
WHEN [PSEnabledModules].[Modulename] = (''DatabaseGrowths'') THEN @DriveSpaceRetentionPeriodInDays+'',''+@DriveSpaceRetentionPeriodInDays
WHEN [PSEnabledModules].[Modulename] = (''DriveSpace'') THEN @DriveSpaceRetentionPeriodInDays
ELSE NULL
END AS RetentionInDays
FROM
(
SELECT
[Servername],
ISNULL([ModuleConfig_Desc], ''Default'') AS [ModuleConfig_Desc]
FROM [Inspector].[CurrentServers]
--WHERE Servername = @Servername
WHERE IsActive = 1
) AS ActiveServers
INNER JOIN [Inspector].[PSEnabledModules] ON [ActiveServers].ModuleConfig_Desc = [PSEnabledModules].[ModuleConfig_Desc]
) AS PSConfigList
WHERE NOT EXISTS (SELECT 1
FROM [Inspector].[PSConfig]
WHERE [PSConfig].[Servername] = [PSConfigList].[Servername]
AND [PSConfig].[ModuleConfig_Desc] = [PSConfigList].[ModuleConfig_Desc]
AND [PSConfig].[Modulename] = [PSConfigList].[Modulename]);
END';
IF OBJECT_ID('Inspector.PSGetSettingsTables') IS NULL
EXEC('CREATE PROCEDURE [Inspector].[PSGetSettingsTables] AS;');
EXEC sp_executesql N'
ALTER PROCEDURE [Inspector].[PSGetSettingsTables]
(
@SortOrder BIT, --0 FOR ORDER BY TableOrder ASC , 1 FOR ORDER BY TableOrder DESC
@PSCollection BIT = 0 --If its a powershell collection ensure that the WarningLevel table is populated
)
AS
--Revision date: 24/11/2020
--Config for Powershell collection use only
--TruncateTable - 0 Delete contents, 1 Truncate table
--ReseedTable - 0 do not reseed identity, 1 reseed identity
BEGIN
IF @SortOrder IS NULL
BEGIN
SET @SortOrder = 0;
END
IF @PSCollection IS NULL
BEGIN
SET @PSCollection = 0;
END
IF @PSCollection = 1
BEGIN
EXEC [Inspector].[PopulateModuleWarningLevel];
END
IF @SortOrder = 0
BEGIN
SELECT Tablename,TruncateTable,ReseedTable
FROM (VALUES
(1,''Settings'',1,1),
(2,''CurrentServers'',0,0),
--(3,''EmailRecipients'',0,0),
(4,''EmailConfig'',0,0),
(5,''CatalogueModules'',0,0),
(6,''ModuleWarningLevel'',0,0),
(7,''AGCheckConfig'',1,0),
(8,''ServerSettingThresholds'',1,0)
) SettingsTables(TableOrder,Tablename,TruncateTable,ReseedTable)
ORDER BY TableOrder ASC;
END
IF @SortOrder = 1
BEGIN
SELECT Tablename,TruncateTable,ReseedTable
FROM (VALUES
(1,''Settings'',1,1),
(2,''CurrentServers'',0,0),
--(3,''EmailRecipients'',0,0),
(4,''EmailConfig'',0,0),
(5,''CatalogueModules'',0,0),
(6,''ModuleWarningLevel'',0,0),
(7,''AGCheckConfig'',1,0),
(8,''ServerSettingThresholds'',1,0)
) SettingsTables(TableOrder,Tablename,TruncateTable,ReseedTable)
ORDER BY TableOrder DESC;
END
END';
IF OBJECT_ID('Inspector.CatalogueMissingLogins') IS NULL
EXEC('CREATE PROCEDURE [Inspector].[CatalogueMissingLogins] AS;');
--Catalogue reporting procs
EXEC sp_executesql N'
ALTER PROCEDURE [Inspector].[CatalogueMissingLogins]
(
@Servername NVARCHAR(128),
@TableHeaderColour VARCHAR(7) = NULL,
@WarningLevelFontColour VARCHAR(7) = NULL,
@HtmlOutput VARCHAR(MAX) OUTPUT,
@ModuleConfig VARCHAR(20),
@PSCollection BIT
)
AS
--Revision date: 09/05/2019
BEGIN
SET NOCOUNT ON;
DECLARE @PreReqsEnabled INT
DECLARE @ReportStart DATETIME = GETDATE();
DECLARE @Duration MONEY
--Ensure that all modules required for this check are enabled
EXEC sp_executesql N''SELECT @EnabledCount = COUNT([Active]) FROM [Catalogue].[ConfigModules] WHERE ModuleName IN (''''Availability Groups'''',''''Databases'''',''''Logins'''',''''Users'''') AND [Active] = 1;'',
N''@EnabledCount INT OUTPUT'',@EnabledCount = @PreReqsEnabled OUTPUT
IF @PreReqsEnabled < 4
BEGIN
RAISERROR(''PreRequisites for the Catalogue Missing Logins module are not enabled in the Catalogue'',0,0) WITH NOWAIT;
RETURN;
END
IF @TableHeaderColour IS NULL BEGIN SET @TableHeaderColour = ''#E6E6FA'' END;
IF OBJECT_ID(''tempdb.dbo.#ServerLogins'') IS NOT NULL
DROP TABLE #ServerLogins;
CREATE TABLE #ServerLogins (
AGName NVARCHAR(128),
LoginName NVARCHAR(128),
ServerName NVARCHAR(128)
);
EXEC sp_executesql
N''INSERT INTO #ServerLogins (AGName,LoginName,ServerName)
SELECT DISTINCT AGs.AGName, Logins.LoginName, AGs2.ServerName
FROM (SELECT DISTINCT AGName FROM Catalogue.AvailabilityGroups WHERE ServerName = @Servername) AGList
INNER JOIN Catalogue.AvailabilityGroups AGs ON AGList.AGName = AGs.AGName
INNER JOIN Catalogue.Logins Logins ON AGs.ServerName = Logins.ServerName
INNER JOIN Catalogue.AvailabilityGroups AGs2 ON AGs.AGName = AGs2.AGName
WHERE NOT EXISTS (SELECT 1
FROM Catalogue.AvailabilityGroups AGs3
JOIN Catalogue.Logins Logins3 ON AGs3.ServerName = Logins3.ServerName
WHERE AGs3.AGName = AGs.AGName
AND AGs3.ServerName = AGs2.ServerName
AND Logins3.LoginName = Logins.LoginName)
AND Logins.LoginName NOT IN (SELECT [LoginName] FROM [Inspector].[CatalogueSIDExclusions] WHERE [AGs].[AGName] = [CatalogueSIDExclusions].[AGName])
AND Logins.LoginName != ''''sa''''
AND AGs.LastRecorded >= DATEADD(DAY,-1,GETDATE())
AND Logins.LastRecorded >= DATEADD(DAY,-1,GETDATE())
AND AGs2.LastRecorded >= DATEADD(DAY,-1,GETDATE())
AND EXISTS (SELECT 1 FROM [Catalogue].[ExecutionLog] WHERE [ExecutionDate] >= DATEADD(DAY,-1,GETDATE()))
SET @HtmlOutput = (
SELECT
@WarningLevelFontColour AS [@bgcolor],
ServerName AS ''''td'''','''''''', +
LoginName AS ''''td'''','''''''', +
CreateCommand AS ''''td'''',''''''''
FROM
(
SELECT DISTINCT
#ServerLogins.ServerName,
Logins.LoginName,
CASE
WHEN Logins.LoginName LIKE ''''%\%'''' THEN ''''CREATE LOGIN '''' + QUOTENAME(Logins.LoginName) + '''' FROM WINDOWS''''
ELSE ''''CREATE LOGIN '''' + QUOTENAME(Logins.LoginName) + '''' WITH PASSWORD = 0x'''' + CONVERT(VARCHAR(MAX), Logins.PasswordHash, 2) + '''' HASHED, SID = 0x'''' + CONVERT(VARCHAR(MAX), Logins.SID, 2)
END AS CreateCommand
FROM Catalogue.AvailabilityGroups AGs
JOIN Catalogue.Logins Logins ON AGs.ServerName = Logins.ServerName
JOIN #ServerLogins ON AGs.AGName = #ServerLogins.AGName AND Logins.LoginName = #ServerLogins.LoginName
JOIN Catalogue.Users Users ON Users.MappedLoginName = #ServerLogins.LoginName
JOIN Catalogue.Databases Databases ON Users.DBName = Databases.DBName
AND AGs.AGName = Databases.AGName
WHERE AGs.Role = ''''PRIMARY''''
AND #ServerLogins.ServerName = @Servername
AND AGs.LastRecorded >= DATEADD(DAY,-1,GETDATE())
AND Logins.LastRecorded >= DATEADD(DAY,-1,GETDATE())
AND Users.LastRecorded >= DATEADD(DAY,-1,GETDATE())
AND Databases.LastRecorded >= DATEADD(DAY,-1,GETDATE())
) AS MissingLoginInfo
FOR XML PATH(''''tr''''),ELEMENTS);'',N''@Servername NVARCHAR(128), @WarningLevelFontColour VARCHAR(7), @HtmlOutput VARCHAR(MAX) OUTPUT'',
@Servername = @Servername, @WarningLevelFontColour = @WarningLevelFontColour, @HtmlOutput = @HtmlOutput OUTPUT;
IF @HtmlOutput IS NOT NULL
BEGIN
SET @HtmlOutput =
''Missing Logins:
| Servername | LoginName | CreateCommand | ''+@HtmlOutput +''
'';
END
SET @Duration = CAST(DATEDIFF(MILLISECOND,@ReportStart,GETDATE()) AS MONEY)/1000;
EXEC [Inspector].[ExecutionLogInsert]
@RunDatetime = @ReportStart,
@Servername = @Servername,
@ModuleConfigDesc = @ModuleConfig,
@Procname = N''CatalogueMissingLogins'',
@Duration = @Duration,
@PSCollection = @PSCollection;
END';
IF OBJECT_ID('Inspector.CatalogueDroppedTables') IS NULL
EXEC('CREATE PROCEDURE [Inspector].[CatalogueDroppedTables] AS;');
EXEC sp_executesql N'
ALTER PROCEDURE [Inspector].[CatalogueDroppedTables]
(
@Servername NVARCHAR(128),
@TableHeaderColour VARCHAR(7) = NULL,
@WarningLevelFontColour VARCHAR(7) = NULL,
@HtmlOutput VARCHAR(MAX) OUTPUT,
@ModuleConfig VARCHAR(20),
@PSCollection BIT
)
AS
--Revision date: 16/03/2019
BEGIN
DECLARE @LastestExecution DATETIME
DECLARE @ReportStart DATETIME = GETDATE();
DECLARE @Duration MONEY
IF @TableHeaderColour IS NULL BEGIN SET @TableHeaderColour = ''#E6E6FA'' END;
EXEC sp_executesql N''SELECT @LastestExecution = MAX(ExecutionDate) FROM [Catalogue].[ExecutionLog];'',
N''@LastestExecution DATETIME OUTPUT'',@LastestExecution = @LastestExecution OUTPUT;
EXEC sp_executesql
N''
SET @HtmlOutput = (
SELECT
@WarningLevelFontColour AS [@bgcolor],
CatalogueTables.ServerName AS ''''td'''','''''''', +
CatalogueTables.DatabaseName AS ''''td'''','''''''', +
CatalogueTables.SchemaName AS ''''td'''','''''''', +
CatalogueTables.TableName AS ''''td'''','''''''', +
CONVERT(VARCHAR(17),CatalogueTables.LastRecorded,113) AS ''''td'''',''''''''
FROM [Inspector].[CurrentServers] InspectorServers
INNER JOIN [Catalogue].[Tables] CatalogueTables ON CatalogueTables.ServerName = InspectorServers.Servername
WHERE CatalogueTables.ServerName = @Servername
AND [CatalogueTables].[LastRecorded] >= DATEADD(DAY,-1,GETDATE())
AND [CatalogueTables].[LastRecorded] < @LastestExecution
AND [DatabaseName] != ''''tempdb''''
AND NOT EXISTS (SELECT 1 FROM [Catalogue].[Databases] CatalogueDatabases
WHERE CatalogueDatabases.ServerName = InspectorServers.Servername
AND CatalogueDatabases.DBName= CatalogueTables.DatabaseName
AND [CatalogueDatabases].[LastRecorded] < @LastestExecution)
FOR XML PATH(''''tr''''),ELEMENTS);'',N''@Servername NVARCHAR(128), @WarningLevelFontColour VARCHAR(7), @LastestExecution DATETIME,@HtmlOutput VARCHAR(MAX) OUTPUT'',
@Servername = @Servername, @WarningLevelFontColour = @WarningLevelFontColour,@LastestExecution = @LastestExecution,@HtmlOutput = @HtmlOutput OUTPUT;
IF @HtmlOutput IS NOT NULL
BEGIN
SET @HtmlOutput =
''Tables dropped in the last 24hrs:
| Servername | Database name | Schema name | Table name | LastSeenByCatalogue | ''+@HtmlOutput +''
'';
END
SET @Duration = CAST(DATEDIFF(MILLISECOND,@ReportStart,GETDATE()) AS MONEY)/1000;
EXEC [Inspector].[ExecutionLogInsert]
@RunDatetime = @ReportStart,
@Servername = @Servername,
@ModuleConfigDesc = @ModuleConfig,
@Procname = N''CatalogueDroppedTables'',
@Duration = @Duration,
@PSCollection = @PSCollection;
END';
IF OBJECT_ID('Inspector.CatalogueDroppedDatabases') IS NULL
EXEC('CREATE PROCEDURE [Inspector].[CatalogueDroppedDatabases] AS;');
EXEC sp_executesql N'
ALTER PROCEDURE [Inspector].[CatalogueDroppedDatabases]
(
@Servername NVARCHAR(128),
@TableHeaderColour VARCHAR(7) = NULL,
@WarningLevelFontColour VARCHAR(7) = NULL,
@HtmlOutput VARCHAR(MAX) OUTPUT,
@ModuleConfig VARCHAR(20),
@PSCollection BIT
)
AS
--Revision date: 16/03/2019
BEGIN
DECLARE @LastestExecution DATETIME
DECLARE @ReportStart DATETIME = GETDATE();
DECLARE @Duration MONEY
IF @TableHeaderColour IS NULL BEGIN SET @TableHeaderColour = ''#E6E6FA'' END;
EXEC sp_executesql N''SELECT @LastestExecution = MAX(ExecutionDate) FROM [Catalogue].[ExecutionLog];'',
N''@LastestExecution DATETIME OUTPUT'',@LastestExecution = @LastestExecution OUTPUT;
EXEC sp_executesql
N''
SET @HtmlOutput = (
SELECT
@WarningLevelFontColour AS [@bgcolor],
CatalogueDatabases.ServerName AS ''''td'''','''''''', +
CatalogueDatabases.DBName AS ''''td'''','''''''', +
ISNULL(AGName,N''''Not in an AG'''') AS ''''td'''','''''''', +
CatalogueDatabases.FilePaths AS ''''td'''','''''''', +
DATEDIFF(DAY,FirstRecorded,LastRecorded) AS ''''td'''','''''''', +
CONVERT(VARCHAR(17),CatalogueDatabases.LastRecorded,113) AS ''''td'''',''''''''
FROM [Catalogue].[Databases] CatalogueDatabases
INNER JOIN [Inspector].[CurrentServers] InspectorServers ON CatalogueDatabases.ServerName = InspectorServers.Servername
WHERE CatalogueDatabases.ServerName = @Servername
AND [CatalogueDatabases].[LastRecorded] >= DATEADD(DAY,-1,GETDATE())
AND [CatalogueDatabases].[LastRecorded] < @LastestExecution
FOR XML PATH(''''tr''''),ELEMENTS);'',N''@Servername NVARCHAR(128), @WarningLevelFontColour VARCHAR(7), @LastestExecution DATETIME,@HtmlOutput VARCHAR(MAX) OUTPUT'',
@Servername = @Servername, @WarningLevelFontColour = @WarningLevelFontColour,@LastestExecution = @LastestExecution,@HtmlOutput = @HtmlOutput OUTPUT;
IF @HtmlOutput IS NOT NULL
BEGIN
SET @HtmlOutput =
''Databases dropped in the last 24hrs:
| Servername | Database name | AG name | File paths | DaysSeenByCatalogue | LastSeenByCatalogue | ''+@HtmlOutput +''
''; END SET @Duration = CAST(DATEDIFF(MILLISECOND,@ReportStart,GETDATE()) AS MONEY)/1000; EXEC [Inspector].[ExecutionLogInsert] @RunDatetime = @ReportStart, @Servername = @Servername, @ModuleConfigDesc = @ModuleConfig, @Procname = N''CatalogueDroppedDatabases'', @Duration = @Duration, @PSCollection = @PSCollection; END'; IF OBJECT_ID('Inspector.ADHocDatabaseCreationsReport') IS NULL EXEC('CREATE PROCEDURE [Inspector].[ADHocDatabaseCreationsReport] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[ADHocDatabaseCreationsReport] ( @Servername NVARCHAR(128), @Modulename VARCHAR(50), @TableHeaderColour VARCHAR(7) = ''#E6E6FA'', @WarningHighlight VARCHAR(7), @AdvisoryHighlight VARCHAR(7), @InfoHighlight VARCHAR(7), @ModuleConfig VARCHAR(20), @WarningLevel TINYINT, @ServerSpecific BIT, @NoClutter BIT, @TableTail VARCHAR(256), @HtmlOutput VARCHAR(MAX) OUTPUT, @CollectionOutOfDate BIT OUTPUT, @PSCollection BIT, @Debug BIT = 0 ) AS BEGIN --Revision date: 20/04/2021 DECLARE @HtmlTableHead VARCHAR(2000); DECLARE @LastCollection DATETIME; DECLARE @ReportFrequency INT; SET @LastCollection = [Inspector].[GetLastCollectionDateTime] (@Modulename); EXEC [Inspector].[GetModuleConfigFrequency] @ModuleConfig, @Frequency = @ReportFrequency OUTPUT; SET @ReportFrequency *= -1; SET @Debug = [Inspector].[GetDebugFlag](@Debug,@ModuleConfig,@Modulename); --Set columns names for the Html table SET @HtmlTableHead = (SELECT [Inspector].[GenerateHtmlTableheader] ( @Servername, @Modulename, @ServerSpecific, ''Potential Ad hoc database creations in the last 7 days'', @TableHeaderColour, ''Database name,Create date,Suppress database'') ); /* if there has been a data collection since the last report frequency minutes ago then run the report */ IF(@LastCollection >= DATEADD(MINUTE,@ReportFrequency,GETDATE())) BEGIN SET @HtmlOutput = (SELECT CASE WHEN @WarningLevel IS NULL THEN @AdvisoryHighlight WHEN @WarningLevel = 1 THEN @WarningHighlight WHEN @WarningLevel = 2 THEN @AdvisoryHighlight WHEN @WarningLevel = 3 THEN @InfoHighlight END AS [@bgcolor], Databasename AS ''td'','''', + CONVERT(VARCHAR(17),Create_Date,113) AS ''td'','''', + ''EXEC [''+DB_NAME()+''].[Inspector].[SuppressAdHocDatabase] @Databasename = ''''''+Databasename+'''''', @Servername = ''''''+@Servername+'''''';'' AS ''td'','''' FROM [Inspector].[ADHocDatabaseCreations] WHERE Servername = @Servername AND Databasename != ''No Ad hoc database creations present'' AND Databasename NOT IN ( SELECT Databasename FROM [Inspector].[ADHocDatabaseSupression] Suppressed WHERE Servername = @Servername AND Suppressed.Suppress = 1) ORDER BY Create_Date ASC FOR XML PATH(''tr''),ELEMENTS); IF @HtmlOutput IS NULL BEGIN SET @HtmlOutput = (SELECT ''#FFFFFF'' AS [@bgcolor], ''No Ad hoc database creations present'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''' FOR XML PATH(''tr''),ELEMENTS); END END ELSE BEGIN SET @HtmlOutput = (SELECT CASE WHEN @WarningLevel IS NULL THEN @AdvisoryHighlight WHEN @WarningLevel = 1 THEN @WarningHighlight WHEN @WarningLevel = 2 THEN @AdvisoryHighlight WHEN @WarningLevel = 3 THEN @InfoHighlight END AS [@bgcolor], ''Data Collection out of date'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''' FOR XML PATH(''tr''),ELEMENTS); --Mark Collection as out of date SET @CollectionOutOfDate = 1; END IF (@NoClutter = 1) BEGIN IF (@HtmlOutput LIKE ''%No Ad hoc database creations present%'') BEGIN SET @HtmlOutput = NULL; END END SET @HtmlOutput = @HtmlTableHead + @HtmlOutput + @TableTail +''
'' IF (@Debug = 1) BEGIN SELECT OBJECT_NAME(@@PROCID) AS ''Procname'', @Servername AS ''@Servername'', @Modulename AS ''@Modulename'', @TableHeaderColour AS ''@TableHeaderColour'', @WarningHighlight AS ''@WarningHighlight'', @AdvisoryHighlight AS ''@AdvisoryHighlight'', @InfoHighlight AS ''@InfoHighlight'', @ModuleConfig AS ''@ModuleConfig'', @WarningLevel AS ''@WarningLevel'', @NoClutter AS ''@NoClutter'', @TableTail AS ''@TableTail'', @HtmlOutput AS ''@HtmlOutput'', @HtmlTableHead AS ''@HtmlTableHead'', @CollectionOutOfDate AS ''@CollectionOutOfDate'', @PSCollection AS ''@PSCollection'' END END'; IF OBJECT_ID('Inspector.AGCheckReport') IS NULL EXEC('CREATE PROCEDURE [Inspector].[AGCheckReport] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[AGCheckReport] ( @Servername NVARCHAR(128), @Modulename VARCHAR(50), @TableHeaderColour VARCHAR(7) = ''#E6E6FA'', @WarningHighlight VARCHAR(7), @AdvisoryHighlight VARCHAR(7), @InfoHighlight VARCHAR(7), @ModuleConfig VARCHAR(20), @WarningLevel TINYINT, @ServerSpecific BIT, @NoClutter BIT, @TableTail VARCHAR(256), @HtmlOutput VARCHAR(MAX) OUTPUT, @CollectionOutOfDate BIT OUTPUT, @PSCollection BIT, @Debug BIT = 0 ) AS BEGIN --Revision date: 20/04/2021 DECLARE @HtmlTableHeadAG VARCHAR(2000); DECLARE @HtmlTableHeadFailover VARCHAR(2000); DECLARE @FailoverCheckHTML VARCHAR(MAX); DECLARE @LastCollection DATETIME; DECLARE @ReportFrequency INT; SET @LastCollection = [Inspector].[GetLastCollectionDateTime] (@Modulename); EXEC [Inspector].[GetModuleConfigFrequency] @ModuleConfig, @Frequency = @ReportFrequency OUTPUT; SET @ReportFrequency *= -1; SET @Debug = [Inspector].[GetDebugFlag](@Debug,@ModuleConfig,@Modulename); --Set columns names for the Html table SET @HtmlTableHeadAG = (SELECT [Inspector].[GenerateHtmlTableheader] ( @Servername,@Modulename, @ServerSpecific, ''Availability Group Health Check'', @TableHeaderColour, ''Server name,AG name,State,Replica Server Name,Replica Role,Failover Ready,Suspended,Suspend Reason,Failover Ready Threshold'') ); SET @HtmlTableHeadFailover = (SELECT [Inspector].[GenerateHtmlTableheader] ( @Servername, @Modulename, @ServerSpecific, ''New Primary servers in the last 24 hours'', @TableHeaderColour, ''Previously checked,Last checked,AG name,Primary Replica'') ); /* if there has been a data collection since the last report frequency minutes ago then run the report */ IF(@LastCollection >= DATEADD(MINUTE,@ReportFrequency,GETDATE())) BEGIN SET @HtmlOutput = ( SELECT CASE WHEN @WarningLevel IS NULL AND (([AGCheck].[State] != ''HEALTHY'' AND [AGCheck].[State] != ''N/A'' ) OR ([FailoverReadyCount] < ISNULL([FailoverReadyNodeCount],2)) AND [AGCheck].[ReplicaServername] = @Servername AND [AGCheck].[ReplicaRole] = N''PRIMARY'') THEN @WarningHighlight WHEN @WarningLevel = 1 AND (([AGCheck].[State] != ''HEALTHY'' AND [AGCheck].[State] != ''N/A'') OR ([FailoverReadyCount] < ISNULL([FailoverReadyNodeCount],2)) AND [AGCheck].[ReplicaServername] = @Servername AND [AGCheck].[ReplicaRole] = N''PRIMARY'') THEN @WarningHighlight WHEN @WarningLevel = 2 AND (([AGCheck].[State] != ''HEALTHY'' AND [AGCheck].[State] != ''N/A'') OR ([FailoverReadyCount] < ISNULL([FailoverReadyNodeCount],2)) AND [AGCheck].[ReplicaServername] = @Servername AND [AGCheck].[ReplicaRole] = N''PRIMARY'') THEN @AdvisoryHighlight WHEN @WarningLevel = 3 AND (([AGCheck].[State] != ''HEALTHY'' AND [AGCheck].[State] != ''N/A'') OR ([FailoverReadyCount] < ISNULL([FailoverReadyNodeCount],2)) AND [AGCheck].[ReplicaServername] = @Servername AND [AGCheck].[ReplicaRole] = N''PRIMARY'') THEN @InfoHighlight ELSE ''#FFFFFF'' END AS [@bgcolor], [AGCheck].Servername AS ''td'','''', + [AGCheck].[AGname] AS ''td'','''', + [AGCheck].[State] AS ''td'','''', + ISNULL([AGCheck].[ReplicaServername],''N/A'') AS ''td'','''', + ISNULL([AGCheck].[ReplicaRole],''N/A'') AS ''td'','''', + CASE WHEN [AGCheck].[FailoverReady] = 1 THEN ''Y'' WHEN [AGCheck].[FailoverReady] = 0 THEN ''N'' ELSE ''N/A'' END AS ''td'','''', + CASE WHEN [AGCheck].[Suspended] = 1 THEN ''Y'' WHEN [AGCheck].[Suspended] = 0 THEN ''N'' ELSE ''N/A'' END AS ''td'','''', + ISNULL([AGCheck].[SuspendReason],''N/A'') AS ''td'','''', + ISNULL([FailoverReadyNodeCount],2) AS ''td'','''' FROM [Inspector].[AGCheck] LEFT JOIN (SELECT AGname, COUNT(AGname) AS FailoverReadyCount FROM [Inspector].[AGCheck] WHERE [FailoverReady] = 1 AND [Servername] = @Servername GROUP BY AGname) AS FailoverReadyCounts ON FailoverReadyCounts.AGname = [AGCheck].AGname LEFT JOIN (SELECT AGname, CASE WHEN [FailoverReadyNodeCount] > 10 THEN [FailoverReadyNodePercentCount] ELSE [FailoverReadyNodeCount] END AS [FailoverReadyNodeCount] FROM [Inspector].[AGCheckConfig]) AS FailoverReadyConfig ON [FailoverReadyConfig].[AGname] = [AGCheck].[AGname] WHERE [AGCheck].[Servername] = @Servername ORDER BY [AGCheck].[AGname] ASC,[AGCheck].[ReplicaServername] ASC FOR XML PATH(''tr''),ELEMENTS); --Failover check added in V1.4 /* if there has been a data collection since the last report frequency minutes ago then run the report */ IF(@LastCollection >= DATEADD(MINUTE,@ReportFrequency,GETDATE())) BEGIN SET @FailoverCheckHTML += (SELECT CASE WHEN @WarningLevel IS NULL THEN @WarningHighlight WHEN @WarningLevel = 1 THEN @WarningHighlight WHEN @WarningLevel = 2 THEN @AdvisoryHighlight WHEN @WarningLevel = 3 THEN @InfoHighlight ELSE ''#FFFFFF'' END AS [@bgcolor], CONVERT(VARCHAR(17),[Log_Date],113) AS ''td'','''', + CONVERT(VARCHAR(17),[CollectionDateTime],113) AS ''td'','''', + [AGname] AS ''td'','''', + [Servername] AS ''td'','''' FROM [Inspector].[AGPrimaryHistory] WHERE [Servername] = @Servername AND [CollectionDateTime] >= DATEADD(DAY,-1,GETDATE()) ORDER BY [AGname] ASC, [Servername] ASC FOR XML PATH(''tr''),ELEMENTS); SET @FailoverCheckHTML += ISNULL(@TableTail,'''') + ''
'' END --If @NoClutter is on we do not want to show the table if it has @InfoHighlight against the row/s IF (@NoClutter = 1) BEGIN IF (@HtmlOutput LIKE ''%HADR IS NOT ENABLED ON THIS SERVER OR YOU HAVE NO AVAILABILITY GROUPS%'') BEGIN SET @HtmlOutput = NULL; END END IF (@HtmlOutput IS NOT NULL) BEGIN SET @HtmlOutput = @HtmlTableHeadAG + @HtmlOutput + @TableTail +''
'' + CASE WHEN @FailoverCheckHTML IS NOT NULL THEN ISNULL(@HtmlTableHeadFailover,'''') + ISNULL(@FailoverCheckHTML,'''') ELSE '''' END; END END ELSE BEGIN SET @HtmlOutput = ( SELECT CASE WHEN @WarningLevel IS NULL THEN @WarningHighlight WHEN @WarningLevel = 1 THEN @WarningHighlight WHEN @WarningLevel = 2 THEN @AdvisoryHighlight WHEN @WarningLevel = 3 THEN @InfoHighlight END AS [@bgcolor], @Servername AS ''td'','''', + ''Data collection out of date'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''' FROM [Inspector].[AGCheck] WHERE Servername = @Servername ORDER BY AGname ASC FOR XML PATH(''tr''),ELEMENTS); --Mark Collection as out of date SET @CollectionOutOfDate = 1; SET @HtmlOutput = @HtmlTableHeadAG + @HtmlOutput + @TableTail +''
'' END --If @NoClutter is on we do not want to show the table if it has @InfoHighlight against the row/s IF (@HtmlOutput LIKE ''%''+@InfoHighlight+''%'') BEGIN SET @HtmlOutput = NULL; END IF (@Debug = 1) BEGIN SELECT OBJECT_NAME(@@PROCID) AS ''Procname'', @Servername AS ''@Servername'', @Modulename AS ''@Modulename'', @TableHeaderColour AS ''@TableHeaderColour'', @WarningHighlight AS ''@WarningHighlight'', @AdvisoryHighlight AS ''@AdvisoryHighlight'', @InfoHighlight AS ''@InfoHighlight'', @ModuleConfig AS ''@ModuleConfig'', @WarningLevel AS ''@WarningLevel'', @NoClutter AS ''@NoClutter'', @TableTail AS ''@TableTail'', @HtmlOutput AS ''@HtmlOutput'', @HtmlTableHeadAG AS ''@HtmlTableHeadAG'', @HtmlTableHeadFailover AS ''@HtmlTableHeadFailover'', @FailoverCheckHTML AS ''@FailoverCheckHTML'', @CollectionOutOfDate AS ''@CollectionOutOfDate'', @PSCollection AS ''@PSCollection'' END END'; IF OBJECT_ID('Inspector.AGDatabasesReport') IS NULL EXEC('CREATE PROCEDURE [Inspector].[AGDatabasesReport] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[AGDatabasesReport] ( @Servername NVARCHAR(128), @Modulename VARCHAR(50), @TableHeaderColour VARCHAR(7) = ''#E6E6FA'', @WarningHighlight VARCHAR(7), @AdvisoryHighlight VARCHAR(7), @InfoHighlight VARCHAR(7), @ModuleConfig VARCHAR(20), @WarningLevel TINYINT, @ServerSpecific BIT, @NoClutter BIT, @TableTail VARCHAR(256), @HtmlOutput VARCHAR(MAX) OUTPUT, @CollectionOutOfDate BIT OUTPUT, @PSCollection BIT, @Debug BIT = 0 ) AS BEGIN --Revision date: 20/04/2021 DECLARE @HtmlTableHead VARCHAR(2000); DECLARE @LastCollection DATETIME; DECLARE @ReportFrequency INT; SET @LastCollection = [Inspector].[GetLastCollectionDateTime] (@Modulename); EXEC [Inspector].[GetModuleConfigFrequency] @ModuleConfig, @Frequency = @ReportFrequency OUTPUT; SET @ReportFrequency *= -1; SET @Debug = [Inspector].[GetDebugFlag](@Debug,@ModuleConfig,@Modulename); --Set columns names for the Html table SET @HtmlTableHead = (SELECT [Inspector].[GenerateHtmlTableheader] ( @Servername, @Modulename, @ServerSpecific, ''Databases not in an AG'', @TableHeaderColour, ''Server name,Last Checked,Database name,Suppress database'') ); /* if there has been a data collection since the last report frequency minutes ago then run the report */ IF(@LastCollection >= DATEADD(MINUTE,@ReportFrequency,GETDATE())) BEGIN SET @HtmlOutput =( SELECT CASE WHEN @WarningLevel IS NULL THEN @AdvisoryHighlight WHEN @WarningLevel = 1 THEN @WarningHighlight WHEN @WarningLevel = 2 THEN @AdvisoryHighlight WHEN @WarningLevel = 3 THEN @InfoHighlight END AS [@bgcolor], [Servername] AS ''td'','''', + CONVERT(VARCHAR(17),[LastUpdated],113) AS ''td'','''', + [Databasename] AS ''td'','''', + ''EXEC [''+DB_NAME()+''].[Inspector].[SuppressAGDatabase] @Databasename = ''''''+Databasename+'''''', @Servername = ''''''+@Servername+'''''';'' AS ''td'','''' FROM [Inspector].[AGDatabases] WHERE [Is_AG] = 1 AND [Is_AGJoined] = 0 AND Servername = @Servername ORDER BY [Databasename] ASC FOR XML PATH(''tr''),ELEMENTS); IF @HtmlOutput IS NULL BEGIN SET @HtmlOutput =( SELECT ''#FFFFFF'' AS [@bgcolor], @Servername AS ''td'','''', + ''No Databases marked as AG and not joined'' AS ''td'','''', + ''N/A'' AS ''td'','''',+ ''N/A'' AS ''td'','''' FOR XML PATH(''tr''),ELEMENTS); END --If @NoClutter is on we do not want to show the table if it has @InfoHighlight against the row/s IF (@NoClutter = 1) BEGIN IF (@HtmlOutput LIKE ''%No Databases marked as AG and not joined%'') BEGIN SET @HtmlOutput = NULL; END END IF (@HtmlOutput IS NOT NULL) BEGIN SET @HtmlOutput = @HtmlTableHead + @HtmlOutput + @TableTail +''
'' END END ELSE BEGIN SET @HtmlOutput = (SELECT CASE WHEN @WarningLevel IS NULL THEN @AdvisoryHighlight WHEN @WarningLevel = 1 THEN @WarningHighlight WHEN @WarningLevel = 2 THEN @AdvisoryHighlight WHEN @WarningLevel = 3 THEN @InfoHighlight END AS [@bgcolor], @Servername AS ''td'','''', + ''Data Collection out of date'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''' FOR XML PATH(''tr''),Elements); --Mark Collection as out of date SET @CollectionOutOfDate = 1; SET @HtmlOutput = @HtmlTableHead + @HtmlOutput + @TableTail +''
'' END IF (@Debug = 1) BEGIN SELECT OBJECT_NAME(@@PROCID) AS ''Procname'', @Servername AS ''@Servername'', @Modulename AS ''@Modulename'', @TableHeaderColour AS ''@TableHeaderColour'', @WarningHighlight AS ''@WarningHighlight'', @AdvisoryHighlight AS ''@AdvisoryHighlight'', @InfoHighlight AS ''@InfoHighlight'', @ModuleConfig AS ''@ModuleConfig'', @WarningLevel AS ''@WarningLevel'', @NoClutter AS ''@NoClutter'', @TableTail AS ''@TableTail'', @HtmlOutput AS ''@HtmlOutput'', @HtmlTableHead AS ''@HtmlTableHead'', @CollectionOutOfDate AS ''@CollectionOutOfDate'', @PSCollection AS ''@PSCollection'' END END'; IF OBJECT_ID('Inspector.BackupsCheckReport') IS NULL EXEC('CREATE PROCEDURE [Inspector].[BackupsCheckReport] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[BackupsCheckReport] ( @Servername NVARCHAR(128), @Modulename VARCHAR(50), @TableHeaderColour VARCHAR(7) = ''#E6E6FA'', @WarningHighlight VARCHAR(7), @AdvisoryHighlight VARCHAR(7), @InfoHighlight VARCHAR(7), @ModuleConfig VARCHAR(20), @WarningLevel TINYINT, @ServerSpecific BIT, @NoClutter BIT, @TableTail VARCHAR(256), @HtmlOutput VARCHAR(MAX) OUTPUT, @CollectionOutOfDate BIT OUTPUT, @PSCollection BIT, @Debug BIT = 0 ) AS BEGIN --Revision date: 12/10/2021 DECLARE @FullBackupThreshold INT = (SELECT ISNULL(TRY_CAST([Inspector].[GetServerModuleThreshold] (@Servername,@Modulename,''FullBackupThreshold'') AS INT),8)); DECLARE @DiffBackupThreshold INT = (SELECT TRY_CAST([Inspector].[GetServerModuleThreshold] (@Servername,@Modulename,''DiffBackupThreshold'') AS INT)); DECLARE @LogBackupThreshold INT = (SELECT ISNULL(TRY_CAST([Inspector].[GetServerModuleThreshold] (@Servername,@Modulename,''LogBackupThreshold'') AS INT),20)); DECLARE @LastCollection DATETIME; DECLARE @ReportFrequency INT; DECLARE @MaxFullThreshold INT = (SELECT MAX(FullThreshold) FROM [Inspector].[BackupsCheckThresholds] WHERE [IsActive] = 1); IF (@FullBackupThreshold < @MaxFullThreshold) BEGIN SET @FullBackupThreshold = @MaxFullThreshold; END SET @LastCollection = [Inspector].[GetLastCollectionDateTime] (@Modulename); EXEC [Inspector].[GetModuleConfigFrequency] @ModuleConfig, @Frequency = @ReportFrequency OUTPUT; SET @ReportFrequency *= -1; --Excluded from Warning level control IF OBJECT_ID(''tempdb.dbo.#RawData'') IS NOT NULL DROP TABLE #RawData; CREATE TABLE #RawData ( Log_Date DATETIME, Databasename NVARCHAR(128), LastFull DATETIME, LastDiff DATETIME, LastLog DATETIME, AGname NVARCHAR(128), GroupingMethod NVARCHAR(128), Servername NVARCHAR(128), IsFullRecovery BIT, IsSystemDB BIT, primary_replica NVARCHAR(128), backup_preference NVARCHAR(60) ); IF OBJECT_ID(''tempdb.dbo.#Aggregates'') IS NOT NULL DROP TABLE #Aggregates; CREATE TABLE #Aggregates ( Log_Date DATETIME, Databasename NVARCHAR(128), LastFull DATETIME, LastDiff DATETIME, LastLog DATETIME, AGname NVARCHAR(128), GroupingMethod NVARCHAR(128), IsFullRecovery BIT, IsSystemDB BIT, primary_replica NVARCHAR(128), backup_preference NVARCHAR(60) ); CREATE CLUSTERED INDEX [CIX_RawData] ON #RawData (GroupingMethod,Databasename,IsFullRecovery,IsSystemDB,AGname); IF OBJECT_ID(''tempdb.dbo.#Validations'') IS NOT NULL DROP TABLE #Validations; CREATE TABLE #Validations ( Databasename NVARCHAR(128), AGname NVARCHAR(128), FullState VARCHAR(25), DiffState VARCHAR(25), LogState VARCHAR(25), IsFullRecovery CHAR(1), Serverlist VARCHAR(1000), primary_replica NVARCHAR(128), backup_preference NVARCHAR(60), NamedInstance BIT, Thresholds VARCHAR(128) ); DECLARE @NamedInstance BIT IF @Servername LIKE ''%\%'' BEGIN SET @NamedInstance = 1 END ELSE BEGIN SET @NamedInstance = 0 END; DECLARE @HtmlTableHead VARCHAR(2000); SET @Debug = [Inspector].[GetDebugFlag](@Debug,@ModuleConfig,@Modulename); --Set columns names for the Html table SET @HtmlTableHead = (SELECT [Inspector].[GenerateHtmlTableheader] (@Servername,@Modulename,@ServerSpecific,''The following Databases are missing database backups:'',@TableHeaderColour,''Servername,Database name,AG name,Last Full,Last Diff,Last Log,Full Recovery,AG Backup Pref,Preferred Servers,Thresholds'')); /* if there has been a data collection since the last report frequency minutes ago then run the report */ IF(@LastCollection >= DATEADD(MINUTE,@ReportFrequency,GETDATE())) BEGIN INSERT INTO #RawData (Log_Date,Databasename,LastFull,LastDiff,LastLog,AGname,GroupingMethod,Servername,IsFullRecovery,IsSystemDB,primary_replica,backup_preference) SELECT Log_Date, LTRIM(RTRIM(BackupSet.Databasename)), --Added trim as Leading and trailing spaces can cause misreporting [FULL] AS LastFull, [DIFF] AS LastDiff, [LOG] AS LastLog, BackupSet.AGname, CASE WHEN BackupSet.AGname = ''Not in an AG'' THEN Servername ELSE BackupSet.AGname END AS GroupingMethod, Servername, BackupSet.IsFullRecovery, BackupSet.IsSystemDB, BackupSet.primary_replica, BackupSet.backup_preference FROM [Inspector].[BackupsCheck] BackupSet WHERE NOT EXISTS (SELECT 1 FROM [Inspector].[BackupsCheckExcludes] WHERE [Servername] = [BackupSet].[Servername] AND [Databasename] = [BackupSet].[Databasename] AND ([SuppressUntil] IS NULL OR [SuppressUntil] >= GETDATE()) ); INSERT INTO #Aggregates (Log_Date,Databasename,LastFull,LastDiff,LastLog,AGname,GroupingMethod,IsFullRecovery,IsSystemDB,primary_replica,backup_preference) SELECT MAX(Log_Date), RawData.Databasename, MAX(LastFull) AS LastFull, MAX(LastDiff) AS LastDiff, MAX(LastLog) AS LastLog, AGname, GroupingMethod, IsFullRecovery, IsSystemDB, MAX(primary_replica), UPPER(backup_preference) AS backup_preference FROM #RawData RawData GROUP BY Databasename,AGname,GroupingMethod,IsFullRecovery,IsSystemDB,backup_preference; INSERT INTO #Validations (Databasename,AGname,FullState,DiffState,LogState,IsFullRecovery,Serverlist,primary_replica,backup_preference,NamedInstance,Thresholds) SELECT Aggregates.Databasename, Aggregates.AGname, CASE WHEN Aggregates.[LastFull] = ''19000101'' THEN ''More than ''+CAST(@FullBackupThreshold AS VARCHAR(3))+'' Days Ago'' WHEN (Aggregates.[LastFull] >= ''19000101'' AND Aggregates.[LastFull] < DATEADD(DAY,-ISNULL([Thresholds].[FullThreshold],@FullBackupThreshold),Aggregates.[Log_Date]) OR Aggregates.[LastFull] IS NULL) THEN ISNULL(CONVERT(VARCHAR(17),Aggregates.[LastFull],113),''More than ''+CAST(@FullBackupThreshold AS VARCHAR(3))+'' days ago'') ELSE ''OK'' END AS [FullState], CASE WHEN [Thresholds].[Databasename] IS NOT NULL AND [Thresholds].[DiffThreshold] IS NULL THEN ''N/A'' WHEN @DiffBackupThreshold IS NOT NULL THEN CASE WHEN Aggregates.[LastDiff] = ''19000101'' AND Aggregates.IsSystemDB = 0 THEN ''More than ''+CAST(@FullBackupThreshold AS VARCHAR(3))+'' Days Ago'' WHEN (Aggregates.[LastDiff] >= ''19000101'' AND Aggregates.[LastDiff] < DATEADD(HOUR,-ISNULL([Thresholds].[DiffThreshold],@DiffBackupThreshold),Aggregates.[Log_Date]) OR Aggregates.[LastDiff] IS NULL) AND Aggregates.IsSystemDB = 0 THEN ISNULL(CONVERT(VARCHAR(17),Aggregates.[LastDiff],113),''More than ''+CAST(@DiffBackupThreshold AS VARCHAR(3))+'' Hours ago'') WHEN Aggregates.IsSystemDB = 1 THEN ''N/A'' ELSE ''OK'' END ELSE ''N/A'' END AS [DiffState], CASE WHEN Aggregates.[LastLog] = ''19000101'' AND Aggregates.IsSystemDB = 0 AND Aggregates.IsFullRecovery = 1 THEN ''More than ''+CAST(@FullBackupThreshold AS VARCHAR(3))+'' Days Ago'' WHEN ((Aggregates.[LastLog] >= ''19000101'' AND Aggregates.[LastLog] < DATEADD(MINUTE,-ISNULL([Thresholds].[LogThreshold],@LogBackupThreshold),Aggregates.[Log_Date]) OR Aggregates.[LastLog] IS NULL) AND Aggregates.IsSystemDB = 0 AND (Aggregates.IsFullRecovery = 1 OR CAST(Aggregates.IsFullRecovery AS VARCHAR(3)) = ''N/A'')) THEN ISNULL(CONVERT(VARCHAR(17),[LastLog] ,113),''More than ''+CAST(@LogBackupThreshold AS VARCHAR(3))+'' Minutes ago'') WHEN Aggregates.IsFullRecovery = 0 OR Aggregates.IsSystemDB = 1 THEN ''N/A'' ELSE ''OK'' END AS [LogState], CASE Aggregates.IsFullRecovery WHEN 1 THEN ''Y'' ELSE ''N'' END AS IsFullRecovery, STUFF(Serverlist.Serverlist,1,1,'''') AS Serverlist, Aggregates.primary_replica, Aggregates.backup_preference, CASE WHEN Aggregates.primary_replica LIKE ''%\%'' THEN 1 ELSE 0 END, ''Full: ''+CAST(ISNULL([FullThreshold],@FullBackupThreshold) AS VARCHAR(20))+'' Day(s) , ''+ ''Diff: ''+ISNULL(CAST((CASE WHEN [Thresholds].[Databasename] IS NOT NULL THEN [DiffThreshold] ELSE @DiffBackupThreshold END) AS VARCHAR(20))+'' Hour(s) , '','' Ignoring , '')+ ''Log: ''+CAST(ISNULL([LogThreshold],@LogBackupThreshold) AS VARCHAR(20))+'' Minute(s)'' AS Thresholds FROM #Aggregates Aggregates LEFT JOIN (SELECT [Servername], [Databasename], [FullThreshold], [DiffThreshold], [LogThreshold] FROM [Inspector].[BackupsCheckThresholds] WHERE [IsActive] = 1) Thresholds ON Aggregates.[primary_replica] = Thresholds.[Servername] AND Aggregates.[Databasename] = Thresholds.[Databasename] CROSS APPLY (SELECT CASE WHEN backup_preference IN (''PRIMARY'',''NON AG'') THEN '', '' + primary_replica ELSE '', '' + Servername END FROM #RawData RawData WHERE Aggregates.GroupingMethod = RawData.GroupingMethod AND Aggregates.Databasename = RawData.Databasename AND Aggregates.IsFullRecovery = RawData.IsFullRecovery AND Aggregates.IsSystemDB = RawData.IsSystemDB AND Aggregates.AGname = RawData.AGname ORDER BY 1 ASC FOR XML PATH('''') ) AS Serverlist (Serverlist); SET @HtmlOutput = ( SELECT @WarningHighlight AS [@bgcolor], Servername AS ''td'','''', + Databasename AS ''td'','''', + AGname AS ''td'','''', + FullState AS ''td'','''', + DiffState AS ''td'','''', + LogState AS ''td'','''', + IsFullRecovery AS ''td'','''', + backup_preference AS ''td'','''', + Serverlist AS ''td'','''', + Thresholds AS ''td'','''' FROM ( SELECT @Servername AS Servername, Databasename, AGname, FullState, DiffState, LogState, IsFullRecovery, CASE WHEN backup_preference = ''PRIMARY'' THEN ''Primary only'' WHEN backup_preference = ''SECONDARY'' THEN ''Prefer secondary'' WHEN backup_preference = ''SECONDARY_ONLY'' THEN ''Secondary only'' WHEN backup_preference = ''NONE'' THEN ''Any replica'' WHEN backup_preference = ''NON AG'' THEN ''N/A'' ELSE backup_preference END AS backup_preference, CASE --Ensure only the relevant server names are being shown in the Server list WHEN ([FullState] != ''OK'' OR [DiffState] != ''OK'' AND [DiffState] != ''N/A'') AND [LogState] = ''OK'' THEN primary_replica WHEN backup_preference = ''SECONDARY_ONLY'' THEN REPLACE(REPLACE(Serverlist,'', ''+@Servername,''''),@Servername+'', '','''') ELSE Serverlist END AS Serverlist, Thresholds FROM #Validations WHERE ([FullState] != ''OK'' OR ([DiffState] != ''OK'' AND [DiffState] != ''N/A'') OR ([LogState] != ''OK'' AND [LogState] != ''N/A'')) AND Serverlist like ''%''+@Servername+''%'' AND NamedInstance = @NamedInstance ) AS DerivedValidations WHERE (Serverlist = Servername OR Serverlist LIKE ''%''+Servername+''%'') ORDER BY Databasename ASC FOR XML PATH(''tr''),ELEMENTS); IF @HtmlOutput IS NULL BEGIN SET @HtmlOutput = ( SELECT ''#FFFFFF'' [@bgcolor], @Servername AS ''td'','''', + ''No backup issues present'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''' FOR XML PATH(''tr''),ELEMENTS) END --Append html table to the report SET @HtmlOutput = CASE WHEN @HtmlOutput LIKE ''%No backup issues present%'' AND @NoClutter = 1 THEN '''' ELSE ISNULL(@HtmlTableHead, '''') + ISNULL(@HtmlOutput, '''') +''
Global Thresholds:
Last FULL backup older than ''+CAST(@FullBackupThreshold AS VARCHAR(3))+'' Day/s
''+ CASE WHEN @DiffBackupThreshold IS NOT NULL THEN ''Last DIFF backup older than ''+ CAST(@DiffBackupThreshold AS VARCHAR(3))+'' Hour/s
'' ELSE ''DIFF backups excluded from check
'' END +
''Last Log backup older than ''+CAST(@LogBackupThreshold AS VARCHAR(3))+'' Minute/s
Databases Excluded for this server: ''+(SELECT CAST(COUNT(Servername) AS VARCHAR(6)) FROM [Inspector].[BackupsCheckExcludes] WHERE Servername = @Servername AND ([SuppressUntil] IS NULL OR [SuppressUntil] >= GETDATE()))+''
'' END; --IF (@HtmlOutput IS NOT NULL) --BEGIN --SET @HtmlOutput = -- @HtmlTableHead -- + @HtmlOutput -- + @TableTail -- +''
'' --END END ELSE BEGIN SET @HtmlOutput = ( SELECT @WarningHighlight [@bgcolor], @Servername AS ''td'','''', + ''Data Collection out of date'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''' FOR XML PATH(''tr''),ELEMENTS) --Mark Collection as out of date SET @CollectionOutOfDate = 1; SET @HtmlOutput = @HtmlTableHead + @HtmlOutput + @TableTail +''
'' END IF (@Debug = 1) BEGIN SELECT OBJECT_NAME(@@PROCID) AS ''Procname'', @Servername AS ''@Servername'', @Modulename AS ''@Modulename'', @TableHeaderColour AS ''@TableHeaderColour'', @WarningHighlight AS ''@WarningHighlight'', @AdvisoryHighlight AS ''@AdvisoryHighlight'', @InfoHighlight AS ''@InfoHighlight'', @ModuleConfig AS ''@ModuleConfig'', @WarningLevel AS ''@WarningLevel'', @NoClutter AS ''@NoClutter'', @TableTail AS ''@TableTail'', @HtmlOutput AS ''@HtmlOutput'', @HtmlTableHead AS ''@HtmlTableHead'', @CollectionOutOfDate AS ''@CollectionOutOfDate'', @PSCollection AS ''@PSCollection'' END END'; IF OBJECT_ID('Inspector.BackupSizesByDayReport') IS NULL EXEC('CREATE PROCEDURE [Inspector].[BackupSizesByDayReport] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[BackupSizesByDayReport] ( @Servername NVARCHAR(128), @Modulename VARCHAR(50), @TableHeaderColour VARCHAR(7) = ''#E6E6FA'', @WarningHighlight VARCHAR(7), @AdvisoryHighlight VARCHAR(7), @InfoHighlight VARCHAR(7), @ModuleConfig VARCHAR(20), @WarningLevel TINYINT, @ServerSpecific BIT, @NoClutter BIT, @TableTail VARCHAR(256), @HtmlOutput VARCHAR(MAX) OUTPUT, @CollectionOutOfDate BIT OUTPUT, @PSCollection BIT, @Debug BIT = 0 ) AS BEGIN --Revision date: 20/04/2021 DECLARE @HtmlTableHead VARCHAR(2000); DECLARE @LastCollection DATETIME; DECLARE @ReportFrequency INT; SET @LastCollection = [Inspector].[GetLastCollectionDateTime] (@Modulename); EXEC [Inspector].[GetModuleConfigFrequency] @ModuleConfig, @Frequency = @ReportFrequency OUTPUT; SET @ReportFrequency *= -1; SET @Debug = [Inspector].[GetDebugFlag](@Debug,@ModuleConfig,@Modulename); --Set columns names for the Html table SET @HtmlTableHead = (SELECT [Inspector].[GenerateHtmlTableheader] (@Servername,@Modulename,@ServerSpecific,''Backup Sizes by Day for server:'',@TableHeaderColour,''Day Of Week,Total Backup Size GB'')); --Excluded from Warning level control /* if there has been a data collection since the last report frequency minutes ago then run the report */ IF(@LastCollection >= DATEADD(MINUTE,@ReportFrequency,GETDATE())) BEGIN SET @HtmlOutput = (SELECT ''#FFFFFF'' AS [@bgcolor], [DayOfWeek] AS ''td'','''', + [TotalBackupSize_GB] AS ''td'','''' FROM ( SELECT [DayOfWeek], [CastedDate], CAST(SUM(((TotalSizeInBytes)/1024)/1024)/1024 AS DECIMAL (10,1)) AS [TotalBackupSize_GB] FROM [Inspector].[BackupSizesByDay] WHERE Servername = @Servername AND Log_Date IS NOT NULL GROUP BY [DayOfWeek],[CastedDate] ) BackupSizesByDay ORDER BY CastedDate ASC FOR XML PATH(''tr''),ELEMENTS); IF (@HtmlOutput IS NOT NULL) BEGIN SET @HtmlOutput = @HtmlTableHead + @HtmlOutput + @TableTail +''
'' END END ELSE BEGIN IF EXISTS (SELECT Log_Date FROM [Inspector].[BackupSizesByDay] WHERE Servername = @Servername AND Log_Date IS NULL) BEGIN SET @HtmlOutput = (SELECT ''#FFFFFF'' AS [@bgcolor], ''No Backups for the past 7 days'' AS ''td'','''', + ''N/A'' AS ''td'','''' FOR XML PATH(''tr''),ELEMENTS); END IF @HtmlOutput IS NULL BEGIN SET @HtmlOutput = (SELECT ''#FFFFFF'' AS [@bgcolor], ''Data collection out of Date'' AS ''td'','''', + ''N/A'' AS ''td'','''' FOR XML PATH(''tr''),ELEMENTS); --Mark Collection as out of date SET @CollectionOutOfDate = 1; END SET @HtmlOutput = @HtmlTableHead + @HtmlOutput + @TableTail +''
'' END IF (@Debug = 1) BEGIN SELECT OBJECT_NAME(@@PROCID) AS ''Procname'', @Servername AS ''@Servername'', @Modulename AS ''@Modulename'', @TableHeaderColour AS ''@TableHeaderColour'', @WarningHighlight AS ''@WarningHighlight'', @AdvisoryHighlight AS ''@AdvisoryHighlight'', @InfoHighlight AS ''@InfoHighlight'', @ModuleConfig AS ''@ModuleConfig'', @WarningLevel AS ''@WarningLevel'', @NoClutter AS ''@NoClutter'', @TableTail AS ''@TableTail'', @HtmlOutput AS ''@HtmlOutput'', @HtmlTableHead AS ''@HtmlTableHead'', @CollectionOutOfDate AS ''@CollectionOutOfDate'', @PSCollection AS ''@PSCollection'' END END'; IF OBJECT_ID('Inspector.DatabaseFilesReport') IS NULL EXEC('CREATE PROCEDURE [Inspector].[DatabaseFilesReport] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[DatabaseFilesReport] ( @Servername NVARCHAR(128), @Modulename VARCHAR(50), @TableHeaderColour VARCHAR(7) = ''#E6E6FA'', @WarningHighlight VARCHAR(7), @AdvisoryHighlight VARCHAR(7), @InfoHighlight VARCHAR(7), @ModuleConfig VARCHAR(20), @WarningLevel TINYINT, @ServerSpecific BIT, @NoClutter BIT, @TableTail VARCHAR(256), @HtmlOutput VARCHAR(MAX) OUTPUT, @CollectionOutOfDate BIT OUTPUT, @PSCollection BIT, @Debug BIT = 0 ) AS BEGIN --Revision date: 20/04/2021 DECLARE @HtmlTableHead VARCHAR(2000); DECLARE @LastCollection DATETIME; DECLARE @ReportFrequency INT; SET @LastCollection = [Inspector].[GetLastCollectionDateTime] (@Modulename); EXEC [Inspector].[GetModuleConfigFrequency] @ModuleConfig, @Frequency = @ReportFrequency OUTPUT; SET @ReportFrequency *= -1; SET @Debug = [Inspector].[GetDebugFlag](@Debug,@ModuleConfig,@Modulename); --Set columns names for the Html table SET @HtmlTableHead = (SELECT [Inspector].[GenerateHtmlTableheader] ( @Servername, @Modulename, @ServerSpecific, ''Data or Log files on incorrect drives'', @TableHeaderColour, ''Server name,Database name,File type,File path'' ) ); /* if there has been a data collection since the last report frequency minutes ago then run the report */ IF(@LastCollection >= DATEADD(MINUTE,@ReportFrequency,GETDATE())) BEGIN SET @HtmlOutput = (SELECT CASE WHEN @WarningLevel = 1 THEN @WarningHighlight WHEN @WarningLevel = 2 THEN @AdvisoryHighlight WHEN @WarningLevel = 3 THEN @InfoHighlight END AS [@bgcolor], Servername AS ''td'','''', + Databasename AS ''td'','''', + FileType AS ''td'','''', + FilePath AS ''td'','''' FROM [Inspector].[DatabaseFiles] WHERE Servername = @Servername AND Databasename != ''No Database File issues present'' FOR XML PATH(''tr''),ELEMENTS); IF @HtmlOutput IS NULL BEGIN SET @HtmlOutput = (SELECT ''#FFFFFF'' AS [@bgcolor], @Servername AS ''td'','''', + ''No Database File issues present'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''' FOR XML PATH(''tr''),ELEMENTS); END END ELSE BEGIN SET @HtmlOutput = (SELECT CASE WHEN @WarningLevel = 1 THEN @WarningHighlight WHEN @WarningLevel = 2 THEN @AdvisoryHighlight WHEN @WarningLevel = 3 THEN @InfoHighlight END AS [@bgcolor], @Servername AS ''td'','''', + ''Data Collection out of date'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''' FOR XML PATH(''tr''),ELEMENTS); --Mark Collection as out of date SET @CollectionOutOfDate = 1; END --If @NoClutter is on we do not want to show the table if it has @InfoHighlight against the row/s IF (@NoClutter = 1) BEGIN IF (@HtmlOutput LIKE ''%No Database File issues present%'') BEGIN SET @HtmlOutput = NULL; END END IF (@HtmlOutput IS NOT NULL) BEGIN SET @HtmlOutput = @HtmlTableHead + @HtmlOutput + @TableTail +''
'' END IF (@Debug = 1) BEGIN SELECT OBJECT_NAME(@@PROCID) AS ''Procname'', @Servername AS ''@Servername'', @Modulename AS ''@Modulename'', @TableHeaderColour AS ''@TableHeaderColour'', @WarningHighlight AS ''@WarningHighlight'', @AdvisoryHighlight AS ''@AdvisoryHighlight'', @InfoHighlight AS ''@InfoHighlight'', @ModuleConfig AS ''@ModuleConfig'', @WarningLevel AS ''@WarningLevel'', @NoClutter AS ''@NoClutter'', @TableTail AS ''@TableTail'', @HtmlOutput AS ''@HtmlOutput'', @HtmlTableHead AS ''@HtmlTableHead'', @CollectionOutOfDate AS ''@CollectionOutOfDate'', @PSCollection AS ''@PSCollection'' END END'; IF OBJECT_ID('Inspector.DatabaseGrowthsReport') IS NULL EXEC('CREATE PROCEDURE [Inspector].[DatabaseGrowthsReport] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[DatabaseGrowthsReport] ( @Servername NVARCHAR(128), @Modulename VARCHAR(50), @TableHeaderColour VARCHAR(7) = ''#E6E6FA'', @WarningHighlight VARCHAR(7), @AdvisoryHighlight VARCHAR(7), @InfoHighlight VARCHAR(7), @ModuleConfig VARCHAR(20), @WarningLevel TINYINT, @ServerSpecific BIT, @NoClutter BIT, @TableTail VARCHAR(256), @HtmlOutput VARCHAR(MAX) OUTPUT, @CollectionOutOfDate BIT OUTPUT, @PSCollection BIT, @Debug BIT = 0 ) AS BEGIN --Revision date: 12/09/2019 --Excluded from Warning level control DECLARE @HtmlTableHead VARCHAR(2000); DECLARE @DatabaseGrowthsAllowedPerDay INT = (SELECT ISNULL(TRY_CAST([Inspector].[GetServerModuleThreshold] (@Servername,@Modulename,''DatabaseGrowthsAllowedPerDay'') AS INT),1)); DECLARE @MAXDatabaseGrowthsAllowedPerDay INT = (SELECT ISNULL(TRY_CAST([Inspector].[GetServerModuleThreshold] (@Servername,@Modulename,''MAXDatabaseGrowthsAllowedPerDay'') AS INT),10)); SET @Debug = [Inspector].[GetDebugFlag](@Debug,@ModuleConfig,@Modulename); --Set columns names for the Html table SET @HtmlTableHead = (SELECT [Inspector].[GenerateHtmlTableheader] ( @Servername, @Modulename, @ServerSpecific, ''The following Database files have grown more than ''+CAST(@DatabaseGrowthsAllowedPerDay AS VARCHAR(5))+'' time(s) in the past 24hours:'', @TableHeaderColour, ''Server name,Database name,Type desc,File ID,Filename,Pre Growth Size MB,Growth Rate MB,Growth Increments,Post Growth Size MB,Suggested Growth Rate MB,Growth Rate trend (Last 5 days)'' ) ); SELECT @HtmlOutput = (SELECT CASE WHEN [GrowthIncrements] > @DatabaseGrowthsAllowedPerDay AND [GrowthIncrements] < @MAXDatabaseGrowthsAllowedPerDay THEN @AdvisoryHighlight WHEN [GrowthIncrements] > @DatabaseGrowthsAllowedPerDay AND [GrowthIncrements] >= @MAXDatabaseGrowthsAllowedPerDay THEN @WarningHighlight END AS [@bgcolor], [Servername] AS ''td'','''', + [Database_name] AS ''td'','''', + [Type_Desc] AS ''td'','''', + [File_id] AS ''td'','''', + [FileName] AS ''td'','''', + [PreGrowthSize_MB] AS ''td'','''', + [GrowthRate_MB] AS ''td'','''', + [GrowthIncrements] AS ''td'','''', + [PostGrowthSize_MB] AS ''td'','''',+ CASE WHEN [GrowthRate_MB] < 100 THEN 100 -- if current growth rate is less than 100MB then suggest a minimum of 100MB ELSE [GrowthRate_MB] * [GrowthIncrements] END AS ''td'','''',+ [HistoricGrowths].[LastFiveDaysGrowth] AS ''td'','''' FROM [Inspector].[DatabaseFileSizeHistory] LatestGrowths CROSS APPLY (SELECT STUFF((SELECT TOP 5 '', ['' + DATENAME(WEEKDAY,DATEADD(DAY,-1,[HistoricGrowths].[Log_Date])) + '' '' + CAST([GrowthRate_MB]*[GrowthIncrements] AS VARCHAR(10)) +'' MB]'' FROM [Inspector].[DatabaseFileSizeHistory] HistoricGrowths WHERE [LatestGrowths].[Servername] = [HistoricGrowths].[Servername] AND [LatestGrowths].[Database_id] = [HistoricGrowths].[Database_id] AND [LatestGrowths].[File_id] = [HistoricGrowths].[File_id] AND [HistoricGrowths].[Log_Date] >= DATEADD(DAY,-5,CAST(GETDATE() AS DATE)) ORDER BY [HistoricGrowths].[Log_Date] DESC FOR XML PATH('''')),1,1,'''') ) AS [HistoricGrowths](LastFiveDaysGrowth) WHERE [Log_Date] >= DATEADD(HOUR,-24,GETDATE()) AND [GrowthIncrements] > @DatabaseGrowthsAllowedPerDay AND [Type_Desc] = ''ROWS'' ORDER BY [Servername],[Database_name],[File_id] FOR XML PATH(''tr''),Elements); IF (@HtmlOutput IS NOT NULL) BEGIN SET @HtmlOutput = ''
Server [ALL Servers]
''
+ @HtmlTableHead
+ @HtmlOutput
+ ''
Advisory Highlight - More than ''+CAST(@DatabaseGrowthsAllowedPerDay AS VARCHAR(5))+'' growth event/s in the past 24 hours
Warning Highlight - ''+CAST(@MAXDatabaseGrowthsAllowedPerDay AS VARCHAR(5))+'' or more growth event/s in the past 24 hours
'' +''
''; END IF (@Debug = 1) BEGIN SELECT OBJECT_NAME(@@PROCID) AS ''Procname'', @Servername AS ''@Servername'', @Modulename AS ''@Modulename'', @TableHeaderColour AS ''@TableHeaderColour'', @WarningHighlight AS ''@WarningHighlight'', @AdvisoryHighlight AS ''@AdvisoryHighlight'', @InfoHighlight AS ''@InfoHighlight'', @ModuleConfig AS ''@ModuleConfig'', @WarningLevel AS ''@WarningLevel'', @NoClutter AS ''@NoClutter'', @TableTail AS ''@TableTail'', @HtmlOutput AS ''@HtmlOutput'', @HtmlTableHead AS ''@HtmlTableHead'', @DatabaseGrowthsAllowedPerDay AS ''@DatabaseGrowthsAllowedPerDay'', @MAXDatabaseGrowthsAllowedPerDay AS ''@MAXDatabaseGrowthsAllowedPerDay'', @CollectionOutOfDate AS ''@CollectionOutOfDate'', @PSCollection AS ''@PSCollection'' END END'; IF OBJECT_ID('Inspector.DatabaseOwnershipReport') IS NULL EXEC('CREATE PROCEDURE [Inspector].[DatabaseOwnershipReport] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[DatabaseOwnershipReport] ( @Servername NVARCHAR(128), @Modulename VARCHAR(50), @TableHeaderColour VARCHAR(7) = ''#E6E6FA'', @WarningHighlight VARCHAR(7), @AdvisoryHighlight VARCHAR(7), @InfoHighlight VARCHAR(7), @ModuleConfig VARCHAR(20), @WarningLevel TINYINT, @ServerSpecific BIT, @NoClutter BIT, @TableTail VARCHAR(256), @HtmlOutput VARCHAR(MAX) OUTPUT, @CollectionOutOfDate BIT OUTPUT, @PSCollection BIT, @Debug BIT = 0 ) AS BEGIN --Revision date: 20/04/2021 DECLARE @HtmlTableHead VARCHAR(2000); DECLARE @DatabaseOwnerExclusions VARCHAR(255) = (SELECT REPLACE([Value],'' '' ,'''') FROM [Inspector].[Settings] WHERE [Description] = ''DatabaseOwnerExclusions'') DECLARE @LastCollection DATETIME; DECLARE @ReportFrequency INT; SET @LastCollection = [Inspector].[GetLastCollectionDateTime] (@Modulename); EXEC [Inspector].[GetModuleConfigFrequency] @ModuleConfig, @Frequency = @ReportFrequency OUTPUT; SET @ReportFrequency *= -1; SET @Debug = [Inspector].[GetDebugFlag](@Debug,@ModuleConfig,@Modulename); --Set columns names for the Html table SET @HtmlTableHead = (SELECT [Inspector].[GenerateHtmlTableheader] ( @Servername, @Modulename, @ServerSpecific, ''The following Databases have an owner that is not ''+ISNULL(@DatabaseOwnerExclusions,''[N/A - No Exclusions Set]''), @TableHeaderColour, ''Server name,AG name,Database name,Owner'') ); SET @DatabaseOwnerExclusions = REPLACE(REPLACE(@DatabaseOwnerExclusions,'' '',''''),'','','', ''); /* if there has been a data collection since the last report frequency minutes ago then run the report */ IF(@LastCollection >= DATEADD(MINUTE,@ReportFrequency,GETDATE())) BEGIN SET @HtmlOutput = (SELECT CASE WHEN @WarningLevel IS NULL THEN @AdvisoryHighlight WHEN @WarningLevel = 1 THEN @WarningHighlight WHEN @WarningLevel = 2 THEN @AdvisoryHighlight WHEN @WarningLevel = 3 THEN @InfoHighlight END AS [@bgcolor], [Servername] AS ''td'','''', + [AGname] AS ''td'','''', + [Database_name] AS ''td'','''', + [Owner] AS ''td'','''' FROM [Inspector].[DatabaseOwnership] WHERE [Servername] = @Servername AND [Database_name] != ''No Database Ownership issues present'' ORDER BY [Database_name] FOR XML PATH(''tr''),ELEMENTS); IF @HtmlOutput IS NULL BEGIN SET @HtmlOutput = (SELECT ''#FFFFFF'' AS [@bgcolor], ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''No Database Ownership issues present'' AS ''td'','''', + ''N/A'' AS ''td'','''' FOR XML PATH(''tr''),ELEMENTS); END END ELSE BEGIN SET @HtmlOutput = (SELECT CASE WHEN @WarningLevel IS NULL THEN @AdvisoryHighlight WHEN @WarningLevel = 1 THEN @WarningHighlight WHEN @WarningLevel = 2 THEN @AdvisoryHighlight WHEN @WarningLevel = 3 THEN @InfoHighlight END AS [@bgcolor], ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''Data Collection out of date'' AS ''td'','''', + ''N/A'' AS ''td'','''' FOR XML PATH(''tr''),ELEMENTS); --Mark Collection as out of date SET @CollectionOutOfDate = 1; END --If @NoClutter is on we do not want to show the table if it has @InfoHighlight against the row/s IF (@NoClutter = 1) BEGIN IF (@HtmlOutput LIKE ''%No Database Ownership issues present%'') BEGIN SET @HtmlOutput = NULL; END END IF (@HtmlOutput IS NOT NULL) BEGIN SET @HtmlOutput = @HtmlTableHead + @HtmlOutput + @TableTail +''
'' END IF (@Debug = 1) BEGIN SELECT OBJECT_NAME(@@PROCID) AS ''Procname'', @Servername AS ''@Servername'', @Modulename AS ''@Modulename'', @TableHeaderColour AS ''@TableHeaderColour'', @WarningHighlight AS ''@WarningHighlight'', @AdvisoryHighlight AS ''@AdvisoryHighlight'', @InfoHighlight AS ''@InfoHighlight'', @ModuleConfig AS ''@ModuleConfig'', @WarningLevel AS ''@WarningLevel'', @NoClutter AS ''@NoClutter'', @TableTail AS ''@TableTail'', @HtmlOutput AS ''@HtmlOutput'', @HtmlTableHead AS ''@HtmlTableHead'', @CollectionOutOfDate AS ''@CollectionOutOfDate'', @PSCollection AS ''@PSCollection'' END END'; IF OBJECT_ID('Inspector.DatabaseSettingsReport') IS NULL EXEC('CREATE PROCEDURE [Inspector].[DatabaseSettingsReport] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[DatabaseSettingsReport] ( @Servername NVARCHAR(128), @Modulename VARCHAR(50), @TableHeaderColour VARCHAR(7) = ''#E6E6FA'', @WarningHighlight VARCHAR(7), @AdvisoryHighlight VARCHAR(7), @InfoHighlight VARCHAR(7), @ModuleConfig VARCHAR(20), @WarningLevel TINYINT, @ServerSpecific BIT, @NoClutter BIT, @TableTail VARCHAR(256), @HtmlOutput VARCHAR(MAX) OUTPUT, @CollectionOutOfDate BIT OUTPUT, @PSCollection BIT, @Debug BIT = 0 ) AS BEGIN --Revision date: 20/04/2021 DECLARE @HtmlTableHead VARCHAR(2000); DECLARE @LastCollection DATETIME; DECLARE @ReportFrequency INT; SET @HtmlOutput = ''''; SET @LastCollection = [Inspector].[GetLastCollectionDateTime] (@Modulename); EXEC [Inspector].[GetModuleConfigFrequency] @ModuleConfig, @Frequency = @ReportFrequency OUTPUT; SET @ReportFrequency *= -1; SET @Debug = [Inspector].[GetDebugFlag](@Debug,@ModuleConfig,@Modulename); --Set columns names for the Html table SET @HtmlTableHead = (SELECT [Inspector].[GenerateHtmlTableheader] ( @Servername, @Modulename, @ServerSpecific, ''Database settings'', @TableHeaderColour, ''Collation,Total'') ); /* if there has been a data collection since the last report frequency minutes ago then run the report */ IF(@LastCollection >= DATEADD(MINUTE,@ReportFrequency,GETDATE())) BEGIN SELECT @HtmlOutput = @HtmlOutput + (SELECT ''#FFFFFF'' AS [@bgcolor], [Description] AS ''td'','''', + Total AS ''td'','''' FROM [Inspector].[DatabaseSettings] WHERE Servername = @Servername AND Setting = ''Collation_name'' ORDER BY Total DESC FOR XML PATH(''tr''),ELEMENTS); SELECT @HtmlOutput = @HtmlOutput + ''
'' END IF (@Debug = 1) BEGIN SELECT OBJECT_NAME(@@PROCID) AS ''Procname'', @Servername AS ''@Servername'', @Modulename AS ''@Modulename'', @TableHeaderColour AS ''@TableHeaderColour'', @WarningHighlight AS ''@WarningHighlight'', @AdvisoryHighlight AS ''@AdvisoryHighlight'', @InfoHighlight AS ''@InfoHighlight'', @ModuleConfig AS ''@ModuleConfig'', @WarningLevel AS ''@WarningLevel'', @NoClutter AS ''@NoClutter'', @TableTail AS ''@TableTail'', @HtmlOutput AS ''@HtmlOutput'', @HtmlTableHead AS ''@HtmlTableHead'', @CollectionOutOfDate AS ''@CollectionOutOfDate'', @PSCollection AS ''@PSCollection'' END END'; IF OBJECT_ID('Inspector.DatabaseStatesReport') IS NULL EXEC('CREATE PROCEDURE [Inspector].[DatabaseStatesReport] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[DatabaseStatesReport] ( @Servername NVARCHAR(128), @Modulename VARCHAR(50), @TableHeaderColour VARCHAR(7) = ''#E6E6FA'', @WarningHighlight VARCHAR(7), @AdvisoryHighlight VARCHAR(7), @InfoHighlight VARCHAR(7), @ModuleConfig VARCHAR(20), @WarningLevel TINYINT, @ServerSpecific BIT, @NoClutter BIT, @TableTail VARCHAR(256), @HtmlOutput VARCHAR(MAX) OUTPUT, @CollectionOutOfDate BIT OUTPUT, @PSCollection BIT, @Debug BIT = 0 ) AS BEGIN --Revision date: 20/04/2021 DECLARE @HtmlTableHead VARCHAR(2000); DECLARE @LastCollection DATETIME; DECLARE @ReportFrequency INT; SET @LastCollection = [Inspector].[GetLastCollectionDateTime] (@Modulename); EXEC [Inspector].[GetModuleConfigFrequency] @ModuleConfig, @Frequency = @ReportFrequency OUTPUT; SET @ReportFrequency *= -1; SET @Debug = [Inspector].[GetDebugFlag](@Debug,@ModuleConfig,@Modulename); --Set columns names for the Html table SET @HtmlTableHead = (SELECT [Inspector].[GenerateHtmlTableheader] ( @Servername, @Modulename, @ServerSpecific, ''Database count by state'', @TableHeaderColour, ''Server name,Database state,Total,Database names'' ) ); /* if there has been a data collection since the last report frequency minutes ago then run the report */ IF(@LastCollection >= DATEADD(MINUTE,@ReportFrequency,GETDATE())) BEGIN SET @HtmlOutput =( SELECT CASE WHEN DatabaseState IN (''RECOVERY_PENDING'',''SUSPECT'',''EMERGENCY'') THEN @WarningHighlight --Cannot be overidden using Warning levels WHEN @WarningLevel IS NULL AND DatabaseState IN (''Restoring'',''RECOVERING'',''OFFLINE'',''SNAPSHOT (more than 10 days old)'') THEN @AdvisoryHighlight WHEN @WarningLevel = 1 AND DatabaseState IN (''Restoring'',''RECOVERING'',''OFFLINE'',''SNAPSHOT (more than 10 days old)'') THEN @WarningHighlight WHEN @WarningLevel = 2 AND DatabaseState IN (''Restoring'',''RECOVERING'',''OFFLINE'',''SNAPSHOT (more than 10 days old)'') THEN @AdvisoryHighlight WHEN @WarningLevel = 3 AND DatabaseState IN (''Restoring'',''RECOVERING'',''OFFLINE'',''SNAPSHOT (more than 10 days old)'') THEN @InfoHighlight ELSE ''#FFFFFF'' END AS [@bgcolor], Servername AS ''td'','''', + DatabaseState AS ''td'','''', + Total AS ''td'','''', + DatabaseNames AS ''td'','''' FROM [Inspector].[DatabaseStates] WHERE Servername = @Servername ORDER BY Total DESC FOR XML PATH(''tr''),ELEMENTS); END ELSE BEGIN SET @HtmlOutput =( SELECT CASE WHEN @WarningLevel IS NULL THEN @AdvisoryHighlight WHEN @WarningLevel = 1 THEN @WarningHighlight WHEN @WarningLevel = 2 THEN @AdvisoryHighlight WHEN @WarningLevel = 3 THEN @InfoHighlight END AS [@bgcolor], @Servername AS ''td'','''', + ''Data collection out of date'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''' FOR XML PATH(''tr''),ELEMENTS); --Mark Collection as out of date SET @CollectionOutOfDate = 1; END SET @HtmlOutput = @HtmlTableHead + @HtmlOutput + @TableTail +''
'' IF (@Debug = 1) BEGIN SELECT OBJECT_NAME(@@PROCID) AS ''Procname'', @Servername AS ''@Servername'', @Modulename AS ''@Modulename'', @TableHeaderColour AS ''@TableHeaderColour'', @WarningHighlight AS ''@WarningHighlight'', @AdvisoryHighlight AS ''@AdvisoryHighlight'', @InfoHighlight AS ''@InfoHighlight'', @ModuleConfig AS ''@ModuleConfig'', @WarningLevel AS ''@WarningLevel'', @NoClutter AS ''@NoClutter'', @TableTail AS ''@TableTail'', @HtmlOutput AS ''@HtmlOutput'', @HtmlTableHead AS ''@HtmlTableHead'', @CollectionOutOfDate AS ''@CollectionOutOfDate'', @PSCollection AS ''@PSCollection'' END END'; IF OBJECT_ID('Inspector.DriveSpaceReport') IS NULL EXEC('CREATE PROCEDURE [Inspector].[DriveSpaceReport] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[DriveSpaceReport] ( @Servername NVARCHAR(128), @Modulename VARCHAR(50), @TableHeaderColour VARCHAR(7) = ''#E6E6FA'', @WarningHighlight VARCHAR(7), @AdvisoryHighlight VARCHAR(7), @InfoHighlight VARCHAR(7), @DriveLetterExcludes VARCHAR(10), @ServerSpecific BIT, @ModuleConfig VARCHAR(20), @UseMedian BIT, @NoClutter BIT, @TableTail VARCHAR(256), @DriveSpaceTableOnly VARCHAR(MAX) OUTPUT, @HtmlOutput VARCHAR(MAX) OUTPUT, @CollectionOutOfDate BIT OUTPUT, @PSCollection BIT, @Debug BIT = 0 ) AS --Revision date: 27/07/2021 BEGIN SET NOCOUNT ON; /* Excluded from Warning level control */ DECLARE @HtmlTableHead VARCHAR(2000); DECLARE @DaysUntilDriveFullThreshold INT; DECLARE @FreeSpaceRemainingPercent DECIMAL(5,2); DECLARE @LastCollection DATETIME; DECLARE @ReportFrequency INT; DECLARE @GlobalMedianCalc BIT; SET @Debug = [Inspector].[GetDebugFlag](@Debug, @ModuleConfig, @Modulename); SET @LastCollection = [Inspector].[GetLastCollectionDateTime] (@Modulename); EXEC [Inspector].[GetModuleConfigFrequency] @ModuleConfig, @Frequency = @ReportFrequency OUTPUT; SET @ReportFrequency *= -1; /*Set columns names for the Html table*/ SET @HtmlTableHead = ( SELECT [Inspector].[GenerateHtmlTableheader] (@Servername, @Modulename, @ServerSpecific, ''Drive space Report:'', /*Title for the HTML table*/ @TableHeaderColour, ''Server name,Drive,Total GB,Available GB,% Free,Est.Daily Growth GB,Days Until Disk Full,Days Recorded,Usage Trend,Usage Trend AVG GB,Calculation method,Thresholds'' ) ); SET @GlobalMedianCalc = ( SELECT TRY_CAST([Value] AS BIT) FROM [Inspector].[Settings] WHERE [Description] = ''UseMedianCalculationForDriveSpaceCalc'' ); SET @DaysUntilDriveFullThreshold = ( SELECT ISNULL(TRY_CAST([Inspector].[GetServerModuleThreshold](@Servername, @Modulename, ''DaysUntilDriveFullThreshold'') AS INT), 56) ); SET @FreeSpaceRemainingPercent = ( SELECT ISNULL(TRY_CAST([Inspector].[GetServerModuleThreshold](@Servername, @Modulename, ''FreeSpaceRemainingPercent'') AS DECIMAL(5, 2)), 10.00) ); /* if there has been a data collection since the last report frequency minutes ago then run the report */ IF(@LastCollection >= DATEADD(MINUTE,@ReportFrequency,GETDATE())) BEGIN IF OBJECT_ID(''tempdb.dbo.#TotalDriveEntries'') IS NOT NULL DROP TABLE [#TotalDriveEntries]; CREATE TABLE #TotalDriveEntries ( Servername NVARCHAR(128) NOT NULL, Drive NVARCHAR(128) NULL, TotalEntries INT NOT NULL, MedianCalc BIT NULL, Excluded BIT NOT NULL, DaysRecorded INT NULL, AverageDailyGrowth_GB DECIMAL(10,2) ); INSERT INTO #TotalDriveEntries ([Servername], [Drive], [TotalEntries], [MedianCalc],[Excluded]) SELECT Servername, Drive, COUNT(Drive) AS TotalEntries, CASE WHEN EXISTS (SELECT 1 FROM [Inspector].[DriveSpaceCalc] WHERE [DriveSpaceCalc].Servername = [DriveSpace].Servername AND [DriveSpaceCalc].Drive = [DriveSpace].Drive AND [DriveSpaceCalc].MedianCalc = 1) THEN 1 ELSE ISNULL(@GlobalMedianCalc,0) END, CASE WHEN EXISTS (SELECT 1 FROM [master].[dbo].fn_SplitString(@DriveLetterExcludes, '','') WHERE [StringElement] + '':\'' = [DriveSpace].Drive) THEN 1 ELSE 0 END FROM [Inspector].[DriveSpace] WHERE Servername = @Servername GROUP BY Servername, Drive; UPDATE #TotalDriveEntries SET DaysRecorded = Total FROM ( SELECT DriveByDay.Drive, COUNT(*) AS Total FROM ( SELECT DISTINCT Drive,CAST(Log_Date AS DATE) AS Log_Date FROM Inspector.DriveSpace GROUP BY Drive,CAST(Log_Date AS DATE) ) DriveByDay GROUP BY DriveByDay.Drive ) DriveByDayCounts INNER JOIN #TotalDriveEntries ON DriveByDayCounts.Drive = #TotalDriveEntries.Drive; WITH AverageDailyGrowth AS ( SELECT #TotalDriveEntries.Servername, #TotalDriveEntries.Drive, MedianCalc, CASE WHEN MedianCalc = 1 THEN CAST( ( SELECT TOP 1 PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY [UsedSpaceVarianceGB]) OVER(PARTITION BY Drive) FROM Inspector.DriveSpace Median WHERE Median.Drive = #TotalDriveEntries.Drive ) AS DECIMAL(10,2)) ELSE CAST(SUM(([UsedSpaceVarianceGB])/#TotalDriveEntries.TotalEntries) AS DECIMAL(10,2)) END AS AverageDailyGrowth_GB FROM #TotalDriveEntries INNER JOIN Inspector.DriveSpace ON DriveSpace.Drive = #TotalDriveEntries.Drive AND DriveSpace.Servername = #TotalDriveEntries.Servername GROUP BY #TotalDriveEntries.Servername, #TotalDriveEntries.Drive, MedianCalc) UPDATE #TotalDriveEntries SET AverageDailyGrowth_GB = AverageDailyGrowth.AverageDailyGrowth_GB FROM AverageDailyGrowth INNER JOIN #TotalDriveEntries ON #TotalDriveEntries.Servername = AverageDailyGrowth.Servername AND #TotalDriveEntries.Drive = AverageDailyGrowth.Drive; SELECT @HtmlOutput = ( SELECT CASE WHEN AverageDailyGrowth_GB > 0 AND CAST(COALESCE((LastRecordedFreeSpace.AvailableSpace_GB) / NULLIF(AverageDailyGrowth_GB, 0), 0) AS DECIMAL(20, 2)) < ISNULL(Overrides.[DaysUntilDriveFull], @DaysUntilDriveFullThreshold) THEN @WarningHighlight WHEN CAST((LastRecordedFreeSpace.AvailableSpace_GB / LastRecordedFreeSpace.Capacity_GB) * 100 AS DECIMAL(10, 2)) < COALESCE(CAST((Overrides.[MinAvailableSpace_GB] / LastRecordedFreeSpace.Capacity_GB) * 100.00 AS DECIMAL(5, 2)), Overrides.[FreeSpaceRemainingPercent], @FreeSpaceRemainingPercent) AND TotalDriveEntries.Excluded = 0 THEN @AdvisoryHighlight ELSE ''#FFFFFF'' END AS [@bgcolor], TotalDriveEntries.Servername AS ''td'', '''', +TotalDriveEntries.Drive AS ''td'', '''', +LastRecordedFreeSpace.Capacity_GB AS ''td'', '''', +LastRecordedFreeSpace.AvailableSpace_GB AS ''td'', '''', +CAST((LastRecordedFreeSpace.AvailableSpace_GB / LastRecordedFreeSpace.Capacity_GB) * 100 AS DECIMAL(10, 2)) AS ''td'', '''', +ISNULL(TotalDriveEntries.AverageDailyGrowth_GB, 0.00) AS ''td'', '''', +CASE WHEN AverageDailyGrowth_GB <= 0 THEN ''N/A'' ELSE CAST(CAST(COALESCE((LastRecordedFreeSpace.AvailableSpace_GB) / NULLIF(AverageDailyGrowth_GB, 0), 0) AS DECIMAL(20, 2)) AS VARCHAR(10)) END AS ''td'', '''', +TotalDriveEntries.DaysRecorded AS ''td'', '''', +ISNULL(STUFF( ( SELECT '', ['' + DATENAME(WEEKDAY, x.Log_Date) + '' '' + CAST(SUM([UsedSpaceVarianceGB]) AS VARCHAR(10)) + '' GB]'' FROM ( SELECT CAST(SpaceVariation.Log_Date AS DATE) AS Log_Date, [UsedSpaceVarianceGB] FROM Inspector.DriveSpace SpaceVariation WHERE SpaceVariation.Drive = TotalDriveEntries.Drive AND SpaceVariation.Servername = TotalDriveEntries.Servername AND SpaceVariation.Log_Date >= DATEADD(DAY, -5, GETDATE()) ) x GROUP BY x.Log_Date ORDER BY x.Log_Date DESC FOR XML PATH('''') ), 1, 1, ''''), '' No data available'') AS ''td'', '''', +ISNULL(FiveDayTotal.SUMFiveDayTotal, 0.00) AS ''td'', '''', +CASE WHEN TotalDriveEntries.MedianCalc = 1 THEN ''Median'' WHEN TotalDriveEntries.MedianCalc = 0 THEN ''Average'' ELSE ''Average'' END AS ''td'', '''', +''Minimum available space: '' + ISNULL(CAST(CAST(CASE WHEN Overrides.[MinAvailableSpace_GB] IS NOT NULL THEN Overrides.[MinAvailableSpace_GB] ELSE(LastRecordedFreeSpace.Capacity_GB * 1.00) * (CAST(ISNULL(Overrides.[FreeSpaceRemainingPercent], @FreeSpaceRemainingPercent) AS DECIMAL(5, 2)) / 100.00) END AS DECIMAL(10, 2)) AS VARCHAR(128)) + '' GB'', ''Not set'') + '' ('' + ISNULL(CAST(COALESCE(CAST((Overrides.[MinAvailableSpace_GB] / LastRecordedFreeSpace.Capacity_GB) * 100.00 AS DECIMAL(5, 2)), Overrides.[FreeSpaceRemainingPercent], @FreeSpaceRemainingPercent) AS VARCHAR(128)) + ''%'', ''Not set'') + ''), '' + ''Estimated days remaining: '' + ISNULL(CAST(ISNULL(Overrides.[DaysUntilDriveFull], @DaysUntilDriveFullThreshold) AS VARCHAR(128)), ''Not set'') AS ''td'', '''' FROM #TotalDriveEntries TotalDriveEntries LEFT JOIN [Inspector].[DriveSpaceThresholds] Overrides ON TotalDriveEntries.Drive = Overrides.Drive AND TotalDriveEntries.Servername = Overrides.Servername CROSS APPLY ( SELECT TOP 1 [Capacity_GB], [AvailableSpace_GB] FROM [Inspector].[DriveSpace] DriveSpace WHERE DriveSpace.Drive = TotalDriveEntries.Drive AND DriveSpace.Servername = TotalDriveEntries.Servername ORDER BY Log_Date DESC ) AS LastRecordedFreeSpace CROSS APPLY ( SELECT CAST(AVG([UsedSpaceVarianceGB]) AS DECIMAL(20, 2)) AS SUMFiveDayTotal FROM ( SELECT TOP (7200) /* Maximum of 5 days worth of 1 min collections per drive per server */ Drive, UsedSpaceVarianceGB FROM Inspector.DriveSpace FiveDay WHERE FiveDay.Drive = TotalDriveEntries.Drive AND FiveDay.Servername = TotalDriveEntries.Servername AND FiveDay.Log_Date >= DATEADD(DAY, -5, GETDATE()) ORDER BY FiveDay.Log_Date DESC ) AS LastFiveDays ) AS FiveDayTotal WHERE TotalDriveEntries.Servername = @Servername ORDER BY TotalDriveEntries.Drive ASC FOR XML PATH(''tr''),Elements) /* If @NoClutter is on we do not want to show the table if it has @InfoHighlight against the row/s @NoClutter not applicable to DriveSpace module */ IF (@HtmlOutput LIKE ''%''+@InfoHighlight+''%'') BEGIN SET @HtmlOutput = NULL; END IF (@HtmlOutput IS NOT NULL) BEGIN /* @DriveSpaceTableOnly is for internal use only */ SET @DriveSpaceTableOnly = @HtmlOutput; SET @HtmlOutput = @HtmlTableHead + @HtmlOutput + @TableTail +''
''; END END ELSE BEGIN SET @HtmlOutput = (SELECT @WarningHighlight AS [@bgcolor], @Servername AS ''td'','''', + ''Data Collection out of date'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''' FOR XML PATH(''tr''),Elements); SET @HtmlOutput = @HtmlTableHead + @HtmlOutput + @TableTail +''
''; SET @CollectionOutOfDate = 1; END IF (@Debug = 1) BEGIN SELECT OBJECT_NAME(@@PROCID) AS ''Procname'', @Servername AS ''@Servername'', @Modulename AS ''@Modulename'', @TableHeaderColour AS ''@TableHeaderColour'', @HtmlTableHead AS ''@HtmlTableHead'', @WarningHighlight AS ''@WarningHighlight'', @AdvisoryHighlight AS ''@AdvisoryHighlight'', @InfoHighlight AS ''@InfoHighlight'', @DaysUntilDriveFullThreshold AS ''@DaysUntilDriveFullThreshold'', @FreeSpaceRemainingPercent AS ''@FreeSpaceRemainingPercent'', @DriveLetterExcludes AS ''@DriveLetterExcludes'', @ModuleConfig AS ''@ModuleConfig'', @UseMedian AS ''@UseMedian'', @NoClutter AS ''@NoClutter'', @TableTail AS ''@TableTail'', @HtmlOutput AS ''@HtmlOutput'', @CollectionOutOfDate AS ''@CollectionOutOfDate'', @PSCollection AS ''@PSCollection'' END END'; IF OBJECT_ID('Inspector.FailedAgentJobsReport') IS NULL EXEC('CREATE PROCEDURE [Inspector].[FailedAgentJobsReport] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[FailedAgentJobsReport] ( @Servername NVARCHAR(128), @Modulename VARCHAR(50), @TableHeaderColour VARCHAR(7) = ''#E6E6FA'', @WarningHighlight VARCHAR(7), @AdvisoryHighlight VARCHAR(7), @InfoHighlight VARCHAR(7), @ModuleConfig VARCHAR(20), @WarningLevel TINYINT, @ServerSpecific BIT, @NoClutter BIT, @TableTail VARCHAR(256), @HtmlOutput VARCHAR(MAX) OUTPUT, @CollectionOutOfDate BIT OUTPUT, @PSCollection BIT, @Debug BIT = 0 ) AS BEGIN --Revision date: 20/04/2021 DECLARE @HtmlTableHead VARCHAR(2000); DECLARE @LastCollection DATETIME; DECLARE @ReportFrequency INT; SET @LastCollection = [Inspector].[GetLastCollectionDateTime] (@Modulename); EXEC [Inspector].[GetModuleConfigFrequency] @ModuleConfig, @Frequency = @ReportFrequency OUTPUT; SET @ReportFrequency *= -1; SET @Debug = [Inspector].[GetDebugFlag](@Debug,@ModuleConfig,@Modulename); --Set columns names for the Html table SET @HtmlTableHead = (SELECT [Inspector].[GenerateHtmlTableheader] ( @Servername, @Modulename, @ServerSpecific, ''Failed Agent Jobs'', @TableHeaderColour, ''Server name,Log Date,Job name,Last Step Failed,Last Failed Date,Last Error'' ) ); /* if there has been a data collection since the last report frequency minutes ago then run the report */ IF(@LastCollection >= DATEADD(MINUTE,@ReportFrequency,GETDATE())) BEGIN SET @HtmlOutput = (SELECT CASE WHEN @WarningLevel IS NULL THEN @WarningHighlight WHEN @WarningLevel = 1 THEN @WarningHighlight WHEN @WarningLevel = 2 THEN @AdvisoryHighlight WHEN @WarningLevel = 3 THEN @InfoHighlight END AS [@bgcolor], Servername AS ''td'','''', + CONVERT(VARCHAR(17),Log_Date,113) AS ''td'','''', + Jobname AS ''td'','''', + LastStepFailed AS ''td'','''', + CONVERT(VARCHAR(17),LastFailedDate,113) AS ''td'','''',+ LastError + ''...'' AS ''td'','''' FROM [Inspector].[FailedAgentJobs] WHERE Servername = @Servername AND Jobname != ''No Failed Jobs present'' FOR XML PATH(''tr''),ELEMENTS); IF @HtmlOutput IS NULL BEGIN SET @HtmlOutput = (SELECT ''#FFFFFF'' AS [@bgcolor], @Servername AS ''td'','''', + ''N/A'' AS ''td'','''', + ''No Failed Jobs present'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''' FOR XML PATH(''tr''),ELEMENTS); END END ELSE BEGIN SET @HtmlOutput = (SELECT CASE WHEN @WarningLevel IS NULL THEN @WarningHighlight WHEN @WarningLevel = 1 THEN @WarningHighlight WHEN @WarningLevel = 2 THEN @AdvisoryHighlight WHEN @WarningLevel = 3 THEN @InfoHighlight END AS [@bgcolor], @Servername AS ''td'','''', + ''N/A'' AS ''td'','''', + ''Data Collection out of date'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''', + ''N/A'' AS ''td'','''' FOR XML PATH(''tr''),ELEMENTS); --Mark Collection as out of date SET @CollectionOutOfDate = 1; END --Append html table to the report IF (@NoClutter = 1) BEGIN IF (@HtmlOutput LIKE ''%No Failed Jobs present%'') BEGIN SET @HtmlOutput = NULL; END END SET @HtmlOutput = @HtmlTableHead + @HtmlOutput + @TableTail +''
'' IF (@Debug = 1) BEGIN SELECT OBJECT_NAME(@@PROCID) AS ''Procname'', @Servername AS ''@Servername'', @Modulename AS ''@Modulename'', @TableHeaderColour AS ''@TableHeaderColour'', @WarningHighlight AS ''@WarningHighlight'', @AdvisoryHighlight AS ''@AdvisoryHighlight'', @InfoHighlight AS ''@InfoHighlight'', @ModuleConfig AS ''@ModuleConfig'', @WarningLevel AS ''@WarningLevel'', @NoClutter AS ''@NoClutter'', @TableTail AS ''@TableTail'', @HtmlOutput AS ''@HtmlOutput'', @HtmlTableHead AS ''@HtmlTableHead'', @CollectionOutOfDate AS ''@CollectionOutOfDate'', @PSCollection AS ''@PSCollection'' END END'; IF OBJECT_ID('Inspector.GenerateHeaderInfo') IS NULL EXEC('CREATE PROCEDURE [Inspector].[GenerateHeaderInfo] AS;'); EXEC sp_executesql N' ALTER PROCEDURE [Inspector].[GenerateHeaderInfo] ( @Servername NVARCHAR(128), @ModuleConfig VARCHAR(20), @Modulename VARCHAR(50), @ModuleBodyText VARCHAR(MAX), @WarningHighlight VARCHAR(7), @AdvisoryHighlight VARCHAR(7), @InfoHighlight VARCHAR(7), @WarningLevelFontColour VARCHAR(7), @CollectionOutOfDate BIT, @NoClutter BIT, @WarningLevel TINYINT, @ServerSpecific BIT, @TableTail VARCHAR(65) OUTPUT, @Importance VARCHAR(6) OUTPUT, @CountWarning INT OUTPUT, @CountAdvisory INT OUTPUT, @AlertHeader VARCHAR(1000) OUTPUT, @AdvisoryHeader VARCHAR(1000) OUTPUT, @InfoHeader VARCHAR(1000) OUTPUT, @Debug BIT = 0 ) AS --Revision date: 13/05/2021 BEGIN DECLARE @HeaderText VARCHAR(100); DECLARE @CountInfo INT = 0; DECLARE @MultiWarningModule BIT = (SELECT 1 FROM [Inspector].[MultiWarningModules] WHERE [Modulename] = @Modulename) --If the module passed in is not server specific then replace @servername with ''ALL_SERVERS'' IF (@ServerSpecific = 0) BEGIN SET @Servername = ''ALL_SERVERS''; END --Remove table tail as a colour key might exist in the table tail (DriveSpace for example) and this will affect header counts counts. IF CHARINDEX('''',@ModuleBodyText) > 0 BEGIN SET @ModuleBodyText = LEFT(@ModuleBodyText,CHARINDEX('''',@ModuleBodyText)-1); END --Set the header text for the module SET @HeaderText = (SELECT [HeaderText] FROM [Inspector].[Modules] WHERE [Modulename] = @Modulename AND [ModuleConfig_Desc] = @ModuleConfig); --Set a default value of the module name if no row was returned from above query IF (@HeaderText IS NULL) BEGIN --Set default header text SET @HeaderText = (SELECT [HeaderText] FROM [Inspector].[DefaultHeaderText] WHERE [Modulename] = @Modulename); IF (@HeaderText IS NULL) --If it is still NULL just set to the Modulename BEGIN SET @HeaderText = @Modulename; END END IF (@WarningLevel > 3) BEGIN SET @WarningLevel = 3; END IF (@WarningLevelFontColour IS NULL) BEGIN SET @WarningLevelFontColour = CASE WHEN @WarningLevel = 1 THEN @WarningHighlight WHEN @WarningLevel = 2 THEN @AdvisoryHighlight WHEN @WarningLevel = 3 THEN @InfoHighlight END; END --Multi warning modules need to be treated differently as there are two highlights --Populate Alert header IF (@WarningLevel = 1 OR @MultiWarningModule = 1) BEGIN IF @ModuleBodyText LIKE ''%''+@WarningHighlight+''%'' BEGIN --Multi warning modules need to be overidden as they use two highlights IF (@MultiWarningModule = 1) BEGIN SET @WarningLevelFontColour = @WarningHighlight END SET @CountWarning = (LEN(@ModuleBodyText) - LEN(REPLACE(@ModuleBodyText,@WarningHighlight, '''')))/LEN(@WarningHighlight) SELECT @AlertHeader = CAST('''' AS VARCHAR(200)) + CASE --Add hyperlinks to the respective table and also Anchors for Back to Top for each table WHEN @CollectionOutOfDate = 0 THEN ''''+@Servername+'' - has (''+CAST(@CountWarning AS VARCHAR(5))+'') ''+@HeaderText+''
'' WHEN @CollectionOutOfDate = 1 THEN ''''+@Servername+'' - has (''+CAST(@CountWarning AS VARCHAR(5))+'') ''+@HeaderText+'' (Data collection out of Date)
'' END SET @Importance = ''High''; END END --Populate Advisory header IF (@WarningLevel = 2 OR @MultiWarningModule = 1) BEGIN IF @ModuleBodyText LIKE ''%''+@AdvisoryHighlight+''%'' BEGIN --Multi warning modules need to be overidden as they use two highlights IF (@MultiWarningModule = 1) BEGIN SET @WarningLevelFontColour = @AdvisoryHighlight END SET @CountAdvisory = (LEN(@ModuleBodyText) - LEN(REPLACE(@ModuleBodyText,@AdvisoryHighlight, '''')))/LEN(@AdvisoryHighlight) SELECT @AdvisoryHeader = CAST('''' AS VARCHAR(200)) + CASE --Add hyperlinks to the respective table and also Anchors for Back to Top for each table WHEN @CollectionOutOfDate = 0 THEN ''''+@Servername+'' - has (''+CAST(@CountAdvisory AS VARCHAR(5))+'') ''+@HeaderText+''
'' WHEN @CollectionOutOfDate = 1 THEN ''''+@Servername+'' - has (''+CAST(@CountAdvisory AS VARCHAR(5))+'') ''+@HeaderText+'' (Data collection out of Date)
'' END --If Multi warning is enabled for this module and Importance was set to high in the previous block then do not reset Importance SET @Importance = ( SELECT CASE WHEN (@MultiWarningModule = 1 AND @Importance IN (''High'')) THEN @Importance WHEN (@MultiWarningModule = 1 AND @Importance IS NULL) THEN ''Normal'' WHEN (@MultiWarningModule = 0) THEN ''Normal'' ELSE ''Normal'' END ); END END --Populate Info header IF (@WarningLevel = 3 OR @MultiWarningModule = 1) BEGIN IF @ModuleBodyText LIKE ''%''+@InfoHighlight+''%'' BEGIN --Multi warning modules need to be overidden as they use two highlights IF (@MultiWarningModule = 1) BEGIN SET @WarningLevelFontColour = @InfoHighlight END SET @CountInfo = (LEN(@ModuleBodyText) - LEN(REPLACE(@ModuleBodyText,@InfoHighlight, '''')))/LEN(@InfoHighlight) SELECT @InfoHeader = CAST('''' AS VARCHAR(200)) + CASE --Add hyperlinks to the respective table and also Anchors for Back to Top for each table WHEN @CollectionOutOfDate = 0 THEN ''''+@Servername+'' - has (''+CAST(@CountInfo AS VARCHAR(5))+'') ''+@HeaderText+''
'' WHEN @CollectionOutOfDate = 1 THEN ''''+@Servername+'' - has (''+CAST(@CountInfo AS VARCHAR(5))+'') ''+@HeaderText+'' (Data collection out of Date)
'' END --If Multi warning is enabled for this module and Importance was set to high or Normal in the previous blocks then do not reset Importance SET @Importance = ( SELECT CASE WHEN (@MultiWarningModule = 1 AND @Importance IN (''High'',''Normal'')) THEN @Importance WHEN (@MultiWarningModule = 1 AND @Importance IS NULL) THEN ''Low'' WHEN (@MultiWarningModule = 0) THEN ''Low'' ELSE ''Low'' END ); END END --If no Headers are populated then revert the table tail to the standard back to top IF COALESCE(@AlertHeader,@AdvisoryHeader,@InfoHeader) IS NULL BEGIN SET @TableTail = ''
'';
END
IF (@Debug = 1)
BEGIN
SELECT
OBJECT_NAME(@@PROCID) AS ''Procname'',
@Servername AS ''@Servername'',
@ModuleConfig AS ''@ModuleConfigDetermined'',
@Modulename AS ''@Modulename'',
@ModuleBodyText AS ''@ReportModuleHtml'',
@WarningHighlight AS ''@WarningHighlight'',
@AdvisoryHighlight AS ''@AdvisoryHighlight'',
@InfoHighlight AS ''@InfoHighlight'',
@WarningLevelFontColour AS ''@WarningLevelFontColour'',
@CollectionOutOfDate AS ''@CollectionOutOfDate'',
@NoClutter AS ''@NoClutter'',
@WarningLevel AS ''@WarningLevel'',
@Importance AS ''@Importance'',
@CountWarning AS ''@CountWarning'',
@CountAdvisory AS ''@CountAdvisory'',
@CountInfo AS ''@CountInfo'',
@AlertHeader AS ''@AlertHeader'',
@AdvisoryHeader AS ''@AdvisoryHeader'',
@InfoHeader AS ''@InfoHeader'',
@TableTail AS ''@TableTail'';
END
END';
SET @SQLStatement = CONVERT(NVARCHAR(MAX), '')+N'
CREATE FUNCTION [Inspector].[GenerateHtmlTableheader] (
@Servername NVARCHAR(128),
@Modulename VARCHAR(128),
@ServerSpecific BIT,
@Tableheadermessage VARCHAR(128) = '''',
@TableHeaderColour VARCHAR(7) = ''#E6E6FA'',
@Columnnames VARCHAR(4000)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @TableHeader VARCHAR(MAX)
IF (@ServerSpecific = 0)
BEGIN
SET @Servername = N''ALL_SERVERS'';
END
--Remove space following commas
SET @Columnnames = REPLACE(@Columnnames,'', '','','');
SELECT @TableHeader =
''''+@Tableheadermessage+''
''; SET @StandardTableTail = NULL; SET @MultiWarningModule = NULL; SET @MultiWarningModule = (SELECT 1 FROM [Inspector].[MultiWarningModules] WHERE [Modulename] = @Modulename); RAISERROR(''Reporting on Module: [%s] for server [%s] '',0,0,@Modulename,@Serverlist) WITH NOWAIT; --Only Server specific procs will be executed inside the cursor IF (@ServerSpecific = 1) BEGIN --The DriveSpace module needs to be executed slightly different to other modules. IF (@Modulename = N''DriveSpace'') BEGIN --Reset header output variables SET @AlertHeaderOutput = NULL; SET @AdvisoryHeaderOutput = NULL; SET @InfoHeaderOutput = NULL; SET @DriveSpaceTableOnly = NULL; EXEC [Inspector].[DriveSpaceReport] @Servername = @Serverlist, @Modulename = @Modulename, @TableHeaderColour = @TableHeaderColour, @WarningHighlight = @WarningHighlight, @AdvisoryHighlight = @AdvisoryHighlight, @InfoHighlight = @InfoHighlight, @DriveLetterExcludes = @DriveLetterExcludes, @ServerSpecific = @ServerSpecific, @PSCollection = @PSCollection, @ModuleConfig = @ModuleConfigDetermined, @UseMedian = @UseMedian, @NoClutter = @NoClutter, @TableTail = @TableTail, @DriveSpaceTableOnly = @DriveSpaceTableOnly OUTPUT, @CollectionOutOfDate = @CollectionOutOfDate OUTPUT, @HtmlOutput = @ReportModuleHtml OUTPUT, @Debug = @Debug; RAISERROR(''Generating header info for Module: [%s] for server [%s] '',0,0,@Modulename,@Serverlist) WITH NOWAIT; --Generate header information EXEC [Inspector].[GenerateHeaderInfo] @Servername = @Serverlist, @ModuleConfig = @ModuleConfigDetermined, @Modulename = @Modulename, @ModuleBodyText = @ReportModuleHtml, @WarningHighlight = @WarningHighlight, @AdvisoryHighlight = @AdvisoryHighlight, @InfoHighlight = @InfoHighlight, @WarningLevelFontColour = @WarningLevelFontColour, @CollectionOutOfDate = @CollectionOutOfDate, @NoClutter = @NoClutter, @Importance = @Importance OUTPUT, @WarningLevel = @WarningLevel, @ServerSpecific = @ServerSpecific, @TableTail = @StandardTableTail OUTPUT, @CountWarning = @CountWarning OUTPUT, @CountAdvisory = @CountAdvisory OUTPUT, @AlertHeader = @AlertHeaderOutput OUTPUT, @AdvisoryHeader = @AdvisoryHeaderOutput OUTPUT, @InfoHeader = @InfoHeaderOutput OUTPUT, @Debug = @Debug; --Check @Importance and set to the highest Importance seen so far SET @HighestImportance = ( SELECT CASE WHEN @HighestImportance IS NULL THEN Importance WHEN @HighestImportance > Importance THEN Importance ELSE @HighestImportance END FROM ( SELECT CASE @Importance WHEN ''High'' THEN 1 WHEN ''Normal'' THEN 2 WHEN ''Low'' THEN 3 END AS Importance ) Importance ); --If no headers were populated by the module then revert the back to top hyperlink IF @StandardTableTail IS NOT NULL BEGIN SET @ReportModuleHtml = REPLACE(@ReportModuleHtml,@TableTail,@StandardTableTail); END -- Append the Report module html to the main report body. SELECT @EmailBody = @EmailBody + ISNULL(@ReportModuleHtml,''''); --Append to the Headers --Add warning counts for summary column in ReportData table IF (@AlertHeaderOutput IS NOT NULL) BEGIN SET @AlertHeader += ISNULL(@AlertHeaderOutput,''''); SET @TotalWarningCount += @CountWarning; --Internal use only IF OBJECT_ID(''Inspector.DriveExtensionRequest'') IS NOT NULL BEGIN RAISERROR(''Executing DriveExtensionRequest with @Email = 0'',0,0) WITH NOWAIT; EXEC sp_executesql N''EXEC [Inspector].[DriveExtensionRequest] @html = @DriveSpaceTableOnly, @WarningHighlight = @WarningHighlight,@Email = 0, @EmailOutput = @DriveExtensionRequest OUTPUT;'', N''@DriveSpaceTableOnly VARCHAR(MAX), @WarningHighlight VARCHAR(7), @DriveExtensionRequest VARCHAR(MAX) OUTPUT'', @DriveSpaceTableOnly = @DriveSpaceTableOnly, @WarningHighlight = @WarningHighlight, @DriveExtensionRequest = @DriveExtensionRequest OUTPUT; SET @DriveExtensionRequestStage = @DriveExtensionRequestStage + ISNULL(@DriveExtensionRequest,''''); END END IF (@AdvisoryHeaderOutput IS NOT NULL) BEGIN SET @AdvisoryHeader += ISNULL(@AdvisoryHeaderOutput,''''); SET @TotalAdvisoryCount += @CountAdvisory; END IF (@InfoHeaderOutput IS NOT NULL) BEGIN --No count required here just append to header SET @InfoHeader += ISNULL(@InfoHeaderOutput,''''); END END ELSE --for all other modules use this block BEGIN --Reset header output variables SET @AlertHeaderOutput = NULL; SET @AdvisoryHeaderOutput = NULL; SET @InfoHeaderOutput = NULL; --Get Module warning level SELECT @WarningLevel = [Inspector].[GetWarningLevel](@ModuleConfigDetermined, @Modulename); BEGIN TRY SET @SQLstatement = N'' EXEC [Inspector].[''+@ReportProcedurename+''] @Servername = @Serverlist, @Modulename = @Modulename, @TableHeaderColour = @TableHeaderColour, @WarningHighlight = @WarningHighlight, @AdvisoryHighlight = @AdvisoryHighlight, @InfoHighlight = @InfoHighlight, @PSCollection = @PSCollection, @ModuleConfig = @ModuleConfigDetermined, @WarningLevel = @WarningLevel, @ServerSpecific = @ServerSpecific, @NoClutter = @NoClutter, @TableTail = @TableTail, @CollectionOutOfDate = @CollectionOutOfDate OUTPUT, @HtmlOutput = @ReportModuleHtml OUTPUT, @Debug = @Debug;'' EXEC sp_executesql @SQLstatement, N''@Serverlist NVARCHAR(128), @Modulename VARCHAR(50), @TableHeaderColour VARCHAR(7), @WarningHighlight VARCHAR(7), @AdvisoryHighlight VARCHAR(7), @InfoHighlight VARCHAR(7), @PSCollection BIT, @ModuleConfigDetermined VARCHAR(20), @WarningLevel TINYINT, @ServerSpecific BIT, @NoClutter BIT, @TableTail VARCHAR(256), @CollectionOutOfDate BIT OUTPUT, @ReportModuleHtml VARCHAR(MAX) OUTPUT, @Debug BIT'', @Serverlist = @Serverlist, @Modulename = @Modulename, @TableHeaderColour = @TableHeaderColour, @WarningHighlight = @WarningHighlight, @AdvisoryHighlight = @AdvisoryHighlight, @InfoHighlight = @InfoHighlight, @PSCollection = @PSCollection, @ModuleConfigDetermined = @ModuleConfigDetermined, @WarningLevel = @WarningLevel, @ServerSpecific = @ServerSpecific, @NoClutter = @NoClutter, @TableTail = @TableTail, @CollectionOutOfDate = @CollectionOutOfDate OUTPUT, @ReportModuleHtml = @ReportModuleHtml OUTPUT, @Debug = @Debug; SET @ErrorMessage = NULL; END TRY BEGIN CATCH SET @ErrorMessage = CAST(ERROR_MESSAGE() AS NVARCHAR(128)); END CATCH RAISERROR(''Generating header info for Module: [%s] for server [%s] '',0,0,@Modulename,@Serverlist) WITH NOWAIT; --Generate header information EXEC [Inspector].[GenerateHeaderInfo] @Servername = @Serverlist, @ModuleConfig = @ModuleConfigDetermined, @Modulename = @Modulename, @ModuleBodyText = @ReportModuleHtml, @WarningHighlight = @WarningHighlight, @AdvisoryHighlight = @AdvisoryHighlight, @InfoHighlight = @InfoHighlight, @WarningLevelFontColour = @WarningLevelFontColour, @CollectionOutOfDate = @CollectionOutOfDate, @NoClutter = @NoClutter, @Importance = @Importance OUTPUT, @WarningLevel = @WarningLevel, @ServerSpecific = @ServerSpecific, @TableTail = @StandardTableTail OUTPUT, @CountWarning = @CountWarning OUTPUT, @CountAdvisory = @CountAdvisory OUTPUT, @AlertHeader = @AlertHeaderOutput OUTPUT, @AdvisoryHeader = @AdvisoryHeaderOutput OUTPUT, @InfoHeader = @InfoHeaderOutput OUTPUT, @Debug = @Debug; --Check @Importance and set to the highest Importance seen so far SET @HighestImportance = ( SELECT CASE WHEN @HighestImportance IS NULL THEN Importance WHEN @HighestImportance > Importance THEN Importance ELSE @HighestImportance END FROM ( SELECT CASE @Importance WHEN ''High'' THEN 1 WHEN ''Normal'' THEN 2 WHEN ''Low'' THEN 3 END AS Importance ) Importance ); --If no headers were populated by the module then revert the back to top hyperlink IF @StandardTableTail IS NOT NULL BEGIN SET @ReportModuleHtml = REPLACE(@ReportModuleHtml,@TableTail,@StandardTableTail); END -- Append the Report module html to the main report body. SELECT @EmailBody = @EmailBody + ISNULL(@ReportModuleHtml,''''); --Append to the Headers --Add warning counts for summary column in ReportData table IF (@WarningLevel = 1 OR @MultiWarningModule = 1) BEGIN SET @AlertHeader += ISNULL(@AlertHeaderOutput,''''); SET @TotalWarningCount += @CountWarning; END IF (@WarningLevel = 2 OR @MultiWarningModule = 1) BEGIN SET @AdvisoryHeader += ISNULL(@AdvisoryHeaderOutput,''''); SET @TotalAdvisoryCount += @CountAdvisory; END IF (@WarningLevel = 3 OR @MultiWarningModule = 1) BEGIN --No count required here just append to header SET @InfoHeader += ISNULL(@InfoHeaderOutput,''''); END END IF (@DetailedSummary = 1) BEGIN IF (@Modulename IS NOT NULL) BEGIN SET @ReportSummary += '' ''+@Modulename+'': Warnings(''+CAST(ISNULL(@CountWarning,0) AS VARCHAR(10))+'') Advisories(''+CAST(ISNULL(@CountAdvisory,0) AS VARCHAR(10))+'')''+CHAR(13)+CHAR(10); END END SET @Duration = CAST(DATEDIFF(MILLISECOND,@ModuleReportStart,GETDATE()) AS MONEY)/1000; EXEC [Inspector].[ExecutionLogInsert] @RunDatetime = @ModuleReportStart, @Servername = @Serverlist, @ModuleConfigDesc = @ModuleConfig, @Procname = @ReportProcedurename, @Frequency = @Frequency, @Duration = @Duration, @PSCollection = @PSCollection, @ErrorMessage = @ErrorMessage; END END ELSE BEGIN RAISERROR(''No Report procedure found for Module: %s'',0,0,@Modulename,@Serverlist) WITH NOWAIT; EXEC [Inspector].[ExecutionLogInsert] @RunDatetime = @ModuleReportStart, @Servername = @Serverlist, @ModuleConfigDesc = @ModuleConfig, @Procname = @ReportProcedurename, @Frequency = @Frequency, @Duration = 0, @PSCollection = @PSCollection, @ErrorMessage = ''No Report procedure found for Module''; END FETCH NEXT FROM ReportProc_cur INTO @ModuleConfig,@TableHeaderColour,@Modulename,@ReportProcedurename,@ServerSpecific,@Frequency END CLOSE ReportProc_cur DEALLOCATE ReportProc_cur --Only populate the Advisory header with Version/Edition changes if this run is not the PeriodicBackupCheck IF ISNULL(@ModuleDesc,@ModuleConfig) != ''PeriodicBackupCheck'' BEGIN --Check for Instance version or edition changes in [Inspector].[InstanceVersionHistory] --Excluded from Warning level control SET @VersionNo = NULL; SET @Edition = NULL; SELECT @VersionNo = CAST([VersionNo] AS VARCHAR(128)), @Edition = CAST([Edition] AS VARCHAR(128)) FROM [Inspector].[InstanceVersionHistory] WHERE [Servername] = @Serverlist AND [CollectionDatetime] >= DATEADD(DAY,-1,GETDATE()); --If version has changed then create an entry in the advisory header IF @VersionNo IS NOT NULL BEGIN SET @AdvisoryHeader = @AdvisoryHeader + ''''+@Serverlist+'' - SQL ''+@VersionNo+''
''; END --If Edition has changed then create an entry in the advisory header IF @Edition IS NOT NULL BEGIN SET @AdvisoryHeader = @AdvisoryHeader + ''''+@Serverlist+'' - SQL ''+@Edition+''
'';
END
END
IF @AlertHeader LIKE ''%''+@Serverlist+''%''
BEGIN
SET @ServerSummaryFontColour = ''''
END
ELSE
IF @AdvisoryHeader LIKE ''%''+@Serverlist+''%''
BEGIN
SET @ServerSummaryFontColour = ''''
END
ELSE
IF @InfoHeader LIKE ''%''+@Serverlist+''%''
BEGIN
SET @ServerSummaryFontColour = ''''
END
ELSE
BEGIN
SET @ServerSummaryFontColour = ''''
END
--Evaluate server and colour code accordingly
INSERT INTO #TrafficLightSummary ([SummaryHeader],[WarningPriority])
SELECT
CASE
WHEN @ServerSummaryFontColour = '''' THEN ''''+ @ServerSummaryFontColour+''''+''[''+''''+@Serverlist+''] ''
WHEN @ServerSummaryFontColour = '''' THEN ''''+ @ServerSummaryFontColour+''''+''[''+''''+@Serverlist+''] ''
WHEN @ServerSummaryFontColour = '''' THEN ''''+ @ServerSummaryFontColour+''''+''[''+''''+@Serverlist+''] ''
WHEN @ServerSummaryFontColour = '''' THEN ''''+ @ServerSummaryFontColour+''''+''[''+''''+@Serverlist+''] ''
END,
CASE
WHEN @ServerSummaryFontColour = '''' THEN 1
WHEN @ServerSummaryFontColour = '''' THEN 2
WHEN @ServerSummaryFontColour = '''' THEN 3
WHEN @ServerSummaryFontColour = '''' THEN 4
END
--Add Break to the end of the Server warning ready for the next
IF @AlertHeader LIKE ''%''+@Serverlist+''%'' BEGIN SET @AlertHeader = @AlertHeader + '' '';
SET @MultiWarningModule = NULL;
SET @MultiWarningModule = (SELECT 1 FROM [Inspector].[MultiWarningModules] WHERE [Modulename] = @Modulename);
--Get Module warning level
SELECT @WarningLevel = [Inspector].[GetWarningLevel](@ModuleConfigDetermined, @Modulename);
BEGIN TRY
SET @SQLstatement = N''
EXEC [Inspector].[''+@ReportProcedurename+'']
@Servername = @Serverlist,
@Modulename = @Modulename,
@TableHeaderColour = @TableHeaderColour,
@WarningHighlight = @WarningHighlight,
@AdvisoryHighlight = @AdvisoryHighlight,
@InfoHighlight = @InfoHighlight,
@PSCollection = @PSCollection,
@ModuleConfig = @ModuleConfigDetermined,
@WarningLevel = @WarningLevel,
@ServerSpecific = @ServerSpecific,
@NoClutter = @NoClutter,
@TableTail = @TableTail,
@CollectionOutOfDate = @CollectionOutOfDate OUTPUT,
@HtmlOutput = @ReportModuleHtml OUTPUT,
@Debug = @Debug;''
EXEC sp_executesql @SQLstatement,
N''@Serverlist NVARCHAR(128),
@Modulename VARCHAR(50),
@TableHeaderColour VARCHAR(7),
@WarningHighlight VARCHAR(7),
@AdvisoryHighlight VARCHAR(7),
@InfoHighlight VARCHAR(7),
@PSCollection BIT,
@ModuleConfigDetermined VARCHAR(20),
@WarningLevel TINYINT,
@ServerSpecific BIT,
@NoClutter BIT,
@TableTail VARCHAR(256),
@CollectionOutOfDate BIT OUTPUT,
@ReportModuleHtml VARCHAR(MAX) OUTPUT,
@Debug BIT'',
@Serverlist = @Serverlist,
@Modulename = @Modulename,
@TableHeaderColour = @TableHeaderColour,
@WarningHighlight = @WarningHighlight,
@AdvisoryHighlight = @AdvisoryHighlight,
@InfoHighlight = @InfoHighlight,
@PSCollection = @PSCollection,
@ModuleConfigDetermined = @ModuleConfigDetermined,
@WarningLevel = @WarningLevel,
@ServerSpecific = @ServerSpecific,
@NoClutter = @NoClutter,
@TableTail = @TableTail,
@CollectionOutOfDate = @CollectionOutOfDate OUTPUT,
@ReportModuleHtml = @ReportModuleHtml OUTPUT,
@Debug = @Debug;
SET @ErrorMessage = NULL;
END TRY
BEGIN CATCH
SET @ErrorMessage = CAST(ERROR_MESSAGE() AS NVARCHAR(128));
END CATCH
SET @Duration = CAST(DATEDIFF(MILLISECOND,@ModuleReportStart,GETDATE()) AS MONEY)/1000;
EXEC [Inspector].[ExecutionLogInsert]
@RunDatetime = @ModuleReportStart,
@Servername = @Serverlist,
@ModuleConfigDesc = @ModuleConfig,
@Procname = @ReportProcedurename,
@Frequency = @Frequency,
@Duration = @Duration,
@PSCollection = @PSCollection,
@ErrorMessage = @ErrorMessage;
--Generate header information
EXEC [Inspector].[GenerateHeaderInfo]
@Servername = ''ALL_SERVERS'',
@ModuleConfig = @ModuleConfigDetermined,
@Modulename = @Modulename,
@ModuleBodyText = @ReportModuleHtml,
@WarningHighlight = @WarningHighlight,
@AdvisoryHighlight = @AdvisoryHighlight,
@InfoHighlight = @InfoHighlight,
@WarningLevelFontColour = @WarningLevelFontColour,
@CollectionOutOfDate = @CollectionOutOfDate,
@NoClutter = @NoClutter,
@Importance = @Importance OUTPUT,
@WarningLevel = @WarningLevel,
@ServerSpecific = @ServerSpecific,
@TableTail = @StandardTableTail OUTPUT,
@CountWarning = @CountWarning OUTPUT,
@CountAdvisory = @CountAdvisory OUTPUT,
@AlertHeader = @AlertHeaderOutput OUTPUT,
@AdvisoryHeader = @AdvisoryHeaderOutput OUTPUT,
@InfoHeader = @InfoHeaderOutput OUTPUT,
@Debug = @Debug;
--Check @Importance and set to the highest Importance seen so far
SET @HighestImportance = (
SELECT
CASE
WHEN @HighestImportance IS NULL THEN Importance
WHEN @HighestImportance > Importance THEN Importance
ELSE @HighestImportance
END
FROM
(
SELECT
CASE @Importance
WHEN ''High'' THEN 1
WHEN ''Normal'' THEN 2
WHEN ''Low'' THEN 3
END AS Importance
) Importance
);
IF (@DetailedSummary = 1)
BEGIN
IF @Modulename IS NOT NULL
BEGIN
SET @ReportSummary += '' ''+@Modulename+'': Warnings(''+CAST(ISNULL(@CountWarning,0) AS VARCHAR(10))+'') Advisories(''+CAST(ISNULL(@CountAdvisory,0) AS VARCHAR(10))+'')''+CHAR(13)+CHAR(10);
END
END
--If no headers were populated by the module then revert the back to top hyperlink
IF @StandardTableTail IS NOT NULL
BEGIN
SET @ReportModuleHtml = REPLACE(@ReportModuleHtml,@TableTail,@StandardTableTail);
END
-- Append the Report module html to the main report body.
SELECT @EmailBody = @EmailBody + ISNULL(@ReportModuleHtml,'''');
--Append to the Headers
--Add warning counts for summary column in ReportData table
IF (@WarningLevel = 1 OR @MultiWarningModule = 1)
BEGIN
SET @AlertHeader += ISNULL(@AlertHeaderOutput,'''');
SET @TotalWarningCount += @CountWarning;
END
IF (@WarningLevel = 2 OR @MultiWarningModule = 1)
BEGIN
SET @AdvisoryHeader += ISNULL(@AdvisoryHeaderOutput,'''');
SET @TotalAdvisoryCount += @CountAdvisory;
END
IF (@WarningLevel = 3 OR @MultiWarningModule = 1)
BEGIN
--No count required here just append to header
SET @InfoHeader += ISNULL(@InfoHeaderOutput,'''');
END
END
ELSE
BEGIN
RAISERROR(''No Report procedure found for Module: %s'',0,0,@Modulename,@Serverlist) WITH NOWAIT;
EXEC [Inspector].[ExecutionLogInsert]
@RunDatetime = @ModuleReportStart,
@Servername = @Serverlist,
@ModuleConfigDesc = @ModuleConfig,
@Procname = @ReportProcedurename,
@Frequency = @Frequency,
@Duration = 0,
@PSCollection = @PSCollection,
@ErrorMessage = ''No Report procedure found for Module'';
END
FETCH NEXT FROM OneoffsReportProc_cur INTO @ModuleConfig,@Modulename,@ReportProcedurename,@ServerSpecific,@Frequency
END
CLOSE OneoffsReportProc_cur
DEALLOCATE OneoffsReportProc_cur
IF @Importance = ''High''
BEGIN
SET @SubjectText = @AlertSubjectText;
END
IF @AlertHeader != ''''
BEGIN
SET @AlertHeader = ''
''
+@AlertHeader
END
ELSE
BEGIN
SET @AlertHeader = ''
'' END
--Add Break to the end of the Server Advisory Condition ready for the next
IF @AdvisoryHeader LIKE ''%''+@Serverlist+''%'' BEGIN SET @AdvisoryHeader = @AdvisoryHeader + ''
'' END
--Add Break to the end of the Server Info header ready for the next
IF @InfoHeader LIKE ''%''+@Serverlist+''%'' BEGIN SET @InfoHeader = @InfoHeader + ''
'' END
FETCH NEXT FROM ServerCur INTO @Serverlist
END
CLOSE ServerCur
DEALLOCATE ServerCur
--Internal use Only
IF OBJECT_ID(''Inspector.DriveExtensionRequest'') IS NOT NULL
BEGIN
IF @DriveExtensionRequest IS NOT NULL
BEGIN
RAISERROR(''Executing DriveExtensionRequest with @Email = 1'',0,0) WITH NOWAIT;
EXEC sp_executesql N''EXEC [Inspector].[DriveExtensionRequest] @html = @DriveExtensionRequestStage, @WarningHighlight = @WarningHighlight,@Email = 1, @EmailOutput = @DriveExtensionRequest OUTPUT'',
N''@DriveExtensionRequestStage VARCHAR(MAX),
@WarningHighlight VARCHAR(7),
@DriveExtensionRequest VARCHAR(MAX) OUTPUT'',
@DriveExtensionRequestStage = @DriveExtensionRequestStage,
@WarningHighlight = @WarningHighlight,
@DriveExtensionRequest = @DriveExtensionRequest OUTPUT;
END
END
IF (@DetailedSummary = 1)
BEGIN
SET @ReportSummary += ''ALL_SERVERS (''+@ModuleConfigDetermined+''):''+CHAR(13)+CHAR(10);
END
SET @ErrorMessage = NULL;
DECLARE OneoffsReportProc_cur CURSOR LOCAL STATIC
FOR
SELECT
[ModuleConfig_Desc],
[Modulename],
[ReportProcedurename],
[ServerSpecific],
[Frequency]
FROM [Inspector].[GetNonServerSpecificModules](@ModuleConfigDetermined,@DatabaseGrowthCheckRunEnabled)
ORDER BY [ReportOrder] ASC
OPEN OneoffsReportProc_cur
FETCH NEXT FROM OneoffsReportProc_cur INTO @ModuleConfig,@Modulename,@ReportProcedurename,@ServerSpecific,@Frequency
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FullReportProcedurename = N''Inspector.''+@ReportProcedurename;
IF OBJECT_ID(@FullReportProcedurename) IS NOT NULL
BEGIN
RAISERROR(''Reporting on Module: [%s] for server [%s] '',0,0,@Modulename,@Serverlist) WITH NOWAIT;
--Reset header output variables
SET @AlertHeaderOutput = NULL;
SET @AdvisoryHeaderOutput = NULL;
SET @InfoHeaderOutput = NULL;
SET @ModuleReportStart = GETDATE();
SET @TableTail = ''
Warning Conditions:
No Warnings are present
Advisory Conditions:
''+@AdvisoryHeader
END
ELSE
BEGIN
SET @AdvisoryHeader = ''
No Advisories are present
''
END
IF @InfoHeader != ''''
BEGIN
SET @InfoHeader = ''
Informational Conditions:
''+@InfoHeader
END
ELSE
BEGIN
SET @InfoHeader = ''
No Informational conditions are present
''
END
--Red
IF EXISTS (SELECT SummaryHeader FROM #TrafficLightSummary WHERE WarningPriority = 1 )
BEGIN
SELECT @ServerSummaryHeader = @ServerSummaryHeader +
(SELECT STUFF(SummaryHeader,1,0,''Warnings Present -
'')
FROM
(
SELECT SummaryHeader + '' ''
FROM #TrafficLightSummary
WHERE WarningPriority = 1
FOR XML PATH('''')
) AS SummaryHeader(SummaryHeader)
)
END
--Amber
IF EXISTS (SELECT SummaryHeader FROM #TrafficLightSummary WHERE WarningPriority = 2 )
BEGIN
SELECT @ServerSummaryHeader = @ServerSummaryHeader + ''
'' +
(SELECT STUFF(SummaryHeader,1,0,''Advisories/No Warnings -
'')
FROM
(
SELECT SummaryHeader + '' ''
FROM #TrafficLightSummary
WHERE WarningPriority = 2
FOR XML PATH('''')
) AS SummaryHeader(SummaryHeader)
)
END
--White
IF EXISTS (SELECT SummaryHeader FROM #TrafficLightSummary WHERE WarningPriority = 3 )
BEGIN
SELECT @ServerSummaryHeader = @ServerSummaryHeader + ''
'' +
(SELECT STUFF(SummaryHeader,1,0,''Informational/No Advisories or Warnings -
'')
FROM
(
SELECT SummaryHeader + '' ''
FROM #TrafficLightSummary
WHERE WarningPriority = 3
FOR XML PATH('''')
) AS SummaryHeader(SummaryHeader)
)
END
--Green
IF EXISTS (SELECT SummaryHeader FROM #TrafficLightSummary WHERE WarningPriority = 4 )
BEGIN
SELECT @ServerSummaryHeader = @ServerSummaryHeader + ''
'' +
(SELECT STUFF(SummaryHeader,1,0,''OK -
'')
FROM
(
SELECT SummaryHeader + '' ''
FROM #TrafficLightSummary
WHERE WarningPriority = 4
FOR XML PATH('''')
) AS SummaryHeader(SummaryHeader)
)
END
/* Reuse the ServerSummaryHeader parameter setting to a new value */
SET @ServerSummaryHeader = ''