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


          • Related Articles

          • How to auto share requests to groups specified in the request tasks.

            This post described the use of a python script to share requests to groups specified in the tasks that are associated with a request.This script can be configured under Request custom triggers and you will find more information about this here. This ...
          • How to generate the support file

            Logs are required to analyze and find the root cause of an issue. In ServiceDesk Plus, logs can be generated by technicians who have admin access. Logs can be generated from two locations, 1. Community (headset icon) tab > Troubleshoot > Support ...
          • ADMP Integration - Add/Remove users from Groups

            Use Case:  Adding or Removing a user from a group is quite common.  This process entails redundant work for the IT Department and its time consuming. This can be automated with ServiceDesk Plus’ in-product capabilities. Making use of the DRE in ...
          • Support for VM Host and Virtual Machine and product name field in CMDB API

            Here the changes done in CIExecutor.java file to add/update the VM host/VM machine in CMDB API and also support to update the product name field in cmdb api. Also asset tag is not getting populated while adding assets using CMDB API. Need to check ...
          • How to auto share request to users specified in a request field (V3)

            Note: ServiceDesk Plus build should be  11.0 and above This post described the use of a python script to share requests to users specified in a request field. This script can be configured under Request custom triggers and you will find more ...