Introducing: The SQLUndercover Inspector

Page Updated: 22/12/2019

Current changes:
Inspector V2 changes

Previous changes:
Inspector V1.4 changes.
Inspector V1.3 changes.
Inspector V1.2 changes.

Introducing: The SQLUndercover Inspector! now available on GitHub but what exactly is it?

2018-01-22 14_44_08

The All new Inspector gives you the power to Create, configure and schedule SQL modules, control when modules execute and control when reports are produced/emailed.

It’s completely open source under MIT licence , we would love to hear from you with your suggestions and improvements.
You can find our GitHun Repo here and we have a dedicated channel over on Slack in the SQL community #sqlundercovertools

When you install the Inspector you will get some default modules:

ADHocDatabaseCreations
AGCheck
AGDatabases
BackupsCheck
BackupSizesByDay
BackupSpace
DatabaseFiles
DatabaseGrowths
DatabaseOwnership
DatabaseSettings
DatabaseStates
DriveSpace
FailedAgentJobs
JobOwner
LoginAttempts
LongRunningTransactions
ServerSettings
SuspectPages
TopFiveDatabases
UnusedLogshipConfig
Instance Version/Edition changes

All of these modules can be enabled/disabled as required, most of these modules have been around throughout V1 but this time around what we really wanted to do was offer the ability to create your own modules so that you can collect your own data , schedule it and if you want to you can report on it.

We will be offering our very own custom modules as add ins which will be available as separate sql files that you can install.

So how does it work?

It is all driven from a SQL agent job which runs on a schedule every minute, each time the job runs it will look for Servers in the CurrentServers table and Module schedules that are due (collections) from the Modules table, it will also check for any Reports (ModuleConfig) that are due and execute accordingly.

Scheduling basics

Modules can be found in the Modules table, here you can set schedules for existing modules or add new ones that you can code yourself. The Modules table allows you to specify the Start time and End time for your collection to create a run window i.e StartTime – 09:00, EndTime – 18:00 , you will also be able to specify a frequency in mins too e.g 1440 for once a day or any other number between 1 and 1440 to represent minutes within the time window specified. Schedules within this table only control execution of the collection stored procedure for that row.

The ModuleConfig table works exactly the same except this table is used to determine report schedules, there are a couple of other configurations here too such as ShowWarningsOnly, so if you only want the report to email out when there is a WarningLevel 1 present then switch that to a 1. Schedules in this table only control Report stored procedures within the Modules table where the ModuleConfig_Desc matches the ModuleConfig_Desc in the ModuleConfig table.

Warnings and Thresholds

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

These colours can be changed for any html hex colour in the ModuleWarnings table, you can also change the gradient colours in the header too!

Configurations and Customisation

The Inspector offers plenty of configuration and customisation options , check out the settings table for threshold values and change them to suit your needs and add your own if you need them!

Need to change the email recipient or the email subject? there are corresponding tables for those: Inpector.EmailRecipients and Inspector.EmailConfig


Do not like the Red, Yellow, White Warning/Advisory scheme? go ahead and change it!

Do not like the Module heading text? Change it in the Modules table using the HeaderText column.

Fed up of seeing tables stating that there are no issues present? Go ahead and suppress those tables from showing in the ModuleConfig table using the NoClutter column.

Do not like the order in which the modules are displayed? Update the Modules table and set the ReportOrder column as required.

We want you to have as much control over the tool as possible so that you can have it your way.

Create your own modules

Create you own modules – you have three options available when you create your own module.

Collect data only
Report on data only
Collect and report on the data

There are a few steps to get your custom module added, here is a quick overview of what you will need to do.

Create a table:

Create a table to store your data in the Inspector schema, the table must include these columns:
Servername NVARCHAR(128) NOT NULL – Populate with @@SERVERNAME
Log_Date DATETIME NOT NULL – Populate with GETDATE()
and then add any other columns you need.

Create your collection stored procedure:

Create your collection stored procedure in the Inspector schema to populate the table you created

Create your report stored procedure

Create your Report procedure in the Inspector schema ,use the template proc “ModuleReportProcTemplate” to help you with required syntax.

Insert schedule/config rows

Insert a row into Inspector.Modules and Inspector.ModuleConfig tables as required.

And the rest is taken care of.

Track what the Inspector has been doing

