Configuration Management - SLAs configured / details of all technicians and requesters / last logged in time of technicians and requesters
Below report returns the complete list of SLAs configured in the application.
SELECT sdo.name "Site Name" ,
sla.slaname "SLA",
MAX(sla.duebydays) "SLA Days",
MAX(sla.duebyhours) "SLA Hours",
MAX(sla.duebyminutes) "SLA Minutes" ,
MAX(sla.fr_duebydays) "First Response Days",
MAX(sla.fr_duebyhours) "First Response Hours",
MAX(sla.fr_duebyminutes) "First Response Minutes",
array_to_string(array_agg(au.first_name), ' ') "Escalate to" FROM sladefinition sla
LEFT JOIN sitedefinition sdf ON sla.siteid=sdf.siteid
LEFT JOIN sdorganization sdo ON sdf.siteid=sdo.org_id
LEFT JOIN timezonedefinition tz ON sdf.timezoneid=tz.timezoneid
LEFT JOIN slaescalation es ON sla.slaid=es.slaid
LEFT JOIN operationalhoursdef oh ON sdf.siteid=oh.siteid
LEFT JOIN hoursofoperation op ON oh.operationalhoursdefid=op.operationalhoursdefid
LEFT JOIN escalateton eton ON es.escalatetoid=eton.escalatetoid
LEFT JOIN aaauser au ON au.user_id=eton.userid
LEFT JOIN daysofoperation od ON oh.operationalhoursdefid=od.operationalhoursdefid
GROUP BY sdo.name,
sla.slaname ORDER BY 1
Below report returns the complete details of all technicians.
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",
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
INNER JOIN HelpDeskCrew ON SDUser.USERID=HelpDeskCrew.TECHNICIANID
LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
WHERE (SDUser.STATUS = 'ACTIVE')
Below report returns the complete details of all requesters.
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'))
Below report returns the last logged in time of technicians and requesters.
SELECT AaaUser.FIRST_NAME "Name",
MAX(AaaLogin.NAME) "LoginName",
MAX(AaaContactInfo.EMAILID) "Email",
MAX(AaaAccSession.USER_HOST) "IP Address",
longtodate(MAX(AaaAccSession.OPENTIME)) "Last Logged In Time" FROM AaaUser
INNER JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID
INNER JOIN HelpDeskCrew ON SDUser.USERID=HelpDeskCrew.TECHNICIANID
LEFT JOIN AaaLogin ON AaaUser.USER_ID=AaaLogin.USER_ID
INNER JOIN AaaAccount ON AaaAccount.login_id=AaaLogin.LOGIN_ID
INNER JOIN AaaAccSession ON AaaAccount.ACCOUNT_ID = AaaAccSession.ACCOUNT_ID
LEFT JOIN AaaUserContactInfo ON AaaUser.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
WHERE SDUser.STATUS = 'ACTIVE'
GROUP BY AaaUser.FIRST_NAME
Click this link to navigate to the next report.