tag:blogger.com,1999:blog-7233186359690994418.post6742327062417281608..comments2023-04-07T16:52:30.185+03:00Comments on Freelance Development for Dynamics CRM: CRM 2011 - reports on data auditingAnonymoushttp://www.blogger.com/profile/03225186683208658482noreply@blogger.comBlogger11125tag:blogger.com,1999:blog-7233186359690994418.post-67262943520030231162013-04-02T12:24:54.056+03:002013-04-02T12:24:54.056+03:00Andrii thanks for ur SQl Query, it's perfect, ...Andrii thanks for ur SQl Query, it's perfect, I only fixed a little bit.İsmail Tutumluerhttps://www.blogger.com/profile/13880939201119695372noreply@blogger.comtag:blogger.com,1999:blog-7233186359690994418.post-50012707715394912562013-04-02T12:22:37.737+03:002013-04-02T12:22:37.737+03:00This comment has been removed by the author.İsmail Tutumluerhttps://www.blogger.com/profile/13880939201119695372noreply@blogger.comtag:blogger.com,1999:blog-7233186359690994418.post-16469649633931932642013-04-01T15:57:51.350+03:002013-04-01T15:57:51.350+03:00How should I react on your comment?How should I react on your comment?Anonymoushttps://www.blogger.com/profile/03225186683208658482noreply@blogger.comtag:blogger.com,1999:blog-7233186359690994418.post-22889882208903522292013-04-01T15:52:45.455+03:002013-04-01T15:52:45.455+03:00Try
---------------------------
Declare @attribute...Try<br />---------------------------<br />Declare @attributes VarChar(Max), @values VarChar(Max), @ObjectTypeCode int, @LogDateTime DateTime, @RecordId uniqueidentifier, @UserId Uniqueidentifier, @ActionId int<br /><br />Declare @Result Table(AttributeId VarChar(Max), Value VarChar(Max),CurrentValue VarChar(Max), ObjectTypeCode int, LogDateTime DateTime, RecordId uniqueidentifier, UserId uniqueidentifier, ActionId int)<br />Declare @CurrentAttribute VarChar(max), @CurrentValue VarChar(Max)<br /><br />DECLARE DataAuditCursor CURSOR FOR <br />Select <br /> Case When IsNull(a.AttributeMask, '') = '' Then '' Else Substring(a.AttributeMask, 2, Len(a.AttributeMask) - 2) End<br /> ,a.ChangeData<br /> ,a.ObjectTypeCode<br /> ,a.CreatedOn<br /> ,a.ObjectId<br /> ,a.UserId<br /> ,a.[Action]<br />From Audit a <br />OPEN DataAuditCursor<br /><br />FETCH NEXT FROM DataAuditCursor <br />INTO @attributes, @values, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br /> WHILE CHARINDEX(',',@attributes,0) <> 0<br /> BEGIN<br /> SELECT<br /> @CurrentAttribute=RTRIM(LTRIM(SUBSTRING(@attributes,1,CHARINDEX(',',@attributes,0)-1))),<br /> @attributes=RTRIM(LTRIM(SUBSTRING(@attributes,CHARINDEX(',',@attributes,0)+1,LEN(@attributes)))),<br /> @CurrentValue=RTRIM(LTRIM(SUBSTRING(@values,1,CHARINDEX('~',@values,0)-(case when CHARINDEX('~',@values,0)<=0 then 0 else 1 End)))),<br /> @values=RTRIM(LTRIM(SUBSTRING(@values,CHARINDEX('~',@values,0)+1,LEN(IsNull(@values,0)))))<br /> <br /> <br /> <br /> IF LEN(@CurrentAttribute) > 0<br /> INSERT INTO @Result Values(CAST(@CurrentAttribute as nvarchar), @CurrentValue,@CurrentValue, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId)<br /> END<br /><br /> INSERT INTO @Result Values((Case When IsNull(@attributes, '') = '' Then Null Else CAST(@attributes as nvarchar) End), @values,@CurrentValue, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId) <br /><br /> FETCH NEXT FROM DataAuditCursor <br /> INTO @attributes, @values, @ObjectTypeCode, @LogDateTime, @RecordId, @UserId, @ActionId<br />END<br /><br />CLOSE DataAuditCursor;<br />DEALLOCATE DataAuditCursor;<br /> <br /><br /> <br />Select <br /> (Select Top 1 Name From MetadataSchema.Entity e Where r.ObjectTypeCode = e.ObjectTypeCode) EntityName<br /> ,(Select Top 1 a.Name From MetadataSchema.Attribute a <br /> Inner Join MetadataSchema.Entity e On a.EntityId = e.EntityId and a.ColumnNumber = r.AttributeId and e.ObjectTypeCode = r.ObjectTypeCode<br /> ) AttributeName<br /> ,u.fullname UserName<br /> ,r.Value OldFieldValue<br /> ,r.RecordId ModifiedRecordId<br />From @Result r<br /> Left Join FilteredSystemUser u On r.UserId = u.systemuseridİsmail Tutumluerhttps://www.blogger.com/profile/13880939201119695372noreply@blogger.comtag:blogger.com,1999:blog-7233186359690994418.post-40041367147084363602012-09-12T23:16:03.052+03:002012-09-12T23:16:03.052+03:00Thansk for the post. I'm getting an error -
I...Thansk for the post. I'm getting an error - <br />Invalid length parameter passed to the LEFT or SUBSTRING function.<br /><br />Am I missing anything?DhivyaSivahttps://www.blogger.com/profile/11082514209154837861noreply@blogger.comtag:blogger.com,1999:blog-7233186359690994418.post-62872559240468231922012-08-07T20:59:11.434+03:002012-08-07T20:59:11.434+03:00Good post Andrii,
I want to know the differnce be...Good post Andrii,<br /><br />I want to know the differnce between "Action" and "Operation" Columns in Audit table.<br /><br />Thanks and Ragards,<br />YusufAsadhttps://www.blogger.com/profile/12050700665188401229noreply@blogger.comtag:blogger.com,1999:blog-7233186359690994418.post-11115305408587430002012-07-13T14:10:13.612+03:002012-07-13T14:10:13.612+03:00AttributeMask is the field that is causing this er...AttributeMask is the field that is causing this error. Recognised that I did not actually need it. So I decide to remove it. I will post my complete Query when I am done with it.BlackCidhttps://www.blogger.com/profile/11809074939149969805noreply@blogger.comtag:blogger.com,1999:blog-7233186359690994418.post-56631436698697706692012-07-02T12:38:33.788+03:002012-07-02T12:38:33.788+03:00I got error when executing the query above
Msg 53...I got error when executing the query above<br /><br />Msg 537, Level 16, State 5, Line 26<br />Invalid length parameter passed to the LEFT or SUBSTRING function.<br /><br />Would you able to assist me on this?PQ's Technical Bloghttps://www.blogger.com/profile/15954474492559639418noreply@blogger.comtag:blogger.com,1999:blog-7233186359690994418.post-77401912568074806912012-04-27T15:52:40.005+03:002012-04-27T15:52:40.005+03:00Hello,
Do you expect some reaction from my side?Hello,<br />Do you expect some reaction from my side?Anonymoushttps://www.blogger.com/profile/03225186683208658482noreply@blogger.comtag:blogger.com,1999:blog-7233186359690994418.post-43122743044521921732012-04-27T15:26:54.075+03:002012-04-27T15:26:54.075+03:00Display users that did not access the system in x ...Display users that did not access the system in x days<br /><br />DECLARE @NoOfDays INT<br />DECLARE @Today DATETIME = GETDATE()<br />SET @NoOfDays = 10<br /><br /> SELECT FirsAccess = MIN(dbo.fn_UTCToTzSpecificLocalTime(Audit.CreatedOn, us.TimeZoneBias,us.TimeZoneDaylightBias,us.TimeZoneDaylightYear,us.TimeZoneDaylightMonth,us.TimeZoneDaylightDay,us.TimeZoneDaylightHour,us.TimeZoneDaylightMinute,us.TimeZoneDaylightSecond,0,us.TimeZoneDaylightDayOfWeek,us.TimeZoneStandardBias,us.TimeZoneStandardYear,us.TimeZoneStandardMonth,us.TimeZoneStandardDay,us.TimeZoneStandardHour,us.TimeZoneStandardMinute,us.TimeZoneStandardSecond,0,us.TimeZoneStandardDayOfWeek)),<br /> LastAccess = MAX(dbo.fn_UTCToTzSpecificLocalTime(Audit.CreatedOn, us.TimeZoneBias,us.TimeZoneDaylightBias,us.TimeZoneDaylightYear,us.TimeZoneDaylightMonth,us.TimeZoneDaylightDay,us.TimeZoneDaylightHour,us.TimeZoneDaylightMinute,us.TimeZoneDaylightSecond,0,us.TimeZoneDaylightDayOfWeek,us.TimeZoneStandardBias,us.TimeZoneStandardYear,us.TimeZoneStandardMonth,us.TimeZoneStandardDay,us.TimeZoneStandardHour,us.TimeZoneStandardMinute,us.TimeZoneStandardSecond,0,us.TimeZoneStandardDayOfWeek)),<br /> UserFullName = FilteredSystemUser.fullname<br /> FROM FilteredSystemUser<br /> LEFT OUTER JOIN Audit ON (FilteredSystemUser.systemuserid = audit.ObjectId)<br /> LEFT OUTER JOIN SystemUserBase u on (u.SystemUserId = dbo.fn_FindUserGuid() and u.IsDisabled = 0)<br /> LEFT OUTER JOIN UserSettingsBase us on us.SystemUserId = u.SystemUserId<br /> WHERE Audit.AuditId IS NULL OR (Audit.ObjectTypeCode = 8 AND <br /> Audit.userIDName = 'SYSTEM' AND<br /> Audit.[Action] IN (64, 65))<br /> GROUP BY FilteredSystemUser.fullname<br /> HAVING DATEDIFF(d, MAX(dbo.fn_UTCToTzSpecificLocalTime(Audit.CreatedOn, us.TimeZoneBias,us.TimeZoneDaylightBias,us.TimeZoneDaylightYear,us.TimeZoneDaylightMonth,us.TimeZoneDaylightDay,us.TimeZoneDaylightHour,us.TimeZoneDaylightMinute,us.TimeZoneDaylightSecond,0,us.TimeZoneDaylightDayOfWeek,us.TimeZoneStandardBias,us.TimeZoneStandardYear,us.TimeZoneStandardMonth,us.TimeZoneStandardDay,us.TimeZoneStandardHour,us.TimeZoneStandardMinute,us.TimeZoneStandardSecond,0,us.TimeZoneStandardDayOfWeek)), @Today) >= @NoOfDaysbits & boltshttps://www.blogger.com/profile/05338805265115925266noreply@blogger.comtag:blogger.com,1999:blog-7233186359690994418.post-3381380534646848642012-01-18T11:45:49.186+02:002012-01-18T11:45:49.186+02:00Great Post Andriy!
I tried your SQL and found that...Great Post Andriy!<br />I tried your SQL and found that both the old and new value for a field are displayed as separate records returned by this query.<br />Is there any way I can see them in one record?<br /><br />My need is to find out how many opportunity records got their "SalesStageCode" set equal to "Quote Sent" between two given dates.<br /><br />Thank you,CrmDeveloperhttps://www.blogger.com/profile/17859477239167085979noreply@blogger.com