SQLUndercover Inspector user guide

Overview

The Inspector is a free SQL server monitoring and alerting tool written in T-SQL covering some aspects of database management utilizing sp_send_dbmail to send HTML reports based on configurable thresholds. Running from a single Agent job use the internal Scheduling tables within the Inspector database to schedule data collections and reports with all the customisation and configurations managed within the database. For a run down of included modules check out our Introduction post

Contents

Installation:

Getting started

Additional config

Customisation

Custom modules

Installation using T-SQL

Single instance installation (no centralisation)

  • Download SQLUndercoverinspectorV2.sql
  • Execute it against your Inspector database to create/alter the setup proc and then execute the stored procedure (exec call can be found in the messages tab)
  • Repeat for all SQL instances where you want to install//update the Inspector

Multi Instance using linked server for centralisation

  • Create a linked server (On all non central servers e.g SQL02 and SQL03) pointing to your central server (e.g SQL01) the user associated with the linked server will need read/write access to the monitoring database.
  • Download SQLUndercoverinspectorV2.sql
  • Execute it against your Inspector database to create/alter the setup proc
  • Copy the exec call from the messages tab and paste into a query window against your database
  • Starting with the central server leave @LinkedServername as NULL and execute the command to install on your central server. We need to start with the central server to ensure that all the objects referenced across linked server will succeed in the following step.
  • For all non central servers change the value for @LinkedServername to the name of your linked server and execute the command

Installation using Powershell

  • Download Install-Inspector.ps1 if you haven’t already downloaded the Powershell zip
  • Open Install-Inspector in Powershell ISE
  • update parameter $SQLInstances with a comma delimited list of your servers i.e “SQL01,SQL02,SQL03”
  • update parameter $LoggingDB with the name of the Inspector database, if it does not exist you will be prompted if you want to create it (this will use your SQL default data/log paths).
  • For single instance install (including powershell centralisation) leave $LinkedServername = “NULL” if you are centralising data via linked server update $LinkedServername = “YourLinkedServer” but ensure you have the linked server in place on your instances.
  • Run the script.

Config basics

Servers/instances: For Servers/Instances you wish to report on, these Servers/Instances must have a row in [Inspector].[CurrentServers] with the IsActive flag set to 1.

Modules: You will find the modules listed in the [Inspector.Modules] table.
Each module collection listed in the Modules table directly affects the server where that database is restored to, for example SQL01 has its module collections controlled via the Inspector database on the SQL01 instance, SQL02 on SQL02 and so on (this only affects data collections).
Each Module can:

  • Collect data only (ReportProcedurename NULL)
  • Report on data only (CollectionProcedurename NULL)
  • Collect and report on data (ReportProcedurename and CollectionProcedurename NOT NULL)

Enable/Disable modules: The IsActive column in the Modules table controls whether the module is enabled or disabled for the server which you are connected to.

Global settings: The [Inspector].[Settings] table contains global options for the Inspector, for example:
DriveSpaceRetentionPeriodInDays
ReportDataRetention
FullBackupThreshold
DiffBackupThreshold
LogBackupThreshold
PSAutoUpdateModules

See the standard settings section for a full list of global settings.

Basic Scheduling

Scheduling overview:

The Inspector runs from a single agent job, this job runs every minute looking for module collections and module reports which are due and Active.

For example – Frequency 1440 (mins) is a once a day module, the StartTime and EndTime window will determine when that module will be executed.

A module with Frequency of 120 will run two hourly based on your StartTime and EndTime and so on.


Module Collections: For data collections the schedule information is taken from the [Inspector].[Modules] table, i.e (Inspector.Modules CollectionProcedurename) where IsActive = 1 and the frequency,StartTime and EndTime all meet the requirements

Module Reports: For Reports these can be found in [Inspector].[ModuleConfig] i.e Inspector.ModuleConfig where IsActive = 1 and the frequency,StartTime and EndTime, however the modules which are subscribed to that report (to be included) are found in the Modules table (ReportProcedurename NOT NULL AND IsActive = 1)

