Capabilities

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

Bidirectional capabilities apply to ASSIST and NEAL. See Bidirectional CAD Integrations.

Overview

The Data Agent will read from the SQL Reporting/Archive Database. To do this we need a user to connect.

The agent can also connect to the Training SQL database to enable Training CAD system use during simulations

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

Database permissions

The database user needs read-only SELECT access on these tables: DISPCALL, DISPHIST, DISPSTATCODE, DISPCALLTYPE, DISPCALLDISPOSITION, DISPCALLSOURCE, DISPCALLLINK and FDID.

Call taker names need one more grant. Choose one:

SELECT on SEC.
SELECT on a view of SEC that has only the SECID, USERNAME, LOGINNAME and PERSID columns. Enter the view name in the call taker lookup setting. Use this when the site does not grant access to SEC, because SEC also stores login credentials.

SELECT on PERS is optional. With it, Call Taker Name shows the last and first name of the person. Without it, Call Taker Name shows the user name from SEC or the view.

Without access to SEC or a view, incidents still arrive. The call taker then shows as the raw DISPCALL.SECID value, and Call Taker Name is empty.

Standard Data Fields

These are the standard data fields the Data Agent reads from RedAlert. The tables below show each GovWorx field and the RedAlert column it comes from. The Data Agent reads closed incidents from DISPCALL. A closed incident has a DATETIMEIN value.

Note: Additional fields are considered enhancement requests.

Incident fields

Source names are columns of the DISPCALL table unless the row says otherwise.

GovWorx field	RedAlert source	Notes
Incident ID	`DISPCALL.DISPCALLID`	CAD record key.
Incident Number	`DISPCALL.DISPCALLID`
Case Number	`DISPCALLLINK.INCNUM`	The first linked incident number.
Incident Type	`DISPCALLTYPE.DESCR`
Initial Incident Type	`DISPCALL.INITTYPE`
Caller Number	`DISPCALL.CALLPHONE`
Caller Name	`DISPCALL.CALLNAME`
Agency	`FDID.DEPTNAME`
Incident Date	`DISPCALL.DATETIMECREATE`
Disposition	`DISPCALLDISPOSITION.DESCR`
Location	`DISPCALL.ADDRESS`
Priority	`DISPCALL.DISPPRIOR`
Latitude	`DISPCALL.LATITUDE`	The Data Agent leaves values outside -90 to 90 empty.
Longitude	`DISPCALL.LONGITUDE`	The Data Agent leaves values outside -180 to 180 empty.
Call Taker ID	`SEC.LOGINNAME`	Starts as DISPCALL.SECID. The Data Agent replaces it with LOGINNAME when the user lookup finds the user. The call taker lookup setting names the table or view.
Call Taker Name	`PERS.LNAME, FNAME`	Last name, then first name. Uses the user name from the lookup view when no person record matches. Empty when the lookup finds no user.
Call Source	`DISPCALLSOURCE.DESCR`
Call Received Time	`DISPCALL.DATETIME911`	The earliest CAD note timestamp setting replaces this with the earliest note time.
Call Pickup Time	`DISPCALL.DATETIMEREC`
Closed At	`DISPCALL.DATETIMEIN`	The Data Agent reads incidents by this time. Empty for no-unit calls pulled with the no-unit setting.
Dispatch Queue At	`DISPCALL.DATETIMEDISP`
Radio Channel	`DISPCALL.WORKINGTG`

Location detail fields

The Data Agent sends the address as separate parts. GovWorx builds the displayed location from these parts with a location mask. GovWorx sets the mask for each customer, so a different address format needs no Data Agent change. The Data Agent reads these columns only when they exist in the database.

GovWorx field	RedAlert source	Notes
Location Name	`DISPCALL.SITENAME`	Mask token: `locationName`
Street Number	`DISPCALL.STRNUM`	Mask token: `streetNumber`
Street Name	`DISPCALL.STREET`	Mask token: `streetName`
Apartment	`DISPCALL.ROOMAPT`	Mask token: `apartment`
City	`DISPCALL.CITY`	Mask token: `city`
State	`DISPCALL.STATE`	Mask token: `state`
Zip	`DISPCALL.ZIP`	Mask token: `zip`
Cross Street 1	`DISPCALL.CROSS1`	Mask token: `crossStreet1`
Cross Street 2	`DISPCALL.CROSS2`	Mask token: `crossStreet2`

