Technician details
MSSQL query to get the Technician details.
SELECT AaaUser.FIRST_NAME "User NAme",AaaContactInfo.EMAILID "Email", AaaContactInfo.LANDLINE "Phone", SDUser.EMPLOYEEID "Employee ID",SDUser.JOBTITLE "Job Title",AaaLogin.NAME "Login Name", dept.DEPTNAME "Department", sdo.NAME "Site",AaaLogin.DOMAINNAME "Domain Name",
(SELECT STUFF((SELECT AaaRole.NAME + CHAR(10) FROM AaaRole LEFT JOIN AaaRoleToCategory ON AaaRole.ROLE_ID=AaaRoleToCategory.ROLE_ID LEFT JOIN AaaAuthorizedRole ON AaaRole.ROLE_ID=AaaAuthorizedRole.ROLE_ID LEFT JOIN AaaAccount ON AaaAuthorizedRole.ACCOUNT_ID=AaaAccount.ACCOUNT_ID LEFT JOIN AaaLogin aal ON AaaAccount.LOGIN_ID=aal.LOGIN_ID WHERE aal.LOGIN_ID=AaaLogin.LOGIN_ID AND (((AaaRoleToCategory.ROLE_ID IS NULL) AND (AaaRole.SERVICE_ID = 5)) AND ((AaaRole.NAME) NOT LIKE 'SDApprovePO')) FOR XML PATH ('')), 1, 0, '')) 'Role' ,
(SELECT STUFF((SELECT sdo.NAME + ', ', qd.QUEUENAME +char(10) FROM QueueDefinition qd LEFT JOIN Queue_technician qt ON qd.QUEUEID=qt.QUEUEID LEFT JOIN AaaLogin al ON qt.TECHNICIANID=al.USER_ID LEFT JOIN SDOrganization sdo ON qd.SITEID=sdo.ORG_ID WHERE al.LOGIN_ID=AaaLogin.LOGIN_ID FOR XML PATH ('')), 1, 0, '')) 'Group'
FROM AaaUser INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID INNER JOIN HelpDeskCrew ON SDUser.USERID=HelpDeskCrew.TECHNICIANID LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID LEFT JOIN UserDepartment udept On SDUser.USERID=udept.USERID LEFT JOIN DepartmentDefinition dept ON udept.DEPTID=dept.DEPTID LEFT JOIN SDOrganization sdo ON dept.SITEID=sdo.ORG_ID WHERE SDUser.STATUS='ACTIVE'
ORDER BY 1
MYSQL database:
SELECT AaaUser.FIRST_NAME "Name",AaaContactInfo.EMAILID "Email", AaaContactInfo.LANDLINE "Phone", SDUser.EMPLOYEEID "Employee ID",SDUser.JOBTITLE "Job Title",AaaLogin.NAME "Login Name", dept.DEPTNAME "Department", sdo.NAME "Site",AaaLogin.DOMAINNAME "Domain Name",
(SELECT GROUP_CONCAT(AaaRole.NAME SEPARATOR '\n') FROM AaaRole LEFT JOIN AaaRoleToCategory ON AaaRole.ROLE_ID=AaaRoleToCategory.ROLE_ID LEFT JOIN AaaAuthorizedRole ON AaaRole.ROLE_ID=AaaAuthorizedRole.ROLE_ID LEFT JOIN AaaAccount ON AaaAuthorizedRole.ACCOUNT_ID=AaaAccount.ACCOUNT_ID LEFT JOIN AaaLogin aal ON AaaAccount.LOGIN_ID=aal.LOGIN_ID WHERE aal.LOGIN_ID=AaaLogin.LOGIN_ID AND (((AaaRoleToCategory.ROLE_ID IS NULL) AND (AaaRole.SERVICE_ID = 5)) AND ((AaaRole.NAME) NOT LIKE 'SDApprovePO')) ) 'Role' ,
(SELECT GROUP_CONCAT(CONCAT(sdo.name, ' , ', qd.QUEUENAME) SEPARATOR '\n') FROM Queue_technician qt LEFT JOIN QueueDefinition qd ON qt.QUEUEID=qd.QUEUEID LEFT JOIN AaaLogin al ON qt.TECHNICIANID=al.USER_ID LEFT JOIN SDOrganization sdo ON qd.SITEID=sdo.ORG_ID WHERE al.LOGIN_ID=AaaLogin.LOGIN_ID ) 'Group' ,
CASE WHEN POApproverDetails.APPROVER!=0 THEN 'Yes' ELSE 'NO' END "PO Approver"
FROM AaaUser INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID
INNER JOIN HelpDeskCrew ON SDUser.USERID=HelpDeskCrew.TECHNICIANID
LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
LEFT JOIN UserDepartment udept On SDUser.USERID=udept.USERID
LEFT JOIN DepartmentDefinition dept ON udept.DEPTID=dept.DEPTID
LEFT JOIN SDOrganization sdo ON dept.SITEID=sdo.ORG_ID
LEFT JOIN POApproverDetails ON SDUser.USERID=POApproverDetails.APPROVER
WHERE SDUser.STATUS='ACTIVE'
ORDER BY 1
Regards,
Stephen
SELECT AaaUser.FIRST_NAME "User NAme",AaaContactInfo.EMAILID "Email", AaaContactInfo.LANDLINE "Phone", SDUser.EMPLOYEEID "Employee ID",SDUser.JOBTITLE "Job Title",AaaLogin.NAME "Login Name", dept.DEPTNAME "Department", sdo.NAME "Site",AaaLogin.DOMAINNAME "Domain Name",
(SELECT STUFF((SELECT AaaRole.NAME + CHAR(10) FROM AaaRole LEFT JOIN AaaRoleToCategory ON AaaRole.ROLE_ID=AaaRoleToCategory.ROLE_ID LEFT JOIN AaaAuthorizedRole ON AaaRole.ROLE_ID=AaaAuthorizedRole.ROLE_ID LEFT JOIN AaaAccount ON AaaAuthorizedRole.ACCOUNT_ID=AaaAccount.ACCOUNT_ID LEFT JOIN AaaLogin aal ON AaaAccount.LOGIN_ID=aal.LOGIN_ID WHERE aal.LOGIN_ID=AaaLogin.LOGIN_ID AND (((AaaRoleToCategory.ROLE_ID IS NULL) AND (AaaRole.SERVICE_ID = 5)) AND ((AaaRole.NAME) NOT LIKE 'SDApprovePO')) FOR XML PATH ('')), 1, 0, '')) 'Role' ,
(SELECT STUFF((SELECT sdo.NAME + ', ', qd.QUEUENAME +char(10) FROM QueueDefinition qd LEFT JOIN Queue_technician qt ON qd.QUEUEID=qt.QUEUEID LEFT JOIN AaaLogin al ON qt.TECHNICIANID=al.USER_ID LEFT JOIN SDOrganization sdo ON qd.SITEID=sdo.ORG_ID WHERE al.LOGIN_ID=AaaLogin.LOGIN_ID FOR XML PATH ('')), 1, 0, '')) 'Group'
FROM AaaUser INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID INNER JOIN HelpDeskCrew ON SDUser.USERID=HelpDeskCrew.TECHNICIANID LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID LEFT JOIN UserDepartment udept On SDUser.USERID=udept.USERID LEFT JOIN DepartmentDefinition dept ON udept.DEPTID=dept.DEPTID LEFT JOIN SDOrganization sdo ON dept.SITEID=sdo.ORG_ID WHERE SDUser.STATUS='ACTIVE'
ORDER BY 1
MYSQL database:
SELECT AaaUser.FIRST_NAME "Name",AaaContactInfo.EMAILID "Email", AaaContactInfo.LANDLINE "Phone", SDUser.EMPLOYEEID "Employee ID",SDUser.JOBTITLE "Job Title",AaaLogin.NAME "Login Name", dept.DEPTNAME "Department", sdo.NAME "Site",AaaLogin.DOMAINNAME "Domain Name",
(SELECT GROUP_CONCAT(AaaRole.NAME SEPARATOR '\n') FROM AaaRole LEFT JOIN AaaRoleToCategory ON AaaRole.ROLE_ID=AaaRoleToCategory.ROLE_ID LEFT JOIN AaaAuthorizedRole ON AaaRole.ROLE_ID=AaaAuthorizedRole.ROLE_ID LEFT JOIN AaaAccount ON AaaAuthorizedRole.ACCOUNT_ID=AaaAccount.ACCOUNT_ID LEFT JOIN AaaLogin aal ON AaaAccount.LOGIN_ID=aal.LOGIN_ID WHERE aal.LOGIN_ID=AaaLogin.LOGIN_ID AND (((AaaRoleToCategory.ROLE_ID IS NULL) AND (AaaRole.SERVICE_ID = 5)) AND ((AaaRole.NAME) NOT LIKE 'SDApprovePO')) ) 'Role' ,
(SELECT GROUP_CONCAT(CONCAT(sdo.name, ' , ', qd.QUEUENAME) SEPARATOR '\n') FROM Queue_technician qt LEFT JOIN QueueDefinition qd ON qt.QUEUEID=qd.QUEUEID LEFT JOIN AaaLogin al ON qt.TECHNICIANID=al.USER_ID LEFT JOIN SDOrganization sdo ON qd.SITEID=sdo.ORG_ID WHERE al.LOGIN_ID=AaaLogin.LOGIN_ID ) 'Group' ,
CASE WHEN POApproverDetails.APPROVER!=0 THEN 'Yes' ELSE 'NO' END "PO Approver"
FROM AaaUser INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID
INNER JOIN HelpDeskCrew ON SDUser.USERID=HelpDeskCrew.TECHNICIANID
LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
LEFT JOIN UserDepartment udept On SDUser.USERID=udept.USERID
LEFT JOIN DepartmentDefinition dept ON udept.DEPTID=dept.DEPTID
LEFT JOIN SDOrganization sdo ON dept.SITEID=sdo.ORG_ID
LEFT JOIN POApproverDetails ON SDUser.USERID=POApproverDetails.APPROVER
WHERE SDUser.STATUS='ACTIVE'
ORDER BY 1
Regards,
Stephen