Reports not visible on 2.49.1

[Nele90]

Hey David,

Really appreciate your work on DBA Dash, I'm using it as daily monitoring tool for my environment, awesome work. :-)

However I realized that on the latest 2.49.1 there is no Report option. Is the issue on my side or this is something that you are aware of?

One more thing, is it possible to add new option 'sql error log' per instance? :-) It would be nice to have that in DBA Dash.

[DavidWiseman]

Hi, the reports node will only be visible in the tree when you create a custom report stored procedure. It will also depend on what parameters the stored procedure has as to where in the tree it's visible. This is explained in some more detail here:

https://dbadash.com/docs/how-to/create-custom-reports/

If you have already created the stored procedure either refresh the tree (Clear All option in the Filter menu will do it) or restart the app.

The next version will include custom collections which could be used to capture the error log on your SQL instances. Combined with the custom reporting feature this data can be available within the app.

Error logs can contain some useful information but they can also contain a lot of noise and be quite large on some instances so I'm not sure if I would add it as a built-in collection. Is there something in particular you are looking for in the error log?

[DavidWiseman]

It's pretty quick to setup custom collections. I was able to capture the error log in a few mins in my lab environment and have a report setup. It would need some additional customization but DBA Dash will generate a lot of the code for you as a starting point. This is coming in the next release.

[Nele90]

Hi David,

Thanks so much for your explanation. Now i get it. :) Well I thought to get only errors from the log not the whole one. For example I'm using something like this in my env, filtering some errors and some info logs. It's running in a job and I'm getting an email if some error occurs. It would be nice to have something like this on DBA Dash like refreshlog or getlogs button, to get just errors. Also it would be cool to have a new tab, and there you can manually filter logs on error and time. :)

Here is the code:

`DECLARE @Time_Start DATETIME; DECLARE @Time_End DATETIME; SET @Time_Start = DATEADD(HOUR, -1, GETDATE()); SET @Time_End = getdate();

-- Create the temporary table CREATE TABLE #ErrorLog ( logdate DATETIME ,processinfo VARCHAR(MAX) ,Message VARCHAR(MAX) ) -- Populate the temporary tablesys INSERT #ErrorLog ( logdate ,processinfo ,Message ) EXEC master.dbo.xp_readerrorlog 0 ,1 ,NULL ,NULL ,@Time_Start ,@Time_End ,N'desc';

SELECT logdate ,processinfo ,Message FROM #ErrorLog WHERE Message LIKE '%error%' OR Message LIKE '%failed%' AND processinfo NOT LIKE 'logon' AND (Message NOT LIKE 'DBCC CHECKDB%found 0 errors%') AND (Message NOT LIKE 'CHECKDB for database%finished without errors%') AND (Message NOT LIKE 'The error log has been reinitialized.%') AND (Message NOT LIKE 'Logging SQL Server messages in file%') AND (Message NOT LIKE 'The client was unable to reuse a session with SPID%') AND (Message NOT LIKE 'Error: 18056, Severity: 20, State%') AND (Message NOT LIKE 'Error: 18054, Severity: 16, State%') AND (Message NOT LIKE 'Error 777980050, severity 16, state 1 was raised, but no message with that error number was found%') AND (Message NOT LIKE 'DbMgrPartnerCommitPolicy%') AND (Message NOT LIKE 'Always On Availability Groups connection with%') AND (Message NOT LIKE 'DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint%') AND (Message NOT LIKE 'Error 777980008, severity 16%') AND (Message NOT LIKE 'Error: 9642, Severity: 16, State: 3%') AND (Message NOT LIKE 'An error occurred in a Service Broker/Database Mirroring transport connection endpoint%') `

Here is some basic output:

[DavidWiseman]