Tokens with no RedAlert source: building, floor, municipality, subdivision, mapPage and county. RedAlert has no column for these. A mask that uses them shows nothing in that place.

Additional incident fields

The Data Agent adds each one only when the column exists and has a value. These fields also work as mask tokens. Type them into the mask by hand. They do not appear in the mask builder token list, and the mask builder preview shows them blank. Incidents show the real values.

GovWorx field	RedAlert source	Notes
crossStreet3	`DISPCALL.CROSS3`
locationDetail	`DISPCALL.MISC`
addressInfo	`DISPCALL.ADDINFO`
emdCode	`DISPCALL.PQEMDCODE`
emdDescription	`DISPCALL.PQEMDDESC`
alarmNumber	`DISPCALL.ALARMNUM`

Notes

GovWorx field	RedAlert source	Notes
Note ID	dc- plus DISPCALL.DISPCALLID, or dh- plus DISPHIST.DISPHISTID	One note comes from the call narrative. Each history entry with a narrative is another note.
Note Text	`DISPCALL.CADNARR, DISPHIST.NARR`	The Data Agent skips empty narratives.
Note By	`DISPCALL.UNITNUM, DISPHIST.UNITNUM and SECID`	Unit number and user name, joined with a hyphen. The user name comes from the same lookup as Call Taker Name. Uses the unit or the user alone when the other is empty.
Note Time	`DISPCALL.DATETIMECREATE, DISPHIST.DATETIMEENT`	The call narrative uses DATETIMECREATE. A history entry uses DATETIMEENT.

Responders and units

GovWorx field	RedAlert source	Notes
Unit Name	`DISPHIST.UNITNUM`	One responder for each unit on an incident.
Personnel ID	`DISPHIST.UNITNUM`	The unit number.
Dispatched At	`DISPCALL.DATETIMEDISP`
Enroute At	`DISPHIST.DATETIMESTAT`	The earliest entry where DISPSTATCODE.CODE matches the en route status code setting.
Arrived At	`DISPHIST.DATETIMESTAT`	The earliest entry where DISPSTATCODE.CODE matches the arrived on scene status code setting.
Cleared At	`DISPHIST.DATETIMESTAT`	The latest entry where DISPSTATCODE.INSERVICE is Y.

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
`Host*`	Text	Host of the reporting database
`Username*`	Text	Database user
`Password*`	Password	Database password
`Database name*`	Text	Database Schema to read from
`Use Integrated Security`	Toggle	Toggle will enable the use of a domain service account that is configured to run the agent service as authentication to SQL server
`Agency FDIDs to include (comma separated)`	Text	Comma separated FDID values. The Data Agent reads only incidents from these agencies. Leave blank to read all agencies.
`Timestamps are in UTC`	Toggle	Toggle on if the database stores times in UTC. Toggle off and GovWorx converts the times using the time zone of each agency.
`Lookback on the first run, in hours`	Number	How far back the first run looks for closed incidents, before the Data Agent has a saved position. Default: 3.
`Maximum catch-up lookback in hours`	Number	Caps how far back the Data Agent looks after the connector has been idle. Default: 8.
`Also pull closed calls that never had a unit assigned`	Toggle	Toggle will also pull closed calls that never had a unit assigned. Turn this on only if this CAD records no close time for them.
`How far back to look for closed calls with no units, in minutes`	Number	Used with the toggle above. Default: 2160. Maximum: 2880.
`Table or view that resolves the call taker`	Text	Table or view that resolves the call taker from DISPCALL.SECID. Default: SEC. Use a restricted view if the account has no access to SEC. The view needs the SECID, USERNAME, LOGINNAME and PERSID columns. Enter none to skip the name lookup. See Database permissions.
`DISPSTATCODE.CODE meaning en route`	Text	The status code for en route. Varies by department, so confirm it before you rely on en route times. Default: 21.
`DISPSTATCODE.CODE meaning arrived on scene`	Text	The status code for arrived on scene. Varies by department, so confirm it before you rely on arrival times. Default: 22.
`Position Inclusion Filter`	Text	Leave blank. RedAlert has no call position source. With any value here, the Data Agent skips every RedAlert incident.
`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

CAD Connector - RedAlert