How to Check Table and Column Auditing Settings in Microsoft Dataverse Using SQL 4 CDS

Auditing is an important feature in Microsoft Dataverse that helps organizations track changes made to records.

When working with a large Dataverse implementation, checking the auditing configuration manually for multiple tables and columns can be cumbersome.

In this article, we will see how to use XrmToolBox and SQL 4 CDS to quickly retrieve Dataverse table and column metadata and identify whether auditing is enabled or disabled.

Microsoft documents auditing configuration at the environment, table, and column levels. When environment auditing is enabled, audit data is recorded for enabled tables, and enabled columns are included in the audit data when changes occur.

Prerequisites

For this example, you need:

A Microsoft Dataverse environment
XrmToolBox
SQL 4 CDS Tool installed in XrmToolBox
System Admin or System Customizer access to the Dataverse environment

Note: This article focuses on inspecting the table and column auditing configuration, not retrieving the actual audit-history records.

Microsoft notes that audit history itself isn't available through the Dataverse TDS SQL endpoint. Audit history can instead be retrieved using supported Dataverse APIs/messages.

Step 1: Open XrmToolBox

Launch XrmToolBox and connect to the required Microsoft Dataverse environment.

Once connected, open:

SQL 4 CDS

We will use SQL 4 CDS to query the Dataverse metadata.

Step 2: Understand Dataverse Auditing Levels

Before querying the metadata, it is useful to understand how Dataverse auditing is configured.

There are three important levels:

Environment level:

Auditing first needs to be enabled at the environment level. The Dataverse Organization table contains auditing-related settings including isauditenabled.

Table level:

Individual tables can then be configured for auditing.

Column level:

Individual columns within an audited table can also be enabled or disabled for auditing.

Microsoft specifically states that Dataverse APIs can query table and column definitions to determine which tables and columns are enabled for auditing.

So, when reviewing your configuration, it is useful to check both.

Step 3: Copy and paste the below query and execute in SQL 4 CDS.

SELECT
e.DisplayName AS TableDisplayName,
e.LogicalName AS TableLogicalName,
CASE
WHEN e.IsAuditEnabled = 1 THEN 'Enabled'
WHEN e.IsAuditEnabled = 0 THEN 'Disabled'
ELSE 'Not Set'
END AS TableAuditing,
a.DisplayName AS ColumnDisplayName,
a.LogicalName AS ColumnLogicalName,
a.SchemaName AS ColumnSchemaName,
a.AttributeType AS ColumnType,
CASE
WHEN a.IsAuditEnabled = 1 THEN 'Enabled'
WHEN a.IsAuditEnabled = 0 THEN 'Disabled'
ELSE 'Not Set'
END AS ColumnAuditing
FROM metadata.entity e
INNER JOIN metadata.attribute a
ON e.LogicalName = a.EntityLogicalName
ORDER BY
e.LogicalName,
a.LogicalName

Step 4: Filter Condition

For a production environment containing many tables, the output can naturally be large, so filtering to the tables relevant to your audit review will generally make the report easier to consume.

Use the below filter to retrieve only required Tables and corresponding columns metadata.

WHERE e.LogicalName IN
(
'account',
'contact'
)

Then the final query looks like the below,

SELECT
e.DisplayName AS TableDisplayName,
e.LogicalName AS TableLogicalName,
CASE
WHEN e.IsAuditEnabled = 1 THEN 'Enabled'
WHEN e.IsAuditEnabled = 0 THEN 'Disabled'
ELSE 'Not Set'
END AS TableAuditing,
a.DisplayName AS ColumnDisplayName,
a.LogicalName AS ColumnLogicalName,
a.SchemaName AS ColumnSchemaName,
a.AttributeType AS ColumnType,
CASE
WHEN a.IsAuditEnabled = 1 THEN 'Enabled'
WHEN a.IsAuditEnabled = 0 THEN 'Disabled'
ELSE 'Not Set'
END AS ColumnAuditing,
CASE
WHEN a.IsCustomAttribute = 1 THEN 'Yes'
WHEN a.IsCustomAttribute = 0 THEN 'No'
ELSE 'Not Set'
END AS CustomColumn
FROM metadata.entity e
INNER JOIN metadata.attribute a
ON e.LogicalName = a.EntityLogicalName
WHERE e.LogicalName IN
(
'account',
'contact'
)
ORDER BY
e.LogicalName,
a.LogicalName

Practical Use Cases

This query can be useful when:

Reviewing auditing configuration across multiple Dataverse tables
Identifying columns where auditing is disabled
Comparing audit configuration between environments
Performing governance or configuration reviews
Documenting the current auditing setup
Preparing an audit configuration report before making changes

Also keep in mind that Dataverse audit logs consume log storage capacity, so auditing configuration should be considered alongside your environment's auditing and retention strategy.

Conclusion

Microsoft Dataverse provides auditing configuration at the environment, table, and column levels. Instead of manually opening each table and reviewing individual column settings, metadata queries can help make the configuration easier to inspect.

Using XrmToolBox with SQL 4 CDS, we can create a readable report containing:

Table name
Table auditing status
Column name
Column data type
Column auditing status
Custom or standard column indicator

This can be particularly useful when working with environments containing a large number of Dataverse tables and columns.