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

问题描述

通过选择获取数据"，选择"odata"选项并使用url/api/data/v9.1/audits，我已经能够从Dynamics 365中提取审核表并将其加载到Power BI中.我看到了列RetrieveAuditDetails，但是我不明白为什么所有值都表示 Function .有没有一种方法可以扩展它以显示旧值/新值，就像您可以更改的方式一样，例如将UserID扩展为全名?

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?

推荐答案

对于审核数据，由于审核数据存储为定界值，因此OData/Web API REST端点在PowerBI中不太友好.数据库. 在此SO线程中引用我的答案.

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.

如果是javascript或.net应用程序，则可以使用RetrieveAuditDetails函数进行迭代调用，以在使用https://crmdev.crm.dynamics.com/api/data/v9.1/audits获取完整列表后获取完整详细信息.这就是为什么您在其中看到功能的原因.

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.

例如:

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);
    }
);

更新: 经过进一步分析-上面针对单个auditid的RetrieveAuditDetails查询与下面针对单个recordid的过滤后的audits查询的输出模式之间没有太大差异.

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

事实是Web api或fetchxml，结果集无法获取包含已更改字段值的重要列changedata-由于以下限制:Retrieve can only return columns that are valid for read. Column : changedata. Entity : audit

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

我在FetchXML构建器中得到了这个

I get this in FetchXML builder:

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

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屋！