229 lines
8.9 KiB
Transact-SQL
229 lines
8.9 KiB
Transact-SQL
USE msdb
|
|
GO
|
|
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.usp_CheckAudit
|
|
@recipient_address NVARCHAR(1000) = null
|
|
AS
|
|
SET NOCOUNT ON;
|
|
DECLARE @result nvarchar(max) = ''
|
|
,@sql nvarchar(max)
|
|
,@remote_sql nvarchar(max)
|
|
,@db_cursor cursor
|
|
,@db_name nvarchar(100)
|
|
,@table_exists bit;
|
|
|
|
CREATE TABLE #AsyncActivity (
|
|
ServerName nvarchar(100)
|
|
,DatabaseName nvarchar(100)
|
|
,Broker bit null
|
|
,LastPageVisit datetime null
|
|
,LastEntityHistory datetime null
|
|
,FirstQueueItem datetime null
|
|
,QueueLength int null
|
|
,QueueEnabled bit null
|
|
,ProcessActivated bit null
|
|
,LastHourErrors int null
|
|
,LastHourFailedItems int null
|
|
,OpenConversations int null
|
|
,DisabledTriggers int null
|
|
,ErrorMessage nvarchar(4000) null,
|
|
Errors as (
|
|
case when (ErrorMessage > '') then 1 else 0 end +
|
|
case when (Broker = 0) then 2 else 0 end +
|
|
case when (QueueLength > 1 AND ISNULL(LastEntityHistory, GETDATE()) < ISNULL(DATEADD(hour, -3, LastPageVisit), GETDATE())) then 4 else 0 end +
|
|
case when (QueueEnabled = 0) then 8 else 0 end +
|
|
case when (QueueLength > 100000) then 16 else 0 end +
|
|
case when (ProcessActivated = 0) then 32 else 0 end +
|
|
case when (LastHourErrors > 0) then 64 else 0 end +
|
|
case when (LastHourFailedItems > 0) then 128 else 0 end +
|
|
case when (DisabledTriggers > 0) then 256 else 0 end +
|
|
case when (OpenConversations > 1000) then 512 else 0 end
|
|
)
|
|
);
|
|
|
|
SET @sql = '
|
|
SELECT
|
|
--@@SERVERNAME AS ServerName,
|
|
DB_NAME() AS DatabaseName,
|
|
(SELECT TOP 1 is_broker_enabled FROM sys.databases WHERE database_id = db_id()) AS Broker,
|
|
(SELECT TOP 1 CREATED FROM dbo.T_OLDALLATOGATOTTSAG (NOLOCK) ORDER BY ID DESC) AS LastPageVisit,
|
|
(SELECT TOP 1 CREATED FROM dbo.T_ENTITYHISTORY (NOLOCK) ORDER BY ID DESC) AS LastEntityHistory,
|
|
(SELECT TOP 1 message_enqueue_time FROM auditlog.AuditQueue WITH (NOLOCK) ORDER BY message_enqueue_time) AS FirstQueueItem,
|
|
(SELECT COUNT(*) FROM auditlog.AuditQueue WITH (NOLOCK)) AS QueueLength,
|
|
(SELECT TOP 1 is_enqueue_enabled FROM sys.service_queues WHERE name = ''AuditQueue'') AS QueueEnabled,
|
|
(SELECT TOP 1 is_activation_enabled FROM sys.service_queues WHERE name = ''AuditQueue'') AS ProcessActivated,
|
|
(SELECT COUNT(*) FROM auditlog.ErrorLog (NOLOCK) WHERE ErrorTime > DATEADD(minute, -61, GETDATE())) AS LastHourErrors,
|
|
(SELECT COUNT(*) FROM auditlog.AuditServiceFailedItems (NOLOCK) WHERE EventDate > DATEADD(minute, -61, GETDATE())) AS LastHourFailedItems,
|
|
(SELECT COUNT(*) FROM sys.triggers WHERE parent_class = 1 AND name LIKE ''tr_AsyncAudit%'' and is_disabled = 1) AS DisabledTriggers,
|
|
(SELECT COUNT(*) FROM sys.conversation_endpoints (NOLOCK) WHERE far_service = ''AuditService'') AS OpenConversations
|
|
';
|
|
|
|
SET @db_cursor = CURSOR STATIC FOR
|
|
SELECT DISTINCT name
|
|
FROM sys.databases d
|
|
LEFT JOIN sys.dm_hadr_database_replica_states rs on rs.database_id = d.database_id
|
|
WHERE d.state = 0
|
|
AND d.is_read_only = 0
|
|
AND d.database_id > 4
|
|
AND d.name NOT LIKE '%GINOP%'
|
|
AND d.source_database_id IS NULL
|
|
AND (
|
|
NOT EXISTS(SELECT * FROM sys.dm_hadr_database_replica_states r WHERE r.database_id = d.database_id)
|
|
OR
|
|
(rs.is_local = 1 AND rs.is_primary_replica = 1)
|
|
);
|
|
|
|
SET LOCK_TIMEOUT 5000;
|
|
OPEN @db_cursor;
|
|
FETCH NEXT FROM @db_cursor INTO @db_name;
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
SET @remote_sql = 'SELECT @table_exists = SIGN(COUNT(*)) FROM [' + @db_name + '].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''T_ENTITYHISTORY'' and TABLE_SCHEMA = ''dbo''';
|
|
|
|
EXEC sp_executesql @remote_sql, N'@table_exists BIT OUTPUT', @table_exists = @table_exists OUTPUT;
|
|
|
|
IF @table_exists = 0 GOTO FETCH_NEXT;
|
|
|
|
SET @remote_sql = 'EXEC [' + @db_name + '].sys.sp_executesql @SQL';
|
|
|
|
BEGIN TRY
|
|
|
|
INSERT INTO #AsyncActivity(DatabaseName, Broker, LastPageVisit, LastEntityHistory, FirstQueueItem, QueueLength, QueueEnabled, ProcessActivated, LastHourErrors, LastHourFailedItems, DisabledTriggers, OpenConversations)
|
|
EXEC sp_executesql @remote_sql, N'@SQL NVARCHAR(max)', @SQL = @sql;
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
|
|
INSERT INTO #AsyncActivity(ServerName, DatabaseName, ErrorMessage)
|
|
VALUES(@@SERVERNAME, @db_name, ERROR_MESSAGE());
|
|
|
|
END CATCH
|
|
|
|
FETCH_NEXT:
|
|
FETCH NEXT FROM @db_cursor INTO @db_name;
|
|
|
|
END
|
|
|
|
CLOSE @db_cursor
|
|
|
|
IF @recipient_address IS NULL
|
|
BEGIN
|
|
SELECT * FROM #AsyncActivity;
|
|
RETURN;
|
|
END
|
|
|
|
SELECT @result += '<tr>' +
|
|
'<td>' + DatabaseName + '</td>' +
|
|
CASE WHEN ErrorMessage IS NOT NULL THEN
|
|
'<td colspan=11' + IIF(Errors & 1 = 1, ' class="error"', '') + '>Error:' + ErrorMessage + '</td>'
|
|
ELSE
|
|
'<td' + IIF(Errors & 2 = 2, ' class="error"', '') + '>' + ISNULL(CAST(Broker AS VARCHAR(10)), ' ') + '</td>' +
|
|
'<td>' + ISNULL(CONVERT(VARCHAR(19), LastPageVisit, 120), ' ') + '</td>' +
|
|
'<td' + IIF(Errors & 4 = 4, ' class="error"', '') + '>' + ISNULL(CONVERT(VARCHAR(19), LastEntityHistory, 120), ' ') + '</td>' +
|
|
'<td>' + ISNULL(CONVERT(VARCHAR(19), FirstQueueItem, 120), ' ') + '</td>' +
|
|
'<td' + IIF(Errors & 16 = 16, ' class="error"', '') + '>' + ISNULL(CAST(QueueLength AS VARCHAR(10)), ' ') + '</td>' +
|
|
'<td' + IIF(Errors & 8 = 8, ' class="error"', '') + '>' + ISNULL(CAST(QueueEnabled AS VARCHAR(10)), ' ') + '</td>' +
|
|
'<td' + IIF(Errors & 32 = 32, ' class="error"', '') + '>' + ISNULL(CAST(ProcessActivated AS VARCHAR(10)), ' ') + '</td>' +
|
|
'<td' + IIF(Errors & 64 = 64, ' class="error"', '') + '>' + ISNULL(CAST(LastHourErrors AS VARCHAR(10)), ' ') + '</td>' +
|
|
'<td' + IIF(Errors & 128 = 128, ' class="error"', '') + '>' + ISNULL(CAST(LastHourFailedItems AS VARCHAR(10)), ' ') + '</td>' +
|
|
'<td' + IIF(Errors & 256 = 256, ' class="error"', '') + '>' + ISNULL(CAST(DisabledTriggers AS VARCHAR(10)), ' ') + '</td>' +
|
|
'<td' + IIF(Errors & 512 = 512, ' class="error"', '') + '>' + ISNULL(CAST(OpenConversations AS VARCHAR(10)), ' ') + '</td>'
|
|
END +
|
|
'</tr>'
|
|
FROM #AsyncActivity
|
|
WHERE Errors > 0;
|
|
|
|
IF @@ROWCOUNT = 0 RETURN;
|
|
|
|
SET @result = '
|
|
<style>
|
|
p {
|
|
font-size: 10pt;
|
|
font-family: Calibri;
|
|
}
|
|
th {
|
|
border:1px solid #EEEEEE;
|
|
padding: 4px;
|
|
font-weight: bold;
|
|
font-size: 10pt;
|
|
font-family: Calibri;
|
|
background-color: #EEEEEE;
|
|
}
|
|
td {
|
|
border:1px solid #EEEEEE;
|
|
padding:4px;
|
|
text-align: right;
|
|
font-size: 10pt;
|
|
font-family: Calibri;
|
|
nowrap;
|
|
}
|
|
td.error {
|
|
border:1px solid #EEEEEE;
|
|
padding:4px;
|
|
text-align: right;
|
|
font-size: 10pt;
|
|
font-family: Calibri;
|
|
color: #FFFFFF;
|
|
background-color: #FF0000;
|
|
font-weight: bold;
|
|
nowrap;
|
|
}
|
|
|
|
</style>
|
|
<p>Figyelem! Hiba az AUDIT rendszerben a <b>' + @@SERVERNAME + '</b> szerveren:</p>
|
|
<table style="border-collapse:collapse">
|
|
<thead>
|
|
<th>DatabaseName</th>
|
|
<th>Broker On</th>
|
|
<th>Last Visit</th>
|
|
<th>Last History</th>
|
|
<th>First Queue</th>
|
|
<th>Queue Items</th>
|
|
<th>Queue On</th>
|
|
<th>Process Active</th>
|
|
<th>Latest Errors</th>
|
|
<th>Latest Fails</th>
|
|
<th>Disabled Triggers</th>
|
|
<th>Open Conversations</th>
|
|
</thead>
|
|
' + @result + '
|
|
</table>
|
|
<p>További információk és teendők: <a href= "https://ekretapedia.ekreta.hu/main/_layouts/15/WopiFrame.aspx?sourcedoc={9934831a-3b80-4c13-abc1-9274cceed8bb}&action=edit&wd=target%28Checklistek.one%7C2b67a257-f2e8-4e0e-9977-42134b49d577%2FAudit%20kezel%C3%A9se%20%28Audit%202.0%5C%29%7Cc1f02bcc-4bd9-485b-8deb-d98f310d946e%2F%29&wdorigin=703">OneNote|Checklistek\Audit kezelése (Audit 2.0)</a></p>
|
|
'
|
|
IF EXISTS(SELECT * FROM #AsyncActivity WHERE Errors & 32 = 32)
|
|
SET @result += '
|
|
<p><b>Process Active:</b> Újraindítás a következő paranccsal:</p>
|
|
<pre style="background-color: #EEEEEE">
|
|
ALTER QUEUE auditlog.AuditQueue
|
|
WITH
|
|
STATUS = ON,
|
|
ACTIVATION (
|
|
STATUS = ON,
|
|
PROCEDURE_NAME = auditlog.usp_AuditProcessing,
|
|
MAX_QUEUE_READERS = 1,
|
|
EXECUTE AS ''kreta_tech_user''
|
|
),
|
|
POISON_MESSAGE_HANDLING (STATUS = ON);
|
|
|
|
</pre>'
|
|
|
|
IF EXISTS(SELECT * FROM #AsyncActivity WHERE Errors & 512 = 512)
|
|
SET @result += '
|
|
<p><b>Open Conversations:</b>Javítás:</p>
|
|
<pre style="background-color: #EEEEEE">
|
|
SELECT * FROM sys.dm_broker_activated_tasks WHERE database_id = DB_ID();
|
|
SELECT * FROM sys.sysprocesses WHERE spid = $spid;
|
|
KILL $spid;
|
|
|
|
</pre>'
|
|
|
|
|
|
EXEC msdb.dbo.sp_send_dbmail
|
|
--@profile_name = 'SQL Server Alert Mail Profile',
|
|
@recipients = @recipient_address
|
|
,@body = @result
|
|
,@subject = N'KRÉTA - Audit TRIGGER/QUEUE/PROCESS hiba'
|
|
,@importance = 'High'
|
|
,@body_format = 'HTML'
|