You could definitely run something like this as a scheduled collection with the new custom collection feature (in 2.50 when it's available) and have a custom report for it.

Note: At the moment there isn't a way for the GUI to talk to the DBA Dash service to get it to collect data on demand. The GUI itself only communicates with the repository DB. In some cases the monitored instances might be collected indirectly via a S3 bucket.

[Nele90]

Great, thanks for the explanation. I will defenetly use report feature in the future. :)

[DavidWiseman]

To get this to work as a custom collection you need to convert your script into a stored procedure. I've made a slight alteration to change the LogDate to UTC - this will make things easier as DBA Dash assumes dates are in UTC format. Otherwise I've kept your query as it was.

CREATE OR ALTER PROC ErrorLogCapture
AS
DECLARE @Time_Start DATETIME;
DECLARE @Time_End DATETIME;
SET @Time_Start = DATEADD(HOUR, -1, GETDATE());
SET @Time_End = getdate();

-- Create the temporary table
CREATE TABLE #ErrorLog (
logdate DATETIME
,processinfo VARCHAR(MAX)
,Message VARCHAR(MAX)
)
-- Populate the temporary tablesys
INSERT #ErrorLog (
logdate
,processinfo
,Message
)
EXEC master.dbo.xp_readerrorlog 0
,1
,NULL
,NULL
,@Time_Start
,@Time_End
,N'desc';

SELECT logdate AT TIME ZONE 'UTC' AS LogDate ,processinfo ,Message
FROM #ErrorLog
WHERE
Message LIKE '%error%'
OR Message LIKE '%failed%'
AND processinfo NOT LIKE 'logon'
AND (Message NOT LIKE 'DBCC CHECKDB%found 0 errors%')
AND (Message NOT LIKE 'CHECKDB for database%finished without errors%')
AND (Message NOT LIKE 'The error log has been reinitialized.%')
AND (Message NOT LIKE 'Logging SQL Server messages in file%')
AND (Message NOT LIKE 'The client was unable to reuse a session with SPID%')
AND (Message NOT LIKE 'Error: 18056, Severity: 20, State%')
AND (Message NOT LIKE 'Error: 18054, Severity: 16, State%')
AND (Message NOT LIKE 'Error 777980050, severity 16, state 1 was raised, but no message with that error number was found%')
AND (Message NOT LIKE 'DbMgrPartnerCommitPolicy%')
AND (Message NOT LIKE 'Always On Availability Groups connection with%')
AND (Message NOT LIKE 'DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint%')
AND (Message NOT LIKE 'Error 777980008, severity 16%')
AND (Message NOT LIKE 'Error: 9642, Severity: 16, State: 3%')
AND (Message NOT LIKE 'An error occurred in a Service Broker/Database Mirroring transport connection endpoint%')

DROP TABLE #ErrorLog

You then need to follow the steps here to add the custom collection.

The script it generates will need to be modified. We want to use the LogDate rather than the SnapshotDate and we want to handle the import differently. We want to insert new log data since the last import rather than inserting all the data collected as a new snapshot. The custom reports can also be changed to work better for this collection. I had a go at doing the customizations so you can use this instead of the generated script:

/*
----------------------------------------------------------
|   ____   ____     _      ____               _          |
|  |  _ \ | __ )   / \    |  _ \   __ _  ___ | |__       |
|  | | | ||  _ \  / _ \   | | | | / _` |/ __|| '_ \      |
|  | |_| || |_) |/ ___ \  | |_| || (_| |\__ \| | | |     |
|  |____/ |____//_/   \_\ |____/  \__,_||___/|_| |_|     |
|                                                        |
|  DBA Dash - Custom Collection Setup Script             |
|  Generated: 2023-11-22 20:59                           |
|  Version 2.50.0                                        |
----------------------------------------------------------

    Script to setup ErrorLogCapture custom collection

    This script is designed to be used as a template to allow fast setup of custom data collections.
    It is recommended that you review the script and make any changes required for your environment.
    Run this script in your DBA Dash repository database
    The collected data will be inserted into a table called UserData.ErrorLogCapture with additional columns for InstanceID and SnapshotDate
    The InstanceID column can be joined to dbo.Instances

    !! WARNING: Consider the cost of running your custom data collection !!
*/

