Query to get technician details(domain, associated account, email) from builds (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306)
SELECT
aau.first_name "First Name",
sdu.lastname "Last Name",
AaaContactInfo.EMAILID "Email Address",
sdo.name "Associated Sites",
adef.org_name "Associated Accounts",
df.domainname "Domain 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
LEFT JOIN AaaUserContactInfo ON aau.USER_ID = AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID = AaaContactInfo.CONTACTINFO_ID
LEFT JOIN domaininfo df ON sdu.domain_id = df.domainid
WHERE aau.user_id IN (
SELECT
technicianid
FROM
helpdeskcrew ) and (df.domainname like 'sdpmspad1.com')
ORDER BY 1
New to ADSelfService Plus?
Related Articles
Query to list technician name, site, email and login name (MSSQL & PGSQL)
The below PGSQL query doesnt work in builds below PGSQL (14000) ERROR: missing FROM-clause entry for table "helpdeskcrew" Pgsql: SELECT HelpDeskCrew.TECHNICIANID "Technician ID", AaaUser.FIRST_NAME "Technician Name",AaaContactInfo.EMAILID "Email", ...Query to get Requesters details for each account (Not Working)
Doesnt work in builds from PGSQL (14300) or MSSQL (14306) ERROR: missing FROM-clause entry for table "sdu" PGSQL & MSSQL: Execute the query under Reports->New Query Report and export it to the desired format. select au.user_id "User Id", ...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 ...Query to list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...Query report to extract Active Technician Login and IP Address (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports- New Query Report and execute this query. select DISTINCT ac.ACCOUNT_ID as "Login Account ID", au.FIRST_NAME as "Technician Name ( First Name )", count(acs.SESSION_ID) as "Number of ...