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 ADSelfService Plus?
Related Articles
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 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 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 show Technicians' login name, email, role and last login (PGSQL & MSSQL )
Tested in Build PGSQL (14300) or MSSQL (14306) SELECT AaaUser.FIRST_NAME "First Name", AaaUser.last_name "Last Name", adef.org_name "Account Name", sdo.name "Site Name", AaaContactInfo.EMAILID "E-mail", sduser.jobtitle, ar.name "Role", ...Query to get the user details (both technicians and requesters) account-wise with User ID (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Tested in builds : 14000, 14201, 14301 1) Query to get the First Name, Last Name, Display Name, Login User ID, Login Name, Email and Phone Number of all users in the application, account-wise, with the ...