Inspector V2.00 now available

,

The SQL Installation file can be found here.
The new Powershell installer can be found here.
If you are running the powershell collection you will need to grab the latest revision.
The latest powerBI template can be found here.
As more features are documented they will be updated on the Inspector V2 page.

New Features

  • Schedule report windows and frequencies
  • Schedules Module collection frequencies
  • Create your own Modules including collection procedure and report procedure.
  • Adjust the report ordering of modules in the HTML report
  • Customize the warning/advisory header text
  • Have more than one warning level per module (Custom modules only)
  • Revised PowerBi Template to show Schedule information

Improvements

  • Servers used to be locked down to just one ModuleConfig , now they can be locked into one or they can subscribe to all ModuleConfig as you may want to have multiple collections/reports on different schedules for the same server.
  • #168 Thanks to Peter Jenner who let us know about a limitation in the Backup space module. The Backup space module now accepts a comma delimited list of backup paths rather than just a single backup path as you may be backing up to more than one place across your servers.
  • A new view is available called ExecutionLogDetails which will show you all executions and their frequency information
  • ReportData.ReportSummary column now has Warning/Advisory counts per server per module in detailed mode, this can be switched back to normal mode if required.
  • #145 Thanks again to Peter Jenner for a great suggestion to change the behavior of ‘Back to top’ hyperlinks. Back to top hyperlinks now return you back to where you clicked in the header rather than to the top of the report.


Screenshot tour of new features

Custom Report Order

This image has an empty alt attribute; its file name is 2019-11-08-10_18_19-presentation1-powerpoint.png
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 – Non server specific module 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:

 
DECLARE @ModuleConfig VARCHAR(20) = 'Default' 
SELECT *
FROM [Inspector].[Modules]
WHERE [IsActive] = 1
AND ModuleConfig_Desc = @ModuleConfig
ORDER BY
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 created modules so that it allows you to code accordingly. Report order however is fully supported.

Custom Module and Report schedules

This image has an empty alt attribute; its file name is 2019-11-11-10_36_20-e2978f-notebook-0-azure-data-studio.png
ModuleConfig and Modules tables , The Default Moduleconfig ran a bit late this morning as I didn’t turn my laptop on til after 9am ??

The Inspector now runs from a single agent job, this job runs every minute looking for modules (Inspector.Modules CollectionProcedurename) where IsActive = 1 and the frequency, StartTime and EndTime all meet the requirements.

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, we are giving you the control on when you want modules to execute. If you really want to you can go with a frequency as low as 1 and your module will run every minute , just be sure that the modules you choose will complete within the minute or you may start running behind on your schedules.

You can keep track of module executions in the ExecutionLog table or if you are particularly interested in the frequency of schedules with detailed information you can use the view ExecutionLogDetails.

This image has an empty alt attribute; its file name is 2019-11-11-10_34_09-e2978f-notebook-0-azure-data-studio.png
Execution log information for the AGHealth Moduleconfig created as a custom schedule.

Customise report 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 .

Modules header text and Default header text
This image has an empty alt attribute; its file name is 2019-11-11-10_54_24-powerpoint-slide-show-blog-page.png
If headertext in the Modules table is null then the Default header text will be used.

Create custom modules

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.

To Report at report run time only:

  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 + Report 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.

Collect data and report on that data:

  1. Create a new table in the Inspector Schema , this table MUST have the following columns:
    Servername NVARCHAR(128)
    Log_Date DATETIM
    E
    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 + Report 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)
This image has an empty alt attribute; its file name is 2019-11-15-00_49_34-sqlquery_1-inspector.modulereportproctemplate-destiny_sql02.sqlundercoverv1_4-.png
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.

Improvements to existing functionality.

Improved “back to top” hyperlinks

This image has an empty alt attribute; its file name is 2019-11-11-10_00_24-blog-page-powerpoint.png

Previously back to top hyperlinks would return you to the top of the HTML report regardless of which header hyperlink took you to the table. We made improvements here to return you back to where you clicked from so you can pick up where you left off rather than having to scroll back to where you were!

Server Module config subscription

Because of the way the new Inspector works it made sense to change the NULL = Default original design. 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.

This image has an empty alt attribute; its file name is 2019-11-14-23_44_54-e2978f-sqlquery_2-destiny_sql02.sqlundercoverv1_4-integrated-azure-data-studio.png
Subscribe server to all Moduleconfig
This image has an empty alt attribute; its file name is 2019-11-14-23_49_27-e2978f-sqlquery_2-destiny_sql02.sqlundercoverv1_4-integrated-azure-data-studio.png
Subscribe server to a single Moduleconfig only

Report data summary column

There is a new setting in the Settings table called ‘ReportDataDetailedSummary’ this setting is on or off (0 or 1) and will control the level of detail logged in the summary column. When set to a 1 you will get granular detail of Warning/Advisory counts per server per module, setting this setting to 0 will return it back to the original way of logging which was to summarize the entire report into Warning count and advisory count.

This image has an empty alt attribute; its file name is 2019-11-15-00_21_30-sqlquery3.sql-destiny_sql02.sqlundercoverv1_4-destiny_adest-84_-microsof.png
This image has an empty alt attribute; its file name is 2019-11-15-00_22_40-summary5.xml-microsoft-sql-server-management-studio.png

PowerBi Changes

Last collection date time now shown on the backups tab along with the backup threshold settings from the Inpector.Settings table

A new tab is now available that shows the schedule information and the Execution log information to keep track of your executed modules.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

Share

Share

Rate