Support Groups

Support Groups

This Report is used to export the list of Support groups configured in the application with the 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


                  New to ADSelfService Plus?

                    • Related Articles

                    • Support Groups configuration

                      1) The support groups that we are configuring in a template is always based on the sites. When we have more than one account assigned in the template, only the support groups added under “Default Settings” will get displayed in the template. Refer ...
                    • Query to show technicians associated accounts, sites and Support groups - (MSSQL & PGSQL)

                      Tested in Build PGSQL (14300) or MSSQL (14306) 1.Technicians and associated Accounts/Sites: select aau.User_id, aau.first_name "First Name", sdu.lastname "Last Name", aal.name "Login Name", AaaContactInfo.EMAILID "Email Address",sdu.jobtitle "Job ...
                    • Select site for available groups drop down

                      Drop down shown in the screenshot will be appeared when there are more than 2500 support groups. This is shown to make it easier for the user to select and associate groups when the count is more.
                    • Query to show support groups and its individual custom attributes (MSSQL & PGSQL)

                      Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT ci.CINAME AS "Support Group Name", su.first_name "Owned By", ci.DESCRIPTION AS "Description",ia.attributename "Additional Attributes Name",ia.attributevalue "Additional Attributes ...
                    • Shared Mailbox for Support Groups

                      In the support group mail configuration, some are using an alias email as the "Sender's Email" and encounter an issue where, upon sending emails from the application support groups, the recipient sees the primary address associated with the alias, ...