Query for request attachment details (MSSQL & PGSQL)

Query for request attachment details (MSSQL & PGSQL)


Tested in builds from PGSQL (14300) or MSSQL (14306)

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 ADManager Plus?

          New to ADSelfService Plus?