Monday, February 9, 2015

MS CRM Audit Database Table Details

CRM audit table contains column


AttributeMask - Column number of attribute from either Attribute table or from MetadataSchema.Attribute

CRM stores all attributes changed in single transaction in a string format concatenate by ,

Attribute mask column contains values like

,47,45,42,96,93,107,201,28,25,53,32,14,90,178,121,61,55,108,11,35,8,177,120,69,122,117,209,46,80,78,59,10051,30,102,50,71,4,202,73,3,5,128,54,51,206,210,236,19,126,124,38,184,34,31,231,118,33,43,98,52,10037,70,123,129,132,133,103,39,114,22,48,56,10026,235,99,97,181,183,95,234,18,21,200,10045,49,106,109,110,16,24,67,74,175,44,27,139,125,208,134,10053,180,130,2,41,36,92,76,94,179,113,127,17,10039,101,233,10022,15,176,174,10043,29,203,119,37,72,40,20,173,211,91,111,105,131,10047,6,116,104,23,12,57,10041,66,100,115,26,

To get list of attribute name use query like


SELECT ar.name, 
  ar.ColumnNumber 
FROM MetadataSchema.Attribute ar INNER JOIN 
MetadataSchema.Entity en ON ar.EntityId = en.EntityId
WHERE en.ObjectTypeCode=2 AND ar.ColumnNumber=47

Or

SELECT ar.name, 
  ar.ColumnNumber 
FROM Attribute ar INNER JOIN 
Entity en ON ar.EntityId = en.EntityId
WHERE en.ObjectTypeCode=2 AND ar.ColumnNumber=47


Action: 
Action column stores what kind of action performed on record. Action would be Create, Update, Delete, Activate, Fulfill etc. In audit table CRM stored action value like 2 or 3 or 4 etc.

To get all action value from Database  use following query

SELECT Value as Action,AttributeValue as ActionValue FROM StringMap WHERE AttributeName='action'

and here is list of actions in CRM 2013.
0 - Unknown,  25 - Disqualify,  44 - Win,  63 - Enabled for organization, 
1 - Create,  26 - Submit,  45 - Lose,  64 - User Access via Web, 
2 - Update,  27 - Reject,  46 - Internal Processing,  65 - User Access via Web Services, 
3 - Delete,  28 - Approve,  47 - Reschedule,  100 - Delete Entity, 
4 - Activate,  29 - Invoice,  48 - Modify Share,  101 - Delete Attribute, 
5 - Deactivate,  30 - Hold,  49 - Unshare,  102 - Audit Change at Entity Level, 
11 - Cascade,  31 - Add Member,  50 - Book,  103 - Audit Change at Attribute Level, 
12 - Merge,  32 - Remove Member,  51 - Generate Quote From Opportunity,  104 - Audit Change at Org Level, 
13 - Assign,  33 - Associate Entities,  52 - Add To Queue,  105 - Entity Audit Started, 
14 - Share,  34 - Disassociate Entities,  53 - Assign Role To Team,  106 - Attribute Audit Started, 
15 - Retrieve,  35 - Add Members,  54 - Remove Role From Team,  107 - Audit Enabled, 
16 - Close,  36 - Remove Members,  55 - Assign Role To User,  108 - Entity Audit Stopped, 
17 - Cancel,  37 - Add Item,  56 - Remove Role From User,  109 - Attribute Audit Stopped, 
18 - Complete,  38 - Remove Item,  57 - Add Privileges to Role,  110 - Audit Disabled, 
20 - Resolve,  39 - Add Substitute,  58 - Remove Privileges From Role,  111 - Audit Log Deletion, 
21 - Reopen,  40 - Remove Substitute,  59 - Replace Privileges In Role,  112 - User Access Audit Started, 
22 - Fulfill,  41 - Set State,  60 - Import Mappings,  113 - User Access Audit Stopped, 
23 - Paid,  42 - Renew,  61 - Clone, 
24 - Qualify,  43 - Revise,  62 - Send Direct Email, 


ObjectId: 
Object Id the record id, for which this audit record is created.

UserId:
The user who performed action on record.

ChangeData:
Change Data column stores old data for the attribute. If you changed First name of contact from James to Jim, then in Change data James will be stored.
Values in Change Data are stored in same order as attribute mask stored.
If First name attribute mask is 2 and last name is 3, then in AttributeMask it would be ,2,3 and Change data would be James~Bond

