Capabilities

Capabilities	Supported
Connector Capabilities
Read	Yes
Backfill	Yes
Bidirectional Capabilities
Incident Create	No
Incident Update	No
Narrative Update	No

Overview

GovWorx offers two ways to connect to Hexagon CAD depending on your particular deployment.

Direct Database Query

The Data Agent will read from a Hexagon replicated reporting database. Agent will run prebuilt read-only SQL queries on a customer determined time interval. To do this we will need a SQL reader user account to connect.

SQL Server Configuration Notes

To ensure a successful and secure connection, verify the following:

Firewall Access: Ensure the server hosting the GovWorx Data Agent can reach the SQL Server over the appropriate network and port (typically TCP 1433).
Minimum Permissions: The provided database user must have read-only access to relevant tables, views, or stored procedures. Write access is not required or recommended.
Instance Visibility: If using a named instance, confirm SQL Server Browser service is running, or explicitly provide the port number.

Troubleshooting Tips

Login Failed? Verify the SQL user is enabled and not locked out.
Timeout Errors? Check network routes and firewall rules.
Invalid Database? Ensure the database field in the config matches the actual schema name.

Network Requirements: The server running the Data Agent should be able to connect to the CAD Reporting server over port 1433. This can be tested using PowerShell.

PowerShell Command:

Test-NetConnection -ComputerName {CAD Reporting Server name or IP} -Port 1433

CSV file via SFTP

We have also worked with customers who want more control over the query to programmatically export and upload a CSV. See this article for more details on our SFTP flow.

https://support.govworx.net/en/articles/9585970-sftp-incident-ingest

Waiting for related incidents

Hexagon can split one master event into several agency incidents that share an EID. For example, a fire incident and a law incident can share one EID. By default, the Data Agent uploads each incident when it closes. Notes that the incidents share can then be missing from the incident that closed first.

The "Wait for every visible incident sharing an EID to close before first upload" setting changes this. With the setting on, the Data Agent holds a closed incident until every incident that shares its EID has closed. This includes incidents that your filters leave out of the upload. When the group closes, the Data Agent reads each incident again and uploads it with its current notes and units.

24-hour maximum wait: The Data Agent holds a group for no more than 24 hours. The 24 hours start when the Data Agent first holds the group or when an incident in the group closed, whichever is earlier. A group that is already older than 24 hours can upload at once. After 24 hours, the Data Agent uploads the closed incidents in the group. An incident that is still open stays held until it closes. Query and upload errors are retried, so an upload can arrive later than 24 hours. You cannot change the 24-hour limit.
Tradeoff: Each incident uploads one time. After an upload at the 24-hour limit, notes that are added later to a shared incident do not reach the incident that already uploaded. The Data Agent does not update an incident after it uploads.
Restarts: Held incidents, their hold times, and retry progress survive a Data Agent restart. When you turn the setting off, the Data Agent still finishes the incidents that it already holds. Other incidents use the standard upload.
Scope: The setting covers incidents that share an EID only. It does not link cross-referenced incidents. It does not recover incidents that the Data Agent missed before you turned the setting on.
Troubleshooting: The Data Agent log shows a warning when a group reaches the 24-hour limit and when a held group still needs a retry. If held incidents do not upload, check the log for query or upload errors. The Data Agent also limits how many groups it holds at one time. At that limit, new groups can wait until held groups upload.

Standard Data Fields

These are the standard data fields the Data Agent reads from Hexagon CAD with the SQL connection. The Data Agent reads closed incidents. The tables below show each GovWorx field and the Hexagon source it comes from. Source names are columns of the table named before the dot.

Note: Additional fields are considered enhancement requests.

Incident fields

