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 "Status(Active/Inactive)",longtodate(MAX(AaaAccSession.OPENTIME)) "Last Logged In Date/Time" from PortalTechnicians pu
left join AaaUser au on pu.userid=au.USER_ID
left JOIN SDUser ON au.USER_ID=SDUser.USERID
left join aaausercontactinfo auci on auci.user_id = SDUser.userid
left join aaacontactinfo aci on aci.contactinfo_id = auci.contactinfo_id
left join AaaLogin al on au.USER_ID=al.USER_ID
left join AaaAccount aa on al.LOGIN_ID=aa.LOGIN_ID
LEFT JOIN AaaAccSession ON aa.ACCOUNT_ID = AaaAccSession.ACCOUNT_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') group by au.first_name,ar.name,al.name,aci.emailid,sduser.status
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 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 ...Technician Roles
This report is used to get the roles of the technicians. For version 10.5 and below SELECT au.FIRST_NAME "Technician", (ar.NAME) "Role" FROM HelpdeskCrew hdc LEFT JOIN SDUser sd ON hdc.TECHNICIANID=sd.USERID LEFT JOIN AaaLogin al ON ...Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...