Query to export All Requesters from All Accounts
Execute the below query under Reports > New Query Reports:
SELECT AaaUser.USER_ID,AaaUser.FIRST_NAME "FullName",AaaLogin.NAME "LoginName",AaaLogin.DOMAINNAME "Domain",AaaContactInfo.EMAILID "Email",DepartmentDefinition.DEPTNAME "Department",SDOrganization.NAME "Site",ad.org_name"Account",AaaContactInfo.LANDLINE "Phone",SDUser.JOBTITLE "JobTitle",AaaContactInfo.MOBILE "Mobile" FROM AaaUser LEFT JOIN UserDepartment ON AaaUser.USER_ID=UserDepartment.USERID LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID LEFT JOIN DepartmentDefinition ON UserDepartment.DEPTID=DepartmentDefinition.DEPTID LEFT JOIN SiteDefinition ON DepartmentDefinition.SITEID=SiteDefinition.SITEID LEFT JOIN SDOrganization ON SiteDefinition.SITEID=SDOrganization.ORG_ID INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID LEFT JOIN HelpDeskCrew ON SDUser.USERID=HelpDeskCrew.TECHNICIANID LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
LEFT JOIN accountsitemapping asm ON asm.SITEID=SiteDefinition.SITEID LEFT JOIN accountdefinition ad ON ad.ORG_ID=asm.ACCOUNTID
WHERE ((HelpDeskCrew.TECHNICIANID IS NULL) AND (SDUser.STATUS = 'ACTIVE')) order by 8,7,1
User the query given below from builds 10600 and above.
SELECT
AaaUser.USER_ID,
AaaUser.FIRST_NAME "FullName",
AaaLogin.NAME "LoginName",
AaaLogin.DOMAINNAME "Domain",
AaaContactInfo.EMAILID "Email",
DepartmentDefinition.DEPTNAME "Department",
SDOrganization.NAME "Site",
ad.org_name "Account",
AaaContactInfo.LANDLINE "Phone",
SDUser.JOBTITLE "JobTitle",
AaaContactInfo.MOBILE "Mobile"
FROM
AaaUser
LEFT JOIN UserDepartment ON AaaUser.USER_ID = UserDepartment.USERID
LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID = AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID = AaaContactInfo.CONTACTINFO_ID
LEFT JOIN DepartmentDefinition ON UserDepartment.DEPTID = DepartmentDefinition.DEPTID
LEFT JOIN SiteDefinition ON DepartmentDefinition.SITEID = SiteDefinition.SITEID
LEFT JOIN SDOrganization ON SiteDefinition.SITEID = SDOrganization.ORG_ID
INNER JOIN SDUser ON AaaUser.USER_ID = SDUser.USERID
LEFT JOIN Portalusers PU ON SDUser.USERID = PU.userid
LEFT JOIN portaltechnicians PT ON PU.id = pt.id
LEFT JOIN AaaLogin ON AaaUser.USER_ID = AaaLogin.USER_ID
LEFT JOIN accountsitemapping asm ON asm.SITEID = SiteDefinition.SITEID
LEFT JOIN accountdefinition ad ON ad.ORG_ID = asm.ACCOUNTID
WHERE
(
(
pt.id IS NULL
)
AND (SDUser.STATUS = 'ACTIVE')
)
order by
8,
7,
1
New to ADSelfService Plus?
Related Articles
Export accounts from the SDP MSP application
Way to export the accounts configured in the SDP MSP application. Login as an Admin and click on the "Reports"icon . Then, click on "New query report" and run the below query: select adef.org_name "Account ...Query to find out who created Accounts
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 the Org_IDs of the above query from the result ...Query to get Requesters details for each account
PGSQL & MSSQL: Execute the query under Reports->New Query Report and export it to the desired format. select au.user_id "User Id", au.first_name "First Name", au.last_name "Last Name", sdu.isvipuser "VIP User", sdu.employeeid "Employee ...SQL Query to export request details
Supported only for SQL DB(14201 build). Executed in SQL DB directly to export the requests. Below query to export requests for one account / site. SELECT "wo"."WORKORDERID" AS "Request ID", "wo"."TITLE" AS "Subject", "wotodesc"."FULLDESCRIPTION" AS ...Query to List down Account and corresponding sites and its requesters
Applicable for 10604 builds For Accounts and Site: select adef.org_name "Account",sdo.name "Site Name" from accountdefinition adef left join accountsitemapping asm on adef.org_id=asm.accountid Left Join Sitedefinition sdef on asm.siteid=sdef.siteid ...