SQL Queries (SQL4CDS) – Dataverse / Dynamics 365

  1) Get the list of table with audit enabled -

SELECT   logicalname,

         displayname,

         isauditenabled

FROM     metadata.entity

WHERE    isauditenabled = 1

ORDER BY logicalname;

2) Get the list of fields per table with audit enabled -

SELECT   entitylogicalname,

         logicalname AS columnname,

         displayname,

         isauditenabled

FROM     metadata.attribute

WHERE    isauditenabled = 1

         AND entitylogicalname IN (SELECT logicalname

                                   FROM   metadata.entity

                                   WHERE  isauditenabled = 1)

ORDER BY entitylogicalname, columnname;







3) Get the total number of activity records by different activity type - 




SELECT   activitytypecodename,

         activitytypecode,

         Count(activitytypecode) AS Total

FROM     activitypointer

GROUP BY activitytypecode, activitytypecodename

ORDER BY Total DESC;







4) Get the Time Zone information of all the users -




SELECT   su.fullname,

         su.domainname,

         us.timezonecode,

         tz.userinterfacename,

         tz.standardname

FROM     usersettings AS us

         INNER JOIN

         systemuser AS su

         ON us.systemuserid = su.systemuserid

         LEFT OUTER JOIN

         timezonedefinition AS tz

         ON us.timezonecode = tz.timezonecode

ORDER BY su.fullname;







5) Get the list of cloud flows where a specific field is referred / used -




SELECT wf.name,

       wf.workflowid,

       wf.clientdata

FROM   workflow AS wf

WHERE  wf.category = 5

       AND LOWER(wf.clientdata) LIKE '%columnName%';







6) Get the list of Business Rules in the environment -







SELECT   primaryentity,

         primaryentityname,

         workflowid,

         workflow.name AS BusinessRuleName,

         workflow.ismanaged,

         statecode,

         statecodename,

         categoryname

FROM     workflow

         INNER JOIN

         entity

         ON workflow.primaryentity = entity.objecttypecode

WHERE    category = 2

ORDER BY primaryentity;







7) Get the list of Plugin Registration Steps where a particular attribute is used in the Image -




SELECT

    spi.sdkmessageprocessingstepimageid,

     s.name AS StepName,

    spi.name AS ImageName,

    spi.imagetype,

    spi.attributes,

    s.name AS StepName,

    m.name AS MessageName,

    e.name AS EntityName

FROM

    sdkmessageprocessingstepimage spi

INNER JOIN

    sdkmessageprocessingstep s ON spi.sdkmessageprocessingstepid = s.sdkmessageprocessingstepid

INNER JOIN

    sdkmessagefilter f ON s.sdkmessagefilterid = f.sdkmessagefilterid

INNER JOIN

    sdkmessage m ON f.sdkmessageid = m.sdkmessageid

INNER JOIN

    entity e ON f.primaryobjecttypecode = e.objecttypecode

WHERE

    spi.attributes LIKE '%columnName%'

ORDER BY

    EntityName, MessageName







8) Get the list of Security Role and total number of users assigned that role -




SELECT   r.name AS RoleName,

         COUNT(DISTINCT sur.systemuserid) AS AssignedUsers

FROM     systemuserroles AS sur

         INNER JOIN

         role AS r

         ON sur.roleid = r.roleid

GROUP BY r.name

ORDER BY AssignedUsers DESC;




9) Get number of security roles assigned per user -




SELECT   u.systemuserid,

         u.fullname,

         COUNT(sur.roleid) AS RoleCount

FROM     systemuser AS u

         INNER JOIN

         systemuserroles AS sur

         ON u.systemuserid = sur.systemuserid

GROUP BY u.systemuserid, u.fullname

ORDER BY RoleCount DESC;







10 ) Get the list of security roles assigned to a particular user - 

SELECT

    su.systemuserid,

    su.fullname AS UserName,

    r.roleid,

    r.name AS RoleName,

    r.businessunitidname AS BusinessUnit

FROM systemuser su

INNER JOIN systemuserroles sur

    ON su.systemuserid = sur.systemuserid

INNER JOIN role r

    ON sur.roleid = r.roleid

where sur.systemuserid = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx'







11) Get the list of users and security roles assigned to them-




SELECT

    u.systemuserid,

    u.fullname AS UserName,

    u.domainname AS UserDomain,

    u.isdisabled AS IsDisabled,

    u.businessunitidname AS BusinessUnit,

    STRING_AGG(r.name, ', ') AS SecurityRoles      

FROM systemuser u

INNER JOIN systemuserroles ur

    ON u.systemuserid = ur.systemuserid

INNER JOIN role r

    ON ur.roleid = r.roleid

WHERE u.isdisabled = 0

  AND u.accessmode = 0              -- Only interactive users

  AND u.domainname NOT LIKE '#%'    -- Exclude system/app users

GROUP BY

    u.systemuserid, u.fullname, u.domainname, u.isdisabled, u.businessunitidname, u.accessmode

ORDER BY u.fullname;