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?
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 ...Query to list account wise technicians and their respective support group details
Tested in: 14610 Compatible with: Both Postgres and MSSQL DB. Query 1: To return technicians and respective support group details from all accounts: SELECT ad.ORG_NAME AS "Account Name", au.FIRST_NAME AS "Technician Name", qd.QUEUENAME AS "Support ...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 ...Query to list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...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 ...