Dynamics 365 User Access Report with FetchXml and Power BI

ByRob Peledie

Dynamics 365 User Access Report with FetchXml and Power BI

Ok, so that’s not the snappiest of titles, but essentially it’s my lazy way of covering a couple of topics in one blog post:

  1. Need to report on Licensed Users and When they Last Accessed Dynamics
  2. Showing that in a Power BI Report using FetchXML

It’s worth noting at this point, how fantastic the Dynamics 365, and generally the Microsoft community is. Most of what I did has already been done and blogged about for free. I’ve tried to reference everyone who’s blog posts helped me.

So the first thing I needed was some fetch to give me the last logged in (or Access in Audit entity terms) for users.  I got the following from www.mscrmsolution.com but could have hand crafted in FetchXML Builder in XrmToolbox

<fetch aggregate='true' >
    <entity name='audit' >
    <attribute name='createdon' alias='LastAccessTime' aggregate='max' />
        <condition attribute='operation' operator='eq' value='4' />
        <condition entityname='su' attribute='isdisabled' operator='eq' value='0' />
    <link-entity name='systemuser' from='systemuserid' to='objectid' alias='su' >
        <attribute name='systemuserid' alias='SystemUserId' groupby='true' />
        <attribute name='domainname' alias='DomainName' groupby='true' />
        <attribute name='fullname' alias='FullName' groupby='true' />

Couple of things worth noting here. Operation = 4 which is the Access record in the Audit (as opposed to Update, Create etc)

So at this point, I had the first piece done, as all I initially wanted was a way to see how long it was since users logged in. To get a quick view I just pasted the results in Excel and used a formula to calculate the days.

What I actually wanted to do was create a Power BI report that would connect to Dynamics, and use this FetchXml dynamically.

Ulrik Carlsson (CRMChartGuy) has a great post on this, and all credit to him for the following. (You may want to read his post in entirety, as I have summarized some bits)

Firstly open up your Power BI desktop, and choose Get Data –> Web

When this opens choose the Advanced option

So there a few steps here to get the connection working:

  1. The Service Root URL that you would otherwise use in Power BI https://MYCRMORG.api.crm.dynamics.com/api/data/v9.1/
  2. The PLURAL name of the entity schemaname follow by ?fetchXml=
    Example: audits?fetchXml=
  3. The FetchXML above, encoded as a URL (You can get this encoded at 
    https://www.freeformatter.com/url-encoder.html )
  4. Type in “Prefer” – it is not option you can select
  5. Exactly type: odata.include-annotations=”OData.Community.Display.V1.FormattedValue” (Important that you type in the Quote marks to avoid formatting issues)

Click OK

Open up the Query Editor

Click on “To Table” in the convert area, then click on the expand button (two arrows pointing away from each other)

And there you have it…..A live power BI report showing users access.

I’m not going to go in to how to “Pretty” the report up, I’ll leave that to you! 

The process above would be the same for any FetchXml query, and if you don’t need to hand craft the Fetch, then you can start with an Advanced Find, and export it out.

About the author

Rob Peledie administrator

1 Comment so far

Dynamics 365 User Access Report with FetchXml and Power BI – 365Knowledge – Dynamics365BlogsPosted on11:32 am - Mar 6, 2019

[…] Dynamics 365 User Access Report with FetchXml and Power BI […]

Leave a Reply