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?
- 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
- 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?
Related Articles
Query to show Request and associated task details (PGSQL & MSSQL)
Tested in build PGSQL (14300) and MSSQL (14306) 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", ...Query to show ticket with its attachment details ( PGSQL & MSSQL)
Working on Builds 14500 and above too Databases: 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 ...Query to show request details along with technician's and requester's department (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Along with technician's department: 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 ...Query to get the login failed attempt details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports tab > New Query Report, use the below query to get the login failed attempts details, select principal "User Name", LONGTODATE(timestamp), HOSTNAME, OPERATIONNAME from AuditRecord ar LEFT ...Incoming and Outgoing conversations with Attachment name and location (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: Incoming: (Referred as 'Conversations' in the database) select conv.conversationid "Conversation ID", conv.workorderid "Request ID", wo.title "Request Subject", aaau.first_name "Sender", ...