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 * 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
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?
Resources
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 ...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: ...Phone number need to be mandatory in 8.1 for contacts
Kindly follow the steps below to create the FAFR, 1. Access admin Module 2. Select the request template option and click the edit option the required request template the script needs to be applied on 3. Select the Fields and Forms Rule tab and paste ...