Birkaç kullanışlı Dataverse / Dynamics 365 SQL Sorgusu – 2

Ortamdaki İş Kuralları listesini sorgulama :
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;

Image olarak belirli bir özelliğin kullanıldığı Plugin Registration Steps listesini sorgulama :
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 ‘%custom_myfield%’
ORDER BY
EntityName, MessageName

Güvenlik rollerinin listesini ve bu rollere atanmış toplam kullanıcı sayısını sorgulama :
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;

Kullanıcı başına atanmış güvenlik rolü sayısını sorgulama :
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;

Belirli bir kullanıcıya atanmış güvenlik rollerinin listesini sorgulama :
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 = ‘415a2261-d9b4-ea11-a812-000d3a6aaf70’

Kullanıcıların listesini ve onlara atanmış güvenlik rollerini sorgulama :
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;