Query to get Technician list with associated Project Role info.
Version : 10609 DB : PGSQL OUTPUT : SELECT aaauser.First_name AS "Name", al.name "Login Name", sduser.FIRSTNAME AS "First Name", aaacontact.EMAILID AS "E-Mail", deptTable.DEPTNAME AS "Department", cisite.NAME AS "Site", projro.rolename AS "Project Role",
Query to get Announcement List and info
Version : 13002 DB : MSSQL / PGSQL OUTPUT : select ad.org_name "Account Associated", anno.TITLE "Announcement Title", LONGTODATE(anno.CREATEDDATE) "Created time", LONGTODATE(anno.DATE_TO) "End time" from announcement anno Left JOIN announcementaccountmapping
Query to get Users/Requesters list who has login.
Version : 14200 DB : MSSQL / PGSQL OUTPUT : SELECT AaaUser.USER_ID, AaaUser.FIRST_NAME "FullName", AaaLogin.NAME "LoginName", AaaLogin.DOMAINNAME "Domain", AaaContactInfo.EMAILID "Email" FROM AaaUser LEFT JOIN UserDepartment ON AaaUser.USER_ID = UserDepartment.USERID
Query to get Group hop count of request.
Version : 10524 DB : MSSQL OUTPUT : SELECT MAX("wo"."WORKORDERID") AS "Request ID", Longtodate("wo"."CREATEDTIME") AS "Created Date", "sdo"."NAME" AS "Site", "qd"."QUEUENAME" AS "Current Group", "std"."STATUSNAME" AS "Request Status", count(wohd.columnname)
Query to get Info about request technician change and changed by
Version : 14001 DB: MSSQL / PGSQL OUTPUT : SELECT wo.WORKORDERID "Request ID", (aau.FIRST_NAME) "CREATEDBY", (wo.TITLE) "Subject", (std.STATUSNAME) "Request Status", aau.FIRST_NAME AS "Changed By", auTech.FIRST_NAME as "Tech Changed From", auNextTech.FIRST_NAME
Query to get Template list with fields mapped to it and default values.
Version : 14000 DB : PGSQL OUTPUT : select sd.name "Service Catalog Name",rt.templatename "Template name",fc.field_name "Fields",fc.default_value "Field Value" from requesttemplate_list rt left join servicedefinition sd on sd.serviceid = rt.parent_service
Query to get Project and milestone timespent in same query.
Version : 10.6 DB : MSSQL OUTPUT : SELECT tpr.PROJECTID , MAX(pr.TITLE) "Title", (SELECT SUM(ct.TIMESPENT)/1000/3600 "Total Time Spent" FROM ChargesTable ct LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID LEFT JOIN TaskDetails tk ON tkc.TASKID=tk.TASKID
Query to Request created from Mail and waiting for an Tech Reply.
Version : 14001 DB : PGSQL / MSSQL OUTPUT : SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", ad.ORG_NAME AS "Account" FROM WorkOrder wo LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID LEFT JOIN WorkOrderAccountMapping wam ON wo.WORKORDERID=wam.WORKORDERID
Query to get Request Closure Comments added by requester.
Version : 13000 DB : MSSQL / PGSQL SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", rtdef.NAME AS "Request Type", lvd.LEVELNAME AS "Level", std.STATUSNAME AS "Request Status", wo.CREATEDTIME
Query to get SLA and First Response voilated percentage based on account
Version : 10609 DB : PGSQL / MSSQL OUTPUT : SELECT ad.org_name "Account", count(wo.WORKORDERID) "Total Request", count(case when std.ISPENDING='0' THEN 1 ELSE NULL END) "Completed Requests" , count(case when (wos.ISOVERDUE='1') THEN 1 ELSE NULL END) "SLA
Query to get Change WorkLog
Version : 14001 DB : PGSQL / MSSQL OUTPUT : SELECT ch.CHANGEID "Change ID", ch.TITLE "Change Title", rctd.FIRST_NAME "WorkLog Owner", Longtodate(ct.TS_ENDTIME) "WorkLog END Time", ct.DESCRIPTION, chfd.UDF_DATE2 AS "Signature Date" FROM ChargesTable ct
Query to get Time calculation of request from created time to resolved / closed status
Version : 10.5 DB : PGSQL SELECT wo.WORKORDERID AS "Request ID", case when (wo.completedtime=0) then null else TO_CHAR(((wo.completedtime-wo.createdtime)/1000 || ' second')::interval, 'HH24:MI:SS') end "Completed Date - created Date", case when (wo.resolvedtime=0)
Query to get list of template name, rule name, event, conditions and actions information
Version : 13000 DB : PGSQL OUTPUT: SELECT rd.rulename "Rule Name", rd.usertype "Applies To", rd.jscode "Script", ry.ruletype "Event", rff.fieldname"Field Condition", rcm.condition_string "Condition", rcfv.fieldvalue "field value", raty.actionname "Action
Query to get Active Technician key list with Technician info.
Version : 13004 DB : PGSQL OUTPUT: select au.first_name, techkey.techniciankey, techkey.status from techniciankeydefinition techkey left join AaaLogin al on techkey.LOGIN_ID = al.LOGIN_ID Left join AaaUser au on al.user_id=au.user_id where techkey.status
[Analytics Plus webinar] How analytics-driven IT service management improves employee productivity
Hello folks, We're back with an insightful new webinar on how analytics-driven IT service management can improve employee productivity . Date and time: April 27, 2023 2pm AEST | 10am GMT | 10am PST Why attend this webinar? When faced with a plethora of
Query to get PO Approval Details
Version : 13000 DB : PGSQL / MSSQL OUTPUT: SELECT po.POCUSTOMID AS "PO Number", max(po.PONAME) AS "PO Name", ad.ORG_NAME AS "Account", longtodate(max(po.DATEORDERED)) AS "Created On", (li.name) "Approver Level", (aaa.FIRST_NAME) "Approver Name", (pos1.STATUSNAME)
Query to get complete Change module info with all sub modules
Version : 10524 DB : MSSQL Output : SELECT "chdt"."CHANGEID" AS "Change ID", "chdt"."TITLE" AS "Title", longtodate("chdt"."CREATEDTIME") AS "Created Time", "chdt"."SCHEDULEDSTARTTIME" AS "Scheduled Start Time", "chdt"."SCHEDULEDENDTIME" AS "Scheduled
query to get Technician list of each request with time taken to resolve.
Version : 13004 DB: PGSQL OUTPUT: SELECT wo.WORKORDERID "Request ID", ti.FIRST_NAME "Technician", std.STATUSNAME "Request Status", pd.PRIORITYNAME "Priority", longtodate(wo.CREATEDTIME) "Created Time", adef.org_name "Account", cast((wo.TIMESPENTONREQ/1000
Query to get Request Attachment details with customer / requester info
Version : 13000 DB : PGSQL / MSSQL OUTPUT : select sda.attachmentid AS "Attachment ID", sda.attachmentname AS "Attachment Name", sda.attachmentpath AS "Attachement Path", sda.attachmentsize AS "File Size", sda.description AS "Comments", wo.workorderid
Query to get Response dueby time and dueby time change info
Version : 14001 DB : PGSQL OUTPUT: SELECT ti.FIRST_NAME AS "Technician", wo.WORKORDERID AS "Request ID", ad.ORG_NAME AS "Account", longtodate(wo.CREATEDTIME) AS "Created Time", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", ti.FIRST_NAME AS "Technician",
Worklog time reporting
When you record a worklog time for a task specifically, even though there are hours put against it, it does not count towards billing. You have to put the worklog against the request itself, and not a specific task.
Query to get Solution of particular topic and its first level child topic list and with status filter.
Version : 10524 DB : MSSQL OUTPUT : SELECT solution.solutionid "Solutionid" , kb1.TOPICNAME " Topic", case when kb2.topicname is null then kb1.TOPICNAME else kb2.topicname end "Parent", Solution.TITLE "Subject ", Sol_StatusDefinition.STATUSNAME "Status"
Query to get Contracts Expiring in 30 days
Version : 13000 DB : PGSQL SELECT mcdt.CONTRACTNAME AS "Contract Name", Vendors.NAME as "Vendor Name", longtodate(mcdt.FROMDATE) AS "From Date", longtodate(mcdt.TODATE) AS "To Date", ad.ORG_NAME AS "Account", cst.STATUSNAME AS "Contract Status", cadf.UDF_CHAR1
Query to get Status change info from particular status to others
Version : 10609 DB : MSSQL & PGSQL OUTPUT : SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.createdtime) AS "CREATEDTIME", sisd.STATUSNAME as "Changed From", sinsd.STATUSNAME as "Changed To", aaa.first_name as "Created By" FROM WorkOrder wo LEFT
Query to get Asset Harddisk and software Information
Version : 13000 DB :MSSQL OUTPUT : SELECT MAX("workstation"."WORKSTATIONNAME") AS "Machine Name", MAX("ad"."ORG_NAME") AS "Account", MAX("resource"."ACQUISITIONDATE") AS "Acquisition Date", MAX("resFields"."UDF_CHAR6") AS "Asset Description", MAX("state"."DISPLAYSTATE")
Query to get Due date difference calculation in hours.
Version : 10609 DB : Postgres Output: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", qd.QUEUENAME AS "Group", wo.TITLE AS "Subject", std.STATUSNAME AS "Request Current Status", longtodate(wh.operationtime) AS "Due Date changed",
Query to get Project and worklog detailed information
Version : 13000 DB : PGSQL OUTPUT: SELECT projectdet.PROJECTID AS "Project id", projectdet.TITLE AS "Project NAME", ad.ORG_NAME AS "Account", projectestimations.TOT_TASKHOURS_COST AS "Actual Cost", projectdet.ACTUALENDTIME AS "Actual end", projectestimations.ACTUALTASKHOURS
Query to get First Assigned Tech info including Auto Assign details
Version : 10609 DB : MSSQL & PGSQL OUTPUT : SELECT wo.WORKORDERID "Request ID", Longtodate(max(wo.CREATEDTIME)) "Created Time", longtodate(min(woa.executedtime)) "Assigned time", max(aau.FIRST_NAME) "Requester", max(wo.TITLE) "Subject", max(qd.QUEUENAME)
Query to get Survey info with custom filter
Version : 10609 DB : MSSQL & PGSQL OUTPUT: SELECT wo.WORKORDERID AS "Request ID",longtodate(srm.responsetime) AS "Responded Date", longtodate(srm.SENDTIME) AS "Survey Created Time", smn.SURVEYNAME AS "Survey Name", AaaLogin.NAME AS "Username Received
Query to get Request details with SLA and Group change info
Version : 10524 DB : MSSQL Output: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", reqtl.TEMPLATENAME "Template Name", std.STATUSNAME AS "Request Status", serdef.NAME AS "Service Category", sladefinition.slaname "SLA NAME", convert(varchar(5),((wo.TIMESPENTONREQ))/1000/3600)+':'+convert(varchar(5),(((wo.TIMESPENTONREQ))/1000)%3600/60)+':'+convert(varchar(5),
Query to get Request details with resolution and resolved time calculation.
Version : 10537 DB : Postgres Output: SELECT LONGTODATE(wo.CREATEDTIME) AS "Created Time", wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", cd.CATEGORYNAME
Query to get Software Summary info
Version : 10609 DB : MSSQL & PGSQL OUTPUT : SELECT wssl.SOFTWARENAME AS "Software Name", swmfg.NAME AS "Manufacturer", wmiswui.SOFTWAREUSAGE AS "Software Usage", wsst.SOFTWARETYPE AS "Software Type", wssc.SOFTWARECATEGORY AS "Software Category", wssct.COMPLIANCETYPE
Query to get Asset details
Version : 10609 DB : Postgres OUTPUT: SELECT MAX(workstation.WORKSTATIONNAME) AS "Machine Name", resource.resourcename "Asset Name", MAX(rtype.TYPE) AS "Device Type", citype.TYPENAME AS "Product Type", MAX(net.ipaddress) "IP Address", MAX(workstation.MANUFACTURER)
Query to get Requester details with login info.
Version : 10609 DB : MSSQL OUTPUT: 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", AaaContactInfo.landline, AaaContactInfo.mobile,
Query to get Request details with requester and Notes details
Version : 13001 DB : PGSQL & MSSQL OUTPUT : SELECT "wo"."WORKORDERID" AS "Request ID", "aau"."FIRST_NAME" AS "Requester", "wo"."TITLE" AS "Subject", "ti"."FIRST_NAME" AS "Technician", "pd"."PRIORITYNAME" AS "Priority", "wotodesc"."FULLDESCRIPTION" AS
Query to get Request Details and Date field in Custom Format
Version : 10609 DB : PGSQL OUTPUT: SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", impdef.NAME AS "Impact", urgdef.NAME AS "Urgency", rtdef.NAME AS "Request Type", to_char(from_unixtime(wo.CREATEDTIME/1000),'DD
Query to get Request details and first technician assigned
Version : 10609 DB : MSSQL OUTPUT: SELECT "wo"."WORKORDERID" AS "Request ID",aauTech.FIRST_NAME As "First Technician", CASE WHEN (wo.is_catalog_template) = 'false' THEN 'Incident' ELSE 'Service Request' END "Request Type", "wo"."TITLE" AS "Subject", "mdd"."MODENAME"
Query to get Change Auto closure info
Version:10600 DB: PGSQL OUTPUT: SELECT chdt.CHANGEID AS "change_id", max(stageDef.DISPLAYNAME) AS "Stage", max(statusDef.STATUSDISPLAYNAME) AS "Status", longtodate(max(operationtime)) "Operation time" FROM ChangeDetails chdt LEFT JOIN Change_StageDefinition
Query to get Change approval details and Change role info of one particular role.
Version : 10609 Database : MSSQL OUTPUT: Query to find Change Role ID : select chrol.name,chrolusermap.ROLEID from changeroles chrol LEFT JOIN changeroleusermapping chrolusermap on chrolusermap.roleid = chrol.ID where chrol.name = 'Initimation' Note :
Query to get Service Request Approval Details
Version : 10609 Database : MSSQL Output: SELECT "wo"."IS_CATALOG_TEMPLATE" AS "Service Request", "serdef"."NAME" AS "Service Category", "appStDef"."STATUSNAME" AS "Approval Status", "cd"."CATEGORYNAME" AS "Category", "ti"."FIRST_NAME" AS "Technician",
Next Page