Report to show last date and time a request was responded too.
Hello I am trying to generate a Quality Assurance Report to show the last date and time a request has been replied too. I do not see the options in the custom report nor do I know the field in which to query The report I need would look like this Request ID, Technician, Requestor, Title, Last date/time requestor rely, Last date/time technician reply status=open, onhold, Group by group then by technician I need to be able to query the last month, then week by week from then on to auto email.
Receiving errors when generating reports
We are unable to create any standard or custom ServiceDesk Plus report. The attached file displays the Error Trace. What are the steps we must do to correct the problem?
Report to show amended Due Dates
Database : PGSQL Query SELECT wo.WORKORDERID "Request ID", max(wo.title)"Subject", longtodate(max(woh.operationtime))"Changed Time" FROM WorkOrder wo LEFT JOIN workorderhistory woh on wo.workorderid=woh.workorderid LEFT JOIN workorderhistorydiff wohd on woh.historyid=wohd.historyid WHERE (wohd.columnname like '%DUEBYTIME%') and ((wohd.prev_value)!='-1') and woh.operationownerid!='1' and ((wohd.current_value)!='-1') and (to_timestamp(wo.createdtime/1000) between date '2013-01-01' and date '2013-12-31')
Report on Due date Changes
Database : MYSQL/MSSQL/PGSQL Query select wo.WORKORDERID "Request ID",TITLE "Title", longtodate(wo.CREATEDTIME) "Created Time",longtodate(wo.COMPLETEDTIME) "Completed Time", MODENAME "Mode", STATUSNAME "Status", aau.FIRST_NAME "Technician", longtodate(woh.OPERATIONTIME) "Operation Time",woh.OPERATION,aau1.FIRST_NAME "Updated By" from WorkOrder wo LEFT JOIN ModeDefinition md ON wo.MODEID = md.MODEID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID = wos.WORKORDERID LEFT JOIN StatusDefinition sd on
Responded By Report
Query Execute the below query under Report tab--> New Query Report Database : PGSQL SELECT distinct wo.WORKORDERID "Request ID",wo.TITLE "Subject",scd.NAME "Sub Category",ti.FIRST_NAME "Technician",longtodate(wo.COMPLETEDTIME) "Completed Time",au2.first_name "Responded By",longtodate(woh.operationtime)"Responded time" FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON
Queries
Account details Query SELECT vd.org_name'Account',aci.LANDLINE'Site Contact number',apa.CITY'CITY',apa.POSTALCODE'Postal Code',apa.state'State',aci.WEB_URL'WEB URL' FROM accountdefinition vd LEFT JOIN sdorgcontactinfo SDCI ON sdci.ORG_ID=vd.ORG_ID LEFT JOIN aaacontactinfo aci ON aci.CONTACTINFO_ID=sdci.CONTACTINFO_ID LEFT JOIN SDOrgPostalAddr sdp ON sdp.ORG_ID=vd.ORG_ID LEFT JOIN AaaPostalAddress apa ON sdp.POSTALADDR_ID=apa.POSTALADDR_ID ORDER BY 1 Site details query SELECT
Priority wise requests count
Query SELECT pd.PRIORITYNAME'Priority',SUM(CASE WHEN std.statusname LIKE 'open' THEN 1 ELSE 0 END)'open requests', SUM(CASE WHEN wos.ownerid IS NULL THEN 1 ELSE 0 END)'Unassigned requests', SUM(CASE WHEN std.ispending=1 AND std.STATUSSTOPCLOCK=1 THEN 1 ELSE 0 END)'In progress Requests', COUNT(wo.workorderid)'Total Requests' FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
Report on requester conversations of a request
Query : SELECT workorder.WORKORDERID "Request ID" , workorder.TITLE "SUBJECT" , longtodate(conversation.CREATEDTIME) "Conversation TIME" , conversation.TITLE "Reply SUBJECT" , conversationdescription.DESCRIPTION "Reply" , aaauser.FIRST_NAME "Replied requester" FROM conversation LEFT JOIN workorder ON (conversation.WORKORDERID = workorder.WORKORDERID) LEFT JOIN aaauser ON (conversation.REQUESTERID = aaauser.USER_ID) LEFT JOIN conversationdescription ON (conversationdescription.CONVERSATIONID
Request -Task report
Query Go to Reports > New Query Report, copy the below query to query editor and run the report. SELECT wo.WORKORDERID "Request ID",LONGTODATE(wo.CREATEDTIME) "Created Time",aau.FIRST_NAME "Requester",wo.TITLE "Subject",std.STATUSNAME "Request Status",ti.FIRST_NAME "Technician",tk.TITLE "Task Title",tstd.STATUSNAME "Task Status", LONGTODATE(tk.SCHEDULEDSTARTTIME) "Task Scheduled Start Time" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
SLA Breach report account wise
Query SELECT ad.org_name"Account", SUM(CASE WHEN sdf.statusid='1' THEN 1 ELSE 0 END) "Count Of Open Request", SUM(CASE WHEN sdf.statusid='3' THEN 1 ELSE 0 END) "Count Of Closed Request", SUM(CASE(isoverdue) WHEN TRUE THEN 1 ELSE 0 END) "Count Of calls Exceeded SLA", SUM(CASE(isoverdue) WHEN FALSE THEN 1 ELSE 1 END) "Count Of calls within SLA" , (SUM(CASE(isoverdue) WHEN TRUE THEN 1 ELSE 0 END)*100)/COUNT(wo.workorderid) "% Calls Exceeded SLA", ((COUNT(wo.workorderid) - SUM(CASE(isoverdue) WHEN
First responded date for Service request
Query SELECT wo.WORKORDERID "Request ID",max(mdd.MODENAME) "Request Mode",max(aau.FIRST_NAME) "Requester",max(qd.QUEUENAME) "Group",max(wo.TITLE) "Subject",max(rtdef.NAME) "Request Type",max(ti.FIRST_NAME) "Technician",max(std.STATUSNAME) "Request Status",longtodate(max(wo.CREATEDTIME)) "Created Time", longtodate(min(nfs.NOTIFICATIONDATE)) "First responded Date" FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID
TimeSpent Report - Pie Chart
Hi everybody I have a small problem/question : when I create a new custom report for TimeSpent for our technicians, or when I use a predefined report, it appears that the chart count the number of requests, and don't sum the time spent on the requests. Is it possible to change this, and could sum the time spent on these requests ? And, if it's possible, how can I make this ? I have searched on this forum, but the calls with this problem have no answer/solution to this problem. Thanks Best Regards
Category and Associated accounts
Query to find categories and its associated accounts Query: SELECT categorydefinition.CATEGORYNAME"Category", (case when accountdefinition.ORG_NAME is NULL then 'ALL Accounts' else accountdefinition.ORG_NAME end)"Associated Accounts" FROM categorydefinition LEFT JOIN categoryaccountmapping ON (categoryaccountmapping.CATEGORYID = categorydefinition.CATEGORYID) LEFT JOIN accountdefinition ON (categoryaccountmapping.ACCOUNTID = accountdefinition.ORG_ID) order by 1 Krishna Bharat ServiceDesk Plus -
Resolved Time query
Database : MSSQL Use the below query under Report tab--> New Query Report SELECT wo.WORKORDERID 'RequestID', max(ti.FIRST_NAME) 'Technician Name', dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + max(wo.CREATEDTIME/1000),'1970-01-01 00:00:00')'Created Time', (case when max(wo.respondedtime)!=0 then dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + max(wo.respondedtime/1000),'1970-01-01 00:00:00') else NULL end) 'Responded Time', (case when max(wo.completedtime)!=0 then dateadd(s,datediff(s,GETUTCDATE()
Report to get Notes added in the request
I would like to pull notes added in the request to a report. Below is the query in which I want the notes to be included. Report 1: SELECT wo.WORKORDERID "Request ID",longtodate(wo.CREATEDTIME) "Created Time",longtodate(wo.DUEBYTIME) "DueBy Time",std.STATUSNAME "Request Status",pd.PRIORITYNAME "Priority",qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",rtdef.NAME "Request Type",cd.CATEGORYNAME "Category",scd.NAME "Subcategory",icd.NAME "Item",aau.FIRST_NAME "Requester",wo.TITLE "Subject",wotodesc.FULLDESCRIPTION
Surveys not answered
Execute the below query under Report tab--> New Query Report select nw.workorderid"RequestID",nof.notificationtitle"Survey Message",nof.notificationtype'Type',au.first_name"Sent To" from notification nof left join notify_workorder nw on nw.notificationid=nof.notificationid left join aaauser au on au.user_id=nof.recipientid where notificationtitle like '%improve%survey%' and nw.WORKORDERID not in (Select WORKORDERID from SurveyPerRequest) Krishna Bharat ServiceDesk Plus - MSP Support team
Help whit report by account and totals
Hi everybody, I need some help to generate the follow combined columns report (this month): Account, Site, Total Time Spent, Total closed resquests, Total % SLA violated I am using MSSQL,
Two queries combined
Hi, I have trying to combine two queries in one. Firstly, I'm looking tickets assigned to specific technician and in the second one, I try to get the data of the field "elapsed time". If separated each one, the are working well so I understand the problem is in the combination. Can anyone help me? Thanks in advance, SELECT wo.WORKORDERID "Request ID",qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",wof.UDF_CHAR2 "Invoiced Society " FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
Simple Matrix with Timespent and Request Type Report
Hello, I need a the report by selecting the Simple Matrix Report with "Request Type" on the top, "Technician" on the side and Sum of "TimeSpent". Is it possible? Technican RT1 RT2 RT3 name_1 01:30 00:30 00:00 name_2 08:30 15:30 10:00 name_3 01:30 00:30 00:00 name_4 01:30 00:30 00:00 name_5 01:30 00:30 00:00 Regards, -Rodrigo EV
Survey Report Account Based
Please use the below query under report tab--> new query report SELECT wo.WORKORDERID "Request ID",ad.ORG_NAME'Account',sdo.NAME "Site",wo.TITLE "Request Title",aau.FIRST_NAME "Requester",pd.PRIORITYNAME "Priority",qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",longtodate(wo.CREATEDTIME) "Request Created Time",longtodate(wo.COMPLETEDTIME) "Completed Time",longtodate(spre.CREATEDTIME) "Survey Created Time",sqdt.QUESTION "Survey Question",sadt.ANSVALUES "Rating", (SUM(sadt.RATING)/(COUNT(sdt.SURANSID)
Reporting: Requests opened today and resolved today
Hi all, Is there a way to get a report that shows me 2 simple pieces of data at the same time - the number of requests opened today, and the number of requests marked "Resolved" today (but may have been opened at any time). Obviously each is easy enough to get on its own, but I can't figure out how to get them into one report. Any help would be gratefully appreciated! Stu
Report request
I define a field on incident template e.g : field : Satisfaction Lable : 1,2,3,4,5,6,7,8,9 I want to report base on satisfaction field to show just the numbers that are more than 3 How can i do it ? Please help me
Number of suveys sent , number of surveys received and the return rate percentage
Dear Service Desk Support Teams, Can you help me insert Requester and Technician Columm on table below ? I really need it. https://forums.manageengine.com/topic/number-of-suveys-sent-number-of-surveys-received-and-the-return-rate-percentage Thanks & Best Regards Huan Tran
'My Reports' - mine or everybody's ?
I assumed that this folder within the reports tab, would be a place where I could store my custom reports i.e. only visible to my user id. However I have noticed that within this folder are reports from other users So is it really 'my folder' or is it available to everyone, in which case that's a misleading name?
Add Item into report below
SELECT wo.WORKORDERID "Request ID",cd.CATEGORYNAME "Category",scd.NAME "Sub Category" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID WHERE ((ti.FIRST_NAME IS NOT NULL) AND (wo.DEPARTMENTID = 1)) AND wo.ISPARENT=1
Add Item into report below
SELECT wo.WORKORDERID "Request ID",cd.CATEGORYNAME "Category",scd.NAME "Sub Category" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID WHERE ((ti.FIRST_NAME IS NOT NULL) AND (wo.DEPARTMENTID = 1)) AND wo.ISPARENT=1
Problem and Change Timespent reports
I need to be able to create Problem and Change Timespent reports, summarized by technician for the last week. What SQL code can I use to do this?
SLA and KPI report by priority for the given account
Hello. I need a tabular report that gives me the following: Priority,Amount of requests,% Response Overdue,% Resolution Overdue 1 - Critical,30,2%,98% 2 - High,x,x%,x% 3 - Medium,x,x%,x% 4 - Low,x,x%,x% 5 - Planned,x,x%,x% but it have to obey the account selected, not all. I already have the following: select pd.priorityname "Prioridade", (count(wo.workorderid)) "Quantidade de Solicitações", (sum(case(is_fr_overdue) when 1 then 1.0 else 0.0 end)*100)/count(wo.workorderid) "% Atendimentos Atrasados",
What table has the software licenses...
that are seen on the Assets Tab, select a group, click on a computer listed in the details page, then click on the software tab. In this details page I have gotten custom SQL from SD+ that uses the SoftwareLicenses table. This table does not hold the licenses in this report. I could also use the table that holds the product ID as well. Thank You, Harry
Groups passed queue
Hi, We need to have a query that can list all tickets that has passed a special group within an specified time frame. Anyone that has something like that available?
TimeSpent Report
Hi Can someone help me out, I want to run a report which shows me a list of requests and a cumulative total of time spent from the work log, even if there is no worklog entry. The reason we want to use the work log, is that this is a more accurate time of how much time we actually spend on a case, as our technicians may not be able to get access to the system to place requests on hold etc. and when they get the opportunity, they go back in an fill in the work log. Is there any way to run this report