Trying to build a flow that will pull back the last time users have logged into Dynamics.

[Dan_B]

Hello PA community 🙂

I've been tasked with trying to report on the times that all of our user base have last logged into Dynamics.

I found a video on youtube, which I have partly used, and works up to a point.

There are only two connections in use, which both have a green tick, and they are Microsoft Dataverse, and Excel online.

The reason for excel, is that I thought it made sense to write the information straight into a spreadsheet - this is located on a sharepoint site that I have referenced in the flow.

We start with a list rows, and this works fine:

The youtube video has the filter IsLicensed eq yes, but it immediately errored with a syntax error that I could not get around, so I generated the fetchxml instead which seems to have the same effect.

Next we have an Add a row into a table within an Apply to each:

It basically never moves from the first record, and sits there trying to run, with no errors that I can see.

IF that had worked, the next two steps are, List rows present in the table:

Then we have a 2nd list rows, within an apply to each which interrogates the Audit table, the Action eq 64 indicates web access has occurred.

And then a sort by underneath.

Finally putting it all together into the one sheet:

Can anyone see why it is stalling so early on, or have any suggestions for different approaches I could take - as the initial data gathering part from the Users table is working correctly.

Thanks hopefully in advance 🙂

[wskinnermctc]

Could you try the filter islicensed boolean/YN equal to true so don't surround it in quotes.

islicensed

I looked at the dataverse outputs for the User table and it has

"islicensed":true

So the true is not surrounded by " quotes so it is either a number or a true type value.

Dataverse List Rows

I made this and it worked fine.

If I look at the Peek Code for the Dataverse List Rows it looks like:

{
    "inputs": {
        "host": {
            "connectionName": "shared_commondataserviceforapps",
            "operationId": "ListRecords",
            "apiId": "/providers/Microsoft.PowerApps/apis/shared_commondataserviceforapps"
        },
        "parameters": {
            "entityName": "systemusers",
            "$select": "fullname,internalemailaddress,systemuserid",
            "$filter": "islicensed eq true",
            "$orderby": "fullname asc"
        },
        "authentication": {
            "type": "Raw",
            "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
        }
    }
}

[Dan_B]

Thankyou very much for your reply @wskinnermctc , I have some time booked into look at this today, so will implement the above and report back 🙂

[LinnZawWin]

Hi @Dan_B 

Not related to the issue that you are facing but I am just curious about where are you going to retrieve the last time users logged into Dynamics. I only see the fullname, internalemailaddress and systemuserid columns in your query and I am not sure if such information is stored in the User table.

[Dan_B]

Hi @LinnZawWin , you are correct - the audit table is referenced further down in the flow:

Action eq 64 relates to a browser log on event.

Does that answer your question?

It's tested fine in dev, not gotten around to testing in prod yet, but hoping to do so soon.