Exporting full body copy of Change Requests and Service Requests/Incidents
Hello
I have the need to move a bunch of my tickets to my billing ticketing platform.
I'd like to get the query of Service Requests and Incident Requests I generated from Reports (shown below) altered to add the the data of the conversations = the responses between customer and staff on the Request. Reports seem to only offer a summary with the subject line.
Basically, I am looking for a query joining the Requests and the Conversations - if that can be accomplished in a better query than the one below, please provide that too.
Can you do the same for Change Requests too please?
Can you give me timing on when you could do this for me?
Our company is a licensed user of ServiceDesk under xAnd (or DPSI or TierPoint).
Thank you
Ernie Marquez
TierPoint / xAnd Development
ernie.marquez@tierpoint.com
206-281-0493
Out of box Report Query Incident and Service Requests
SELECT wo.WORKORDERID "Request ID",mdd.MODENAME "Request Mode",qd.QUEUENAME "Group",aau.FIRST_NAME "Requester",dpt.DEPTNAME "Department",cd.CATEGORYNAME "Category",scd.NAME "Subcategory",icd.NAME "Item",wo.TITLE "Subject",cri.FIRST_NAME "Created By",urgdef.NAME "Urgency",impdef.NAME "Impact",rtdef.NAME "Request Type",ti.FIRST_NAME "Technician",wotodesc.FULLDESCRIPTION "Description",sdo.NAME "Site",regionDef.REGIONNAME "Region",appStDef.STATUSNAME "Approval Status",rcode.NAME "Request Closure Code",wos.CLOSURECOMMENTS "Request Closure Comments",serdef.NAME "Service Category",pd.PRIORITYNAME "Priority",lvd.LEVELNAME "Level",res.RESOURCENAME "Asset",std.STATUSNAME "Request Status",std.ISPENDING "Pending Status",wo.CREATEDTIME "Created Time",wo.RESPONDEDTIME "Responded Date",wo.DUEBYTIME "DueBy Time",wo.COMPLETEDTIME "Completed Time",wo.TIMESPENTONREQ "Time Elapsed",wos.ISOVERDUE "Overdue Status",rrs.RESOLUTION "Resolution",wos.REOPENED "ReOpened",oboau.FIRST_NAME "OnBehalfOfUser",wof.UDF_CHAR5 "Escalation",wof.UDF_CHAR6 "Escalation",wof.UDF_CHAR3 "Carrier Information",wof.UDF_CHAR4 "Carrier Ticket",wof.UDF_CHAR1 "Contact Method",wof.UDF_CHAR2 "Xand Facility",wof.UDF_DATE1 "Requested Completion Date",wo.FR_DUETIME "Response DueBy Time",wos.IS_FR_OVERDUE "First Response Overdue Status",wos.LAST_TECH_UPDATE "Last Update Time" FROM WorkOrder wo LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID LEFT JOIN SDUser crd ON wo.CREATEDBYID=crd.USERID LEFT JOIN AaaUser cri ON crd.USERID=cri.USER_ID LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID LEFT JOIN RegionDefinition regionDef ON siteDef.REGIONID=regionDef.REGIONID LEFT JOIN ServiceDefinition serdef ON wo.SERVICEID=serdef.SERVICEID LEFT JOIN Resources res ON wo.WORKSTATIONID=res.RESOURCEID 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 ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN UrgencyDefinition urgdef ON wos.URGENCYID=urgdef.URGENCYID LEFT JOIN ImpactDefinition impdef ON wos.IMPACTID=impdef.IMPACTID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID LEFT JOIN ApprovalStatusDefinition appStDef ON wos.APPR_STATUSID=appStDef.STATUSID LEFT JOIN RequestClosureCode rcode ON wos.CLOSURECODEID=rcode.CLOSURECODEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN LevelDefinition lvd ON wos.LEVELID=lvd.LEVELID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN RequestResolver rrr ON wo.WORKORDERID=rrr.REQUESTID LEFT JOIN RequestResolution rrs ON rrr.REQUESTID=rrs.REQUESTID LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID LEFT JOIN SDUser obosdu ON wo.OBOID=obosdu.USERID LEFT JOIN AaaUser oboau ON obosdu.USERID=oboau.USER_ID WHERE (((wo.CREATEDTIME >= 1441166400000) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL))) AND ((wo.CREATEDTIME <= 1441252799000) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1)))) AND wo.ISPARENT='1'
Out of box Report Query for Change Requests
SELECT chdt.CHANGEID "Change ID",chdt.TITLE "Title",chdt.CREATEDTIME "Created Time",chdt.SCHEDULEDSTARTTIME "Scheduled Start Time",chdt.SCHEDULEDENDTIME "Scheduled End",chdt.COMPLETEDTIME "Completed Time",orgaaa.FIRST_NAME "Change Requester",ownaaa.FIRST_NAME "Technician",priodef1.PRIORITYNAME "Priority",urgdef.NAME "Urgency",ctdef.NAME "Change Type",oldStageDef.NAME "Old Status",approvaldef.STATUSNAME "Approval Status",catadef.CATEGORYNAME "Category",subcatadef.NAME "Subcategory",itemdef1.NAME "Item",impactdef.NAME "Impact",chargeTable.TIMESPENT "Time Spent",chargeTable.TOTAL_CHARGE "Total Charges",chargeTable.TS_STARTTIME "Time Spent Start Date",chargeTable.TS_ENDTIME "Time Spent End Date",ownaaa1.FIRST_NAME "Time Spent Technician",chandes.FULL_DESCRIPTION "Description",sdo.NAME "Site",riskDef.NAME "Risk",clcodeDef.NAME "Change Closure Code",rfc.NAME "Reason For Change",stageDef.DISPLAYNAME "Stage" FROM ChangeDetails chdt LEFT JOIN SDUser orgsd ON chdt.INITIATORID=orgsd.USERID LEFT JOIN AaaUser orgaaa ON orgsd.USERID=orgaaa.USER_ID LEFT JOIN SDUser ownsd ON chdt.TECHNICIANID=ownsd.USERID LEFT JOIN AaaUser ownaaa ON ownsd.USERID=ownaaa.USER_ID LEFT JOIN PriorityDefinition priodef1 ON chdt.PRIORITYID=priodef1.PRIORITYID LEFT JOIN UrgencyDefinition urgdef ON chdt.URGENCYID=urgdef.URGENCYID LEFT JOIN ChangeTypeDefinition ctdef ON chdt.CHANGETYPEID=ctdef.CHANGETYPEID LEFT JOIN StageDefinition oldStageDef ON chdt.STAGEID=oldStageDef.STAGEID LEFT JOIN ApprovalStatusDefinition approvaldef ON chdt.APPR_STATUSID=approvaldef.STATUSID LEFT JOIN CategoryDefinition catadef ON chdt.CATEGORYID=catadef.CATEGORYID LEFT JOIN SubCategoryDefinition subcatadef ON chdt.SUBCATEGORYID=subcatadef.SUBCATEGORYID LEFT JOIN ItemDefinition itemdef1 ON chdt.ITEMID=itemdef1.ITEMID LEFT JOIN ImpactDefinition impactdef ON chdt.IMPACTID=impactdef.IMPACTID LEFT JOIN ChangeToCharge changeCharge ON chdt.CHANGEID=changeCharge.CHANGEID LEFT JOIN ChargesTable chargeTable ON changeCharge.CHARGEID=chargeTable.CHARGEID LEFT JOIN AaaUser ownaaa1 ON chargeTable.TECHNICIANID=ownaaa1.USER_ID LEFT JOIN ChangeToDescription chandes ON chdt.CHANGEID=chandes.CHANGEID LEFT JOIN RiskDefinition riskDef ON chdt.RISKID=riskDef.RISKID LEFT JOIN SiteDefinition siteDef ON chdt.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID LEFT JOIN ReasonForChangeDetails rfc ON chdt.REASONFORCHANGEID=rfc.ID LEFT JOIN Change_StageDefinition stageDef ON chdt.WFSTAGEID=stageDef.WFSTAGEID LEFT JOIN ChangeToClosureCode clcodeMapDef ON chdt.CHANGEID=clcodeMapDef.CHANGEID LEFT JOIN Change_ClosureCode clcodeDef ON clcodeMapDef.ID=clcodeDef.ID WHERE (((chdt.CREATEDTIME >= 1441166400000) AND ((chdt.CREATEDTIME != 0) AND (chdt.CREATEDTIME IS NOT NULL))) AND ((chdt.CREATEDTIME <= 1441252799000) AND (((chdt.CREATEDTIME != 0) AND (chdt.CREATEDTIME IS NOT NULL)) AND (chdt.CREATEDTIME != -1))))
I have the need to move a bunch of my tickets to my billing ticketing platform.
I'd like to get the query of Service Requests and Incident Requests I generated from Reports (shown below) altered to add the the data of the conversations = the responses between customer and staff on the Request. Reports seem to only offer a summary with the subject line.
Basically, I am looking for a query joining the Requests and the Conversations - if that can be accomplished in a better query than the one below, please provide that too.
Can you do the same for Change Requests too please?
Can you give me timing on when you could do this for me?
Our company is a licensed user of ServiceDesk under xAnd (or DPSI or TierPoint).
Thank you
Ernie Marquez
TierPoint / xAnd Development
ernie.marquez@tierpoint.com
206-281-0493
Out of box Report Query Incident and Service Requests
SELECT wo.WORKORDERID "Request ID",mdd.MODENAME "Request Mode",qd.QUEUENAME "Group",aau.FIRST_NAME "Requester",dpt.DEPTNAME "Department",cd.CATEGORYNAME "Category",scd.NAME "Subcategory",icd.NAME "Item",wo.TITLE "Subject",cri.FIRST_NAME "Created By",urgdef.NAME "Urgency",impdef.NAME "Impact",rtdef.NAME "Request Type",ti.FIRST_NAME "Technician",wotodesc.FULLDESCRIPTION "Description",sdo.NAME "Site",regionDef.REGIONNAME "Region",appStDef.STATUSNAME "Approval Status",rcode.NAME "Request Closure Code",wos.CLOSURECOMMENTS "Request Closure Comments",serdef.NAME "Service Category",pd.PRIORITYNAME "Priority",lvd.LEVELNAME "Level",res.RESOURCENAME "Asset",std.STATUSNAME "Request Status",std.ISPENDING "Pending Status",wo.CREATEDTIME "Created Time",wo.RESPONDEDTIME "Responded Date",wo.DUEBYTIME "DueBy Time",wo.COMPLETEDTIME "Completed Time",wo.TIMESPENTONREQ "Time Elapsed",wos.ISOVERDUE "Overdue Status",rrs.RESOLUTION "Resolution",wos.REOPENED "ReOpened",oboau.FIRST_NAME "OnBehalfOfUser",wof.UDF_CHAR5 "Escalation",wof.UDF_CHAR6 "Escalation",wof.UDF_CHAR3 "Carrier Information",wof.UDF_CHAR4 "Carrier Ticket",wof.UDF_CHAR1 "Contact Method",wof.UDF_CHAR2 "Xand Facility",wof.UDF_DATE1 "Requested Completion Date",wo.FR_DUETIME "Response DueBy Time",wos.IS_FR_OVERDUE "First Response Overdue Status",wos.LAST_TECH_UPDATE "Last Update Time" FROM WorkOrder wo LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID LEFT JOIN SDUser crd ON wo.CREATEDBYID=crd.USERID LEFT JOIN AaaUser cri ON crd.USERID=cri.USER_ID LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID LEFT JOIN RegionDefinition regionDef ON siteDef.REGIONID=regionDef.REGIONID LEFT JOIN ServiceDefinition serdef ON wo.SERVICEID=serdef.SERVICEID LEFT JOIN Resources res ON wo.WORKSTATIONID=res.RESOURCEID 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 ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN UrgencyDefinition urgdef ON wos.URGENCYID=urgdef.URGENCYID LEFT JOIN ImpactDefinition impdef ON wos.IMPACTID=impdef.IMPACTID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID LEFT JOIN ApprovalStatusDefinition appStDef ON wos.APPR_STATUSID=appStDef.STATUSID LEFT JOIN RequestClosureCode rcode ON wos.CLOSURECODEID=rcode.CLOSURECODEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN LevelDefinition lvd ON wos.LEVELID=lvd.LEVELID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN RequestResolver rrr ON wo.WORKORDERID=rrr.REQUESTID LEFT JOIN RequestResolution rrs ON rrr.REQUESTID=rrs.REQUESTID LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID LEFT JOIN SDUser obosdu ON wo.OBOID=obosdu.USERID LEFT JOIN AaaUser oboau ON obosdu.USERID=oboau.USER_ID WHERE (((wo.CREATEDTIME >= 1441166400000) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL))) AND ((wo.CREATEDTIME <= 1441252799000) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1)))) AND wo.ISPARENT='1'
Out of box Report Query for Change Requests
SELECT chdt.CHANGEID "Change ID",chdt.TITLE "Title",chdt.CREATEDTIME "Created Time",chdt.SCHEDULEDSTARTTIME "Scheduled Start Time",chdt.SCHEDULEDENDTIME "Scheduled End",chdt.COMPLETEDTIME "Completed Time",orgaaa.FIRST_NAME "Change Requester",ownaaa.FIRST_NAME "Technician",priodef1.PRIORITYNAME "Priority",urgdef.NAME "Urgency",ctdef.NAME "Change Type",oldStageDef.NAME "Old Status",approvaldef.STATUSNAME "Approval Status",catadef.CATEGORYNAME "Category",subcatadef.NAME "Subcategory",itemdef1.NAME "Item",impactdef.NAME "Impact",chargeTable.TIMESPENT "Time Spent",chargeTable.TOTAL_CHARGE "Total Charges",chargeTable.TS_STARTTIME "Time Spent Start Date",chargeTable.TS_ENDTIME "Time Spent End Date",ownaaa1.FIRST_NAME "Time Spent Technician",chandes.FULL_DESCRIPTION "Description",sdo.NAME "Site",riskDef.NAME "Risk",clcodeDef.NAME "Change Closure Code",rfc.NAME "Reason For Change",stageDef.DISPLAYNAME "Stage" FROM ChangeDetails chdt LEFT JOIN SDUser orgsd ON chdt.INITIATORID=orgsd.USERID LEFT JOIN AaaUser orgaaa ON orgsd.USERID=orgaaa.USER_ID LEFT JOIN SDUser ownsd ON chdt.TECHNICIANID=ownsd.USERID LEFT JOIN AaaUser ownaaa ON ownsd.USERID=ownaaa.USER_ID LEFT JOIN PriorityDefinition priodef1 ON chdt.PRIORITYID=priodef1.PRIORITYID LEFT JOIN UrgencyDefinition urgdef ON chdt.URGENCYID=urgdef.URGENCYID LEFT JOIN ChangeTypeDefinition ctdef ON chdt.CHANGETYPEID=ctdef.CHANGETYPEID LEFT JOIN StageDefinition oldStageDef ON chdt.STAGEID=oldStageDef.STAGEID LEFT JOIN ApprovalStatusDefinition approvaldef ON chdt.APPR_STATUSID=approvaldef.STATUSID LEFT JOIN CategoryDefinition catadef ON chdt.CATEGORYID=catadef.CATEGORYID LEFT JOIN SubCategoryDefinition subcatadef ON chdt.SUBCATEGORYID=subcatadef.SUBCATEGORYID LEFT JOIN ItemDefinition itemdef1 ON chdt.ITEMID=itemdef1.ITEMID LEFT JOIN ImpactDefinition impactdef ON chdt.IMPACTID=impactdef.IMPACTID LEFT JOIN ChangeToCharge changeCharge ON chdt.CHANGEID=changeCharge.CHANGEID LEFT JOIN ChargesTable chargeTable ON changeCharge.CHARGEID=chargeTable.CHARGEID LEFT JOIN AaaUser ownaaa1 ON chargeTable.TECHNICIANID=ownaaa1.USER_ID LEFT JOIN ChangeToDescription chandes ON chdt.CHANGEID=chandes.CHANGEID LEFT JOIN RiskDefinition riskDef ON chdt.RISKID=riskDef.RISKID LEFT JOIN SiteDefinition siteDef ON chdt.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID LEFT JOIN ReasonForChangeDetails rfc ON chdt.REASONFORCHANGEID=rfc.ID LEFT JOIN Change_StageDefinition stageDef ON chdt.WFSTAGEID=stageDef.WFSTAGEID LEFT JOIN ChangeToClosureCode clcodeMapDef ON chdt.CHANGEID=clcodeMapDef.CHANGEID LEFT JOIN Change_ClosureCode clcodeDef ON clcodeMapDef.ID=clcodeDef.ID WHERE (((chdt.CREATEDTIME >= 1441166400000) AND ((chdt.CREATEDTIME != 0) AND (chdt.CREATEDTIME IS NOT NULL))) AND ((chdt.CREATEDTIME <= 1441252799000) AND (((chdt.CREATEDTIME != 0) AND (chdt.CREATEDTIME IS NOT NULL)) AND (chdt.CREATEDTIME != -1))))