从 Dynamics 365 获取审核记录详细信息到 Power BI [英] Getting Audit Record Details from Dynamics 365 to Power BI

问题描述

通过选择获取数据"、选择 odata 选项并使用 url/api/data/v9.1/audits.我看到列 RetrieveAuditDetails，但我不明白为什么所有值都显示 Function.有没有办法扩展它以显示旧值/新值，就像您可以更改一样，例如，将 UserID 扩展为全名?

解决方案

在审计数据方面，OData/Web API REST 端点在 PowerBI 中不是那么友好，因为审计数据是作为分隔值存储在数据库.

还有另一种方法，但无论如何与 PowerBI 不兼容，使用 RetrieveRecordChangeHistory 来定位 recordid 以获取所有带有旧 & 的审计集合新的价值观.示例如下:

https://crmdev.crm.dynamics.com/api/data/v9.0/RetrieveRecordChangeHistory(Target=@Target)?@Target={%22accountid%22:%22449d2fd8-58b8-e911-a839-000d3a315cfc%22,[e-mail]%22:%22Microsoft.Dynamics.CRM.account%22}

I have been able to pull down an audit table from Dynamics 365 and load it into Power BI by selecting Get Data, choosing the odata option and using url/api/data/v9.1/audits. I see the column RetrieveAuditDetails, but I don't understand why all the values say Function. Is there a way to extend this to show the old value/new value in the same way you can change, for example, UserIDs to be extended to the full name?

解决方案

When it comes to audit data, OData/Web API REST endpoint is not so friendly in PowerBI due to the reason that the audit data is stored as delimited values in database. Refer my answer in this SO thread.

If it's a javascript or .net application you can do iterative call using RetrieveAuditDetails function to fetch full details after getting full list using https://crmdev.crm.dynamics.com/api/data/v9.1/audits. This is why you are seeing as Function in there.

For example:

var parameters = {};
var entity = {};
entity.id = "5701259e-59b8-e911-bcd0-00155d0d4a79";
entity.entityType = "audit";
parameters.entity = entity;

var retrieveAuditDetailsRequest = {
    entity: parameters.entity,

    getMetadata: function() {
        return {
            boundParameter: "entity",
            parameterTypes: {
                "entity": {
                    "typeName": "mscrm.audit",
                    "structuralProperty": 5
                }
            },
            operationType: 1,
            operationName: "RetrieveAuditDetails"
        };
    }
};

Xrm.WebApi.online.execute(retrieveAuditDetailsRequest).then(
    function success(result) {
        if (result.ok) {
            var results = JSON.parse(result.responseText);
        }
    },
    function(error) {
        Xrm.Utility.alertDialog(error.message);
    }
);

Update: On further analysis - there is no big difference between the output schema from the above RetrieveAuditDetails query targeting single auditid or the below filtered audits query targeting single recordid.

https://crmdev.crm.dynamics.com/api/data/v9.1/audits?$filter=_objectid_value eq 449d2fd8-58b8-e911-a839-000d3a315cfc

The fact is either web api or fetchxml, the resultset cannot fetch the important column changedata which contains the changed field values - due to the restriction: Retrieve can only return columns that are valid for read. Column : changedata. Entity : audit

I get this in FetchXML builder:

There is another approach but not PowerBI compatible anyway, using RetrieveRecordChangeHistory to target the recordid to get all the audit collections with old & new values. Example below:

https://crmdev.crm.dynamics.com/api/data/v9.0/RetrieveRecordChangeHistory(Target=@Target)?@Target={%22accountid%22:%22449d2fd8-58b8-e911-a839-000d3a315cfc%22,[e-mail]%22:%22Microsoft.Dynamics.CRM.account%22}

这篇关于从 Dynamics 365 获取审核记录详细信息到 Power BI的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！