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",
[Free e-book] The handbook for MSPs to achieve profitability
Hello folks, We're excited to share with you our latest e-book that focuses on nine critical metrics for MSPs to achieve profitability in challenging economic times. In this e-book, you'll discover metrics that can give you multiple perspectives into
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 Open status timespent calculation
Version : 10609 DB : MSSQL OUTPUT : SELECT "wo"."WORKORDERID" AS "Request ID", sisd.STATUSNAME as "Changed From", sinsd.STATUSNAME as "Changed To", case when si.NEXTSTATUSID is NULL THEN cast(datediff(d , dateadd(s,datediff(s,GETUTCDATE() ,getdate())
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",
Query to get Request Details with Group change info and Technician Timespent info
Build: 10609 Database: MSSQL OUTPUT : SELECT "wo"."WORKORDERID" AS "Request ID", LONGTODATE("wo"."CREATEDTIME") AS "Created Time", LONGTODATE("wo"."RESOLVEDTIME") AS "Resolved Time", "cri"."FIRST_NAME" AS "Created By", "aau"."FIRST_NAME" AS "Requester",
Query to get SLA escalation time difference for each request.
Version:10609 DB:PGSQL OUTPUT: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Open date", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", LONGTODATE(wos.LAST_TECH_UPDATE)
Query to get Project details and Worklog timespent details of Project
Version : 10602 DB: MSSQL Output : Query : 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"
Query to get WorkLog timespent For Request and Project
Version:10500 DB:PGSQL SELECT ad.ORG_NAME AS "Account", COALESCE(wtk.WORKORDERID , wo.workorderid) "Module ID", to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time",'Request' "Module", CONCAT(COALESCE(TRUNC(sum(ct.TIMESPENT)/3600000,0)),'
Worklog Queries for various modules
Worklog Queries Database : PGSQL Request Charges SELECT COALESCE(wtk.WORKORDERID , wo.workorderid) "Request ID", CASE WHEN (tk.TASKID) IS NOT NULL THEN (wo2.TITLE) ELSE (wo.TITLE) END "Title",cast((ct.TIMESPENT/1000 * interval '1 second') as varchar) "Time Spent",to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time", rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct LEFT JOIN WorkOrderToCharge wotoc ON ct.CHARGEID=wotoc.CHARGEID LEFT JOIN WorkOrder wo ON wotoc.WORKORDERID=wo.WORKORDERID
Receiving the twice the report email from the schedule.
Hi , We are receiving twice email with one attaching the report subject "Report" and other with no report to attach subject "No data available to generate report" . Please suggest any fix for this.
[Free e-book] Kick-start hyperautomation with AI-powered service management
Did you know that you can save anywhere between 10-50% of IT costs with automation? Also, employees can save between 10-40% of their time by automating manual, laborious, and repetitive tasks. IT service processes are abundant with such repetitive tasks
[Free e-book] 5 reasons why your analytics initiatives are failing and how to fix them using AI
Gartner predicts that 80% of analytics initiatives will fail by 2022. Don't let your business initiatives be one of them. Download our e-book to learn how AI-driven analytics can put your organization on the fast track to success.
Need a Technician and Group combined report
Hi I need a report that lists all technicians and also every group they belong to. So a simple report. A Technician would be listed on multiple lines if they are in more than one Support Group: Technician Name Support Group Technician Name
[Free e-book] Six strategies to eliminate costly inefficiencies in your IT service processes
An organization's IT department is its powerhouse. However, IT can still be affected by service outages, service performance issues and inefficiencies. Discover how advanced analytics for IT service management can provide visibility into IT services,
Report of Time Spent for tasks
We would like to know if is possible to have a report of timespend on tasks. We need the following fields: Task ID Timespend Technician Our DB is MSSQL and build is 10530.
Survey Report Total Count
I'm looking for a way to get a total count of surveys sent so I can compare that to surveys received. I have made a report which can give me all the information I need regarding surveys that were responded to but nothing on how to find out how many surveys
Report of technician last login and quantity of login
Hi I wonder if we can have a report (SDP MSP) that shows the last login of the technicians configured and alslo the quantity of logins performed during a period of time (month, quarter and year). Thanks in advance.
Next Page