GovWorx field	Hexagon source	Notes
Incident ID	`agency_event.num_1`	CAD record key.
Incident Number	`agency_event.num_1`	Incidents that share an eid belong to the same master event.
Incident Type	`agency_event.tycod, sub_tycod`	Uses sub_tycod when tycod is blank. The subtype setting reports type-subtype. The Initial Incident Type setting replaces it with the earliest type.
Initial Incident Type	`agency_event_history.tycod, sub_tycod`	The earliest revision (rev_num) that has a type.
Agency	`agency_event.ag_id`
Priority	`agency_event.priority`
Incident Date	`agency_event.ad_ts`
Disposition	`ev_dispo.dispo`	The first disposition row for the event.
Location	`common_event.elocation`	With the common_event_call join off, built from estnum, efeanme, edirsuf, and efeatyp. The cross street setting is the fallback when blank. The Initial Location setting uses common_event_history.
Location Name	`common_event.ecompl, loc_com`	Uses loc_com when ecompl is blank. A leading @ or # is removed from loc_com.
Street Number	`common_event.estnum`
Street Name	`common_event.efeanme, edirsuf, efeatyp`	Joined with spaces.
Apartment	`common_event.eapt`
Municipality	`common_event.emun`
Cross Street 1	`common_event.xstreet1`
Cross Street 2	`common_event.xstreet2`
Call Source	`common_event_call.call_sour`	With the common_event_call join off, common_event.call_sour.
Caller Number	`common_event_call.clrnum`	With the common_event_call join off, common_event.clrnum.
Caller Name	`common_event_call.clname`	With the common_event_call join off, common_event.clname.
Call Position	`agency_event.cterm`
Call Taker ID	`agency_event.cpers`
Call Taker Name	`persl.fname, lname`	Looked up from agency_event.cpers.
Call Received Time	`agency_event.ssec`	Seconds since 1970. With the common_event_call join off, agency_event.sdts. The earliest CAD note timestamp setting replaces this with the earliest note time.
Call Pickup Time	`agency_event.sdts`
Dispatch Queue At	`agency_event.ad_ts`
Closed At	`agency_event.xdts`	The Data Agent reads incidents by close time. The last-update setting uses agency_event.udts.
Case Number	`agency_event.ProQA_CASE_NUM`	Only with the ProQA setting on.
Response Area	`agency_event.dgroup`
Additional Context	`common_event.see_cp; agency_event.eid`	Only with the caller contact or EID setting on.
Additional Personnel	`evcom.cpers`	Only with the EMS setting on. The user on the first comment that contains Event Type changed from EMS. The type is secondary call taker.
Additional Incident Fields	`common_event column of your choice`	Only with the Additional Fields setting.

Notes

GovWorx field	Hexagon source	Notes
Note Text	`evcom.comm`
Note By	`evcom.cpers`	The full name from persl.fname and lname with the full names setting on.
Note Time	`evcom.cdts`
Related persons note	`perso.nme, dob, age, eye, height, weight, race, sex, remarks, cdts2, cpers`	Only with the related persons and vehicles setting on. One note for each incident. Note By is the call taker. Note Time is Closed At.
Related vehicles note	`vehic.license, vehic_color, make, model, state, vin, remarks, involvement, cdts2, cpers`	Only with the related persons and vehicles setting on. One note for each incident. Note By is the call taker. Note Time is Closed At.

Responders and units

GovWorx field	Hexagon source	Notes
Unit Name	`un_hi.unid`	One responder for each unit and person.
Personnel ID	`un_hi_persl.empid`
Dispatched By	`un_hi.cpers`	From the first DP entry.
Dispatched At	`un_hi.cdts`	The earliest entry with unit status DP. The Data Agent skips units with no DP entry.
Enroute At	`un_hi.cdts`	The earliest entry with unit status ER.
Arrived At	`un_hi.cdts`	The earliest entry with unit status AR.
Cleared At	`un_hi.cdts`	The earliest entry with unit status AV, AP, AQ, or AM.

Data Agent Settings

To prepare for this connection, we need the following details. These are settings in the GovWorx Data Agent configuration for this connector. A toggle is an on/off switch in that configuration.

