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:

• Install using T-SQL
• Install using Powershell

Getting started

• Config basics
• Basic Scheduling
• Email config
• Standard settings

Additional config

• Advanced scheduling
• Module configurations
• Override module thresholds per server

Customisation

• Edit Module Header text
• Edit report colours
• Warning levels and thresholds
• Report order

Custom modules

• Collection only
• Collection and report
• Report only

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

Installation using Powershell

• Download Install-Inspector.ps1 if you haven’t already downloaded the Powershell zip
• Right click and select “Run with PowershellShell”
  
  • You will be asked the following questions:
    
    Install from local files ‘File’ or ‘URL’? Default answer is ‘URL’:
    
    Which branch of Inspector would you like to install , ‘master’ or ‘Inspector-Dev’? Default answer is ‘master’:
    
    Server(s) (pipe delimited) e.g SQL01|SQL02?:
    
    Database name:
    
    Custom modules to install, options are ‘NONE’, ‘ALL’ , or comma delimit from the list: CPU BlitzWaits Catalogue BlitzFileStats:
    
    Use windows auth Y/N?:
    
    Email address for receiving reports, Semi colon delimited eg ‘Email@domain.com;Email2@domain.com’. Press enter to skip:
    
    

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:

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.

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:

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

Override Server Thresholds

We added new functionality in Inspector 2.4 to allow you overrride many of the module thresholds that can be found in the Settings table, the list of thresholds that can be overridden currently are:

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

Here’s how it works – we created a new table called [Inspector].[ServerSettingThresholds] this table allows you to override the global module thresholds for thresholds in the above list at a server level, for example you may be collecting data for SQL01 and SQL02 in SQL01’s Inspector database but perhaps you want to set different thresholds for SQL02 for the backups module:

Insert a row for the server in the ServerSettingThresholds table e.g:

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

Whilst this row is present and IsActive = 1 it will be used to override the threshold in the Settings table (Global settings) this is achieved with a new function which can be found within all relevant Report procs:

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

This will return the value set in the new threshold table, any changes to the global settings table will no longer affect SQL02. If the report runs for a server which has no overrides it will always use the Global setting from the main Settings table just like before.

There are two columns which you can use to specify a threshold value, an INT and a VARCHAR(255) , when the function is executed to get the threshold value the INT column is first evaluated, if this is NULL then the String value is used, if no override is present at all (no row or IsActive = 0) then the Global setting value is returned. Each report proc has been coded with a TRY_CAST to the required data type as long as this evaluates then the value you specified will be used, otherwise a default value will be used (this may differ from the global value) it’s up to you to use sensible values so no putting ‘ABC’ for a logBackupThreshold 😛 otherwise this will just be turned into a default value of 20 on the fly during execution, for example:

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

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

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

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

*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)

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.