To see what the Inspector has been executing query Inspector.ExecutionLog this will show which procedures have been executed , when and their duration. For a more detailed output query the view Inspector.ExecutionLogDetails which will show you schedule frequency information, run number and the actual frequency.

Included modules

Instance Version/Edition changes – notify you of any changes to the Edition of SQL, Major version or Minor version, this information is stored within the database to allow you to check back at any time to see when these changes were identified by the collection. If changes are detected then these changes are reflected in the report.

2019-01-10 20_47_20-dc01 on cataclysm - virtual machine connection

ADHocDatabaseCreations – Checks for any databases that have been created in the last 7 days which include numerics in the database name or keywords ‘Restored’ or ‘Copy’ , any databases found matching the above criteria will be highlighted in yellow (configurable) and show an advisory condition shown at the top of the report.

AGCheck – As with all modules in the Inspector you can control the warning level which will determine the highlighting colours used in the report, This module will check:
Health of availability groups – The health of your availability groups and produce a warning/advisory if any Availability group is unhealthy.

Failover ready node count – Failover ready check for the AG Check module, the failover node count that you consider as being failover ready per AG can be set within a new configuration table [Inspector].[AGCheckConfig], if the total failover ready node count for an AG is less than that specified in the config table then a Warning/Advisory is shown on the report. When you specify a failover ready count of 10 or more this is then considered a percentage based node count therefore the count is determined by the the percent of replicas within the AG

AG Failovers – If a failover has occurred since the last Inspector collection the new primary will be inserted into a new history table [Inspector].[AGCheckConfig] and a Warning/Advisory (Based on your warning level config for AGCheck module) will be shown on the report

2019-05-16 21_08_03-SQLUndercover Inspector
Failover ready Threshold shown and Failover ready flag.
2019-05-16 21_10_05-SQLQuery12.sql - SQL03.SQLUndercover (SQLUNDERCOVER_Administrator (53)) - Micros
Failover ready config based on Node count
2019-05-16 21_11_31-SQLQuery12.sql - SQL03.SQLUndercover (SQLUNDERCOVER_Administrator (53)) - Micros
Example of percentage based count which uses a computed column, The report will use the computed column when FailoverReadyNodeCount is greater than or equal to 10  
2019-05-16 19_58_49-SQLUndercover Inspector
2019-05-16 19_59_10-SQLUndercover Inspector

AGDatabases – If you are using Availability groups and you have this new module enabled the Inspector will assume that all databases should be joined to an AG, every database name for the instance is inserted into a new table called [Inspector].[AGDatabases] and the Is_AG flag is set to a 1 , if databases are joined to an AG then the Is_AGJoined column is set to a 1 therefore no Advisory will be shown on the report. If a database is marked as Is_AG then it will continue to warn if not joined to an AG on the Inspector reports, if you wish to exclude a given database from the advisory condition simply update Is_AG to a 0 .

Instances that are not Hadr enabled with at least one AG will automatically have Is_AG set to 0 and will be excluded from the checking even if the module is enabled.

2018-10-08 19_37_26-DC01 on CATACLYSM - Virtual Machine Connection
2018-10-08 19_40_31-SQLUndercover Inspector


BackupsCheck – Check all backups for all databases including system databases (not TempDB) , compare the last backup dates for FULL, DIFF and LOG against the thresholds set in the Settings table, if there are any breaches then Highlight affected database/s in Red and Show a Warning Condition at the top of the report. If you take transaction log backups from Secondary replicas then the backupscheck will handle this too as long as you are running the collection on all servers taking backups and you are centralizing your collections to a central database. AG Backup Pref will show the Automated backup preference at the time of collection for the given AG (N/A for non AG) and the preferred servers column will show only the relevant server/s which are able to take transaction log backups.

2018-10-08 16_09_13
2018-10-08 20_06_54-SQLUndercover Inspector

BackupSizesByDay – Show total backup size for Server/s broken down by Day of the week.

BackupSizes

BackupSpaceCheck – Checks the space available on the backup drive specified in the settings table against the total estimated backup size required for the next 24 hour period, if there is insufficient space then the information will be highlighted in red (configurable) and a Warning condition shown at the top of the report. *

*This module uses xp_cmdshell therefore it is possible to check a UNC path, multiple backup paths can be specified in the Inspector.Settings table.

DatabaseFileCheck – Checks for Data Files on the Log drive and Log files on the Data drive, these drives are configured by the user during the Inspector setup. If any discrepancies are found then a Red warning condition will be added

