If you have any doubt in the post please post comments. I will try to solve your problem.
In CRM, audit is a super feature to track a record in CRM. It is very useful at the time of production issue. When we view the audit, as you can see except record column other fields we can filter.
But in real time we would need filter audit record based on particular record.
By using the below query you will filter audit based on record. In that query you can
Output
There are 6 columns in the output.
Description:
Entity Name : Name of the entity
AttributeName :Logging field name
UserName : by which user this record create
OldFieldValue :old value of the field
CurrentValue : new value of the field
LogDateTime :When it was created
In CRM, audit is a super feature to track a record in CRM. It is very useful at the time of production issue. When we view the audit, as you can see except record column other fields we can filter.
But in real time we would need filter audit record based on particular record.
By using the below query you will filter audit based on record. In that query you can
- {0}: Start date
- {1}: End date
- {2}: Record guid(example: if you look into account record then put particular account guid.)
drop table #temp1
Declare @attributes VarChar(Max), @values VarChar(Max), @ObjectTypeCode int, @LogDateTime DateTime,
@RecordId uniqueidentifier,
@UserId Uniqueidentifier,
@ActionId int
Declare @Result Table(AttributeId int, Value VarChar(Max),
ObjectTypeCode int, LogDateTime DateTime, RecordId uniqueidentifier, UserId uniqueidentifier,
ActionId int)
Declare @CurrentAttribute VarChar(max), @CurrentValue VarChar(Max)
DECLARE DataAuditCursor
CURSOR FOR
Select top 200
Case When IsNull(a.AttributeMask, '') = ''
Then ''
Else Substring(a.AttributeMask, 2, Len(a.AttributeMask) - 2)
End ,
a.ChangeData ,a.ObjectTypeCode ,a.CreatedOn ,a.ObjectId ,a.UserId ,a.[Action]
From dbo.Audit a
where
-- Createdon > '2014-11-01 00:00:00.000'{0} -- Edit this to match your scenario
--and Createdon < '2014-11-19 00:00:00.000' {1}-- Edit this to match your scenario
a.ObjectId= '3A780EAA-1327-E511-9405-0050568106FE' --{2}
and Action = 2 --(1=Create, 2=Update, 3=Delete)
order by Createdon desc
OPEN DataAuditCursor
FETCH NEXT FROM DataAuditCursor INTO @attributes, @values, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId
WHILE @@FETCH_STATUS = 0
BEGIN
WHILE CHARINDEX(',',@attributes,0) <> 0
BEGIN
SELECT
@CurrentAttribute=RTRIM(LTRIM(SUBSTRING(@attributes,1,CHARINDEX(',',@attributes,0)-1))),
@attributes=RTRIM(LTRIM(SUBSTRING(@attributes,CHARINDEX(',',@attributes,0)+1,LEN(@attributes)))),
@CurrentValue=RTRIM(LTRIM(SUBSTRING(@values,1,CHARINDEX('~',@values,0)-1))),
@values=RTRIM(LTRIM(SUBSTRING(@values,CHARINDEX('~',@values,0)+1,LEN(@values))))
IF LEN(@CurrentAttribute) > 0
INSERT INTO @Result Values(CAST(@CurrentAttribute as int),
@CurrentValue, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId)
END
INSERT INTO @Result
Values((
Case
When IsNull(@attributes, '') = ''
Then Null
Else CAST(@attributes as int)
End
), @values, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId)
FETCH NEXT FROM DataAuditCursor
INTO @attributes, @values, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId
END
CLOSE DataAuditCursor;
DEALLOCATE DataAuditCursor;
Select
(
Select Top 1 Name
From MetadataSchema.Entity e
Where r.ObjectTypeCode = e.ObjectTypeCode
) EntityName ,
(Select Top 1 a.Name
From MetadataSchema.Attribute a Inner Join MetadataSchema.Entity e On a.EntityId = e.EntityId
and a.ColumnNumber = r.AttributeId
and e.ObjectTypeCode = r.ObjectTypeCode
) AttributeName ,
u.fullname UserName ,
r.Value OldFieldValue ,
r.RecordId ModifiedRecordId,
r.LogDateTime
into #temp1
From @Result r Left Join dbo.FilteredSystemUser u On r.UserId = u.systemuserid
create table ##temp2 (AttributeName varchar(200), currentValue varchar(400), RecId uniqueidentifier)
declare @EntityName varchar(100), @AttributeName varchar(100), @UserName varchar(100), @OldFieldValue varchar(700), @ModifiedRecordId uniqueidentifier, @Temp2LogDateTime DateTime
declare @SQLStatement nvarchar(400)
declare @getOldValue varchar(400)
DECLARE TempCursor CURSOR FOR
Select top 10 EntityName, AttributeName, UserName, OldFieldValue, ModifiedRecordId
from #temp1
OPEN TempCursor
FETCH NEXT FROM TempCursor INTO @EntityName, @AttributeName, @UserName, @OldFieldValue, @ModifiedRecordId
WHILE @@FETCH_STATUS = 0
BEGIN
select @SQLStatement = 'insert into ##temp2 select '+ ''''+@AttributeName +''' as AttributeName, ' + @AttributeName+' as currentValue, '+ @EntityName+'id
as RecId from dbo.'+@EntityName+' where '+@EntityName+'id'+' = '+ ''''+'{'+convert(varchar(38),@ModifiedRecordId)+'}'+''''
--select @SQLStatement
EXEC sp_executesql @SQLStatement;
--select @EntityName, @AttributeName, @UserName, @OldFieldValue, @ModifiedRecordId
FETCH NEXT FROM TempCursor INTO @EntityName, @AttributeName, @UserName, @OldFieldValue, @ModifiedRecordId
END
CLOSE TempCursor;
DEALLOCATE TempCursor;
select #temp1.EntityName,#temp1.AttributeName,#temp1.UserName, #temp1.OldFieldValue, ##temp2.currentValue
,#temp1.LogDateTime
from #temp1
join ##temp2 on (##temp2.RecId = #temp1.ModifiedRecordId and ##temp2.AttributeName = #temp1.AttributeName collate Latin1_General_CI_AI)
drop table ##temp2
-- Clean up some trash...
delete from tempTrashChanges where OldFieldValue = '' and currentValue is null
-- Don't need to see this
delete from tempTrashChanges where OldFieldValue = currentValue
|
There are 6 columns in the output.
Description:
Entity Name : Name of the entity
AttributeName :Logging field name
UserName : by which user this record create
OldFieldValue :old value of the field
CurrentValue : new value of the field
LogDateTime :When it was created
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.