Query to know the User details,account,site based on email (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306)
select aaau.user_id,aaau.first_name,
aaaci.emailid,sdu.status,
sdo.name "Site",
adef.org_name "Account" from aaauser aaau
LEFT JOIN aaausercontactinfo aaauci ON aaauci.user_id=aaau.user_id
LEFT JOIN aaacontactinfo aaaci ON aaaci.contactinfo_id=aaauci.contactinfo_id
LEFT JOIN sduser sdu ON sdu.userid=aaau.user_id
LEFT JOIN UserDepartment userDept ON sdu.USERID=userDept.USERID
LEFT JOIN DepartmentDefinition deptTable ON userDept.DEPTID=deptTable.DEPTID
LEFT JOIN sitedefinition sdef ON sdef.siteid=deptTable.siteid
LEFT JOIN accountsitemapping asm ON asm.siteid=sdef.siteid
LEFT JOIN accountdefinition adef ON adef.org_id=asm.accountid
LEFT JOIN sdorganization sdo ON sdo.org_id=sdef.siteid where aaaci.emailid like '%santtech1@sdpmsp.com%'
PS : change the email present in the last as per your needs.
New to ADSelfService Plus?
Related Articles
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 show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...Query to list support groups along with its account , site name , group status ( MSSQL & PGSQL )
Tested in build PGSQL (14300) and MSSQL (14306) select qd.queuename "Group Name",qe.email "Email",sdo.name "Site Name",adef.org_name "Account Name" from queuedefinition qd LEFT JOIN queue_email qe ON qd.queueid=qe.queueid LEFT JOIN sitedefinition ...Query for request attachment details (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Requests with Attachment, its name and path SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", ti.FIRST_NAME AS "Technician", sa.ATTACHMENTNAME "Attachment ...