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:
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' /> <filter> <condition attribute='operation' operator='eq' value='4' /> <condition entityname='su' attribute='isdisabled' operator='eq' value='0' /> </filter> <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' /> </link-entity> </entity> </fetch>
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.
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:
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.