latest Post

Access "Last Access Date" of each User Directly in CRM.

Since CRM 2011  when Microsoft first offered the ability to Audit User Access, I have been waiting for them to offer a simple OOB solution to be able to view the last Access Date of a user from a field on the user form. Rolling on 7 years to Dynamics365 this is still not possible.

Those users who are experienced in CRM Auditing will know Audit Logs cannot be exported or queried using advanced find meaning if any data manipulation is needed on the logs then a 3rd party tool is required or some custom code to export the logs and be able to manipulate the data.

For years now to be able to check user adoption and monitor user licences I have been exporting the logs to excel using either the XRM toolbox solution or writing a fetchxml query and manipulating the data to get a list of users who havent logged in over a period of time. I have always wanted to get round to automating this process so I could simply view the users last access date directly on user profile and thats exactly what I have done. 

To accomplish this task I first wrote the below FetchXML query. The below query returns all Audit logs of type "User Access via Web" in the below code this is referenced by value 64. For a full list of codes for each type click here.  It then also filters out any logs which either were not created today or is not  created before a passed in date parameter. This is because I want to either run it nightly for only todays logs but also have the option to run it as a one off to search all audit logs for a time period.  Finally it filters out disabled users as I am not interested in updating those users. 

Then using SSIS and the KingswaysoftCRM source connector I downloaded the the records returned from the query and stored them in a local SQL database. I then manipulated the data with a simple SQL query to return the Last Login Date for each user.





I  then created a new custom field in CRM on the OOB user entity "Last Access Date" and added it the form. 


I then used the above SQL Query as my source component in my SSIS package to loop through each user and update the corresponding CRM user record with the last access date.  

Now putting that all together I created the below SSIS package.

SSIS Control Flows 

The below shows the SSIS control flow steps in my SSIS package which starts by truncating the local sql table with the user audit logs.



Data Flow 1

First data flow which queries CRM using the above Fetch query and outputs the data to a local SQL table.


Data Flow 2

Second data flow task which uses the SQL query above as the source and loops through each record and updates the corresponding CRM users last access date field.


Finally once the package has ran we have the last access date field populated. 


This SSIS package is then scheduled to run each night to automatically update the last access date for each user and that's how simple it is to get the last access date for each user updated automatically. 

Now I finally got round to this I bet in the next release its added as standard!! haha :-)
Recommended Posts × +

