How to Retrieve Audit History from a Dataverse Table, Including Old Values

(2) Share

Report

Posted on by CU03032015-0

I need to extract the audit history from a Dataverse table, including the following details:

Old Value – The value before the change occurred.
New Value – The updated value (if applicable).
Event Type – Whether the change was a Create, Update, or Delete operation.
Timestamp – The date and time when the change occurred.
Modified By – The user who performed the action.

I understand that we can extract this information from the admin center, but in this case, we need to filter the data for a specific table. Additionally, even after filtering, we still have to manually open each event to view the details.

I want to get the consolidate data.

Thanks

Categories:

Power Apps Pro Dev & ISV

Power app Audit.JPG

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

Inogic 1,135 Super User 2025 Season 2 on at

Like (0)

Report
How to Retrieve Audit History from a Dataverse Table, Including Old Values

Hi,

We could use Dataflows in Power Platform to pull data from the Dynamics 365 Audit Log or directly from the Dynamics 365 environment. Follow the below steps:

Create a dataflow in www.make.powerapps.com portal.

Select OData as data source:

Right click on Query and open advance editor and advance query pane will open:

Add below query:

let
Source = OData.Feed("https://techwork57.crm4.dynamics.com/api/data/v9.2/audits", null, [Implementation = "2.0"]),
#"Removed columns" = Table.RemoveColumns(Source, {"useradditionalinfo", "additionalinfo"}),
#"Filtered rows" = Table.SelectRows(#"Removed columns", each ([objecttypecode] = "lead")),
#"Removed columns 1" = Table.RemoveColumns(#"Filtered rows", {"callinguserid", "userid", "RetrieveAuditDetails", "_callinguserid_value", "timetoliveinseconds"}),
#"Filtered rows 1" = Table.SelectRows(#"Removed columns 1", each ([operation] <> 3)),
#"Removed columns 2" = Table.RemoveColumns(#"Filtered rows 1", {"action", "transactionid", "_regardingobjectid_value", "versionnumber"}),
#"Filtered rows 2" = Table.SelectRows(#"Removed columns 2", each Text.Contains([attributemask], ",10165") or Text.Contains([attributemask], "10165,") or [attributemask] = "10165"),
#"Parsed JSON" = Table.TransformColumns(#"Filtered rows 2", {{"changedata", each Json.Document(_), type any}}),
#"Expanded changedata" = Table.ExpandRecordColumn(#"Parsed JSON", "changedata", {"changedAttributes"}, {"changedata.changedAttributes"}),
#"Expanded changedata.changedAttributes" = Table.ExpandListColumn(#"Expanded changedata", "changedata.changedAttributes"),
#"Expanded changedata.changedAttributes 1" = Table.ExpandRecordColumn(#"Expanded changedata.changedAttributes", "changedata.changedAttributes", {"logicalName", "oldValue", "newValue", "newName"}, {"logicalName", "oldValue", "newValue", "newName"}),
#"Filtered rows 3" = Table.SelectRows(#"Expanded changedata.changedAttributes 1", each ([logicalName] = "statuscode"))
in
#"Filtered rows 3"

You will be able to see all the required audit details. This can be used to create a power bi report or excel accordingly.

Note: This query provides audit of lead entity. If other entity logs are required, expression must be edited accordingly.

Hope this helps.

Thanks!

Inogic

2 people found this reply helpful.

Was this reply helpful? Yes No
CU04111622-0 3 on at

Like (0)

Report

How to Retrieve Audit History from a Dataverse Table, Including Old Values

Thank you SO MUCH for sharing! I've been trying to understand for a long time how to easily get at this data.
There are a bunch of little steps here that are key to getting the data easily and efficiently.

You will have to change the env, steps, and filters to meet your needs, but the core instructions are fabulous.

Dataflow via Odata Connector using Power Apps API URL -> Audit Table (w/ Users) -> Power BI Reporting.

Was this reply helpful? Yes No