Get User’s last logon time in Dynamics 365

We can get the different metrics about the usage of the platform like active user usage, the operation performed, the entity used, plugins and API statics, etc. through Command Data Service Analytics (formerly Organization Insights).

These reports can also be downloaded.

Some of these details can also be captured by enabling Audit user access.

And navigating to Audit Summary View

This view can be filtered to show only the User Access via Web event to get the last logon details for the users.

We can use below FetchXML query to get the same details

<fetch mapping=’logical’ aggregate=’true’ version=’1.0′ >
<entity name=’audit’ >
<attribute name=’createdon’ alias=’LastLoginDate’ aggregate=’max’ />
<condition attribute=’operation’ operator=’eq’ value=’4′ />
<link-entity name=’systemuser’ from=’systemuserid’ to=’objectid’ alias=’su’ link-type=’inner’ >
<attribute name=’fullname’ alias=’fn’ groupby=’true’ />
<attribute name=’domainname’ alias=’dn’ groupby=’true’ />
<attribute name=’userlicensetype’ alias=’ult’ groupby=’true’ />
<attribute name=’accessmode’ alias=’am’ groupby=’true’ />
<attribute name=’isdisabled’ alias=’id’ groupby=’true’ />

We can also run the following SQL Query (in case of on-premise) to get the details

SELECT su.fullname,
max(a.createdon) AS LastLoginDate
FROM audit AS a
systemuser AS su
ON su.systemuserid = a.objectid
WHERE a.operation = 4
GROUP BY su.fullname, su.domainname, su.userlicensetype, su.accessmode, su.isdisabled;

Now there could be some users who have never accessed the application, to get details of those user we can use the below query

SELECT su.fullname,
FROM systemuser AS su
WHERE su.systemuserid IN (SELECT systemuserid
FROM systemuser
FROM audit
WHERE operation = 4);

We can also use the wonderful User Audit Viewer XrmToolBox Plugin for getting the user audit details.

Can we use the new SQL Data Connection for CDS (preview) to query Audit information ?

We cannot as the Audit Table is not available


Hope it helps..

Setting up – Using SQL to query data in Dynamics 365 (Preview)

Recently we were trying out the preview feature of using SQL to query CDS data.

Below are the steps à

To get started,

Download or open SQL Server Management Studio (18.4 or later)

Use Azure Active Directory authentication to connect.

Specify the organization address URL followed by port 5558 in the server name.

If you get the error “TDS protocol endpoint is disabled for this organization”, follow the below steps to enable the TDS i.e. Tabular Data Stream.

Download the OrgDBOrgSettingsTool i.e. CRM2016-Tools-KB4046795-ENU-amd64

Make the following changes in the configuration file of the tool

Highlighted in green

Run the below command (& enter the password for the admin account specified)

Microsoft.Crm.SE.OrgDBOrgSettingsTool Update /u <org-unique-name> EnableTDSEndpoint true

Get the organization unique name from Customization à Developer Resources

After making the above changes, we were able to connect successfully.

This is a definitely one of the most pleasing additions to the product !

Also check out the wonderful XrmToolBox plugin SQL 4 CDS


Skyvia Query 

for running SQL query against Dynamics 365.

Hope it helps..

Data Migration in Dynamics 365 – Key considerations

I was recently watching the FastTrack TechTalks on Data Migration.

Below are some of the key points from the session à

Customer 360 View is more often than not, the starting point of the data migration discussions. Storing all the data in Dynamics 365 might not be a good idea as it could impact the performance and more importantly, the storage has a cost associated with it.

Learn more about the storage in Power Platform here

Important Considerations as part of the Data Migration Strategy

Data Source

  • What is the source of the migration? Is it a single system or multiple systems from which the data needs to be consolidated.
  • The appropriate connectors or technology needed to fetch or extract the data from the source system(s). (SQL, File-based, Web Service, etc.)
  • Need for a staging database for transformation and cleansing of data before migrating it to Dynamics 365.
  • Having a well-defined Dynamics 365 data dictionary and model.

Data Cleansing

  • Removal of the old data.
  • De-duplicate of the data.
  • Checking for null, missing required fields and references.
  • Identify and define the uniqueness of the record.
  • Need for defining Alternate keys

Data Transformation (mapping with Dynamics 365 entities and fields)

  • Transform the data to adapt to the target Dynamics 365 Data model.
  • Format and Range of the fields.
  • Calculate or combination of different fields.
  • Filtering and Enriching of the fields

