Configuration Management - report to view and export the list of support groups
This report helps to export the list of support groups configured in the application with their complete details.
MSSQL:
SELECT qd.QUEUENAME "Support group",
ci.DESCRIPTION "Description",
su.first_name "Owned By",
STUFF(
(SELECT ',' + au.first_name
FROM queue_technician qt
LEFT JOIN sduser sd ON qt.technicianid=sd.userid
LEFT JOIN aaauser au ON sd.userid=au.user_id
WHERE qt.queueid=qd.queueid
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') AS "Interested Technicians",
STUFF(
(SELECT ',' + au.first_name
FROM queueescalation qe
LEFT JOIN escalateton esc ON qe.escalatetoid=esc.escalatetoid
LEFT JOIN sduser sd ON esc.userid=sd.userid
LEFT JOIN aaauser au ON sd.userid=au.user_id
WHERE qe.category='NOTIFY_REQ_ADD_IN_QUEUE'
AND qe.queueid=qd.queueid
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') AS "Notify when new request is added",
STUFF(
(SELECT ',' + au.first_name
FROM queueescalation qe
LEFT JOIN escalateton esc ON qe.escalatetoid=esc.escalatetoid
LEFT JOIN sduser sd ON esc.userid=sd.userid
LEFT JOIN aaauser au ON sd.userid=au.user_id
WHERE qe.category='NOTIFY_UNPICK_REQ_IN_QUEUE'
AND qe.queueid=qd.queueid
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') AS "Notify when request is left unpicked",
STUFF(
(SELECT ',' + au.first_name
FROM queueescalation qe
LEFT JOIN escalateton esc ON qe.escalatetoid=esc.escalatetoid
LEFT JOIN sduser sd ON esc.userid=sd.userid
LEFT JOIN aaauser au ON sd.userid=au.user_id
WHERE qe.category='NOTIFY_REQ_EDIT_IN_QUEUE'
AND qe.queueid=qd.queueid
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') AS "Notify when request is updated",
qe.email "Group E-Mail",
qd.sendername "Sender's Name",
qd.replyaddress "Sender's e-mail",
sdo.name "Site Name" FROM QueueDefinition qd
LEFT JOIN Queue_email qe ON qe.queueid=qd.queueid
LEFT JOIN ci ci ON qd.ciid=ci.ciid
LEFT JOIN SupportGroup sg ON sg.ciid=qd.ciid
LEFT JOIN AaaUser su ON sg.ownedby=su.user_id
LEFT JOIN SiteDefinition site ON qd.siteid=site.siteid
LEFT JOIN SDOrganization sdo ON sdo.org_id=site.siteid
PGSQL:
SELECT qd.QUEUENAME "Support Group",
ci.DESCRIPTION "Description",
su.first_name "Owned By",
sdo.name "Site Name",
(SELECT array_to_string(array_agg(au.first_name), ',')
FROM queue_technician qt
LEFT JOIN sduser sd ON qt.technicianid=sd.userid
LEFT JOIN aaauser au ON sd.userid=au.user_id
WHERE qt.queueid=qd.queueid) "Interested Technicians",
(SELECT array_to_string(array_agg(au.first_name), ',')
FROM queueescalation qe
LEFT JOIN escalateton esc ON qe.escalatetoid=esc.escalatetoid
LEFT JOIN sduser sd ON esc.userid=sd.userid
LEFT JOIN aaauser au ON sd.userid=au.user_id
WHERE qe.category='NOTIFY_REQ_ADD_IN_QUEUE'
AND qe.queueid=qd.queueid) "Notify when new request is added",
(SELECT array_to_string(array_agg(au.first_name), ',')
FROM queueescalation qe
LEFT JOIN escalateton esc ON qe.escalatetoid=esc.escalatetoid
LEFT JOIN sduser sd ON esc.userid=sd.userid
LEFT JOIN aaauser au ON sd.userid=au.user_id
WHERE qe.category='NOTIFY_UNPICK_REQ_IN_QUEUE'
AND qe.queueid=qd.queueid) "Notify when request is left unpicked",
(SELECT array_to_string(array_agg(au.first_name), ',')
FROM queueescalation qe
LEFT JOIN escalateton esc ON qe.escalatetoid=esc.escalatetoid
LEFT JOIN sduser sd ON esc.userid=sd.userid
LEFT JOIN aaauser au ON sd.userid=au.user_id
WHERE qe.category='NOTIFY_REQ_EDIT_IN_QUEUE'
AND qe.queueid=qd.queueid) AS "Notify when request is updated",
qe.email "Group E-Mail",
qd.sendername "Sender's Name",
qd.replyaddress "Sender's e-mail" FROM QueueDefinition qd
LEFT JOIN Queue_email qe ON qe.queueid=qd.queueid
LEFT JOIN ci ci ON qd.ciid=ci.ciid
LEFT JOIN SupportGroup sg ON sg.ciid=qd.ciid
LEFT JOIN AaaUser su ON sg.ownedby=su.user_id
LEFT JOIN SiteDefinition site ON qd.siteid=site.siteid
LEFT JOIN SDOrganization sdo ON sdo.org_id=site.siteid
ORDER BY 1
To make any changes to this query, refer to this post.
Click this link to navigate to the next report.