Configuration Management - to get the list of users enabled with service request approval permission / list of sites present in the application
This report returns all the users enabled with service request approval permission.
SELECT aaauser.first_name "Approver Name",
dpt.deptname "Department",
sdo.name "Site",
sd.jobtitle "Job Title" FROM srapproverdetails
LEFT JOIN aaauser ON aaauser.user_id = srapproverdetails.approver
LEFT JOIN aaacontactinfo ON aaauser.user_id = aaacontactinfo.contactinfo_id
LEFT JOIN sduser sd ON aaauser.user_id=sd.userid
LEFT JOIN userdepartment ud ON aaauser.user_id=ud.userid
LEFT JOIN DepartmentDefinition dpt ON ud.DEPTID=dpt.DEPTID
LEFT JOIN SDOrganization sdo ON dpt.SITEID=sdo.ORG_ID
WHERE sd.status='Active';
Below report returns complete details of sites configured in the application.
select sd.name"Site Name",
max(sd.Description) "Description",
max(regionDef.REGIONNAME) "Region",
max(timezonedefinition.displayname) "Timezone",
max(apa.DOOR_NO) "Door Number",
max(apa.STREET) "Street",
max(apa.LOCATION) "Location",
max(apa.LANDMARK) "Land Mark",
max(apa.CITY) "City",
max(apa.POSTALCODE) "Postal Code",
max(aci.Emailid) "Email ID",
max(aci.landline) "Phone number",
max(apa.STATE) "State",
max(apa.COUNTRY) "Country",
max(scs.SETTINGDETAIL) "Related settings" from sitedefinition vd
left join sdorganization sd on vd.siteid=sd.org_id
left join aaaorgcontactinfo aoci on sd.org_id=aoci.org_id
left join aaacontactinfo aci on aoci.contactinfo_id=aci.contactinfo_id
left join sdorgpostaladdr spa on spa.org_id=sd.org_id
left join aaapostaladdress apa on apa.POSTALADDR_ID=spa.POSTALADDR_ID
LEFT JOIN RegionDefinition regionDef ON vd.REGIONID=regionDef.REGIONID
LEFT JOIN timezonedefinition on vd.timezoneid=timezonedefinition.timezoneid
LEFT JOIN SiteConfigSettings as scs on vd.siteid=scs.siteid group by sd.name
order by 1
Click this link to navigate to the next report.