Skip to content

Retrieving Dataverse Audits in Power Automate

Technology

Mar 27, 2023 - 5 minute read

2907 Dataverse Audits Blog Post 416X300
Amadeusz Nowak Software Developer

He is a Power Platform developer with over 5 years of experience. Outside of office hours, Amadeusz is an amateur football analyst and a PC building enthusiast.

See all Amadeusz's posts

2988 HC Digital Transformation 476X381

Share

This is the third part of my guide about custom Audit Logs in Power Apps. In the previous article, we went through preparations for the development process. In this part, I’m going to show you how to connect your Power Automate flow with the Azure Key vault and get access to audits within the Dataverse.

Power Automate Flow

Finally, it’s time to do some coding. I will demonstrate how to handle updates on records in the target entity, but you can expand this flow to cover adding and deleting rows. You can also create separate flows for new rows and row deletion. I prefer the latter approach, as it is easier to debug and maintain.

Flow Type and Trigger

We start with creating an ‘Automated Flow’ with ‘When a row is added, modified or deleted’ Dataverse connector as the trigger.

For now, we are only interested in modification so we set ‘Change type’ to ‘Modified’, ‘Table name’ will be our main entity while ‘Scope’ must be set as ‘Organization’ to cover all changes done to the records, no matter who made them.

We will also limit our trigger to only those columns we want to track changes on, by listing them by their logical names under ‘Select columns’.

Further read

Variables

Before going further, let’s initialise some variables:

  • arrColumns; Array
  • strChanegs; String
  • strOld; String
  • strNew; String
  • strEmpty; String

All of them should be empty in the moment of initialisation, except for arrColumns. Let’s take a closer look at this one.

You will need to create an array with metadata about all the columns on which you want to log changes. It will be needed later to loop through all the changes you get from your http request.

The object in your array will be a JSON of this structure:

{

 "logical_name": "",

 "display_name": "",

 "type": "",

 "related_table": "",

 "related_column": ""

}

Where:

  • logical_name — logical name of your column
  • display_name — display name of your column
  • type — column type
  • related_table — logical name of the related table (optional, dependent on column type, used for lookups and choices)
  • related_column — logical name of the main column from the related table entity (optional, dependant on column type, used for lookups only)

Further read

So, for my logs, this array will look like this:

[

 {

  "logical_name": "anowak_name",

  "display_name": "Name",

  "type": "Text",

  "related_table": "",

  "related_column": ""

 },

 {

  "logical_name": "anowak_organization",

  "display_name": "Organization",

  "type": "Choice",

  "related_table": "anowak_organization",

  "related_column": ""

 },

 {

  "logical_name": "anowak_leader",

  "display_name": "Leader",

  "type": "Lookup",

  "related_table": "anowak_cast",

  "related_column": "anowak_name"

 },

 {

  "logical_name": "anowak_joined",

  "display_name": "Joined",

  "type": "Date", "related_table": "",

  "related_column": ""

 },

 {

  "logical_name": "statecode",

  "display_name": "Status",

  "type": "Status",

  "related_table": "",

  "related_column": ""

  }

]

At this point, your flow should look like this:

 

Access the Key Vault

If you try to use environmental variables of the ‘Secret’ type, you will quickly find out that they are not on the list of dynamic content. To access the key vault as well as retrieve client ID and secret, you will need to perform an unbound action.

Further read

To do it, you’ll create ‘Perform an unbound action’ action. Let’s set ‘Action Name’ to ‘RetrieveEnvironmentVariableSecretValue’ and in the EnvironmentVariableName field, provide the logical name of variable that stores your Client ID.

Next, you’ll need to go to ‘settings’ and turn on ‘Secure Outputs’ to make sure that your secrets won’t be exposed in the flow run history.

Then, you need to repeat this process for the Client Secret. Now, these 2 actions should be at the end of your flow.

Http Request Is Your Friend.

It’s time to make use of the Web API to get details about the latest change on the record.

You’ll begin by composing a http request to retrieve the latest audit log ID which you will later use to get details about the specific audit log.

Let’s unpack this.

Your request starts with the environment URL. At the beginning, we created a variable to store this URL in case you’d like to move this solution to a different environment. So, let’s utilise dynamic content and retrieve the value from your variable.

@{parameters('EnvironmentURL (anowak_EnvironmentURL)')}

Next, you will request service via Web API for all audits and filter it by a unique identifier of the main entity.

(anowak_EnvironmentURL)')}api/data/v9.0/audits?$filter=_objectid_value eq @{triggerOutputs()?['body/anowak_castid']}

Finally, let’s arrange the audits by ‘created on’ property in descending order and limit them to include only the latest. In the end, the request should look like this.

