Query for Support Rep list in DB

Query for Support Rep list in DB

Please run this query to find the list of Support Reps available with login in the system

select * from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid inner join aaalogin aaal on hd.technicianid=aaal.user_id where sdu.STATUS='ACTIVE'


To know Support Rep with their role details

select aaau.user_id,aaau.first_name,de.departmentname,ar.name,aaal.name login from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid join aaalogin aaal on hd.technicianid=aaal.user_id  left join departmentuser dep on dep.userid=sdu.userid left join user_departmentroles depr on dep.departmentuserid=depr.departmentuserid left join aaarole ar on depr.ROLEID = ar.ROLE_ID left join department de on dep.departmentid=de.departmentid where sdu.STATUS='ACTIVE'

Support Reps with BU & Group

Mysql

select aaau.user_id,aaau.first_name 'Support Rep',de.departmentname 'Business Unit',ar.name 'Role',aaal.name 'Login Name',qdef.queuename 'Group' from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid join aaalogin aaal on hd.technicianid=aaal.user_id left join departmentuser dep on dep.userid=sdu.userid left join user_departmentroles depr on dep.departmentuserid=depr.departmentuserid left join aaarole ar on depr.ROLEID = ar.ROLE_ID left join department de on dep.departmentid=de.departmentid left join queue_technician at on aaau.user_id=at.technicianid left join queuedefinition qdef on at.queueid=qdef.queueid where sdu.STATUS='ACTIVE' order by qdef.queuename

Postgres
select aaau.user_id,aaau.first_name "Support Rep",de.departmentname "Business Unit",ar.name "Role",aaal.name "Login Name",qdef.queuename "Group" from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid join aaalogin aaal on hd.technicianid=aaal.user_id left join departmentuser dep on dep.userid=sdu.userid left join user_departmentroles depr on dep.departmentuserid=depr.departmentuserid left join aaarole ar on depr.ROLEID = ar.ROLE_ID left join department de on dep.departmentid=de.departmentid left join queue_technician at on aaau.user_id=at.technicianid left join queuedefinition qdef on at.queueid=qdef.queueid where sdu.STATUS='ACTIVE' group by aaau.user_id,de.departmentname,ar.name,aaal.name,qdef.queuename order by aaau.first_name,departmentname,qdef.queuename

Support reps with their email address

select * from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid inner join aaalogin aaal on hd.technicianid=aaal.user_id left join aaausercontactinfo aaauci on aaau.user_id=aaauci.user_id left join aaacontactinfo aaauc on aaauci.Contactinfo_id=aaauc.Contactinfo_id where sdu.STATUS='ACTIVE'

select aaau .user_id,aaauc.Emailid,aaal.name from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid inner join aaalogin aaal on hd.technicianid=aaal.user_id left join aaausercontactinfo aaauci on aaau.user_id=aaauci.user_id left join aaacontactinfo aaauc on aaauci.Contactinfo_id=aaauc.Contactinfo_id where sdu.STATUS='ACTIVE'

Support reps without login

select * from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid left join aaalogin aaal on hd.technicianid=aaal.user_id left join aaausercontactinfo aaauci on aaau.user_id=aaauci.user_id left join aaacontactinfo aaauc on aaauci.Contactinfo_id=aaauc.Contactinfo_id where sdu.STATUS='ACTIVE' and aaal.login_id is null

Group,Support Rep

SELECT que.QUEUEID, que.QUEUENAME, que.QUEUEDESCRIPTION, dep.DEPARTMENTNAME, aaau.first_name FROM QueueDefinition que Left Join Queue_Technician qt ON que.QUEUEID=qt.QUEUEID Left Join aaauser aaau  ON qt.TECHNICIANID = aaau.USER_ID left join sduser sdu on aaau.USER_ID=sdu.userid left join department dep on que.departmentid=dep.departmentid where sdu.STATUS='ACTIVE'

                    New to ADSelfService Plus?

                      • Related Articles

                      • Excess license usage shown for support reps in SCP 8.1

                        1. Go to reports - > New query report, and execute the below query.  select * from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid inner join aaalogin aaal on ...
                      • Workaround for Contacts tab not visible for support reps (post 11.0 version)

                        Post 11.0, the contacts tab will not be shown at the top for support reps. As a workaround follow these steps 1. Goto Admin -> ZOHO Creator apps 2. Click on Add Links -> Provide Link Name as Contacts -> URL as ...
                      • End of Support - Builds details

                        Dear Users Support will be provided only for the Supportcenter Plus builds (released within one year from the current date). We recommend you upgrade to the latest version to continue receiving support. As per Feb-24 - We stopped support of all ...
                      • Query to list out the notes added to the tickets

                        QUERY: To list of the notes added to the tickets  while generating report report SELECT  ti.FIRST_NAME AS "Support Rep", wo.WORKORDERID AS "Request ID", longtodate(wo.CREATEDTIME) AS "Created Time", wo.TITLE AS "Subject", aau.FIRST_NAME AS "Contact", ...
                      • Script to Notify Support Reps when a new reply is received for Unassigned Requests

                        Please do follow the below steps to notify support reps when an unassigned request is updated or when a reply is received.  1. Download the configuration.json file, sendMail.py file, requestobj.json file from the below link: ...