How to get the Last Login Date of MS Dynamics CRM| Login Date of CRM users Using Power Automate Flow

hello friend welcome back to my channel CRM Cloud Tech so today we are bringing for you a very good solution in power automatic flow related to Dynamics CRM so here today we will uh okay uh we will uh try a solution that contain a scenario that we can uh we will extract the last login date of the user of the CRM so most of the companies or organization have use a sales licenses uh Dynamics licenses to use the dynamic CRM but those license are very expensive so we need to maintain those licenses or the users who are really using or who are not really using sometimes it happens because user has access the CRM and user consuming the licenses but since long time he is not using the CRM instances so that facility will help us to track users latest login data records or if they are not used we can ask them to if they are not using and we can ask the feedback of them and we can remove the license if then or not needed so here we will discuss how we can achieve those functionality so here first we need to ensure that the some settings from the CRM set so this is our source environment in which we will try that setting that scenario so first we need to ensure that the auditing should be enabled so we will check that auditing should be enabled so let's check so see I have already enabled auditing for this environment you need to tick all the check box here so now uh what we will do how we will make that scenario so what we will uh so for that what actually be required so actually we required users data what the type of data we required we required username user email for review purpose and the most important we require the user ID which is the system user ID of the user in the CRM system okay so first we will collect those data and then we will collect the audience login date of the user last login date how we will achieve that scenario we will start so first I will create a new instant flow so instead flow I'm creating because of that I can manually trigger the flow but whenever I want so so that's why I'm taking this uh flow as I type manually so I'm just giving the name this is class login date CRM okay so now I have created a flow manual flow you can also create a schedule flow so that every in every month of uh and or maybe quarterly I mean after 15 days you can run out of that flow on schedule basis so every 15 days or maybe in every month it will automatically I will trigger on the sub specific time and you will get the data so I have right now for the learning purpose I have choose the instant flow so let's see how it will work okay for yeah meanwhile when it is loading if we require access sheet also where we will save the data okay so I have created a extra sheet in one drive this is the Excel sheet in there I have created table you can directly create a table here by clicking here uh just you need to give the name uh header and now select after select here just you need to click here table automatically will create the Excel sheet should have a table to uh store the data in Excel okay so I have created four Fields over full name email system idea in the last date column that is in my UI Drive so our flow is created and now we will add a step here so first what we will do we will extract the username full name and system ID from the table system user table from the CRM so for that we required connector we can select that user table because our whatever user we have in CRM will be available from the user table and The Logical name for this table is system user okay so we can select the data column whatever we required instead of selecting all the columns so for so I have pasted three columns here full name and it doesn't email address and system ID this is for image this is for full name and this is for ID the unique identifier for the user in the CRM okay then it is completed now so what we will do we will add these data in Excel and also the list will contain all the rows of the user with three column but we don't require all the rules because that might contain system user related by the system by default the CRM what we require we required that some uh license user so we can pass the we can make the filter is license equal to true and then our query is done so we can so always remember just give the naming condition properly if you got working in an organization get the this stock license users from CRM user table entity in serum we called it entity okay so now we receive the data now what we will do we will uh store the data in that Excel so add rows into Excel we will take a connector so we will add a row into the Excel so in which action we will select the Excel from here I have saved in one drive then I need to select the document Library OneDrive and the file which I have created this one and the table I told you that we should have a table available in that Excel this is a table table name one okay now you can see my all the parameter or the field names or the columns whatever inside in my table will be displayed here so I can easily map those data which is coming from this connector so in full name I want the full name and in email I want primary email and in system ID I got from a system ID also this so you can if you have any doubt why this columnars which are these columns so you can also understand by here so we have selected three columns only internal images and so sorry if you want to know about this column details just move the cursor on this column and you will get his name field name the full name is here now is also for the email internal images and the user is system user ID which we have selected here right so now we got the data so now we got this details so scenario is now we have received a data we have we have a list of data now we will receive three three columns okay now we want the data last of hinted so what we will do again so to ah update the list this list in the Excel we should first we will get the data from the Excel okay you can also make uh here also directly into the Excel you can select the you can make a character selecting the list of the user from audit also but now I am just for easy purpose I'm just separating is from the this apply to it so now I will get the data from the list get a Row from myself you can give any name or just it's my point of view to get the connected name so I am just this row which are in the table want to choose this connector I will again select the list and this time I have the I have the data in my list okay so I have that data in my list in this time I got the data so now uh I have now I will get the data from audit table for that we have received we have enabled the auditing for to receive the audit data from the audit entity we also we will also use here list rows so list row will give all the data from The Entity ordered but this time we need to make the filter for because in audit is in audit in audit table what types of data it contains it contains whenever we access the CRM it also record whenever we create any record in CRM maybe in account the contact opportunity whatever we create so it also required we delete it also record whenever we update anything we do in Excel in CRM it also every time it records so but we don't want all the data we want whenever the user is accessed the CRM okay for that we will go the audit entity uh just little bit we will read about the audit entity table in the CRM so here you can see that uh audit entity type so these are the parameter which are which contain in our history field you can consider the field extend object I have created on change data auditory so create it on created date and time when the audit uh when the audit record was created suppose so you have created a record today and okay then it will create a it will record that date here you have access the CRM it will access record the date of the CRM that you have access CRM today okay whenever you perform any operation it will also require record that operation what operation have been done create update delete access absurd as you whatever action is done this type of value you will need to pass that transition whatever version number user info data or will be present so here what we required so you can see ah here so what we require we require ah the data the when the user is accessed the CRM so here you can see the action name action the user can perform the cause of the change because the cause of the change means whenever the audit is record okay whenever the audit a record is created in audit means cause of the change event okay so whether the create event is happened upgrade even you have DVD this stuff is even is happened but we want access whenever user access the CRM so there is a event called 64 user access via web so this is the event we need to get the data okay so how we will achieve we will select the column right this time I am not selecting here first I am just here action equals to which action 64. so now here x n equals to 64. so it may it means it will select all the rows from the audit table audit entity which has action 64. okay so there might be hundreds of user and they might be access CRM 100 times or maybe in day the excess CRM 5.3 506 times okay so every time audit table will record the session so we got the action but we want only one action that is the last recorded okay but for the specific user Okay so here uh we have to make the filter here extend and another field will be object ID object ID is the system user ID in the order table so object ID object ID will be the system user ID matching okay from the user table so here we receive system ID then we will match the record match the system ID in the audit SD audit entity to get the record of the user correct user so and action is 64 and object value equals to which ID the ID which received from the system ID from the Excel here you can see I am selecting the ah data from the Excel not from the list Row from the here so here you can also getting the this of user from Excel this could be any other data source because you have saved data in Excel I you can save also in teams list you can share save data in SharePoint also you can this this this this might be some other database like SQL in third party that for that you need to use the connector but now for the because XL is useful for everyone so you can whenever you store the data Excel every one can use in your office maybe some management they might not know about the SQL or other resources so Excel they uh definitely they know about that so here you can see you can give the name get the data or they from audit okay so here we have make the filter so it is same like a SQL filter so it means select all the data from order table we are actually equals to 64 and object ID it means ID of the user because in an order table this is also there is also field called name objectivity but this is lookup field so every time if we are using liquid lookup field we should pass underscore value until it will not work okay so our liquid value of the I system we are getting from Excel that okay so now we got the data but this section gets all the row of suppose uh the my leukopadies one two one one two three okay now it will what it will do it will fetch all the data which has action 64 of this this user so I might ah access the CRM 100 times maybe two day ten times yesterday 450 PM maybe just give it number 110 so it will calculate all the data which is connected to my user action 64 all the data suppose I got the 100 rows but I want the latest data and the one record I want only one date so what we will do is sort by latest data we I show you that there is a field created on which will capture the record so sort by created on descending order so this is the parameter for descending so what it will do it will select all the 100 row by descending order so the last date will be in top but now I want only one row so it will give the one row for me so it will give the date of the one record which is the latest created so here now I got the data now what I need to do I just need to update the Excel again with the date so update Excel update Row in Excel which Excel again I am selecting the same Excel and now I will select the file and the table so now this time I am updating the data in Excel so I need to pass the parameter on behalf which I am making the changes so these are the columns from the Excel I will choose a matching column and here the matching column will be and this is the from order table I can pass this record value also because this is not my ID here you can see the object ID value or I can also pass this ah system ID from Excel also so what now I have the data in Excel full name email and which what data we don't have this one so now I will pass this one also which I am getting from audit table so that is the change date changed it change Theta and the change state this is the one here you can see the created on now I my flow is completed here I will again give the naming conversation uh updating a date in Excel data based on music now my flow is completed here you can see now we will run the flow and we will try to see the what exactly we got here we got some invalid connection space there foreign it doesn't allow dot in naming condition okay now my flow is saved so that let's run this flow and see the magic so I have used the Excel so it will also create a connection reference for the Excel from my flow and the flow is running now let's see the changes in the Excel we don't know we don't need to close or refresh the thing automaticate will be updated so here you can see my data is coming and right now let's see the flow subscription no no yeah it is successfully run and I have two users in my CRM only so right now it is giving the two user of the user records which have licenses so it is like that you can create a flow which can select the you will fetch the audit login history of the CRM user so whoever the user if some some user are not here and who have not login ever so here it will be the blank date according to your situation or business requirement you can manipulate those data you can send this data to business via email also so in this flow that is enough for my site and thank you for watching this video please share and subscribe my channel thank you

Channel: Crm Coud Tech

Views: 1,078

Keywords: msdynamics, lastlogindate, lastlogincrmusers, msflow, powerautomateflow, crmdata, crmlogin, license, azure, msdynamics crm data, msd365crm, power automate, microsoft power automate, power automate tutorial, dynamics 365, power automate dynamics 365 crm, power automate get data from dynamics 365, power platform, power automate tutorial for beginners, power apps, power automate microsoft, power automate rpa, power automate desktop, microsoft power automate tutorial

Id: cRSG7DYv0-c

Length: 23min 32sec (1412 seconds)

Published: Sun Aug 20 2023