SELECT*INTO[UiPathArchives].[dbo].[ArchiveAuditEvent] from [AutomationSuite_Platform].[adt].[AuditEvent] where 1=2SELECT * INTO [UiPathArchives].[dbo].[ArchiveAuditEvent] from [AutomationSuite_Platform].[adt].[AuditEvent] where 1 = 2
存档数据:
存档审核记录
DECLARE @NumberOfDaysToKeep INTDECLARE @CurrentDate DATETIME-- Specify the number of days
SET @NumberOfDaysToKeep =60-- Archive the list of audit event records that you want to deleteSET @CurrentDate =GetDate()BEGINTRANSACTIONINSERTINTO[UiPathArchives].[dbo].[ArchiveAuditEvent]SELECT[Id],[CreatedOn],[Version],[OrganizationId],[Source],[Category],[Action],[IsUserEvent],[UserId],[FullName],[Email],[DetailsVersion],[Details],[OperationId]FROM[adt].[AuditEvent]WHEREDateDiff(day, CreatedOn, @CurrentDate)> @NumberOfDaysToKeep
-- Delete the audit events
DELETEFROM[adt].[AuditEvent]WHEREEXISTS(SELECT1FROM[UiPathArchives].[dbo].[ArchiveAuditEvent]WHERE Id =[adt].[AuditEvent].[Id])COMMITTRANSACTIONDECLARE @NumberOfDaysToKeep INT
DECLARE @CurrentDate DATETIME
-- Specify the number of days
SET @NumberOfDaysToKeep = 60
-- Archive the list of audit event records that you want to delete
SET @CurrentDate = GetDate()
BEGIN TRANSACTION
INSERT INTO [UiPathArchives].[dbo].[ArchiveAuditEvent]
SELECT
[Id],[CreatedOn],[Version],[OrganizationId],[Source],[Category],[Action],[IsUserEvent],
[UserId],[FullName],[Email],[DetailsVersion],[Details],[OperationId]
FROM [adt].[AuditEvent]
WHERE DateDiff(day, CreatedOn, @CurrentDate) > @NumberOfDaysToKeep
-- Delete the audit events
DELETE FROM [adt].[AuditEvent]
WHERE EXISTS (SELECT 1 FROM [UiPathArchives].[dbo].[ArchiveAuditEvent] WHERE Id = [adt].[AuditEvent].[Id])
COMMIT TRANSACTION
若使用以下查询,则在删除旧数据前,会将旧数据先复制到这些档案中。
删除表格中的数据。
重要提示:在运行以下脚本之前,请确保使它们适应您的环境。
审核事件
declare @NumberOfDaysToKeep int
declare @CurrentDate datetime
-- Specify the number of days
SET @NumberOfDaysToKeep =60-- Create temporary table with the list of audit event records that you want to deleteSET @CurrentDate =GetDate()SELECT[Id],[CreatedOn],[Version],[OrganizationId],[Source],[Category],[Action],[IsUserEvent],[UserId],[FullName],[Email],[DetailsVersion],[Details],[OperationId]INTO #TempAuditRecordsToDelete
FROM[adt].[AuditEvent]WHEREDateDiff(day, CreatedOn, @CurrentDate)> @NumberOfDaysToKeep
-- Review the audit event records to be deleted
SELECT*FROM #TempAuditRecordsToDelete
-- Delete the audit events
BEGINTRANSACTIONDELETEFROM[adt].[AuditEvent]WHEREEXISTS(SELECT1FROM #TempAuditRecordsToDelete WHERE Id =[adt].[AuditEvent].[Id])DROPTABLE #TempAuditRecordsToDelete
COMMITTRANSACTIONdeclare @NumberOfDaysToKeep int
declare @CurrentDate datetime
-- Specify the number of days
SET @NumberOfDaysToKeep = 60
-- Create temporary table with the list of audit event records that you want to delete
SET @CurrentDate = GetDate()
SELECT
[Id],[CreatedOn],[Version],[OrganizationId],[Source],[Category],[Action],[IsUserEvent],
[UserId],[FullName],[Email],[DetailsVersion],[Details],[OperationId]
INTO #TempAuditRecordsToDelete
FROM [adt].[AuditEvent]
WHERE DateDiff(day, CreatedOn, @CurrentDate) > @NumberOfDaysToKeep
-- Review the audit event records to be deleted
SELECT * FROM #TempAuditRecordsToDelete
-- Delete the audit events
BEGIN TRANSACTION
DELETE FROM [adt].[AuditEvent]
WHERE EXISTS (SELECT 1 FROM #TempAuditRecordsToDelete WHERE Id = [adt].[AuditEvent].[Id])
DROP TABLE #TempAuditRecordsToDelete
COMMIT TRANSACTION
select * into [UiPathIdentityArchives].[dbo].[ArchiveUserLoginAttempts] from [AutomationSuite_Platform].[identity].[UserLoginAttempts] where 1=2select * into [UiPathIdentityArchives].[dbo].[ArchiveUserLoginAttempts] from [AutomationSuite_Platform].[identity].[UserLoginAttempts] where 1=2
若使用以下查询,则在删除旧数据前,会将旧数据先复制到这些档案中。
删除表格中的数据。
重要提示:在运行以下脚本之前,请确保使它们适应您的环境。
用户登录尝试次数
例如,要删除超过 60 天的登录尝试,请使用以下查询。它可以手动执行,也可以在 SQL Server 作业中计划。
declare @NumberOfDaysToKeep int
set @NumberOfDaysToKeep =60ifOBJECT_ID('[UiPathIdentityArchives].[dbo].[UserLoginAttemps]')=NULL
begin select * into [UiPathIdentityArchives].[dbo].[UserLoginAttemps] from [identity].UserLoginAttempts where 1=2 end
begin transaction
set identity_insert [UiPathIdentityArchives].[dbo].[UserLoginAttemps] on
insert into [UiPathIdentityArchives].[dbo].[UserLoginAttemps]([Id],[PartitionId],[UserId],[UserNameOrEmailAddress],[ClientIpAddress],[ClientName],[BrowserInfo],[Result],[CreationTime],[AuthenticationProvider],[PartitionName])
select [Id],[PartitionId],[UserId],[UserNameOrEmailAddress],[ClientIpAddress],[ClientName],[BrowserInfo],[Result],[CreationTime],[AuthenticationProvider],[PartitionName]
from [identity].UserLoginAttempts where DateDiff(day, CreationTime,GetDate())> @NumberOfDaysToKeep
delete from [identity].UserLoginAttempts where DateDiff(day, CreationTime,GetDate())> @NumberOfDaysToKeep
commit transactiondeclare @NumberOfDaysToKeep int
set @NumberOfDaysToKeep = 60
if OBJECT_ID('[UiPathIdentityArchives].[dbo].[UserLoginAttemps]') = NULL
begin select * into [UiPathIdentityArchives].[dbo].[UserLoginAttemps] from [identity].UserLoginAttempts where 1=2 end
begin transaction
set identity_insert [UiPathIdentityArchives].[dbo].[UserLoginAttemps] on
insert into [UiPathIdentityArchives].[dbo].[UserLoginAttemps] ([Id],[PartitionId],[UserId],[UserNameOrEmailAddress],[ClientIpAddress],[ClientName],[BrowserInfo],[Result],[CreationTime],[AuthenticationProvider],[PartitionName])
select [Id],[PartitionId],[UserId],[UserNameOrEmailAddress],[ClientIpAddress],[ClientName],[BrowserInfo],[Result],[CreationTime],[AuthenticationProvider],[PartitionName]
from [identity].UserLoginAttempts where DateDiff(day, CreationTime, GetDate()) > @NumberOfDaysToKeep
delete from [identity].UserLoginAttempts where DateDiff(day, CreationTime, GetDate()) > @NumberOfDaysToKeep
commit transaction