Mapping OptionSet in KingswaySoft SSIS Integration Toolkit for Dynamics 365

Mapping OptionSet in Scribe Online

Lookup and Cache Transformation component in KingswaySoft SSIS Integration Toolkitfor Dynamics 365

Data Loading

  • Is it one-time migration (plus delta load daily) or n times migration
  • Based on the time it takes, the batch can run over the weekend or after office hours daily.
  • Order of data load – Master \ Configuration Data à Parent records àChild records.
  • Use Create or Update whenever possible instead of Upsert.
  • For Inactive records, first, we need to create those records as active, associate any child records to it if any, before updating the status of the record.
  • Use multithreading and execute multiple requests.
  • Figure out the optimum batch size and thread.

Optimum batch size

For Upsert in KingswaySoft SSIS Integration Toolkit for Dynamics 365

For Delete in KingswaySoft SSIS Integration Toolkit for Dynamics 365


How many records are to be migrated and how much time will it take.

Storage / Scalability

Do we need all the data to be stored in Dynamics 365, or for Customer 365, can we leverage Power BI reports which could pull data from different data sources?

Do we need all the data for all the entities or last x months?

Compliance and regulations

We need to consider what information is allowed to be stored/accessed by the users in Dynamics 365.

Other key points

  • Use WebAPI for migration.
  • Use Execute Multiple for batch operations.
  • Leverage Application User that provides Server to Server authentication.
  • To update CreatedOn field use OverriddenCreatedOn field.
  • Use Create OnBehalfOf / CallerID for Created By field.
  • Map ModifiedBy and ModifiedOn to custom fields for the historical purpose.
  • Check for the sequence of the auto-number fields in the entities.
  • If possible for improved performance – during migration disable duplicate detection, workflows, plugins, audit, activity feeds, etc.
  • Leverage the same location/region for running the package as the Dynamics 365.
  • For faster date-time conversions set regional settings to UTC for the service account being used.

Check out the below articles

Hope it helps..

Dynamics CRM On-Premise to Online Migration Program – Microsoft FastTrack

Customers planning to migrate to the cloud should check out the below session, as part of FastTrack TechTalks, on Dynamics CRM On-Premise migration to Online.

Highlighting some of the main points

  • FastTrack Service is only available for eligible customers. Check the eligibility requirements below

  • The customer \ partner will be guided and assisted by FastTrack Solution Architect as part of the FastTrack Program.
  • Supported SQL Server version – 2008, 2012, 2014, 2016.
  • Supported CRM On-Premise – 2011, 2013, 2015, 2016, 9.0.

High-level steps of the program include

  • Setting up the migration project in Microsoft Lifecycle Services, which uses the wizard-based hosted tool for the migration.
  • Setting up Azure subscription and storage account (for which customer needs to pay along with the Dynamics 365 Subscriptions) and provision staging sandbox environment.
  • Taking the backup of the CRM On-Premise database and uploading it to Azure Blob Container.

    The backup is restored in the same version in Azure Hosted Virtual Machine i.e. CRM 2015 database will be restored in CRM 2015 VM.

  • PowerApps Checker service runs as part of the next phase, to validate the solution.
  • Finally, the Upgrade process starts, which upgrades the restored CRM Database to CRM 9.0 Database. E.g. CRM 2015 Backup will be restored in CRM 2015 VM, which will be then upgraded to CRM 2016 VM, and then finally to CRM 9.0 VM.
  • This is followed by user-mapping and the migration to the online as the last step.

Here, the customer/partner needs to make sure to transform all SQL Based report to Fetch XML, update the plugin to run in sandbox mode, update JavaScript to the new client object model, check for any 3rd part tools and reconfiguration of integration, make users familiar with new Unified Client interface, cleaning of the database (for audit data), etc to make the solution online compatible. This can be done before restoring to the Azure Storage or during the staging phase (This is customer/ partner responsibility and will be assisted by FastTrack Architect and Microsoft Technical Support team for any upgrade issues).

Hope it helps..

Notes on Dynamics 365 for Phones and Tablets app – Part 12 (Mobile Offline Capabilities and Limitations)

Check the other articles of this series.

In this post, we’d cover some of the limitations and capabilities of Dynamics 365 Mobile App (in offline mode)

  • Add Existing (n-n relationship) is not supported in mobile offline
Mobile App – Online Mobile App – Offline
  • Only System Views are available in offline mode.

“My Custom View” is public view which is available in online mode, but not in offline mode.

Mobile App – Online Mobile App – Offline
  • BPF is not available in offline mode.
Mobile App – Online Mobile App – Offline
  • Custom Ribbon Button are note available in offline mode
Mobile App – Online Mobile App – Offline
  • The mobile offline is only available in iOS and Android phone and tablet app. Below is the screenshot of Tablet App on Windows 10. There is no option for going offline.

  • The offline option in Phones App for Android

  • Business Rules work in Offline mode.
  • Plugins and workflows are not supported in Offline mode.
  • Use Xrm.WebApi.offline to perform CRUD operation offline.

Mobile App – Online Mobile App – Offline
  • Downloading offline updates can take a few minutes to an hour. (The user can still work online while the data is getting synced in the background)

  • Web Resource can be made available offline

Mobile App – Online Mobile App – Offline

The same web resource when defined within the sitemap gives a “No data available” message.

Mobile App – Online Mobile App – Offline

Thus, while designing a solution for Mobile Offline, we need to consider the above behavior of the mobile app.

Hope it helps..

“Enable for mobile” is now “Enable for Unified client”

Just sharing my quick observation –

Enable for mobile”  option for an Entity


is renamed to “Enable for Unified Client


Notes on Dynamics 365 for Phones and Tablets app – Part 11 (Mobile Settings)

Check the other articles of this series

In this post, we’d look at the different settings specific to the mobile app and their effect.

In the mobile app, navigate to Settings – Mobile Settings in Dynamics 365 for Phones app.

Below are the different settings available inside the mobile app.

Let us look at the behavior of each of these properties.

  • Offline Sync

Offline Sync option is only available for iOS and Android Devices

Enabling the offline sync option will start the sync process in the background and will download the data offline for the user. While sync is happening, users can keep working online.

After the offline data is downloaded users can enable the option “Work in offline mode” to work offline.

  • User content and location

This option allows users to take voice notes and attach it as an attachment to notes.

The location details can be used in the getCurrentPosition method of Xrm.Device

  • Camera – Allow Access

It allows the user to take a picture through the camera and attach it to notes as an attachment.

To take Video and attach it to notes, both the below permissions are required

  • User Content and Location
  • Allow Access

  • Camera – Save photo to the library

This will save the picture taken through Camera in the Gallery. (doesn’t need User Content and Location permission)

  • Camera – Photo Resolution

It allows the user to specify the resolution of the picture taken through the camera.

Below were the findings of the size of the picture for different resolutions with different devices.

You can read more about the file size of the pics in mobile apps here

Thus we saw the different mobile settings available in the mobile app and their consequence.

Hope it helps..

Dynamics 365 for Phones and Tablets App posts

Notes on Dynamics 365 for Phones and Tablets app

Using Xrm.Device captureImage method to take a picture and attach it to notes.

Using Xrm.Device pickFile to attach files to notes

Fixed – You do not have any apps in this view error.

Upload files using SharePoint integration in mobile app

Calling SetWordTemplate from the custom ribbon button

Attachment to Notes in the mobile app

Notes on Dynamics 365 for Phones and Tablets app – Part 10 (Data Download Filter in Mobile Offline)

Check the other articles of this series

In the previous posts, we saw how to configure Mobile Offline Profile and how it works for the end-users in the device.

The number of records that can be downloaded for Mobile Offline for a particular entity (and its related entity) can be specified using the Data Download Filter of Mobile Offline Profile Item record.

The total data available offline to the user will depend on Entity (enabled for offline) + Organization Data Filter applied to it + Mobile Offline Profile + Security roles of that user

Navigate to Settings à Mobile Offline à Mobile Offline Profiles

Let us open the Contact Mobile Offline Profile Item that we had specified earlier.

The different options for Data download filter are

  1. All records

Here All records will apply no filtering and all the contact records will be downloaded offline for the configured user.

The one important point to remember is that à

The total entity records (contact in this case) downloaded will be governed by the Organization Data download filter for that entity, what we specify in the mobile offline profile (i.e. data download filter) will be applying further filtering to it.

For contact, the criteria specified is Status equal
to Active.

Organization Data Filter criteria would be modified on less than 10 days for most of the entities by default

So, for All Records, we will have all the active contact records downloaded offline.

On publishing the above mobile offline profile, for users in offline mode, all the active contacts will be available.

And no inactive contacts will be available as shown below.

  1. Other data filter

