Account details Query

SELECT vd.org_name'Account',aci.LANDLINE'Site Contact number',apa.CITY'CITY',apa.POSTALCODE'Postal Code',apa.state'State',aci.WEB_URL'WEB URL' FROM accountdefinition vd

    LEFT JOIN sdorgcontactinfo SDCI ON sdci.ORG_ID=vd.ORG_ID

    LEFT JOIN aaacontactinfo aci ON aci.CONTACTINFO_ID=sdci.CONTACTINFO_ID

    LEFT JOIN SDOrgPostalAddr sdp ON sdp.ORG_ID=vd.ORG_ID

    LEFT JOIN AaaPostalAddress apa ON sdp.POSTALADDR_ID=apa.POSTALADDR_ID

    ORDER BY 1

Site details query

SELECT ad.org_name'Account',sdo.NAME'Site Name',aci.LANDLINE'Site Contact number',sdo.DESCRIPTION'Description',apa.CITY'CITY',apa.POSTALCODE'Postal Code',apa.state'State',aci.WEB_URL'WEB URL' FROM accountsitemapping asm 

LEFT JOIN accountdefinition ad ON ad.ORG_ID=asm.ACCOUNTID

LEFT JOIN sdorganization sdo ON sdo.ORG_ID=asm.SITEID

LEFT JOIN sdorgcontactinfo SDCI ON sdci.ORG_ID=sdo.ORG_ID

LEFT JOIN aaacontactinfo aci ON aci.CONTACTINFO_ID=sdci.CONTACTINFO_ID

LEFT JOIN SDOrgPostalAddr sdp ON sdp.ORG_ID=sdo.ORG_ID

LEFT JOIN AaaPostalAddress apa ON sdp.POSTALADDR_ID=apa.POSTALADDR_ID

ORDER BY 1

User details query

SELECT AaaUser.USER_ID,AaaUser.FIRST_NAME 'FullName',AaaLogin.NAME 'LoginName',AaaLogin.DOMAINNAME 'Domain',AaaContactInfo.EMAILID 'Email',DepartmentDefinition.DEPTNAME 'Department',SDOrganization.NAME 'Site',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 WHERE  ((HelpDeskCrew.TECHNICIANID IS NULL) AND (SDUser.STATUS = 'ACTIVE'))

Krishna Bharat

ServiceDesk Plus - MSP Support team