Query for request attachment details

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 WorkOrder wo 
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID 
LEFT JOIN queuedefinition qdef ON woq.QUEUEID=qdef.QUEUEID
LEFT JOIN statusdefinition sdef ON wos.statusid=sdef.statusid
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 sa.ATTACHMENTID IS NOT NULL and wo.createdtime>=<from_thismonth> and wo.createdtime<=<to_thismonth>

Requests without Attachment

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 WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID 
LEFT JOIN queuedefinition qdef ON woq.QUEUEID=qdef.QUEUEID
LEFT JOIN statusdefinition sdef ON wos.statusid=sdef.statusid
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 sa.ATTACHMENTID IS NULL and wo.createdtime>=<from_thismonth> and wo.createdtime<=<to_thismonth>

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>


      New to ADSelfService Plus?

      Start your free trial

        Resources

            • Related Articles

            • 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", ...

            • 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 ...

            • 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 ...

            • Request add attachment_Python script-API

              Steps for adding attachment to the request through python script-API. Extract the attached  "Attachment_Python_Script.zip" >> Edit attachmentscript.py >>Mention product installed url in the base url ; update the api key; update the request id that ...

            • 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 ...