Query to export All Requesters from All Accounts

Query to export All Requesters from All Accounts

Execute the below query under Reports > New Query Reports:  

SELECT AaaUser.USER_ID,AaaUser.FIRST_NAME "FullName",AaaLogin.NAME "LoginName",AaaLogin.DOMAINNAME "Domain",AaaContactInfo.EMAILID "Email",DepartmentDefinition.DEPTNAME "Department",SDOrganization.NAME "Site",ad.org_name"Account",AaaContactInfo.LANDLINE "Phone",SDUser.JOBTITLE "JobTitle",AaaContactInfo.MOBILE "Mobile" FROM AaaUser LEFT JOIN UserDepartment ON AaaUser.USER_ID=UserDepartment.USERID LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID LEFT JOIN DepartmentDefinition ON UserDepartment.DEPTID=DepartmentDefinition.DEPTID LEFT JOIN SiteDefinition ON DepartmentDefinition.SITEID=SiteDefinition.SITEID LEFT JOIN SDOrganization ON SiteDefinition.SITEID=SDOrganization.ORG_ID INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID LEFT JOIN HelpDeskCrew ON SDUser.USERID=HelpDeskCrew.TECHNICIANID LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID 
LEFT JOIN accountsitemapping asm ON asm.SITEID=SiteDefinition.SITEID LEFT JOIN accountdefinition ad ON ad.ORG_ID=asm.ACCOUNTID
WHERE  ((HelpDeskCrew.TECHNICIANID IS NULL) AND (SDUser.STATUS = 'ACTIVE')) order by 8,7,1

User the query given below from builds 10600 and above.

  AaaUser.FIRST_NAME "FullName", 
  AaaLogin.NAME "LoginName", 
  AaaLogin.DOMAINNAME "Domain", 
  AaaContactInfo.EMAILID "Email", 
  DepartmentDefinition.DEPTNAME "Department", 
  SDOrganization.NAME "Site", 
  ad.org_name "Account", 
  AaaContactInfo.LANDLINE "Phone", 
  SDUser.JOBTITLE "JobTitle", 
  AaaContactInfo.MOBILE "Mobile" 
  LEFT JOIN UserDepartment ON AaaUser.USER_ID = UserDepartment.USERID 
  LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID = AaaUserContactInfo.USER_ID 
  LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID = AaaContactInfo.CONTACTINFO_ID 
  LEFT JOIN DepartmentDefinition ON UserDepartment.DEPTID = DepartmentDefinition.DEPTID 
  LEFT JOIN SiteDefinition ON DepartmentDefinition.SITEID = SiteDefinition.SITEID 
  LEFT JOIN SDOrganization ON SiteDefinition.SITEID = SDOrganization.ORG_ID 
  LEFT JOIN Portalusers PU ON SDUser.USERID = PU.userid 
  LEFT JOIN portaltechnicians PT ON PU.id = pt.id
  LEFT JOIN AaaLogin ON AaaUser.USER_ID = AaaLogin.USER_ID 
  LEFT JOIN accountsitemapping asm ON asm.SITEID = SiteDefinition.SITEID 
  LEFT JOIN accountdefinition ad ON ad.ORG_ID = asm.ACCOUNTID
      pt.id IS NULL
order by 

        New to ADManager Plus?

          New to ADSelfService Plus?

            • Related Articles

            • Export accounts from the SDP MSP application

              Way to export the accounts configured in the SDP MSP application. Login as an Admin and click on the "Reports"icon . Then, click on "New query report" and run the below query: select adef.org_name "Account ...
            • Query to find out who created Accounts

              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 the Org_IDs of the above query from the result ...
            • 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 ...
            • SQL Query to export request details

              Supported only for SQL DB(14201 build). Executed in SQL DB directly to export the requests. Below query to export requests for one account / site. SELECT "wo"."WORKORDERID" AS "Request ID", "wo"."TITLE" AS "Subject", "wotodesc"."FULLDESCRIPTION" AS ...
            • Query to List down Account and corresponding sites and its requesters

              Applicable for 10604 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 Left Join Sitedefinition sdef on asm.siteid=sdef.siteid ...