Query to know the User details,account,site based on email

Query to know the User details,account,site based on email

 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.

      • Related Articles

      • Query to get Requesters details for each account

        PGSQL & MSSQL: Execute the query under Reports->New Query Report and export it to the desired format. select au.user_id "User Id", au.first_name "First Name", au.last_name "Last Name", sdu.isvipuser "VIP User", sdu.employeeid "Employee ...
      • Query to list Account Manager and Point of Contact

        Query to list Account Managers select au.first_name "First Name", au.last_name "Last Name",ad.org_name "Account Name", sdo.name "Site Name", CASE WHEN r.ciid IS NULL THEN 'Technician' ELSE 'Requester' END "Usertype",aci.emailid "Email ID", ...
      • Query for request attachment details

        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 Name", sa.ATTACHMENTPATH "Attachment Path" FROM ...
      • Query to show account additional fields _ Details required

        We Need to know whether that is a common account additional fields or add attribute under individual account.   So please send us the following screenshots.   1. Admin-> Account additional fields list view page.   2. Accounts->Edit account and show ...
      • Query to view task details with account

        For Postgres and MS SQL SELECT org_name "Account", "taskmilestone"."PROJECTID" AS "Project id", "taskdet"."TASKID" AS "Task ID", "taskdet"."TITLE" AS "Title", "taskcreatedby"."FIRST_NAME" AS "Created By", "taskowner"."FIRST_NAME" AS "Owner", ...