Query to show ticket with its attachment details

Query to show ticket with its attachment details

PGSQL & MSSQL:

SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", ti.FIRST_NAME AS "Technician", sa.ATTACHMENTNAME "Attachment Name",
sa.ATTACHMENTPATH "Attachment Path" 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 LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID LEFT JOIN Workorderattachment wa ON wo.workorderid=wa.workorderid LEFT JOIN Sdeskattachment sa ON wa.attachmentid=sa.attachmentid WHERE (wo.ISPARENT='1') AND wo.createdtime>=<from_thismonth> and wo.createdtime<=<to_thismonth> ORDER BY 2

NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.

How to compare date column with auto filled date templates?
  1. Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
    • <from_thisweek> - Starting date of this week
    • <to_thisweek> - Ending date of this week
  2. Available Date Templates
    • Today - <from_today> - <to_today>
    • This week - <from_thisweek> - <to_thisweek>
    • Last week - <from_lastweek> - <to_lastweek>
    • This month - <from_thismonth> - <to_thismonth>
    • Last month - <from_lastmonth> - <to_lastmonth>
    • This quarter - <from_thisquarter> - <to_thisquarter>
    • Last quarter - <from_lastquarter> - <to_lastquarter>
    • Yesterday - <from_yesterday> - <to_yesterday>


      • Related Articles

      • Query for request attachment details

        Requests with Attachment, its name and path SELECT ad.ORG_NAME AS "Account",  wo.WORKORDERID AS "Request ID",  wo.TITLE AS "Subject",  ti.FIRST_NAME AS "Technician",  sa.ATTACHMENTNAME "Attachment Name", sa.ATTACHMENTPATH "Attachment Path" FROM ...
      • Query to show request details along with technician's department

        PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", qd.QUEUENAME AS "Group",ti.FIRST_NAME AS "Technician", dpt.DEPTNAME AS " Technician ...
      • Query to show Request and associated task details

        PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", icd.NAME AS "Item", ti.FIRST_NAME AS "Technician", ...
      • Request missing

        Use case: In some cases, requests are missing. We would get the ID number from acknowledgement notification but we cannot find that in the tickets. In such cases, please follow below steps to find out the details. 1. Make sure to check the Request ...
      • Add attachments using API

        Attachment cannot be added when a request is created, it can be only added after request creation URL : http://localhost:8080/api/v3/attachments?TECHNICIAN_KEY=XXXXXXXXX Body