Email config

Email Recipients: The inspector will use the default mail profile associated with msdb.dbo.sp_send_dbmail , the email recipients can be defined in the [Inspector].[EmailRecipients] table, this table accepts a semicolon separated list.

Customise email subjects: The [Inspector].[EmailConfig] table contains a row per Moduleconfig along with the Email subject text which you can alter to our requirement.

Email Profile: The email profile used for sending emails can be configured by setting the EmailProfile column in theInspector.Moduleconfig table per row (per report).

Email subject: The subject for each report can be specified per ModuleConfig_Desc in the Inspector.EmailConfig table by updating the EmailSubject column.

Standard settings

Global settings overview:

DescriptionValueUsage
SQLUndercoverInspectorEmailSubjectSQLUndercoverName of the SQL stack (default: SQLUndercover)
DriveSpaceRetentionPeriodInDays90Number of days to retain drive space information
AGPrimaryHistoryRetentionPeriodInDays90Number of days to retain AG Failover history
FullBackupThreshold8BackupsCheck threshold in days, warns if the last full backup exceeds this age
DiffBackupThreshold2BackupsCheck threshold in days, warns if the last diff backup exceeds this age
LogBackupThreshold20BackupsCheck threshold in minutes, warns if the last log backup exceeds this age
DaysUntilDriveFullThreshold56Drivespace module – Warn if estimated space remaining on a drive is estimated to run out in less than this value in days.
FreeSpaceRemainingPercent10Drivespace module – Advise if space remaining on a drive is less than this value percent remaining free space.
DatabaseGrowthsAllowedPerDay1Database growth module, do not advise of database growths if they grew this amount of times in 24 hours.
MAXDatabaseGrowthsAllowedPerDay10Database growth module, advise of database growths if they grew more than this amount of times in 24 hours.
LongRunningTransactionThreshold300Long running transactions advise if transactions exceed this time in seconds (when collection runs)
ReportDataRetention30Retention in days to keep Report data html in the ReportData table
BackupsPathF:\Backups\,\\BackupBox\SQLBackupsBackupspace module comma delimited list of backup drives to check.
EmailBannerURLNULLURL for the logo at the top of the report.
PSEmailBannerURLNULLURL for the logo at the top of the report when using the powershell collection.
DatabaseOwnerExclusionssaComma delimited list of database owners which you do not want to warn/advise on.
AgentJobOwnerExclusionssaComma delimited list of agent job owners which you do not want to warn/advise on.
LinkedServernameNULLIf the Inspector has been setup to use a linked server at install then this is populated with the linked server name – Do not amend this row!
InspectorBuild2.1Current Inspector build Do not amend this row!
DriveSpaceDriveLetterExcludesNULLDriveSpace module – if you want to exclude certain drives from thresholds set a comma delimited list here i.e C,D
DataDrivesS,UDatabasefiles module , Comma delimited list of data drives
LogDrivesT,VDatabasefiles module , Comma delimited list of log drives
InspectorUpgradeFilenameSyncNULLInternal use only Do not amend this row!
UseMedianCalculationForDriveSpaceCalc0Drive space, Global setting for space usage calculations, 0 for average and 1 for median based. (individual drives can be overridden in the DriveSpaceCalc table.
ReportDataDetailedSummary1Show a detailed summary of reports in the ReportData Summary column.
CentraliseExecutionLog0If using linked server centralisation you can choose to centralise the execution log data, this setting uses the global setting ‘LinkedServername’
BlitzWaitsTopXRows3Blitz waits custom module – control the Top X rows shown per bucket
BlitzWaitsHourlyBucketSize2Blitz waits custom module – control the amount of data in hours aggregated per bucket
CPUHistoryRetentionInDays7CPU custom module, Retention in days for the CPU table
CPUThresholdWarningHighlight90CPU custom module,warning highlighting if usage exceeds this value in percent
CPUThresholdAdvisoryHighlight85CPU custom module, advisory highlighting if usage exceeds this value in percent
CPUThresholdInfoHighlight75CPU custom module, info highlighting if usage exceeds this value in percent
BackupSpaceWeekdayOffset1Backup space module, you can offset the day which backup sizes are calculated for example you may always want to calculate the space required for the next weekday rather than today. Uses this value in a dateadd.
PSAutoUpdateModules1Enable or disable auto updates via powershell
PSAutoUpdateModulesFrequencyMins1440Frequency in which the powershell collection should check for updates in minutes.
BlitzWaitsAlwaysShowBreached1Force visibility of Poison wait types or any other wait types you are tracking from the Inspector.BlitzWaits_WatchedWaitTypes table where the threshold has been breached
BlitzWaitsBucketColourOdd#E6F5FFSpecify hex colour here to replace the alternating bucket colour for odd rows
BlitzWaitsBucketColourEven#CCEBFFSpecify hex colour here to replace the alternating bucket colour for even rows

Advanced Scheduling

In the Currentservers table you have always had the ability to lock a server down to a specific Module config by setting a ModuleConfig_Desc and by specifying NULL it would always default to the ‘Default’ module config.

The new scheduling functionality took some flexibility away with this format so we made NULL = Subscribe all i.e a server with NULL in ModuleConfig_Desc in the CurrentServers table will now subscribe to ALL Moduleconfig where IsActive = 1 in both the Modules table and the ModuleConfig table.

With these changes it allows you to tailor each server the way you want it as you may want a server or group of servers to have an additional moduleconfig for example, perhaps a custom module that you want to run alongside existing moduleconfig.

Subscribe server to all Moduleconfig
Subscribe server to a single Moduleconfig only

ModuleConfig (Reports)
ModuleConfig can be found in the ModuleConfig table, from this table they can be scheduled, enabled/disabled and various options can be altered. ModuleConfig are a way of being able to customise which modules are reported on and at what frequency/time of day.

To create a new ModuleConfig (Report) you will need to insert a row into Inspector.ModueConfig here is an explanation of the columns:

ModuleConfig_DescName of the ModuleConfig
IsActiveEnable/Disable flag
FrequencyFrequency of the report in minutes
StartTimeTime that the report window starts
EndTimeTime that the report window ends
LastRunDateTimeUsed internally by the Inspector however if you want to force a re run of a report set this to NULL
ReportWarningsOnlyOnly email the report if the specified warning level is present within the report. (See Warnings and thresholds section for more details).
NoClutterSome modules like to tell you there were no issues found, switching this option to 1 will suppress these from showing no issues and reduce the size of your report.
ShowDisabledModulesControls whether disabled modules show as a comma delimited list on the report.
RunDayComma delimited list of weekdays you want the report to run on, NULL = Everyday, alternatively specify weekdays i.e Monday,Wednesday,Thursday

Sample insert statement:

INSERT INTO [Inspector].[ModuleConfig]
           ([ModuleConfig_Desc]
           ,[IsActive]
           ,[Frequency]
           ,[StartTime]
           ,[EndTime]
           ,[ReportWarningsOnly]
           ,[NoClutter]
           ,[ShowDisabledModules]
           ,[RunDay])
     VALUES
           ('MyNewConfig'
		   ,1
		   ,1440
           ,'08:00'
           ,'18:00'
           ,0
           ,1
           ,0
           ,'Monday,Tuesday,Wednesday,Thursday,Friday'
		   );

Now that you have a ModuleConfig in place you will now need to specify which modules are to be included in the report, for this you will need to insert rows into Inspector.Modules ensuring that the ModuleConfig_Desc column matches the name of your new ModuleConfig.

Report only on data that occurred within certain hours:

The new Monitor hours table allows you to set the window in which you want to see report data for, currently this only applies to modules CPU, BlitzWaits and BlitzFileStats. Insert a row per Module and set the MonitorHourStart and MonitorHourEnd accordingly, for example I may want to collect CPU data all day and night but I may only want to see CPU information on reports for the CPU module when the thresholds are breached within the hours of 9 and 17 this can be achieved using the MonitorHours table. If no row is present then 0-23 will be assumed (24/7)

Sample insert into MonitorHours:

INSERT INTO [Inspector].[MonitorHours] ([Servername],[Modulename],[MonitorHourStart],[MonitorHourEnd])
VALUES('SQL01','CPU',9,17);


Excluding server reports

If you want to exclude a specific server from showing a report for a specific module , for example let’s say I want to see the drivespace report for all by servers except for SQL03 I can insert a row into the Inspector.ModuleConfigReportExclusions table setting IsActive to 1 and this will then exclude this server from showing a drivespace table on the report.

INSERT INTO [Inspector].[ModuleConfigReportExclusions] ([Servername],[Modulename],[IsActive])
VALUES(N'SQL03','DriveSpace',1);
SELECT [Servername]
      ,[Modulename]
      ,[IsActive]
FROM [Inspector].[ModuleConfigReportExclusions];

Module configuration

Each Module needs a row in the Inspector.Modules table in order to be executed, for data collection stored procedures the following also need to evaluate to true:

  • IsActive = 1
  • Current time must fall between StartTime and EndTime

For Report only procedures the schedule information in the Modules table is ignored and instead the execution of Report procedures is based on frequency, StartTime and EndTime from the subscription (ModuleConfig_Desc) in the ModuleConfig table based on ModuleConfig_Desc

Edit Module Header text

Previously the header text was hard coded, now you have some control over what the heading text says per module, the Default header text can be found in the table Inspector. DefaultHeaderText but you can override the text in the Modules table .

SELECT 
Modulename,
HeaderText 
FROM [Inspector].Modules];

