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", AaaLogin.NAME "Login Name", SDOrganization.NAME "Site",AaaLogin.DOMAINNAME "Domain Name" 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 SiteDefinition ON dept.SITEID=SiteDefinition.SITEID
LEFT JOIN SDOrganization ON SiteDefinition.SITEID=SDOrganization.ORG_ID
LEFT JOIN RegionDefinition ON SiteDefinition.REGIONID=RegionDefinition.REGIONID
LEFT JOIN POApproverDetails ON SDUser.USERID=POApproverDetails.APPROVER
WHERE SDUser.STATUS='ACTIVE'
ORDER BY 1
The below query works from Builds Over 14000 pgsql / mssql
SELECT AaaUser.FIRST_NAME "First Name",
adef.org_name "Account Name",
sdo.NAME "Site",
DepartmentDefinition.deptname "Department Name",
AaaContactInfo.EMAILID "E-mail",
al.name "Login Name",
al.DOMAINNAME "Domain Name",
sduser.jobtitle 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
WHERE SDUser.STATUS='ACTIVE'
adef.org_name "Account Name",
sdo.NAME "Site",
DepartmentDefinition.deptname "Department Name",
AaaContactInfo.EMAILID "E-mail",
al.name "Login Name",
al.DOMAINNAME "Domain Name",
sduser.jobtitle 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
WHERE SDUser.STATUS='ACTIVE'
New to ADSelfService Plus?
Related Articles
Query report to extract Active Technician Login and IP Address (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports- New Query Report and execute this query. select DISTINCT ac.ACCOUNT_ID as "Login Account ID", au.FIRST_NAME as "Technician Name ( First Name )", count(acs.SESSION_ID) as "Number of ...Query to list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...Query to get the user details (both technicians and requesters) account-wise with User ID (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Tested in builds : 14000, 14201, 14301 1) Query to get the First Name, Last Name, Display Name, Login User ID, Login Name, Email and Phone Number of all users in the application, account-wise, with the ...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", ...Query to show technician created time (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT AaaUser.FIRST_NAME "Technician Name", aci.emailid "Email ID", LONGTODATE(AaaUser.createdtime) "Technician Created Time", SDUSER.STATUS "Status(Active/Inactive)" FROM AaaUser left ...