Setting	Type	Explanation
`SQL Server host*`	Text	Host of the reporting database
`SQL Server username**`	Text	Database user
`SQL Server password**`	Password	Database password
`Hexagon CAD database name*`	Text	Database Schema to read from
`Use Windows authentication instead of username/password`	Toggle	Whether to use gMSA to authenticate to the database. Either enable this or supply both a username and password.
`Connect to Always On read-only replica`	Toggle	Toggle will connect with multi-subnet failover on and a read-only application intent, so the connection goes to a readable secondary in an availability group.
`Include incident subtype (sub_tycod) with type code`	Toggle	Toggle will report incident type as "{type}-{subtype}". A subtype of default is dropped.
`Use Initial Incident Type Instead of Final`	Toggle	Toggle will use the earliest incident type in the incident history as the Incident Type. The Initial Incident Type field is filled from that history whether this is on or off.
`Exclude system-generated notes (evcom COMM_KEY filter)`	Toggle	Toggle will leave out system-generated notes. The Data Agent keeps only evcom rows with a comment key of 0.
`Include related persons and vehicles as notes (perso/vehic tables)`	Toggle	Toggle will add one note of related persons and one note of related vehicles to each incident.
`Show full names for note creators instead of CAD IDs (persl table)`	Toggle	Toggle will show the first and last name of each note author in place of the CAD ID.
`Use Intergraph-style responder join (un_hi.unique_id to un_hi_persl.un_hi_rec_id)`	Toggle	Toggle will join responders with un_hi.unique_id and un_hi_persl.un_hi_rec_id. When off, the join uses un_hi_child_change_id.
`Omit common_event_call join`	Toggle	Toggle will skip the common_event_call table. Call Source, Caller Number, and Caller Name then come from common_event. Call Received Time comes from the agency event start time. Location is built from the street parts in common_event.
`Query lookback window in minutes (default: 30)`	Text	Minutes of history to read when the Data Agent has no saved position from an earlier run. Default: 30.
`Query a fixed date range instead of the rolling lookback window, for backfilling historical data`	Toggle	Toggle will query a fixed date range in place of the rolling lookback window.
`Fixed range: beginning time (yyyy-MM-dd HH:mm)`	Text	Beginning time of the fixed range. Shown when the fixed range toggle is on.
`Fixed range: end time (yyyy-MM-dd HH:mm)`	Text	End time of the fixed range. Shown when the fixed range toggle is on.
`Split fixed range into chunks no longer than (hh:mm)`	Text	Splits the fixed range into chunks to avoid query timeouts. Default: 01:00. Shown when the fixed range toggle is on.
`Agency IDs to exclude from query, comma-separated (e.g. FIRE,EMS)`	Text	Incidents from these agencies are not read.
`Use xstreet1/xstreet2 as location fallback for intersection calls (Hexagon 9.2)`	Toggle	Toggle will build the location from the cross streets and street name when the location is blank.
`Use Initial Location from Common Event History If available`	Toggle	Toggle will replace the location with the earliest address in common_event_history, which is the address first entered when the call address changed during the call.
`Additional Fields`	Text	A JSON object that maps a name you choose to a common_event source. The source is location_name, street_number, street_name, apartment, municipality, cross_street_1, cross_street_2, or a common_event column. The values appear as additional incident fields. Default: {}
`Include master event ID (eid) in additional context`	Toggle	Depending on your configuration, either num_1 or eid is used for each incident's ID. Enable this to always include the EID in the "Additional context" field of each event.
`Include caller contact flag (common_event.see_cp) in additional context`	Toggle	Toggle will add CallerContact: Yes or CallerContact: No to additional context, from common_event.see_cp.
`Add EMS event-type-change user as secondary call taker AdditionalPersonnel`	Toggle	Toggle will add the user who made the first Event Type changed from EMS comment as a secondary call taker in additional personnel.
`Max seconds between pickup and incident times (empty to disable)`	Text	Leaves out incidents where the gap between the dispatch queue time and the call time is this many seconds or more. Empty turns this off.
`agency_event view name (optional override)`	Text	View to read in place of the agency_event table (incident records).
`common_event view name (optional override)`	Text	View to read in place of the common_event table (event details and location).
`common_event_call view name (optional override)`	Text	View to read in place of the common_event_call table (caller and call data).
`common_event_history view name (optional override)`	Text	View to read in place of the common_event_history table (initial and changed location values).
`an_al view name (optional override)`	Text	View to read in place of the an_al table (call pickup times).
`evcom view name (optional override)`	Text	View to read in place of the evcom table (incident notes and comments).
`persl view name (optional override)`	Text	View to read in place of the persl table (personnel names).
`un_hi view name (optional override)`	Text	View to read in place of the un_hi table (unit and responder history).
`un_hi_persl view name (optional override)`	Text	View to read in place of the un_hi_persl table (responder personnel).
`Use last-update timestamp (udts) instead of closed timestamp (xdts)`	Toggle	Toggle will read incidents by the last-update time in place of the close time. Use it for training CADs where calls are never closed.
`Minutes since last update before a training call is treated as complete`	Text	Required when the last-update toggle is on. Shown when that toggle is on.
`Use ProQA Case Number as CC Case Number`	Toggle	Toggle will fill Case Number with the ProQA case number.
`Maximum lookback in hours`	Number	Caps how far back a stale cursor can reach so an idle or reconnected connector does not issue an oversized catch-up query. Default: 8.
`Wait for every visible incident sharing an EID to close before first upload`	Toggle	Toggle will hold each incident until every incident that shares its EID has closed, for no more than 24 hours. See Waiting for related incidents above. Default: off. Do not turn this on with the fixed range or last-update toggles. When either one is also on, the Data Agent does not read incidents and logs an error.
`Use earliest CAD note timestamp for Received At`	Toggle	Toggle will use the earliest CAD note time as Call Received Time. Use it when the first note marks the start of the call better than the CAD pickup time. Call Pickup Time does not change. When no note has a time, Call Received Time stays as read from CAD. Default: off. This setting is on every CAD connector.

*: Required

**: Either Windows authentication or (username and password) is required.

CAD Connector - Hexagon