Getting started with the SQLUndercover Inspector

Post Updated: 20/05/2019

Here is a quick guide to getting started with the SQLUndercover Inspector.

We made some changes to the installation process, please read them here in the revised installation guide

So you have ran through the installation of the Inspector and now you want to know what to do next, below we will guide you through some of the key steps to get you started and following on from that we will explain how you can configure the Inspector to get things working how you want them.

 

First thing to do:

Once you install the Inspector you will find that you have a new Schema called Inspector in the logging database you chose when you installed, within this schema you will find a bunch of tables, views and stored procedures that have been created upon installation along with 4 new SQL Agent jobs.

2018-12-05 19_59_14
SQL Agent jobs created following installation

 

The collection jobs are responsible for collecting the data and populating the Inspector tables and the Report jobs are responsible for generating the HTML reports and emailing out.

 

The first thing you will want to do is to set a schedule for these jobs (Unless you set @CreateJobSchedules = 1 during the setup),  I would suggest the following as a starting point but you should tailor these as you see fit:

SQLUndercover Inspector Data Collection – Set to 08:50am daily

SQLUndercover Inspector Report – Set to 09:00am daily

SQLUndercover Periodic Backups Collection – Set to run 2 hourly from 10:00 to 18:00

SQLUndercover Periodic Backups Report – Set to run 2 hourly from 10:10 to 18:10

 

The above are just guidelines and the other thing you really need to be concerned about is ensuring that the Collection jobs have sufficient time to run before their respective Report jobs start, 10 mins should be more than enough time and after you check the run times of the collections you may find that you can bring these closer together, for example we run them 5 mins apart and tend to have minutes to spare 🙂

To test simply run the collection job and once complete run the report job, if there was no email address specified in [Inspector].[EmailRecipients] you will not receive and email but you will be able to retrieve the HTML report from the [Inspector].[ReportData] table.

 

2018-12-18 11_36_37

 

And that’s it you are up and running!

 

Using a linked server?

Ensure that when you installed the Inspector that you specified the linked server name in the @LinkedServername parameter at point of setup , this will setup the Inspector to insert the collected data into your central database tables rather than your local database tables.

 

Changing thresholds:

If you find that the thresholds you set (or defaults used) at point of installation are not ideal you are able to change the following settings in the table [Inspector].[Settings]:


SELECT [ID]
,[Description]
,[Value]
FROM [Inspector].[Settings]

 

2018-12-18 11_16_32

Just update accordingly

One option you will not find in here unfortunately is you data drive letter/s and your log file letter/s these need to be passed into the setup script so if you got these wrong you will need to re run the setup with the correct drive letters.

 

Further Customisation: 

The following tables will allow you to make custom changes for the report/s.

[Inspector].[Modules] – Enable and disable modules, create new moduleConfig too.

[Inspector].[EmailConfig] – Allows you to change the Subject of the email for each Moduleconfig.

[Inspector].[EmailRecipients] – Change email recipients , comma delimited.

[Inspector].[ADHocDatabaseSupression] – Suppress databases showing in the Adhoc DB check by setting Suppress = 1

[Inspector].[CurrentServers] – Specify custom hex colours for table column headers e.g #4286f4, and also specify specific module configs per server , for example you may have a server which needs to run a different set of modules to the rest , in this case create a new mobuleconfig in the Modules table then set the name of that module config against the server in this table.

 

2018-12-18 11_29_39
Modules table: Enable and disable modules or create new ModuleConfig
2018-12-18 11_30_50
CurrentServers table: Enable or disable servers and set custom colours or specific Moduleconfig.

 

 

 

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: