Author: Nishant Rana

Let us take a scenario, where a data migration package is running, and it either creates or updates (or deletes) a large number of records into Dynamics 365, and we want to get the count of records created/updated/deleted in the last x hour or so.

With views, we are limited to just 5000 records.

One option is to write the console app having the required QueryExpression or FetchXML condition using the Dynamics 365 SDK to get the count.

Or use the Record Counts tool of CDS.Tools

https://xrm.tools/RecordCounts

We can also make use of SSRS reports here.

Create a report using the report wizard, specify the criteria

In the LayOut fields window, specify Count as the summary type for grouping.

Run the report to get the count.

Another option that we have used the most is to use the FetchXML
Builder plugin to build the query, copy it.

And use it in the FetchXML / View Record Counter plugin of XrmToolBox.

Select the entity, contact, in this case, paste the FetchXML query and click Execute Count.

We will get the count.

Along with FetchXML / View Record Counter we can also use
SQL 4 CDS plugin.

Within the FetchXML builder click on Edit in SQL 4 CDS button.

The result

And now with CDS T-SQL endpoint (preview), we can use SQL Server Management Studio as well to directly write the T-SQL instead of fetch xml

https://nishantrana.me/2020/05/21/setting-up-using-sql-to-query-data-in-dynamics-365-preview/

We can also use the CDS T-SQL endpoint within the SQL 4 CDS Plugin.

https://markcarrington.dev/2020/05/24/sql-4-cds-2-1-0-the-t-sql-edition/

What if we want total record count for an Entity?

Apart from all the methods above,

• we can use Record Counter XrmToolBox Plugin for that

https://www.xrmtoolbox.com/plugins/AndyPopkin.RecordCounter/

• or use RetrieveTotalRecordCountRequest
  

https://dreamingincrm.com/2019/07/22/getting-entity-record-counts/

• or use Count aggregrate function
  

https://docs.microsoft.com/en-us/previous-versions/dynamicscrm-2016/developers-guide/gg309565(v=crm.8)?redirectedfrom=MSDN#count

however, it will fail in case of more than 50000 records

‘AggregateQueryRecordLimit exceeded. Cannot perform this operation.’

To increase the limit (on-premise)- https://nishantrana.me/2012/09/06/aggregatequeryrecordlimit-exceeded-cannot-perform-this-operation/

• Will returntotalrecordcount property of FetchXML help ?
  

It will be helpful if total records are less than 5000.

Hope it helps..

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).

https://admin.powerplatform.microsoft.com/analytics/d365ce

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’ />
<filter>
<condition attribute=’operation’ operator=’eq’ value=’4′ />
</filter>
<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’ />
</link-entity>
</entity>
</fetch>

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

SELECT su.fullname,
su.domainname,
su.userlicensetype,
su.accessmode,
su.isdisabled,
max(a.createdon) AS LastLoginDate
FROM audit AS a
INNER JOIN
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,
su.domainname,
su.userlicensetype,
su.accessmode
FROM systemuser AS su
WHERE su.systemuserid IN (SELECT systemuserid
FROM systemuser
EXCEPT
SELECT DISTINCT objectid
FROM audit
WHERE operation = 4);

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

Update – 8 Jun 2021- Just checked it- Audi Table is available in SQL 4 CDS so ignore the below 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..