Query to list users details with account and subaccount

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?

        Resources

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