Query report on Conversation & Notification in a request. (MSSQL & PGSQL)

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>



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

                  New to ADSelfService Plus?