Change Data column data is separated by ~ by per attribute.

Operation:
Operation column stores information of what kind of operation is performed on record.
CRM has only four operations
1 - Create , 2 - Update, 3 - Delete, 4 - Access. 

To get directly operation values from database use

SELECT Value,AttributeValue FROM StringMap WHERE AttributeName='operation'

ObjectTypeCode:
Object Type code is entity object type code.

To find object type code for entity use

SELECT ObjectTypeCode, LogicalName FROM Entity WHERE Name='contact'

If want to get entity details from object type code then use

SELECT ObjectTypeCode, LogicalName FROM Entity WHERE ObjectTypeCode=1



25 comments:

  1. How do you get the new data value of the attribute?

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Hi Mahadeo,

    Is there any relation between ACTION and OPERATION columns similar to statecode and statuscode ?

    I am confused about OPERATION column. What purpose does it serve when everything is covered under ACTION column ?

    ReplyDelete
  4. Did somebody find an answer to the question:
    How do you get the new data value of the attribute?
    TIA.

    ReplyDelete
    Replies
    1. Сheck next stored procedures "[p_GetAuditDetails]","[p_GetNewValueFromAuditTrail]"
      CRM just searching up by Audit table.

      Delete
  5. How about the data from the online instance?

    ReplyDelete
  6. How to get the entity name where the action occurred in CRM 2016?
    I made GET request into /audits endpoint and got the following response
    {
    "_objectid_value": "00000000-0000-0000-0000-000000000000",
    "_userid_value": "82de60d1-362a-e411-80c1-00155d002810",
    "operation": 3,
    "createdon": "2014-09-16T19:05:36Z",
    "auditid": "da3f5570-d43d-e411-80c2-00155d002810",
    "attributemask": "10003",
    "action": 101,
    "transactionid": "b0785070-d43d-e411-80c2-00155d002810",
    "useradditionalinfo": null,
    "_regardingobjectid_value": null,
    "_callinguserid_value": null
    }
    what field of those that correspond to the entity name? is it _regardingobjectid_value and if it's, why it's value is null for all records?

    ReplyDelete
  7. Just found this blog entry. VERY powerful stuff, thank you for sharing, and please keep it up!!

    ReplyDelete
  8. hi Mahadeo,

    I was wondering if you know the action value for user Access. It looks like that you are not able to get action code 64 and 65 in Dynamics 365 online..

    ReplyDelete
  9. Can anyone help me with getting the old as well as the new address for user, meaning how do I extract only this value in the change data table?
    Also, how can I determine which users have had changes in their addresses? (account table?)

    ReplyDelete
  10. I really appreciate information shared above. It’s of great help.
    Microsoft Dynamics AX Online Training

    ReplyDelete
  11. Very valuable post, I was able to understand and parse the audit tables and their linkage to other entities using this. Thank you!

    ReplyDelete
  12. Hi there,
    It is really helpful blog!
    I really enjoyed, But just a question.
    You said about storing the old value in ChangeData; but how about new value?
    I see the new value in audit history of crm, but how can I get it?
    Thank you

    ReplyDelete
  13. How to get the old and new values?
    Also i have a matrix table and a time period filter. Depending upon the filter/time selection, the matrix table count should change depending upon how the old and new values are coming for that time period.. is this possible?

    ReplyDelete
  14. Hey thanks for sharing a good article post in this blog. thanks for sharing it. you can visit here for zero accident consulting services and zero defects solution provider

    ReplyDelete
  15. Recently I read your post, this is very useful for all. Your content was very unique and thank you!
    Multi State Family Law Attorneys
    Best Family Lawyers in Northern Virginia

    ReplyDelete
  16. Very Nice post. Very Informative and Useful Topic. Looking for the Auditing and Accounting Services for your business in Dubai, Visit this Link

    ReplyDelete
  17. Mountain Duck 4.12.1.19928 Crack is based on Cyberduck's robust open-source framework, makes it easy to add remote files in Dealer locator in Windows Xp and.Mountain Duck Box

    ReplyDelete
  18. such a significant topic.ERP Software in Chennai. Keep producing blogs like this since your content always inspires and attracts us and aids in the growth of our company.

    ReplyDelete