Retrieve Last modified date from Audit History where Modified by is not CRM Admin. RRS feed

  • Question

  • Hi All,

    I have requirement to create a report based on last modified date in Lead entity, to know which user has not updated for long time. The issue is that the report is not showing the correct data since as a Admin we are triggering few plugins and workflow on particular status and fields value changes and so the Last modified date and modified by becomes the Admin latest updated date and Admin name. So how do I get the Last modified date by user from audit history.

    Any suggestions would be helpful!

    Regards, Rekha.J

    Friday, February 27, 2015 6:31 AM

All replies

  • If you want a report, and you use Crm OnPremise, then you can use a SQL query on the report. However, there is no FilteredAudit view that users have permission on - instead you'd have to directly query the Audit view, and ensure that whoever runs the report has SQL permission on that view.

    The following query will give you the last date by combination of user and lead

    select UserId, ObjectId, MAX(CreatedOn) as LastAuditDate
    from audit
    where ObjectTypeCode = 4
    group by UserId, ObjectId

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Friday, February 27, 2015 9:48 AM
  • Hi,

    Thanks for the reply, We use online version.

    Regards, Rekha.J

    Friday, February 27, 2015 9:50 AM