Power Automate is one of the components of the Power Platform which is a great tool for automating processes.
With its ability to make use of the numerous connectors available, this means that it is able to connect
to the common data service to retrieve information such as user details and audit information.
So, how do we get the list of users within your Power Platform instance and their last login date?
First of all, for auditing of users needs to be switched on in the Power Platfom instance.
This is done by getting into Advanced Settings.
This can be done by clicking on the cog in your model driven app or in the Power Apps portal.
Once auditing is enabled, ensure that the user which is going to run the Power Automate flow is assigned a security role which allows access to view the audit partitions.
Then it’s on to the Power Automate flow.
This is a flow which is great to be set up as a scheduled flow. So, using the Power Apps portal create a scheduled flow.
This flow will need to connect to the PowerPlatform instance to retrieve data from the user and the audit entities.
In order to do this, the Common Data Service connector will be used.
We need to retrieve the users and so we use the List records trigger and select the users entity.
This is the standard display name for users but this could have been renamed according to an organisations’ requirements.
So, we can loop through the users to get the information needed about the users such as full name, email address etc.
While looping through the user records, we then want to query the audit entity to find the last login date.
In order to do this, the audit entity must be filtered to find the event for user login i.e. action = 64 (user access) and userid of the user.
As we want the last time the user logged in, then we use Top 1 and order the retrieved audit records by created date (i.e. createdon) desc.
As we want to create a csv file, we can use the “Create CSV Table” connector and this allows us to populate the date using an array within which we can decide what fields we want to display.
Then we can send the csv file created via email.
So, there you go, a scheduled flow to send a “csv” file containing a list of users and their last logged in date via an email using Power Automate.
Gotchas
As there are times, the user may not have ever logged in, we can created a variable to store the last logged in date and when looping around the audit records, resetting this variable.
The list records action only returns 513 records by default. So, to overcome this paging has to be enabled.
To do this, we go to settings property on the CDS connector and turn Pagination “on”.
