Query report on Conversation & Notification in a request. (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
Please go to Reports-New Query Report and execute this query.
SELECT wo.WORKORDERID "Request ID",
(aau.FIRST_NAME) "Requester",
(wo.TITLE) "Subject",
(ti.FIRST_NAME) "Technician",
longtodate(wo.CREATEDTIME) "Created Time",
longtodate(c.createdtime)"Conversation CreatedOn",
c.title "Conversation title",
longtodate(n.notificationdate)"Technician reply CreatedOn",
c.first_name "conversation by",
c.description "conversation Description",
n.notificationtitle "NOtification title",
n.description "Notification Description",
n.first_name "Sender name" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
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 StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN
(SELECT con.workorderid,
con.title,
con.createdtime,
au1.first_name,
cond.description
FROM conversation con
LEFT JOIN aaauser au1 ON con.requesterid=au1.user_id
LEFT JOIN conversationdescription cond ON con.CONVERSATIONID= cond.CONVERSATIONID) c ON c.workorderid=wo.workorderid
LEFT JOIN
(SELECT nw.workorderid,
no.notificationtitle,
no.notificationdate,
au2.first_name,
nod.description
FROM notify_workorder nw
LEFT JOIN notification NO ON nw.notificationid=no.notificationid
LEFT JOIN aaauser au2 ON no.senderid=au2.user_id
LEFT JOIN notificationtodesc nod ON no.NOTIFICATIONID=nod.NOTIFICATIONID
WHERE no.senderid !=1) n ON n.workorderid=wo.workorderid
WHERE wo.CREATEDTIME >= <from_thisweek> and wo.createdtime <= <to_thisweek>
- 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>
This is the same query with account filter added. You just need to choose the account from the account drop down and run the below query.
SELECT accountdefinition.org_name "Account",
wo.WORKORDERID "Request ID",
(aau.FIRST_NAME) "Requester",
(wo.TITLE) "Subject",
(ti.FIRST_NAME) "Technician",
longtodate(wo.CREATEDTIME) "Created Time",
longtodate(c.createdtime)"Conversation CreatedOn",
c.title "Conversation title",
longtodate(n.notificationdate)"Technician reply CreatedOn",
c.first_name "conversation by",
c.description "conversation Description",
n.notificationtitle "NOtification title",
n.description "Notification Description",
n.first_name "Sender name" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
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 StatusDefinition std ON wos.STATUSID=std.STATUSID
left join accountsitemapping asm ON wo.siteid=asm.siteid
left join accountdefinition ON accountdefinition.org_id=asm.accountid
LEFT JOIN
(SELECT con.workorderid,
con.title,
con.createdtime,
au1.first_name,
cond.description
FROM conversation con
LEFT JOIN aaauser au1 ON con.requesterid=au1.user_id
LEFT JOIN conversationdescription cond ON con.CONVERSATIONID= cond.CONVERSATIONID) c ON c.workorderid=wo.workorderid
LEFT JOIN
(SELECT nw.workorderid,
no.notificationtitle,
no.notificationdate,
au2.first_name,
nod.description
FROM notify_workorder nw
LEFT JOIN notification NO ON nw.notificationid=no.notificationid
LEFT JOIN aaauser au2 ON no.senderid=au2.user_id
LEFT JOIN notificationtodesc nod ON no.NOTIFICATIONID=nod.NOTIFICATIONID
WHERE no.senderid !=1) n ON n.workorderid=wo.workorderid
WHERE wo.CREATEDTIME >= <from_thisweek> and wo.createdtime <= <to_thisweek>
AND accountdefinition.org_id in ($Account)
New to ADSelfService Plus?
Related Articles
Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...Query to show last conversation and last notification in a request ( MSSQL )
Tested in Build MSSQL (14306) Query SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", wo.TITLE "Subject", ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time", (select conversationdescription.description from ...Query to show conversation description (MSSQL & PGSQL)
Database: MSSQL and PGSQL Tested in build PGSQL (14300) and MSSQL (14306) It is not recommended to use the query often in business hours as it may cause performance issues since it has to pull huge data. Execute the below query under Reports->New ...Query to fetch the fields in the templates (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) The query fetches all the fields with display name used across Request templates PQSQL: select Distinct (CASE when fc.field_name like 'udf_%' then ca.aliasname else fc.field_name END) "Fields", ...Query report to extract Active Technician Login and IP Address (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports- New Query Report and execute this query. select DISTINCT ac.ACCOUNT_ID as "Login Account ID", au.FIRST_NAME as "Technician Name ( First Name )", count(acs.SESSION_ID) as "Number of ...