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

Change Display order of system views

In CRM system views are order by number and alphabets. When view name is starting with number, that view was shown first and then views shown by alphabet orders. 

If want some custom order then we need to modify view name and either add number or alphabets in front of view name 

1.       Go to customization of entity views.

2.       Open view and click on view properties. 

1.       Add either number or alphabet in Front of view name

1.       Save view and change name of other views as per order

1.       Publish customization

with Alphabet order 

Insert Hyperlink in Email, Email Template or Articles

Most of the time we struggle when wants to add hyperlink in emails which want to send from CRM system.

When composing email wants to add Hyperlink, so that recipient will just click on it and link will be opened..

To do this.. very simple step need to do..

select text where you want add hyperlink and keying CTRL+K

Add your click in URL. Then it looks like 

Same think is working for Email template, Articles. 

Sometime might not work in Chrome or in any other browser.. but mostly works in IE.