sqlundercover / undercovertoolbox Goto Github PK

Add Physical server name to the HTML report as this can differ from @@ServerName when running on a clustered instance , showing both is useful for knowing which physical node is currently the active node in a clustered setup.

When the report runs it would only have inserted a row into Inspector.ReportData when @Testmode = 1 OR the Email recipient address was NULL or Blank but it makes sense to always insert the data here as it can be used to view old reports if the email becomes lost.

A cleanup should be added which is configurable within the settings table specified in days which will be used in a new query as below:

DELETE FROM [Inspector].[ReportData]
WHERE ReportDate < DATEADD(DAY,-@ReportDataRetention,GETDATE());

Should probably add a nonclustered index on ReportDate too.

Example:
Check Inspector.Currentservers table where isactive = 1 and for each server run the collection stored procs (ideally at the same time for each server, not one server after the other), then once collections are complete run the report on the central logging server.

RAISERROR commands should precede the execution of each collector proc i.e Inspector.AGCheckInsert within Inspector.InspectorDataCollection not follow.

Add Instance start time to the inspector report

check for new version of the catalogue
target 0.3

Alter scripts so only needed tables and procs are installed on the remote servers and centralised server.

Inspector.Settings table allows NULLs in the Value column, by setting the Value for DiffBackupThreshold to NULL this could allow the Inspector to ignore thresholds for Differential backups and report as 'N/A' to allow users to exclude if they do not take differential backups.

The Table highlight key at the bottom of the backups check table will also need to show 'DIFF backups excluded from check' rather than 'Last DIFF backup older than X day/s' when set to NULL.

Collect info from minion tables

Hello, guys.

Do you want to add some description in README file and github badges and templates like in my repo ? I can help with it if you accepted MIT or another type of open source licence https://github.com/ktaranov/sqlserver-kit

When @DriveLetterExcludes is NULL in the setup script the settings table is not populated.

Fix issue with config tables not all installing

Add existence checks to installation scripts.

Create a collection stored procedure to replace Agent code, this should make adding new modules easier moving forward and will make the agent job code cleaner.

Remove the object list from the beginning of the setup script and place into a new md file in a documentation folder within the Inspector folder.

Summary column should include:
Red warning count
Yellow Warning count.

Automatically install the catalogue on remote servers where it doesn't already exist

Automatically update remote servers where version is different to the config server

Show aggregated space used by drive by server, show Average daily,monthly and yearly usage and MIN/MAX Daily Increment variances.

Add a new table AGDatabases and have a flag that is set to state if a database should be deemed as an AG database , if so then when the collection runs update the table Is_Joined flag and if Is_AG = 1 and Is_Joined = 0 show these databases in the report.

When a value for @DriveLetterExcludes was specified i.e 'C' a convert to INT error isencountered.

Convert setup script to a stored procedure.

When the comparison for the backups check takes place when executing the SQLundercoverInspectorReport procedure , comparisons against last backup date for FULL, DIFF and LOG were done against Getdate() , this was ok if the report was ran within a short period following the collection however if this period was extended then it is possible to exceed thresholds and therefore receive invalid warnings.

Solution: Determine the MAX(Log_Date) per database per AG or Server if non ag, then compare the MAX(Log_Date) against the last backup DATETIME and then evaluate against the threshold values.

Is your feature request related to a problem? Please describe.
A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

Describe the solution you'd like
A clear and concise description of what you want to happen.

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

Add supporting Procs/Views for Powershell collection

Add new table [Inspector].[InspectorUpgradeHistory] to record each run of the new Setup stored procedure which will include:
Log_Date - Date time the proc was ran (in setup mode not help mode)
PreserveData flag value
Current build
Target build
The call to the stored procedure including parameter values passed e.g.

Include AG backup preference in the backup check and show only preferred servers for backups.

The output of the backups check should show the AG backup preference setting (if using AGs) and then list only the replicas applicable for backups for that AG rather than all replicas.

Non AG databases will have 'N/A' as the backup preference and the preferred server/s will be the Server name of the server only.

Make the SQLUndercoverInspector compatible with Case Sensitive collations

Show disabled modules as N/A for PeriodicBackupsCheck Moduleconfig rather than showing a list of disabled modules as this config is dedicated to backups checking and showing all disabled modules clutters the report.

Do an AD look up to get all user accounts that are part of AD groups

Add instance version and edition to header for each server in the report

Check msdb.dbo.suspect_pages and report any rows found.

Currently the database states treat 'Restoring' as a single state and highlights these in yellow showing you an advisory condition, it would be good if Log shipped databases can be separated from the restoring state and counted separately so they they can be shown for informational purposes but not flagged up as an advisory condition.

Group by Servername, Databasename and Filename.

Add aggregated data for:
First recorded growth date
Days since first recorded growth date
Total growths
Last recorded growth rate
Total growth in MB
Average daily growth in MB
Average Monthly growth in MB
Average Yearly growth in MB

Add server settings collection to include:
Cost Threshold
MAXDOP
Backup compression default
Max Memory setting
Agent XPs Enabled
xp_cmdshell enabled

Advisory conditions for Default cost threshold (5) or MAXDOP 0/1

target version 0.3

When the collection runs for Database growths it checks if growth rates have been changed since the last time it was ran, if this is the case then the new growth rate is updated in Inspector.DatabaseFilesSizes however the NextGrowth column is not - this needs to be reclaculated and updated accordingly.

A new line of code needs to be added to the stored procedure Inspector.DatabaseGrowthsInsert as follows:

Snippet from Old code:
--Keep the base table in sync by checking if the growth rates have changed - if they have then update the base table
UPDATE [Sizes]
SET
[GrowthRate] = [GrowthCheck].[GrowthRate_MB],
[Is_percent_growth] = [GrowthCheck].[is_percent_growth],
[LastUpdated] = @lastupdated

Revised code:
--Keep the base table in sync by checking if the growth rates have changed - if they have then update the base table
UPDATE [Sizes]
SET
[GrowthRate] = [GrowthCheck].[GrowthRate_MB],
[Is_percent_growth] = [GrowthCheck].[is_percent_growth],
[NextGrowth] = ([Sizes].[PostGrowthSize_MB] + [GrowthCheck].[GrowthRate_MB]),
[LastUpdated] = @lastupdated

When a database is set to offline it normally shows in the error log, but as the collection for the inspector runs daily it could be possible to add the collection date of the first occurrence of a database showing a state of OFFLINE, this date could then persist for the duration of the database being offline so that you have a record of when the database was recorded as being offline (by the collection).

If the state changes from offline then the date can be removed and report as normal.

e.g.

OFFLINE SQLDB (Since 09 mar 2018)

It is possible to run the setup script against a database where the Inspector is not installed with @initialsetup = 0 (Preserve logged data) the problem is it will not insert any config into the Inspector.Settings table as it will assume you already have config being preserved.

Some logic needs to be added to the setup script to check the following and act accordingly:

• Check the Inspector schema exists , if it doesn't then set @initialsetup = 1 if @initialsetup = 0

• If Inspector schema exists check that the Settings table exists, if it doesn't then set set @initialsetup = 1 if @initialsetup = 0

• If Schema exists and the Settings table exists check that the Settings table has data, if it doesn't then set @initialsetup = 1 if @initialsetup = 0

A bit belt and bracers but want to ensure that it covers all bases making it easier for the user to setup.