Query to show Timesheet details (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306)
MSSQL:
select ts.id "TimeSheet ID", sdu.firstname "Technician Name", LONGTODATE(ts.starttime) "Timesheet Start Date", LONGTODATE(ts.endtime) "Timesheet End Date", CONVERT(varchar, ((ts.totaltime)/1000) / 3600) + ':' + RIGHT('0' + CONVERT(varchar,((ts.totaltime)/1000) % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar, ((ts.totaltime)/1000) % 60), 2)
"Total Time Spent", tsd.name "Approve Timesheet Status" from Timesheet ts LEFT JOIN timesheetstatusdefinition tsd ON ts.statusid=tsd.id LEFT JOIN SDUser sdu ON ts.technicianid=sdu.userid
PGSQL:
select ts.id "TimeSheet ID", sdu.firstname "Technician Name", LONGTODATE(ts.starttime) "Timesheet Start Date", LONGTODATE(ts.endtime) "Timesheet End Date",cast((ts.Totaltime)/1000 * '1 second'::interval as varchar) "Total Time Spent", tsd.name "Approve Timesheet Status" from Timesheet ts LEFT JOIN timesheetstatusdefinition tsd ON ts.statusid=tsd.id LEFT JOIN SDUser sdu ON ts.technicianid=sdu.userid
New to ADSelfService Plus?
Related Articles
Contract and Service Plans details - Query Report (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: select ac.contractno "Contract Number", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name",sp.plantype "Service Plan Type", sp.timeperiod "Bill ...Query to show Problem - Task Report with Problem Details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Attached the sample query output. Query: SELECT prob.PROBLEMID AS "Problem ID", prob.TITLE AS "Title",catadef.CATEGORYNAME AS "Category", longtodate(prob.CLOSEDTIME) AS "Closed Date", ...Query to get the login failed attempt details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports tab > New Query Report, use the below query to get the login failed attempts details, select principal "User Name", LONGTODATE(timestamp), HOSTNAME, OPERATIONNAME from AuditRecord ar LEFT ...Query to show workstation's hard disk details (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT Max(workstation.workstationname) "Workstation", Max(workstation.servicetag) "Service Tag", Max(workstation.model) "Model", Max(osinfo.osname) "OS", MAX(memInfo.TOTALMEMORY/(1024*1024*1024)) AS ...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 ...