2018-01-22 22_49_03


DatabaseGrowthCheck – Checks database data file sizes at collection versus sizes when last logged 24 hours ago , if the total growth increments exceeds the allowable growths per day threshold then you will see the file information highlighted in yellow and an Advisory condition is shown at the top of the report . If the total growths for a given file exceeds your maximum allowable growths threshold then the file information is highlighted in red and a Warning condition show at the top of the report. All growths are logged to the DatabaseFileSizeHistory table.

2018-01-22 22_11_04


DatabaseOwnership – Checks database ownership and reports if the owner differs from your preferred owner list in the Inspector.Settings table.

2018-01-22 22_07_12

DatabaseSettings – Collect counts for Database settings for the following database settings:

Database Collation
Is_Auto_Close_On
Is_Auto_Shrink_On
Is_Auto_Update_Stats_On
Is_Read_Only
User_Access_Desc
Compatibility_Level
Recovery_Model_Desc

Yellow Advisory conditions are set to show if any of the following occur:

Is_Auto_Close_On = Enabled
Is_Auto_Shrink_On = Enabled
Is_Auto_Update_Stats_On = Disabled

DatabaseStates – Checks the States of all databases listed in sys.databases plus snapshot information , there are a few criterias for conditions based on the states as follows:

Yellow Advisories (configurable):
Restoring, Offline, Database Snapshots (more than 10 days old)

Red Warnings:
Recovery_Pending, Suspect, Emergency

2018-01-22 22_02_00


DriveSpace – Shows Drive information for drives which contain databases only, Total capacity, Free space , Average daily data usage and the last five days usage.*

*Daily usage calculation has two modes, Average based calculation or Median based Calculation.

DriveInfo


FailedAgentJobs – Check for failed agent jobs in the last 24 hours that have not succeeded since, any failures will be highlighted in Red and a Warning shown at the top of the report.

2018-01-22 22_50_27


JobOwner – Checks SQL Agent jobs to ensure that all Job ownership matches the login/s in the settings table , any jobs which have a different owner are highlighted in yellow (configurable) and an Advisory condition shown at the top of the report

2018-01-22 22_50_38


LoginAttempts – Shows information on failed logins in the past 24 hours, shows the login name, failed count , last failed date time and the last error produced (No conditions apply to this module this is for informational purposes only)

FailedLogins


LongRunningTransactions – The threshold for long running transactions in seconds can be found in the [Inspector].[Settings] table.

2018-10-01 23_00_32

This setting is the threshold in Seconds, if a transaction exceeds this duration at the time of collection the Report will show a Yellow advisory condition and show some details of the transaction.

2018-10-02 03_18_20


ServerSettings – A Selection of server settings are shown on the report as information only however warnings are shown if Default cost threshold or MAXDOP are set to default values.

2018-10-03 20_53_23
2018-10-03 20_55_16

SuspectPages – If suspect pages have been recorded in msdb , a warning is shown at the top of the report and details contained in a table like below

2018-10-03 20_56_37
2018-10-01 23_26_01


TopFiveDatabaseSizes – Shows Database name and Size for the Top 5 Databases on the server ordered by Largest first (No conditions apply to this module this is for informational purposes only).

Top5DBs

UnusedLogshipConfig – Check if you have log shipping config present for a database that is either not in a restoring state or no longer exists.

Sometimes Log shipping configuration sits around and doesn’t get cleaned up and the database is either online or no longer exists, its nice to have this tidy so that it is not misleading when querying the dmvs for log shipped secondaries.

Views/Procedures for analysing data.

Views:

[Inspector].[DriveSpaceInfo] – Average growth details for Daily, Monthly and Yearly and also the Minimum growth there has been for a single day over the period and the Maximum growth for a single day within the period.

2018-10-08 15_23_42

[Inspector].[DatabaseGrowthInfo] – Aggregated database file growth information for recorded growths within the Inspector, shows actual size growth and some Average based predictions for Daily, Monthly and Yearly growth sizes.

2018-10-08 16_05_00

Procs:

[Inspector].[DriveCapacityHistory] – Get information from the data colected by the DriveSpace module that will show you when your Drive/s had their capacity increased.

2019-05-16 21_04_43-SQLQuery11.sql - SQL01.SQLUndercover (SQLUNDERCOVER_Administrator (65))_ - Micro

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: