Query to show Technicians' login name, email, role and last login (PGSQL & MSSQL )
Tested in Build PGSQL (14300) or MSSQL (14306)
SELECT AaaUser.FIRST_NAME "First Name",
AaaUser.last_name "Last Name",
adef.org_name "Account Name",
sdo.name "Site Name",
AaaContactInfo.EMAILID "E-mail",
sduser.jobtitle,
ar.name "Role",
AaaContactInfo.landline,
AaaContactInfo.mobile FROM AaaUser
INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID
INNER JOIN portaltechnicians ON SDUser.USERID=portaltechnicians.userid
LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT JOIN AaaLogin al ON AaaUser.USER_ID=al.USER_ID
LEFT JOIN UserDepartment ON AaaUser.USER_ID=UserDepartment.USERID
LEFT JOIN DepartmentDefinition ON UserDepartment.DEPTID=DepartmentDefinition.DEPTID
LEFT JOIN SiteDefinition ON DepartmentDefinition.SITEID=SiteDefinition.SITEID
LEFT JOIN SDOrganization sdo ON SiteDefinition.SITEID=sdo.ORG_ID
LEFT JOIN accountsitemapping asm on asm.siteid=sdo.ORG_ID
LEFT JOIN accountdefinition adef on adef.org_id=asm.accountid
left join AaaAccount aa on al.LOGIN_ID=aa.LOGIN_ID
left join sdAuthorizedRole aar on aa.ACCOUNT_ID=aar.ACCOUNT_ID
left join AaaRole ar on aar.ROLE_ID=ar.ROLE_ID
LEFT JOIN AaaRoleToCategory ON ar.ROLE_ID=AaaRoleToCategory.ROLE_ID
WHERE (AaaRoleToCategory.ROLE_ID IS NULL) and SDUser.STATUS='ACTIVE' ORDER BY 1
New to ADSelfService Plus?
Related Articles
Query to show technicians associated accounts, sites and Support groups - (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) 1.Technicians and associated Accounts/Sites: select aau.User_id, aau.first_name "First Name", sdu.lastname "Last Name", aal.name "Login Name", AaaContactInfo.EMAILID "Email Address",sdu.jobtitle "Job ...Query to take report of all techinician details with last login time (V10.6) (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) The query is compatiable with version 10.6 and above: Query: Select au.FIRST_NAME "Technicianname" , ar.NAME "Role",al.name "Login Name",aci.emailid "EmailID",SDUSER.STATUS ...Query to show the last worklog added in a ticket (PGSQL)
Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(wo.TITLE) "Subject", max(qd.QUEUENAME) "Group", max(ti.FIRST_NAME) "Assigned Technician", CAST(ct.TIMESPENT AS FLOAT)/1000/3600 AS "Time ...Query to list technician name, site, email and login name (MSSQL & PGSQL)
The below PGSQL query doesnt work in builds below PGSQL (14000) ERROR: missing FROM-clause entry for table "helpdeskcrew" Pgsql: SELECT HelpDeskCrew.TECHNICIANID "Technician ID", AaaUser.FIRST_NAME "Technician Name",AaaContactInfo.EMAILID "Email", ...Query to show Last added worklog of a ticket ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", Wo.title "Subject", ...