Index / Free SQL

HaloPSA Agent Utilisation Report (free SQL)

Hours each active agent logged in the last 30 days, as a percentage of their available time. The quickest way to spot who is overloaded and who has capacity before you hire.

SELECT TOP 200
    u.uname AS [Agent],
    CAST(SUM(ISNULL(ac.timetaken,0)) AS DECIMAL(8,1)) AS [Hours Logged 30d],
    CAST(100.0 * SUM(ISNULL(ac.timetaken,0)) / (22 * 7.5) AS DECIMAL(5,1)) AS [Utilisation %]
FROM UNAME u
LEFT JOIN ACTIONS ac ON ac.whoagentid = u.Unum
    AND ac.ActionArrivalDate >= DATEADD(DAY,-30,GETDATE())
WHERE ISNULL(u.Uisdisabled,0) = 0
GROUP BY u.uname
ORDER BY [Utilisation %] 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

• The available time is 22 * 7.5 hours. Change it to your own working days and productive hours.
• Part-timers will look low. Filter them out or give them their own copy with their hours.
• Utilisation counts all logged time. For billable-only, see the billable vs non-billable report.