Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
Query Audit history in SQLSERVER RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi all,

    May I ask that if I will be able to query the audit history from SQL Server by SQL Please?

    Because I found a table call Audit and AuditBase in the SQL Server; And they looks holding the audit information of corresponding CRM Organization.

    But I cannot define well which entity these records related to; I found they got a column call "ObjectTypeCode", May I ask how could I define the entity of these records by this column please? Or if I misunderstood the usage of this column, may I ask I should use which field to define the related entity of corresponding record please?

    Please comment or advise;Many thanks

    Monday, April 2, 2012 10:09 AM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote
    ObjectTypeCode is the unique number of each entity. Forexample for Accounts it is 1 and for contacts it is 2. Whenever we create an entity CRM set a ObjectTypeCode to that entity. ObjectTypeCode in Audit is describing that the record relate to which entity. If you run following query on SQL server you will get all the entities and their ObjectTypeCodes:-  
    SELECT [EntityId]
      ,[ObjectTypeCode]
      ,[PhysicalName]
      ,[LogicalName]
       FROM [<OrgName>_MSCRM].[dbo].[EntityLogicalView]

    Regards Faisal



    • Edited by Faisal Fiaz Monday, April 2, 2012 10:33 AM
    • Proposed as answer by pogo69 Monday, April 2, 2012 10:44 AM
    • Marked as answer by Ksun_Chan Tuesday, April 3, 2012 1:56 AM
    Monday, April 2, 2012 10:31 AM

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote
    ObjectTypeCode is the unique number of each entity. Forexample for Accounts it is 1 and for contacts it is 2. Whenever we create an entity CRM set a ObjectTypeCode to that entity. ObjectTypeCode in Audit is describing that the record relate to which entity. If you run following query on SQL server you will get all the entities and their ObjectTypeCodes:-  
    SELECT [EntityId]
      ,[ObjectTypeCode]
      ,[PhysicalName]
      ,[LogicalName]
       FROM [<OrgName>_MSCRM].[dbo].[EntityLogicalView]

    Regards Faisal



    • Edited by Faisal Fiaz Monday, April 2, 2012 10:33 AM
    • Proposed as answer by pogo69 Monday, April 2, 2012 10:44 AM
    • Marked as answer by Ksun_Chan Tuesday, April 3, 2012 1:56 AM
    Monday, April 2, 2012 10:31 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Faisal Fiaz, thank you very much for your information, it exactly what I looking for! many thanks!
    Tuesday, April 3, 2012 1:56 AM