SELECT * 
FROM [Inspector].[DefaultHeaderText];
Modules header text and Default header text
If headertext in the Modules table is null then the Default header text will be used.

Edit report colours

To change the Warning, Advisory and Info header colours including the highlighting colours for the tables you can set what ever hex colour you like in the Inspector.ModuleWarnings table, the colours can be reset back to default by executing Inspector.ResetHtmlColors

Table header colours can be changed on a server level basis in the Insepctor.CurrentServers table by changing the values in the column TableHeaderColour using a hex colour.

Warning levels and thresholds

Module collections:

In the Modules table you will find a WarningLevel column, this column can have a value of 1-3 which controls the highlighting of rows within the report for a given module and also the header generated at the top of the report to point out that a warning/advisory condition is present, by default the warning levels are:
1 – Red
2 – Yellow
3 – White

Module Reports (ModuleConfig):

In the ModuleConfig table you will find the ReportWarningsOnly column, supported values for this column are 0-3 with each value representing the minimum warning level which needs to be present within the report in order for the report to be emailed out to the recipients, for example:

0 – Email the report regardless of the warning levels present in the report
1 – Email the report only if there are Warning level 1 module thresholds breached (Red Warnings)
2 – Email the report only if there are Warning level 2 module thresholds breached (Yellow Advisories)
3 – Email the report only if there are Warning level 3 module thresholds breached (White Informational)