/***************************************************************************************************************************
*                                               Set Retention                                                              *
***************************************************************************************************************************/
/*
    How long should we keep the collected data?
    Daily partitions will be created to make it efficient to clear out old data.
    If retention is over 365 days, monthly partitions will be used instead
*/
DECLARE @RetentionDays INT = 14

EXEC dbo.DataRetention_Upd @SchemaName ='UserData', @TableName = 'ErrorLogCapture',@RetentionDays=@RetentionDays, @Validate=0

/***************************************************************************************************************************
*                                               Setup partitioning                                                         *
***************************************************************************************************************************/
/* Create partition function and scheme to make it efficient to clear out old data */
CREATE PARTITION FUNCTION [PF_UserData_ErrorLogCapture](DATETIME2) AS RANGE RIGHT FOR VALUES()
CREATE PARTITION SCHEME [PS_UserData_ErrorLogCapture] AS PARTITION [PF_UserData_ErrorLogCapture] ALL TO([PRIMARY])
GO

/***************************************************************************************************************************
*                                               Create table to store data                                                 *
***************************************************************************************************************************/
/* Create user defined type so we can pass the collected data to the stored procedure */
CREATE TYPE UserData.[ErrorLogCapture] AS TABLE (
    [LogDate] DATETIME2 NULL,
    [processinfo] VARCHAR(MAX) NULL,
    [Message] VARCHAR(MAX) NULL
);
GO
/* Create table to store the collected data */
GO
CREATE TABLE UserData.[ErrorLogCapture] (
    [InstanceID] INT NOT NULL,
    [LogDate] DATETIME2 NULL,
    [processinfo] VARCHAR(MAX) NULL,
    [Message] VARCHAR(MAX) NULL
    INDEX IX_UserData_ErrorLogCapture CLUSTERED(InstanceID,LogDate)
) ON [PS_UserData_ErrorLogCapture](LogDate)
WITH(DATA_COMPRESSION=PAGE);
GO
/***************************************************************************************************************************
*                                               Create procedure for import                                                *
***************************************************************************************************************************/
GO
CREATE PROCEDURE UserData.[ErrorLogCapture_Upd]
(
    /* InstanceID value from dbo.Instances table (Unique ID associated with the instance) */
    @InstanceID INT,
    /*
    @SnapshotDate represents the time of the data collection in UTC. 
    It's the time all the collections within the same batch (scheduled time) started.
    You can have your query return 'SYSUTCDATETIME() AS SnapshotDate' if you need higher accuracy
    */
    @SnapshotDate DATETIME2,
    /* Table-valued parameter with the contents of our custom data collection */
    @ErrorLogCapture [ErrorLogCapture] READONLY
)
AS
SET XACT_ABORT ON
SET NOCOUNT ON

DECLARE @MaxDate DATETIME2
SELECT @MaxDate = ISNULL(MAX(LogDate),'19000101') 
FROM UserData.[ErrorLogCapture]
WHERE InstanceID = @InstanceID

INSERT INTO UserData.[ErrorLogCapture]
(
    [InstanceID],
    [LogDate],
    [processinfo],
    [Message]
)
SELECT
    @InstanceID AS InstanceID,
    [LogDate],
    [processinfo],
    [Message]
FROM @ErrorLogCapture
WHERE LogDate > @MaxDate

/* Log the data collection */
EXEC dbo.CollectionDates_Upd @InstanceID = @InstanceID,
        @Reference = 'UserData.ErrorLogCapture',
        @SnapshotDate = @SnapshotDate
GO

