Query to find Contact roles
All requests from their account and sub account
SELECT aaauser.FIRST_NAME "Contact Name",org.NAME "Account Name",ucinfo.EMAILID "Contact Email",ucinfo.LANDLINE "Contact Phone",ucinfo.MOBILE "Contact Mobile",ucinfo.FAX "Contact Fax" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo user_contact ON aaauser.USER_ID=user_contact.USER_ID LEFT JOIN AaaContactInfo ucinfo ON user_contact.CONTACTINFO_ID=ucinfo.CONTACTINFO_ID LEFT JOIN SDUser sduser ON aaauser.USER_ID=sduser.USERID LEFT JOIN Customer_Requester custreq ON sduser.USERID=custreq.REQUESTER_ID LEFT JOIN Customer cust ON custreq.CUSTOMER_ID=cust.CUSTOMER_ID LEFT JOIN AaaOrganization org ON cust.CUSTOMER_ID=org.ORG_ID LEFT JOIN DepartmentUser dep_user ON sduser.USERID=dep_user.USERID LEFT JOIN requester req on dep_user.departmentuserid=req.departmentuserid WHERE ((dep_user.DEPARTMENTID = 1) AND ( dep_user.USERTYPE = 'Contact')) AND (sduser.STATUS='ACTIVE') AND req.userstatus='ADMIN'
All requests from their account (Primary Contact)
SELECT aaauser.FIRST_NAME "Contact Name",org.NAME "Account Name",ucinfo.EMAILID "Contact Email",ucinfo.LANDLINE "Contact Phone",ucinfo.MOBILE "Contact Mobile",ucinfo.FAX "Contact Fax" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo user_contact ON aaauser.USER_ID=user_contact.USER_ID LEFT JOIN AaaContactInfo ucinfo ON user_contact.CONTACTINFO_ID=ucinfo.CONTACTINFO_ID LEFT JOIN SDUser sduser ON aaauser.USER_ID=sduser.USERID LEFT JOIN Customer_Requester custreq ON sduser.USERID=custreq.REQUESTER_ID LEFT JOIN Customer cust ON custreq.CUSTOMER_ID=cust.CUSTOMER_ID LEFT JOIN AaaOrganization org ON cust.CUSTOMER_ID=org.ORG_ID LEFT JOIN DepartmentUser dep_user ON sduser.USERID=dep_user.USERID LEFT JOIN requester req on dep_user.departmentuserid=req.departmentuserid WHERE ((dep_user.DEPARTMENTID = 1) AND ( dep_user.USERTYPE = 'Contact')) AND (sduser.STATUS='ACTIVE') AND req.userstatus='Manager'
SELECT aaauser.FIRST_NAME "Contact Name",org.NAME "Account Name",ucinfo.EMAILID "Contact Email",ucinfo.LANDLINE "Contact Phone",ucinfo.MOBILE "Contact Mobile",ucinfo.FAX "Contact Fax" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo user_contact ON aaauser.USER_ID=user_contact.USER_ID LEFT JOIN AaaContactInfo ucinfo ON user_contact.CONTACTINFO_ID=ucinfo.CONTACTINFO_ID LEFT JOIN SDUser sduser ON aaauser.USER_ID=sduser.USERID LEFT JOIN Customer_Requester custreq ON sduser.USERID=custreq.REQUESTER_ID LEFT JOIN Customer cust ON custreq.CUSTOMER_ID=cust.CUSTOMER_ID LEFT JOIN AaaOrganization org ON cust.CUSTOMER_ID=org.ORG_ID LEFT JOIN DepartmentUser dep_user ON sduser.USERID=dep_user.USERID LEFT JOIN requester req on dep_user.departmentuserid=req.departmentuserid WHERE ((dep_user.DEPARTMENTID = 1) AND ( dep_user.USERTYPE = 'Contact')) AND (sduser.STATUS='ACTIVE') AND req.userstatus='User'
SELECT aaauser.FIRST_NAME "Contact Name",org.NAME "Account Name",ucinfo.EMAILID "Contact Email",ucinfo.LANDLINE "Contact Phone",ucinfo.MOBILE "Contact Mobile",ucinfo.FAX "Contact Fax" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo user_contact ON aaauser.USER_ID=user_contact.USER_ID LEFT JOIN AaaContactInfo ucinfo ON user_contact.CONTACTINFO_ID=ucinfo.CONTACTINFO_ID LEFT JOIN SDUser sduser ON aaauser.USER_ID=sduser.USERID LEFT JOIN Customer_Requester custreq ON sduser.USERID=custreq.REQUESTER_ID LEFT JOIN Customer cust ON custreq.CUSTOMER_ID=cust.CUSTOMER_ID LEFT JOIN AaaOrganization org ON cust.CUSTOMER_ID=org.ORG_ID LEFT JOIN DepartmentUser dep_user ON sduser.USERID=dep_user.USERID LEFT JOIN requester req on dep_user.departmentuserid=req.departmentuserid WHERE ((dep_user.DEPARTMENTID = 1) AND ( dep_user.USERTYPE = 'Contact')) AND (sduser.STATUS='ACTIVE') AND req.userstatus='ADMIN'
All requests from their account (Primary Contact)
SELECT aaauser.FIRST_NAME "Contact Name",org.NAME "Account Name",ucinfo.EMAILID "Contact Email",ucinfo.LANDLINE "Contact Phone",ucinfo.MOBILE "Contact Mobile",ucinfo.FAX "Contact Fax" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo user_contact ON aaauser.USER_ID=user_contact.USER_ID LEFT JOIN AaaContactInfo ucinfo ON user_contact.CONTACTINFO_ID=ucinfo.CONTACTINFO_ID LEFT JOIN SDUser sduser ON aaauser.USER_ID=sduser.USERID LEFT JOIN Customer_Requester custreq ON sduser.USERID=custreq.REQUESTER_ID LEFT JOIN Customer cust ON custreq.CUSTOMER_ID=cust.CUSTOMER_ID LEFT JOIN AaaOrganization org ON cust.CUSTOMER_ID=org.ORG_ID LEFT JOIN DepartmentUser dep_user ON sduser.USERID=dep_user.USERID LEFT JOIN requester req on dep_user.departmentuserid=req.departmentuserid WHERE ((dep_user.DEPARTMENTID = 1) AND ( dep_user.USERTYPE = 'Contact')) AND (sduser.STATUS='ACTIVE') AND req.userstatus='Manager'
SELECT aaauser.FIRST_NAME "Contact Name",org.NAME "Account Name",ucinfo.EMAILID "Contact Email",ucinfo.LANDLINE "Contact Phone",ucinfo.MOBILE "Contact Mobile",ucinfo.FAX "Contact Fax" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo user_contact ON aaauser.USER_ID=user_contact.USER_ID LEFT JOIN AaaContactInfo ucinfo ON user_contact.CONTACTINFO_ID=ucinfo.CONTACTINFO_ID LEFT JOIN SDUser sduser ON aaauser.USER_ID=sduser.USERID LEFT JOIN Customer_Requester custreq ON sduser.USERID=custreq.REQUESTER_ID LEFT JOIN Customer cust ON custreq.CUSTOMER_ID=cust.CUSTOMER_ID LEFT JOIN AaaOrganization org ON cust.CUSTOMER_ID=org.ORG_ID LEFT JOIN DepartmentUser dep_user ON sduser.USERID=dep_user.USERID LEFT JOIN requester req on dep_user.departmentuserid=req.departmentuserid WHERE ((dep_user.DEPARTMENTID = 1) AND ( dep_user.USERTYPE = 'Contact')) AND (sduser.STATUS='ACTIVE') AND req.userstatus='User'
New to ADSelfService Plus?
Related Articles
Technician Roles
This report is used to get the roles of the technicians. For version 10.5 and below SELECT au.FIRST_NAME "Technician", (ar.NAME) "Role" FROM HelpdeskCrew hdc LEFT JOIN SDUser sd ON hdc.TECHNICIANID=sd.USERID LEFT JOIN AaaLogin al ON ...How to automatically assign users to different roles in a change request.
This script is applicable only for builds prior to 11138. This is a sample python script to read the value in the Change Owner field, when a Change Request is created and set him/her as the Change Implementer automatically, using Change Custom ...Query to find out who created Accounts (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) Execute the below queries under Reports->New Query Report. select org_id, org_name from accountdefinition ad where ad.org_name in('Requester Name', 'Palanivel Palras','Muhammad Nadeem Khan') Note down ...Query to find users under blocked state
select * from sduser sd left join CI ci on sd.ciid=ci.ciid left join AaaUserContactInfo auc on sd.userid=auc.user_id left join AaaContactInfo aci on auc.contactinfo_id=aci.contactinfo_id where sd.status='blocked'Query to show roles assigned to a technician with created date, status and department name (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query: select auser.first_name as "TECHNICIAN", max(sd.employeeid) as "Employee ID", longtodate(auser.createdtime) as "Creation Date", STRING_AGG (arole.name, ',') "Current Assigned Role", ...