Query to show technician created time

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
inner JOIN HelpDeskCrew ON SDUser.USERID=HelpDeskCrew.TECHNICIANID
LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
LEFT JOIN Queue_Technician qt on qt.TECHNICIANID=AaaUser.USER_ID
LEFT JOIN QueueDefinition qd ON qt.QUEUEID=qd.QUEUEID
left join aaausercontactinfo auci on auci.user_id = SDUser.userid
left join aaacontactinfo aci on aci.contactinfo_id = auci.contactinfo_id
LEFT JOIN aaaaccount aaa on aaa.login_id=AaaLogin .login_id 
WHERE (SDUser.STATUS = 'ACTIVE') group by AaaUser.FIRST_NAME, aci.emailid, sduser.status,  AaaUser.createdtime


For builds greater than 10.6 please use below query:

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
inner JOIN portaltechnicians ON SDUser.USERID=portaltechnicians.userID
LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
LEFT JOIN Queue_Technician qt on qt.TECHNICIANID=AaaUser.USER_ID
LEFT JOIN QueueDefinition qd ON qt.QUEUEID=qd.QUEUEID
left join aaausercontactinfo auci on auci.user_id = SDUser.userid
left join aaacontactinfo aci on aci.contactinfo_id = auci.contactinfo_id
LEFT JOIN aaaaccount aaa on aaa.login_id=AaaLogin .login_id 
WHERE (SDUser.STATUS = 'ACTIVE') group by AaaUser.FIRST_NAME, aci.emailid, sduser.status,  AaaUser.createdtime

        New to ADManager Plus?

          New to ADSelfService Plus?

            • Related Articles

            • Query to list the custom and query reports and the technician created

              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 LEFT JOIN customreport_details custrepdet ...
            • Query report to extract Active Technician Login and IP Address

              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 Logins", LONGTODATE(max(acs.OPENTIME)) as "Last Login ...
            • Query to show time spent by each technician in a ticket (MSSQL & PGSQL )

              Tested in Build PGSQL (14300) or MSSQL (14306) Navigate to Reports->New Query Report and execute this report. Under Help->About, check the database you are using and use the appropriate query. MSSQL: SELECT wo.WORKORDERID "Request ID",wo.TITLE ...
            • Query to show tickets created based on shift time_PGSQL

              Filter mentioned by converting into minutes. PGSQL: 7.30 AM - 7.30 PM SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) AS "Created Time", extract(hour from ...
            • Query to find the time taken to assign a field

              Use case The query will pull a report of when an additional field was first assigned a value DB: MSSQL Query  Please replace additional field name with the Field Name Here. SELECT "wo"."WORKORDERID" AS "Request ID",  "ad"."ORG_NAME" AS "Account",  ...