Query to list users details with account and subaccount
How to run the report
Copy the below query, go to Reports >> New Query Report >> Paste the query and click on run report.
DB
MSSQL and Postgres
Query
SELECT org.NAME AS "Account Name",
adef.org_name "Sub Account",
sduser.FIRSTNAME AS "First Name",
sduser.LASTNAME AS "Last Name",
ucinfo.MOBILE AS "Contact Mobile" FROM PortalUsers ptu
LEFT JOIN SDUser sduser ON ptu.USERID=sduser.USERID
left join aaausercontactinfo aci on sduser.USERID=aci.user_id
left join UserAdditionalFields uaf on sduser.userid=uaf.INSTANCE_ID
LEFT JOIN AaaUser aaauser ON sduser.USERID=aaauser.USER_ID
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 RequesterAccountMapping reqaccmapping ON aaauser.USER_ID=reqaccmapping.USERID
LEFT JOIN SDOrganization org ON reqaccmapping.ACCOUNTID=org.ORG_ID
left join accountdefinition adef ON reqaccmapping.subaccountid=adef.ORG_ID
order by 2
New to ADSelfService Plus?
Related Articles
To find the details about number of users logged in to the application and other related informations
In order to find the details about the list of users logging time to host, their logout time and current status of their connections. Please use the below query, select aas.session_id "SessionID", aal.name "User Name", aas.user_host "User Host", ...Duplicate users while importing from AD
Use the below queries; To find the number of duplicate users: SELECT objectguid, COUNT(*) TotalCount FROM aduser GROUP BY objectguid HAVING COUNT(*) > 1; SELECT userid, samaccname, objectguid, COUNT(*) TotalCount FROM aduser GROUP BY userid, ...Login Frequency Query Report
The below report would help us find the last logged in time of the users in SCP 11.0 SELECT AaaUser.FIRST_NAME "Technician", MAX(AaaLogin.NAME) "LoginName", MAX(AaaContactInfo.EMAILID) "Email", MAX(AaaAccSession.USER_HOST) "IP ...Query for Support Rep list in DB
Please run this query to find the list of Support Reps available with login in the system select * from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid inner join aaalogin aaal on ...Query to show list of contacts that are assigned to an account but not have a login
The below query shows the list of contacts that are assigned to an account, but does not have a login. SELECT org.NAME AS "Account Name" ,aaauser.FIRST_NAME AS " Contact with no login" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo user_contact ...