718 Washington Ave. N. Suite #101
Minneapolis, MN 55401
View map and all Locations

Send us a message

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


query builder error in audit history

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:

    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 ‘

    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!

Avatar for Joe D365

Joe D365

Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.