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 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 ...Requester details
This report is used to find the Requester complete details. SELECT AaaUser.USER_ID, AaaUser.FIRST_NAME "FullName", AaaLogin.NAME "LoginName", AaaLogin.DOMAINNAME "Domain", AaaContactInfo.EMAILID "Email", DepartmentDefinition.DEPTNAME "Department", ...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 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 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 ...