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