10 comments:

  1. If you're running a small business, the installation of the computer at the office can put pressure on your pocket, but at TrueTech we understand this problem and deliver you computer rentals services all over in Gurgaon at an economic or affordable price. For more information, you can also visit our website.
    For More Info :- http://www.truetechservices.in/#

    ReplyDelete
  2. Laptop On Rent
    Rent Foxxy offers you electronic gadgets for rent like as laptop, mobile phones, TV and many other of electronic. So get any gadget for rent on your requirement.Rentfoxxy is a rental basis company which is provide laptop on rent in Gurgaon, delhi, noida, and Bangalore.
    For More Info :- https://rentfoxxy.com/

    ReplyDelete
  3. Refurbished laptop in Gurgaon
    Buy refurbished laptops online at best price in Gurgaon. We have wide range of buy refurbished laptops in gurgaon.
    Efoxxy is an ecommerce based company providing genuine certified refurbished products with exceptional service and warranty. We are an authorized seller of affordable and professionally refurbished laptops. We give products at the lowest price across all sites available in the market. All products get analyzed and any defects and are renewed to original working conditions either by our experts or product manufacturer.
    For More Info :- http://efoxxy.com/

    ReplyDelete
  4. I promise to share this testimony all over the world once my boyfriend returns back to me, and today with all due respect I want to thank DR.ONIHA for bringing joy and happiness to my relationship and my family. I want to inform you all that there is a spell caster that is real and genuine. I never believed in any of these things until I lost my boyfriend, I required help until I found a great spell caster, And he cast a love spell for me, and he assured me that I will get my boyfriend back in two days after the spell has been cast. Three days later, my phone rang, and so shockingly, it was my boyfriend who has not called me for the past 6 years now, and made an apology for the heart break, and told me that he is ready to be my backbone till the rest of his life with me. DR.ONIHA released him to know how much I loved and wanted him. And opened his eyes to picture how much we have shared together. As I`m writing this testimony right now I`m the happiest girl on earth and me and my boyfriend are living a happy life and our love is now stronger than how it was even before our break up. So that`s why I promised to share my testimony all over the universe. All thanks goes to DR.ONIHA for the excessive work that he has done for me. Below is the email address in any situation you are undergoing, it may be a heart break, and I assure you that as he has done mine for me, he will definitely help you too.
    EMAIL: ONIHASPELLTEMPLE@GMAIL.COM
    CALL/WHATSAPP : +16692213962.
    Website: http://onihaspells.com. 

    ReplyDelete
  5. Wow, What an Outstanding post. I found this too much informatics. It is what I was seeking for. I would like to recommend you that please keep sharing such type of info.If possible, Thanks. Expert secrets

    ReplyDelete
  6. How To Get Your Ex Lover/Partner/Boyfriend/Girlfriend/Wife Or Husband Back

    I was married for 16 years to a loving mother and wife. We had 2 children together who are now 11 & 13. I reconnected with an old girlfriend from college on Facebook and we began an affair and I left my wife. The woman I had an affair with is a wonderful woman and I love her too and our kids had begun accepting the situation and my wife has kind of moved on, but not in love with the man she is seeing. I thought I fell out of love with my wife and I felt terrible about what I did to her - she is a good woman and I don't know what came over me. I decided to try and get her back and I was recommended to Lord Zakuza for help to get reunited with my wife and within 48 hours after I made contact with Lord Zakuza my wife decided to work things out with me and now we are back together with our children living as one happy family. I really don't know the words to use in appreciation of what Lord Zakuza did for me but I will say thank you sir for reuniting I and my family back. For those in trying times with their marriages or relationship can communicate with LORD ZAKUZA through the below informations.

    Website via: lordzakuzaspells.com
    WhatsApp/call/text: +1 740 573 9483
    Email: Lordzakuza7 @gmail. com

    ReplyDelete
  7. my partner and I have been trying for a baby for over two years now, We were going to a fertility clinic for about 5 months before somebody at baby center told us to contact this spell caster who is so powerful, We contacted him at this email; babaka.wolf@gmail.com or Facebook at priest.babaka , for him to help us, then we told him our problem, he told us that we will conceive once we follow his instructions ,but after two years of trying we were at a point where we were willing to try anything. And I'm glad we came to Priest Babaka, Because his pregnancy spell cast and herbal remedy help us, and I honestly believe him, and his gods really helped us as well, I am thankful for all he has done. contact him via email: babaka.wolf@gmail.com or Facebook at priest.babaka if you are trying to have a baby or want your lover back. he has powers to do it, he has done mine

    ReplyDelete
  8. I am so happy to share this fantastic testimony of mine. My life has changed round completely in 48hours. I can see myself in the mirror and smile. I have confidence in myself and my abilities for the first time in ages. I have my boyfriend back in my life with the help of Dr Oniha. He said sorry 24 hours after the spell was created. He said he realised that he never really wanted to break up and he could not handle emotional baggage I was carrying around all the time. He said that he thought he was the negative influence on my life and thought it best to leave. He said it was hard that I kept talking about the past all the time. We have both forgiven each other. I have money in my pocket that is my own at last. (My compensation cheque came through. I was told it was months away. I am living proof of what Dr Oniha can do. I thank you so much Dr Oniha for the wonderful thing you did in my relationship. I am so happy now. With your help we were both able to see and speak the truth to each other. I never thought a spell could help a couple before. I am very pleased to contact you and initiate all this to happen. Anyone who has a relationship problem can contact Dr Oniha.
    EMAIL:ONIHASPELLTEMPLE@GMAIL.COM
    WEBSITE http://onihaspells.com
    Call/Whatsapp number: +16692213962

    ReplyDelete
  9. I don't know how to explain this.. I am overwhelmed with happiness, You say it and it comes to pass Dr Oniha..OMG! This is more like a miracle. You are a God sent to me..I love you and I will forever testify about your services to other people who are also facing marital problems. You make my whole life a living testimony. My Man is back according to your word. All thanks to you Dr Oniha. I will forever be grateful to you. Bless you! Bless you!! bless you!!! Anyone out there having similar problems in his/her marital home should contact this great spell caster he can be of help. Contact Dr Oniha on his website or email.
    onihaspelltemple@gmail.com
    CALL/Whatsapp number +16692213962
    Website: onihaspells.com

    ReplyDelete
  10. I really want to thank Dr Emu for saving my marriage. My wife really treated me badly and left home for almost 3 month this got me sick and confused. Then I told my friend about how my wife has changed towards me. Then she told me to contact Dr Emu that he will help me bring back my wife and change her back to a good woman. I never believed in all this but I gave it a try. Dr Emu casted a spell of return of love on her, and my wife came back home for forgiveness and today we are happy again. If you are going through any relationship stress or you want back your Ex or Divorce husband you can contact his whatsapp +2347012841542 or email emutemple@gmail.com website: Https://emutemple.wordpress.com/ Https://web.facebook.com/Emu-Temple-104891335203341

    ReplyDelete