Query to Generate Report of all Technicians in SDP (MSSQL)
I just wanted this to be listed here in the Pitstop, for future reference, and in case others might need it
(Running SDP On-Prem v14.6):
(Running SDP On-Prem v14.6):
select au.FIRST_NAME as "User Name",
al.NAME as "Login Name", dd.DEPTNAME as "Department Name",
sdo.NAME as "Site Name", sdu.JOBTITLE as "Job title",
creby.FIRST_NAME as "Reporting Manager", stuff( ( select distinct ','
+ ar."name" from PortalTechnicians pt1 left join aaalogin al on
pt1.USERID = al.user_id left join aaaaccount ac on al.login_id = ac.login_id
left join sdauthorizedrole sdr on ac.account_id = sdr.account_id left join aaarole
ar on sdr.role_id = ar.role_id where pt.USERID = pt1.USERID for XML PATH('') ),
1, 1, '' ) as "Assigned roles", stuff( ( select distinct ',' +
qd.queuename from portaltechnicians pt2 left join queue_technician qt on
pt2.userid = qt.technicianid left join queuedefinition qd on qt.queueid =
qd.queueid where pt.userid = pt2.USERID for XML PATH('') ), 1, 1, '' ) as
"Assigned group" from PortalTechnicians pt left join aaauser au on
pt.USERID = au.USER_ID left join SDUser sdu on au.USER_ID = sdu.USERID left
join aaalogin al on au.USER_ID = al.USER_ID left join userdepartment ud on
au.user_id = ud.userid left join departmentdefinition dd on ud.deptid =
dd.deptid left join SiteDefinition sd on dd.SITEID = sd.SITEID left join
SDOrganization sdo on sd.SITEID = sdo.ORG_ID left join aaauser creby on
sdu.created_by = creby.user_id where sdu.STATUS = 'ACTIVE';