Have you ever received a Query Builder Error when trying to view the audit history in Dynamics CRM?

The cause of this error might be related to deleted fields in that particular entity after the audit log records are created.

Here's an example scenario:

• Create a custom field.
• Enable auditing on that new field.
• Delete that field after the audit records are created with data in that field.
• This might cause the audit history to show the above error.

Here is how to fix it:

Update/delete the audit log records that contain the deleted fields. The audit log is stored in the AuditBase table in the CRM database.

    WARNING: Always backup your databases if you are updating data in SQL directly.

1. Use the following query to get the current field list from the CRM Database:
   

   SELECT
   distinct a.columnnumber, a.Name, e.ObjectTypeCode
   

   FROM MetadataSchema.Attribute as a
   

   join MetadataSchema.LocalizedLabel as l on a.AttributeId = l.objectid
   

   join EntityView e on e.EntityId=a.EntityId
   

   WHERE e.Name='Entity name goes here '
   

   order
   by a.ColumnNumber
   

2. The AttributeMask column in the AuditBase table stores the entity fields and ChangeData stores the changes.

   AttributeMask data should match with the column numbers you get from the above query. If there are any additional values in AttributeMask, you will get the query builder error. (You can write SQL queries to identify the deleted columns or export the audit data into CSV/Excel to find them manually.)

3. Once you find the wrong data, you can update the audit table or delete the rows all together that are tracking the deleted fields. Keep in mind that by deleting the rows, you will lose the audit history.

Now you know how to fix the Query Builder Error in audit history views. For more helpful Dynamics CRM tips, keeping checking our blog!

Happy CRM'ing!