Technicians
Query to generate the report based on the total number of requests updated by the technicians
Query to generate the report based on the total number of requests updated by the technicians SELECT wo.WORKORDERID AS "Request ID", wo.WORKORDERID AS "Request ID", ad.ORG_NAME AS "Account", sdo.NAME AS "Site", longtodate(wh.operationtime) "Updated ...
Query to get the Login Hours of Technicians (Postgres)
1) To generate the total login hours per technician tested in Builds 14201 (Postgres): select ai as "Account ID", tn as "Technician Name ( First Name )", lh as "Login hours", LONGTODATE(llt) as "Last Login Time", ln as "Login Name", domain as ...
Query to show roles assigned to a technician with created date, status and department name
Database: MSSQL. PGSQL Query: select auser.first_name as "TECHNICIAN", max(sd.employeeid) as "Employee ID", longtodate(auser.createdtime) as "Creation Date", STRING_AGG (arole.name, ',') "Current Assigned Role", max(sd.status) "Current Status", ...
Query to get technician details(domain, associated account, email) from builds: 10600
DB: MSSQL SELECT aau.first_name "First Name", sdu.lastname "Last Name", AaaContactInfo.EMAILID "Email Address", sdo.name "Associated Sites", adef.org_name "Associated Accounts", df.domainname "Domain Name" FROM aaauser aau INNER JOIN aaalogIN aal ON ...
Query to display additional field associations with templates
Use case The reports shows in which templates the created additional fields are associated Query select sd.name "Service Catalog Name", rt.templatename "Template name", fc.field_name "Fields" from requesttemplate_list rt left ...
Query to get Technician Login Count in a month (Total Number of Login made by technician)
Select aaauser.first_name "Technician Name", Count(AaaAccSession.OPENTIME) "Last Logged In Time" FROM AaaUser INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID INNER JOIN HelpDeskCrew ON SDUser.USERID=HelpDeskCrew.TECHNICIANID LEFT JOIN ...
Query to show technician created time
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 JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID ...
Query to show Technicians' login name, email, role and last login_ PGSQL & MSSQL
Query for build: 105 series SELECT AaaUser.FIRST_NAME "Technician Name", aal.name "Login Name", aci.emailid "Email ID", SDUSER.STATUS "Status(Active/Inactive)", aar.name "Role", longtodate(MAX(AaaAccSession.OPENTIME)) "Logged In Time" FROM AaaUser ...
Query to show technicians associated accounts, sites and Support groups - MSSQL
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 Title", sdo.name "Associated Sites",adef.org_name ...
Query to find technicians with login enabled along with sites and account associated. MSSQL & PGSQL
Please go to Reports-New Query reports and execute this query. select aau.User_id,aau.first_name "Technician Name", aal.name "Login Name", aal.Domainname "Domain",sdo.name "Associated Sites",adef.org_name "Associated Accounts" from aaauser aau INNER ...
Query to show technician total tickets and SLA violated percentage.
Go to Reports- New Query Report and execute this query. select aaau.first_name "Tech Name", count (wo.workorderid) "Total No. of Requests/Incidents", count(case when wos.ISOVERDUE='1' THEN 1 ELSE NULL END) "Total Overdue Tickets", sum(CASE WHEN ...
Last Login Time of Technicians
Use Case: The query generates a report with the technician name and his login timestamps. DB: MSSQL & Postgres Query SELECT AaaUser.FIRST_NAME "Technician", MAX(AaaLogin.NAME) "LoginName", MAX(AaaContactInfo.EMAILID) "Email", ...
Query to show technician roles and account associated to him (MSSQL & PGSQL)
Go to Reports-New Query Report and execute this query. SELECT AaaUser.FIRST_NAME "Display name", AaaLogin.LOGIN_ID "Login ID", AaaLogin.NAME "Login Name", AaaLogin.DOMAINNAME "Domain", AaaContactInfo.EMAILID "E-mail", sduser.jobtitle,aar.name ...
Query to show count of tickets in each module per technician (MSSQL & PGSQL)
Go to Reports-New Query Report and execute this query. SELECT 'Request' "Module",aaau.first_name "Technician",count(wo.WORKORDERID) "Request count" FROM WorkOrder wo left join workorderstates wos ON wo.workorderid=wos.workorderid left join aaauser ...
Query to show only SDAdmin users
select first_name "Name",login.name "Login Name",a.name "Role",sdu.status from aaarole a inner join aaaauthorizedrole aa on aa.role_id=a.role_id inner join aaaaccount acc on acc.account_id=aa.account_id inner join aaalogin login on ...
Technician Availability Chart Query Report
Please go to Reports-New Query Report and execute the below query MSSQL SELECT cre.FIRST_NAME "Created By", LONGTODATE(TechUnavailability.CREATEDDATE) "Created Date", avail.FIRST_NAME "Technician on Leave", back.FIRST_NAME "Backup Technician", ...
Technician name, site, email and login name
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 ...
Query for disabling survey link when accessing non login url (or) requires login for filing survey
update globalconfig set paramvalue='true' where parameter like '%Requireslogin%' and category like '%survey%'
Query to show technicians and associated groups_PGSQL
SELECT AaaUser.FIRST_NAME "Technician Name", (sdo.NAME) "Associated Site", array_to_string(array_agg(distinct(qd.queuename)), ',') "Associated Group" FROM AaaUser left JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID inner JOIN HelpDeskCrew ON ...