/***************************************************************************************************************************
*                                               Custom Reports                                                             *
***************************************************************************************************************************/
/* https://dbadash.com/docs/how-to/create-custom-reports/ */
GO
/*
    Custom report example. Returns data associated with the last collection or from a specific snapshot if specified.
    Report is available at root and instance level
*/
CREATE PROC [UserReport].[ErrorLog]
(
    /* Table-valued parameter passing a list of InstanceIDs (dbo.Instance table) associated with the current context in DBA Dash */
    @InstanceIDs IDs READONLY,
    @FromDate DATETIME2,
    @ToDate DATETIME2
)
AS
SELECT  I.InstanceDisplayName AS Instance,
        UD.[LogDate],
        UD.[processinfo],
        UD.[Message]
FROM UserData.ErrorLogCapture UD
JOIN dbo.Instances I ON I.InstanceID = UD.InstanceID
WHERE EXISTS(SELECT 1
            FROM @InstanceIDs T
            WHERE T.ID = UD.InstanceID
            )
AND LogDate >=@FromDate 
AND LogDate < @ToDate
ORDER BY LogDate DESC
GO

/***************************************************************************************************************************
*                                               Create initial partitions                                                 *
***************************************************************************************************************************/

EXEC dbo.Partitions_Add

/***************************************************************************************************************************
*                                   Cleanup script to remove objects created                                               *
****************************************************************************************************************************
IF EXISTS (SELECT * FROM sys.procedures WHERE Name = N'ErrorLogCapture_Upd' AND schema_id = SCHEMA_ID('UserData'))
BEGIN
    DROP PROC UserData.[ErrorLogCapture_Upd]
END
IF EXISTS (SELECT * FROM sys.types WHERE is_user_defined = 1 AND name = N'ErrorLogCapture' AND schema_id = SCHEMA_ID('UserData'))
BEGIN
    DROP TYPE UserData.[ErrorLogCapture]
END
IF EXISTS (SELECT * FROM sys.tables WHERE Name = N'ErrorLogCapture' AND schema_id = SCHEMA_ID('UserData'))
BEGIN
    DROP TABLE UserData.[ErrorLogCapture]
END
IF EXISTS (SELECT * FROM sys.procedures WHERE Name = N'ErrorLog' AND schema_id = SCHEMA_ID('UserReport'))
BEGIN
    DROP PROC [UserReport].[ErrorLog]
END
DELETE dbo.CustomReport WHERE SchemaName = 'UserReport' AND ProcedureName IN('ErrorLogCapture Snapshots','ErrorLogCapture Example')
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'PS_UserData_ErrorLogCapture')
BEGIN
    DROP PARTITION SCHEME [PS_UserData_ErrorLogCapture]
END
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'PF_UserData_ErrorLogCapture')
BEGIN
    DROP PARTITION FUNCTION [PF_UserData_ErrorLogCapture]
END
DELETE dbo.DataRetention WHERE SchemaName = 'UserData' AND TableName = 'ErrorLog'
****************************************************************************************************************************/

This seems to work well but I haven't tested it extensively. Consider the cost of running this query and how frequently you want to collect it. It returns entries over the last hour so you would need the collection to run more frequently than that or adjust the the duration.

Hope this helps.

[Nele90]

Wow, this looks really nice. :) So i could use this also as an example for any other report which i would like to create. :) Thanks so much for your explanation.

[DavidWiseman]

Custom reports are just a case of creating a stored procedure in the UserReport schema with some parameters that the application will use to pass in context. See here for more info. Hope this helps.

[Nele90]

Yes it make sense. Maybe my example with error log was not the greates one, but defenetly sp_Blitz is must to have. :)

[aachaemenes]

Thanks again. Based on your instruction I ran this first CREATE OR ALTER PROC ErrorLogCapture AS DECLARE @Time_Start DATETIME; DECLARE @Time_End DATETIME; SET @Time_Start = DATEADD(HOUR, -1, GETDATE()); SET @Time_End = getdate(); .......

Then I went to Service Config but I cant see ErrorLogCapture in the list of StoreProc to create Custom Collection at the root level.

I have the latest version.