Through Other data filter, we can specify what records to be downloaded based on ownership.

Here we have just selected Download my records, which should result in only the active (coming from organization download data filter) contacts owned by the user to be downloaded.

Let us publish it.

After successful Publish, let us check the same in the Phone App.

The app shows only the active contact records owned by the user.

Active Contacts view for the same user in Web Application.

  1. Custom data filter

The custom data filter will allow us to define the filter using the Define Profile Item Entity Filter dialog box.

Clicking on Define Filter Rule opens the dialog box for us, wherein we can select the fields of the entity for defining the filter criteria and can group it. We cannot choose related entity fields there.

Here for testing, we have kept the criteria as role equals as the Decision-maker.

Now let us publish the change, and check the data for the user in the mobile app offline.

We just have just one record having a role as the Decision-maker.

Back in the mobile app, the user can see only that one record inside the app.

  1. Download related data only

This option allows us to download related entity data. E.g. with the above contact records, we want to download the related lead records only, for this we will create a new profile item for the lead entity.

Now back in the Contact profile, add an associated offline profile item and specify the Entity-relationship between contact and lead.

Let us publish this new profile.

The user can now select the contact record and can see the related lead record

What happens if we delete the Lead Mobile Offline Profile and only keep the Contact offline profile having the lead association to it?

We’d get the below error

What happens if we keep the contact and lead profile and delete the lead association in the contact offline profile?

This will allows us to publish the profile.

So, for contact we have

  • Org filter as active contacts.
  • Data download filter as – Custom Data filter as Role equals Decision maker.

And for lead we have

  • Org filter as active leads.
  • Data download filter as – Download related data only.

The result – only one contact record with a role as the decision maker will be available for the user.

No associated lead

And no leads

Let us add back the mobile offline profile item association record of the lead in the contact offline profile.

The result à only one active contact record having the role as a decision-maker, and only one associated lead record will be available for the user.

Thus we saw, how we can use Data Download Filter to define filtering, which would specify the total records that would be available to users offline, in the mobile app.

Hope it helps..

Using Xrm.Device pickFile to attach file to notes in Dynamics 365 for Phones and Tablet app

Check the other articles of this series

In the previous post we covered the captureImagemethod of Xrm.Device for taking picture and attaching it to notes.

In this post, we’d look into the pickFile method of Xrm.Device

pickFile works for both Web Client and Mobile Client unlike captureImage which is only works for Mobile Client (Dynamics 365 for Phone and Tablet app)

pickFile method of Xrm.Device allows us to select files from the computer (web client) (through FileDialog box) and images (gallery) in the case of mobile clients. (the exception being Windows Tablet App), which then can be attached to notes.

Here we have added a custom ribbon button named “Pick file and attach” on the Lead entity form.

In the case of Web Client, the File dialog box opens

In the case of Windows Tablet App also, the file dialog box opens.

For Phone App for Android (and iOS), users can select the existing image from the gallery.

On successful attachment of file/image to notes, the user is presented with the below message.

Users can refresh the timeline to see the attached file.

The sample code using the pickFile method

function PickFile(entityId) {

entityId = entityId.replace(/[{}]/g,'');

function (data) {

// attach the uploaded file as attachment to note
var entity = {};
entity.subject = "Sample Subject";
entity.documentbody = data[0].fileContent;
entity.filename = data[0].fileName;
entity.mimetype = data[0].mimeType;
entity.notetext = "Sample Text";

// lead entity sample
entity["objectid_lead@odata.bind"] = "/leads(" + entityId + ")";"annotation", entity).then(
function success(result) {
var newEntityId =;

var alertStrings = { confirmButtonLabel: "OK", text: "File successfully attached to note. Please refersh the timeline." };
var alertOptions = { height: 200, width: 300 };
Xrm.Navigation.openAlertDialog(alertStrings, alertOptions);

function (error) {
Xrm.Utility.alertDialog("Error occured while attaching file to notes. Please try again");

function (error) {
Xrm.Utility.alertDialog("Error occured while picking file. Please try again");

Add the Crm Parameter to pass the GUID of the record to the method.

Thus, using pickFile makes it easy for the end-user to capture and attach the file to notes through a click of a button.

Also check out the wonderful tool Notes Metadata Manager , which provides additional capabilities like adding metadata to the attachments, organise attachments using folder structure, drag and drop attachments, upload multiple attachments etc.

Hope it helps..