Report Order

Change the order in which the modules are displayed , ordering can be changed in Inspector.Modules

The report order can now be adjusted via the Inspector.Modules table.
Report order is split into two sections , Server specific and non server specific. Server specific modules will report within their respective server section and show only that servers’ information whereas non server specific will be one table that contains all Servers’ information – these tables are situated at the very end of the report e.g Database Growths.

When the report is produced it will look at these sections separately to determine the report order*.

To confirm your ordering you can run the following query:

SELECT * 
FROM [Inspector].[Modules] 
WHERE IsActive = 1 
ORDER BY 
ModuleConfig_Desc ASC, 
ServerSpecific DESC, 
ReportOrder ASC
Report order

*Unfortunately none of the built in Inspector modules will support switching between Server specific and Non server specific, this feature was added to benefit custom user create modules so that it allows you to code accordingly. Report order however is fully supported.

Custom modules – collection only

We have added the ability for you to code your own modules! not only that but we have been able to allow the following scenarios.

  • Collect data to a table only
  • Report on data only
  • Collect data and report on that data

To collect data only:

  1. Create a new table in the Inspector Schema , this table MUST have the following columns:
    Servername NVARCHAR(128)
    Log_Date DATETIME

    The rest of the columns are up to you!
  2. Create a new stored procedure in the Inspector schema following the naming convention of Procname+’Insert’ i.e DriveSpaceInsert, use this procedure to insert into your new table, for Servername use @@Servername and Log_Date use GetDate()
  3. If you are creating a new Moduleconfig specific to this module then insert a row into Inspector.ModuleConfig and set Frequency to 1440, StartTime and FinishTime to the same value i.e 00:00 (as you do not want to report on it)
  4. Insert a row into Inspector.Modules with the relevant details – CollectionProcedurename = your proc name , set ReportProcedurename to NULL as you are not reporting on it.

