HaloPSA SQL Schema: the tables and columns that matter

HaloPSA reports run read-only T-SQL against a schema inherited from NetHelpDesk. The names make no sense until someone tells you, so here they are.

  AREA (clients) ──Areaint──┐
  UNAME (agents) ─Assignedtoint─┤
  TSTATUS ─────────Status───────┼──► FAULTS (tickets) ◄──Faultid── ACTIONS (notes, time)
  REQUESTTYPE ───Requesttype────┘          │
                                           └── FEEDBACK (CSAT) via FBFaultID

Core tables

Table	What it is	Columns you'll use
FAULTS	Tickets, but also projects, opportunities and tasks	Faultid, Symptom, dateoccured, datecleared, Status, Areaint, Assignedtoint, Requesttype, urgency, impact, fixbydate, FDeleted, Flastactiondate
ACTIONS	Every note, email, status change and time entry	Faultid, whoagentid, ActionArrivalDate, timetaken (decimal hours), ActionChargeHours, ActionChargeAmount
AREA	Clients	Aarea, aareadesc (name), AIsInactive
SITE	Client sites	Ssitenum, sdesc, Sarea
USERS	End users	Uid, uusername, uemail, Usite
UNAME	Agents	Unum, uname, Uisdisabled, usection
TSTATUS	Statuses	Tstatus, tstatusdesc
REQUESTTYPE	Ticket types	RTid, rtdesc, RTIsOpportunity, RTIsProject
FAULTARC / ACTARC	Archived tickets and actions	Same shape. UNION ALL them for long-range history

Five rules that stop your reports lying

Everything is a FAULT. Join REQUESTTYPE and filter RTIsOpportunity = 0 AND RTIsProject = 0, or your ticket counts include your sales pipeline.
Soft deletes everywhere. Always add ISNULL(FDeleted,0) = 0.
No priority column. FAULTS has urgency and impact; priority is derived.
No SLA breach flag. Compute it: datecleared > fixbydate (resolution), FResponseDate > FRespondByDate (response).
Stale tickets need no join. Flastactiondate on FAULTS gives you last activity directly.

Find any column yourself

SELECT TOP 10000 TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'FAULTS'
ORDER BY ORDINAL_POSITION

Swap FAULTS for any table. The TOP is mandatory, see the errors page.