Report on Organizational roles
DB Compatibility : PGSQL & MSSQL
Build Compatibility : Builds above 10000
select r.orgrolename "orgrolename",
r.description "Description",
au.FIRST_NAME "Name",
DepartmentDefinition.DEPTNAME "Department",
SDOrganization.NAME "Site" from aaauser au
left join (select roleid,userid From roletousermapping rm group by roleid,userid) rm on au.user_id = rm.userid
left join orgroles r on rm.roleid = r.orgroleid
LEFT JOIN UserDepartment ON au.USER_ID=UserDepartment.USERID
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
where r.orgrolename is not null
order by 1,2
r.description "Description",
au.FIRST_NAME "Name",
DepartmentDefinition.DEPTNAME "Department",
SDOrganization.NAME "Site" from aaauser au
left join (select roleid,userid From roletousermapping rm group by roleid,userid) rm on au.user_id = rm.userid
left join orgroles r on rm.roleid = r.orgroleid
LEFT JOIN UserDepartment ON au.USER_ID=UserDepartment.USERID
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
where r.orgrolename is not null
order by 1,2
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 ...Roles required for SQL user
The recommended login account for creating the database requires SQL authentication and it should have the following roles enabled: * dbcreator * Public * Sysadmin If you do not want to provide Sysadmin role to the user, db_creator and Public roles ...Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...Unauthorized Access CSV Report for Users Not Present in the Application
Report Details: Currently, the application has not been recording data regarding login attempts when the username used is not available in the application. This report will store the login attempts with usernames who do not exist in the application. ...Query to show users of Organization Roles for each account (MSSQL & PGSQL)
T ested in Build PGSQL (14300) or MSSQL (14306) select ad.org_name "Account Name", org.orgrolename "ORG Role Name", aau.first_name "User" from orgroles org left join roletousermapping rtu ON org.orgroleid=rtu.roleid LEFT JOIN roletouseraccountmapping ...