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 ...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 ...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'