Query to export All Requesters from All Accounts

Query to export All Requesters from All Accounts

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.


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 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
WHERE 
  (
    (
      pt.id IS NULL
    ) 
    AND (SDUser.STATUS = 'ACTIVE')
  ) 
order by 
  8, 
  7, 
  1
      • 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 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 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 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 ...
      • Export solutions

        Run this query under Reports->New Query Report and export it to the desired format SELECT solution.solutionid "Solutionid", KB_Topics.TOPICNAME " Topic",Solution.TITLE "Subject ",Solution.DESCRIPTION "Contents",solutioninfo.noofhits    ...