practical tooling for MSP ops

HaloPSA SLA Breach Report by Client (free SQL)

Resolution SLA breaches by client and urgency for the last 30 days, with breach %. Halo has no breach flag, so this computes it from datecleared > fixbydate and respects exclude from SLA.

SELECT TOP 1000
    a.aareadesc                          AS [Client],
    f.urgency                            AS [Urgency],
    COUNT(*)                             AS [Closed Tickets],
    SUM(CASE WHEN f.datecleared > f.fixbydate THEN 1 ELSE 0 END) AS [Breached],
    CAST(100.0 * SUM(CASE WHEN f.datecleared > f.fixbydate THEN 1 ELSE 0 END)
        / NULLIF(COUNT(*),0) AS DECIMAL(5,1)) AS [Breach %]
FROM FAULTS f
JOIN AREA a        ON a.Aarea = f.Areaint
JOIN REQUESTTYPE r ON r.RTid  = f.Requesttype
WHERE f.datecleared >= DATEADD(DAY, -30, GETDATE())
  AND f.fixbydate IS NOT NULL
  AND ISNULL(f.FDeleted,0) = 0
  AND ISNULL(f.FexcludefromSLA,0) = 0
  AND ISNULL(r.RTIsOpportunity,0) = 0 AND ISNULL(r.RTIsProject,0) = 0
GROUP BY a.aareadesc, f.urgency
ORDER BY [Breach %] DESC

How to add it

  1. In Halo go to Configuration → Reporting → Reports (or the Reports module) and click New.
  2. Set Data Source to Custom SQL Query and paste the SQL.
  3. Click Load Report to preview, then save it. You can feed it into a dashboard widget.

Tweaks