Custom modules – collect and report

  1. Script out stored procedure [Inspector].[ModuleReportProcTemplate] as this will serve as a template.
  2. Amend sections labelled /**  REQUIRED  **/ and if required amend /**  OPTIONAL**/ sections.
    The most important part here is getting the query right with the CASE statement as this case statement is responsible for the Warning/Advisory highlighting.
  3. Create the stored procedure with a naming convention of Your proc name + Insert i.e DriveSpaceReport
  4. If you are creating a new Moduleconfig specific to this module then insert a row into Inspector.ModuleConfig and set Frequency, Warninglevel , StartTime and FinishTime and remaining columns accordingly.
  5. Insert a row into Inspector.Modules with the relevant details – ReportProcedurename = your proc name , set CollectionProcedurename to NULL as you are not collecting data.

Custom modules – report only

  1. Create a new table in the Inspector Schema , this table MUST have the following columns:
    Servername NVARCHAR(128)
    Log_Date DATETIME

    The rest of the columns are up to you!
  2. Create a new stored procedure in the Inspector schema following the naming convention of Procname+’Insert’ i.e DriveSpaceInsert, use this procedure to insert into your new table, for Servername use @@Servername and Log_Date use GetDate()
  3. If you are creating a new Moduleconfig specific to this module then insert a row into Inspector.ModuleConfig and set Frequency, StartTime and FinishTime accordingly (this is for the report proc)
  4. Script out stored procedure [Inspector].[ModuleReportProcTemplate] as this will serve as a template.
  5. Amend sections labelled /**  REQUIRED  **/ and if required amend /**  OPTIONAL**/ sections.
    The most important part here is getting the query right with the CASE statement as this case statement is responsible for the Warning/Advisory highlighting.
  6. Create the stored procedure with a naming convention of Your proc name + Insert i.e DriveSpaceReport
  7. Insert a row into Inspector.Modules with the relevant details – CollectionProcedurename = your collection proc name , set ReportProcedurename to your report proc name , setting the Frequency , Warninglevel, StartTime and EndTime and remaining columns accordingly (StartTime and EndTime in the Modules table applies only to the collection proc)
ModuleReportProcTemplate procedure Required field

Multi warning level custom modules

Since adding the ability to create your own modules we thought it would be a good idea to give you the flexibility to allow your Report procedure to raise more than just one Warning/Advisory highlight at a time as you may decide that you want to use some form of threshold mapping. An example of the multi warning level highlighting is the Drive space module.

When you create your custom module and you decide that you are going to code the report procedure to flag multiple warning levels you will need to make sure that you insert the module name into Inspector.MultiWarningModules otherwise your module will be subscribed to the value you gave it in the Modules table. When your module appears in the MultiWarningModules table, the warning level specified against the module in the modules table is ignored and instead all three warning levels will be evaluated against module by stored procedure Inspector.GenerateHeaderInfo.

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: