Avensoft Help Desk - Service level agreement violation report

Service level agreement violation report

You can set up contracts in Services. You can then define service level agreements within contracts. A service level agreement (SLA) defines three date time values: response due, resolution due and closing due. How do you list the requests that were either responded to late, resolved late or closed later?

You can go to Requests, Reports and create a new SQL report. Paste the SQL statement below into the the SQL box and run the report.

SELECT DISTINCT TOP 500 sr_id,u1.usr_display_name AS SubmitterName,
sr_submitted_on,sr_subject,u2.usr_display_name AS AssignedToName,
rs_name AS StatusName,rp_symbol AS PrioritySymbol 
FROM ns4_service_request 
LEFT JOIN ns4_user u1 ON sr_submitter = u1.usr_id 
LEFT JOIN ns4_user u2 ON sr_assigned_to = u2.usr_id 
LEFT JOIN ns4_req_status ON sr_status_id = rs_id 
LEFT JOIN ns4_req_priority ON sr_priority_id = rp_id 
WHERE sr_deleted = 0 
AND 
(
  (
    sr_respond_due IS NOT NULL 
    AND 
    (
      (sr_responded_on IS NULL AND sr_respond_due < getdate()) 
      OR sr_responded_on > sr_respond_due
    )
  )
  OR
  (
    sr_resolve_due IS NOT NULL 
    AND 
    (
      (sr_resolved_on IS NULL AND sr_resolve_due < getdate()) 
      OR sr_resolved_on > sr_resolve_due
    )
  )
  OR
  (
    sr_close_due IS NOT NULL 
    AND 
    (
      (sr_closed_on IS NULL AND sr_close_due < getdate()) 
      OR sr_closed_on > sr_close_due
    )
  )
)
ORDER BY sr_submitted_on ASC