Query to find technicians with login enabled along with sites and account associated (MSSQL & PGSQL)

Query to find technicians with login enabled along with sites and account associated (MSSQL & PGSQL)

Tested in Build PGSQL (14300) or MSSQL (14306)


Please go to Reports-New Query reports and execute this query.

select aau.User_id,aau.first_name "Technician Name", aal.name "Login Name", aal.Domainname "Domain",sdo.name "Associated Sites",adef.org_name "Associated Accounts" from aaauser aau 
INNER JOIN aaalogin aal on aau.user_id=aal.user_id 
LEFT JOIN sduser sdu on sdu.userid=aau.user_id
LEFT JOIN usersitemapping usm on usm.userid=sdu.userid
INNER JOIN sitedefinition sdef on sdef.siteid=usm.siteid 
INNER JOIN accountsitemapping asm on asm.siteid=sdef.siteid 
INNER JOIN accountdefinition adef on adef.org_id=asm.accountid 
INNER JOIN sdorganization sdo on sdo.org_id=sdef.siteid
where aau.user_id in (select technicianid from helpdeskcrew) order by 2,6


ORDER BY ACCOUNT:

select adef.org_name "Associated Accounts", sdo.name "Associated Sites", aau.first_name "Technician Name" from aaauser aau 
INNER JOIN aaalogin aal on aau.user_id=aal.user_id 
LEFT JOIN sduser sdu on sdu.userid=aau.user_id
LEFT JOIN usersitemapping usm on usm.userid=sdu.userid
INNER JOIN sitedefinition sdef on sdef.siteid=usm.siteid 
INNER JOIN accountsitemapping asm on asm.siteid=sdef.siteid 
INNER JOIN accountdefinition adef on adef.org_id=asm.accountid 
INNER JOIN sdorganization sdo on sdo.org_id=sdef.siteid
where aau.user_id in (select technicianid from helpdeskcrew) order by 1


        New to ADManager Plus?

          New to ADSelfService Plus?