Query to export All Requesters from All Accounts (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306)
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
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 show technicians associated accounts, sites and Support groups - (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) 1.Technicians and associated Accounts/Sites: select aau.User_id, aau.first_name "First Name", sdu.lastname "Last Name", aal.name "Login Name", AaaContactInfo.EMAILID "Email Address",sdu.jobtitle "Job ...Query to List down Account and corresponding sites and its requesters (MSSQL & PGSQL)
*Tested in builds from PGSQL (14300) or MSSQL (14306) *Applicable for 14500 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 ...Query to Export solutions (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Run this query under Reports->New Query Report and export it to the desired format SELECT solution.solutionid "Solutionid", KB_Topics.TOPICNAME " Topic",Solution.TITLE "Subject ",Solution.DESCRIPTION ...Query to get Requesters details for each account (Not Working)
Doesnt work in builds from PGSQL (14300) or MSSQL (14306) ERROR: missing FROM-clause entry for table "sdu" PGSQL & MSSQL: Execute the query under Reports->New Query Report and export it to the desired format. select au.user_id "User Id", ...