@{parameters('EnvironmentURL(anowak_EnvironmentURL)')}api/data/v9.0/audits?$filter=_objectid_value eq @{triggerOutputs()?['body/anowak_castid']}&$orderby=createdon desc&$top=1

With that in place, you can create your http request action.

We are using the ‘GET’ method, the URI will be outputs of the ‘Compose’ action in the previous step and you will authenticate this request with ‘Active Directory OAuth’ where:

Tenant — tenant ID stored in the variable

Audience — environment URL from the variable

Client ID — result of the unbound action to retrieve client ID from Key Vault

@{outputs('Perform_an_unbound_action_-_get_client_id')?['body/EnvironmentVariableSecretValue']}

Secret — result of the unbound action to retrieve secret from Key Vault

@{outputs('Perform_an_unbound_action_-_get_client_secret')?['body/EnvironmentVariableSecretValue']}

Even though, you’re retrieving only a single audit log, you will still get JSON with an array including just one object. To get your Audit ID, we need to first retrieve this singular value.

This is the schema of the response you’ll get from this request:

{

    "type": "object",

    "properties": {

        "@@odata.context": {

            "type": "string"

        },

        "value": {

            "type": "array",

            "items": {

                "type": "object",

                "properties": {

                    "_objectid_value": {

                        "type": "string"

                    },

                    "_userid_value": {

                        "type": "string"

                    },

                    "operation": {

                        "type": "integer"

                    },

                    "createdon": {

                        "type": "string"

                    },

                    "auditid": {

                        "type": "string"

                    },

                    "changedata": {

                        "type": "string"

                    },

                    "attributemask": {

                        "type": "string"

                    },

                    "action": {

                        "type": "integer"

                    },

                    "objecttypecode": {

                        "type": "string"

                    },

                    "transactionid": {

                        "type": "string"

                    },

                    "_regardingobjectid_value": {},

                    "useradditionalinfo": {},

                    "_callinguserid_value": {},

                    "versionnumber": {}

                },

                "required": [

                    "_objectid_value",

                    "_userid_value",

                    "operation",

                    "createdon",

                    "auditid",

                    "changedata",

                    "attributemask",

                    "action",

                    "objecttypecode",

                    "transactionid",

                    "_regardingobjectid_value",

                    "useradditionalinfo",

                    "_callinguserid_value",

                    "versionnumber"

                ]

            }

        }

    }

}

 

 

The expression you should use is:

last(body('HTTP_-_get_audit_id')?['value'])?['auditid']

With that said, it is time to compose another request, this time to retrieve the details of said audit log.

As previously, you’ll with environment URL. Then, you’ll build a query using the Web API to filter audits by audit ID you got from the previous Http request. To do that, you will use the expression above. Finally you can ask for details of said audit.

@{parameters('EnvironmentURL(anowak_EnvironmentURL)')}api/data/v9.0/audits(@{last(body('HTTP_-_get_audit_id')?['value'])?['auditid']})/Microsoft.Dynamics.CRM.RetrieveAuditDetails()

With the new request ready, you will again use HTTP action in a similar matter as previously. The only difference will be the URI, which will be outputs of the latest ‘Compose’ action.

Let’s look at the example of the response:

 

{

  "@odata.context": "https://org4a297f94.crm.dynamics.com/api/data/v9.0/$metadata#Microsoft.Dynamics.CRM.RetrieveAuditDetailsResponse",

  "AuditDetail": {

    "@odata.type": "#Microsoft.Dynamics.CRM.AttributeAuditDetail",

    "InvalidNewValueAttributes": [],

    "LocLabelLanguageCode": 0,

    "DeletedAttributes": {

      "Count": 0,

      "Keys": [],

      "Values": []

    },

    "OldValue": {

      "@odata.type": "#Microsoft.Dynamics.CRM.anowak_cast"

    },

    "NewValue": {

      "@odata.type": "#Microsoft.Dynamics.CRM.anowak_cast",

      "anowak_joined": "2006-12-08T23:00:00Z"

    }

  }

}

Responses will differ based on the changes that were made to the record. In this example, you can see that a new value was assigned to the ‘Joined’ column, which previously was empty.

 

Now, what’s left is to get a list of old and new values from the response.

body('HTTP_-_get_audit_log')?['AuditDetail']?['OldValue']

body('HTTP_-_get_audit_log')?['AuditDetail']?['NewValue']

Further Read

And with that, I’d like to conclude the third part of this guide. In the next part, I will show you how to work with different column data types and log changes in your custom audit solution.

2988 HC Digital Transformation 476X381
Amadeusz Nowak Software Developer

He is a Power Platform developer with over 5 years of experience. Outside of office hours, Amadeusz is an amateur football analyst and a PC building enthusiast.

See all Amadeusz's posts

Related posts

You might be also interested in

Contact

Start your project with Objectivity

CTA Pattern - Contact - Middle