Attached you will find South Sound 911's presentation slides for the upcoming Hexagon Public Safety Users' Group Conference on November 5-7, 2017 in Huntsville, Alabama.

We are hoping you can use this preview to get an idea of what content will be presented and also have a chance to brainstorm how these solutions can help your own agency.

Thank you for taking a look!

Brien Foss | IS – ITS II, South Sound 911 | brien.foss@SouthSound911.org

The XML for the Systems.xml looks like:

  <System>

    <Group>DEFAULT</Group>

    <Affinity>0</Affinity>

    <RedundancyMode>1</RedundancyMode>

    <Name>SS911_RelocateUnit.xml</Name>

    <LoadOrder>0</LoadOrder>

    <LoadDelay>0</LoadDelay>

    <LoadType>0</LoadType>

    <AllowHTTPAccess>0</AllowHTTPAccess>

    <TryReloading>0</TryReloading>

    <ReloadDelay>0</ReloadDelay>

    <HasErrors>0</HasErrors>

    <LastModified>12/31/1600 4:00:00 PM</LastModified>

  </System>

Hello EdgeFrontier Users!

South Sound 911 has developed several different EdgeFrontier systems which trigger in-house business processes upon detecting specific events in our CAD system. For example, we have processes that trigger when specific units are dispatched or primary units are assigned to an event. We found that server and network outages would occasionally disrupt these processes and decided that a system to re-trigger these events would be valuable to our users. This is also useful during and following patches or configuration changes to the CAD system because it allows monitoring of expected vs. actual notifications.

The “ReQueue_Missed_Events_Or_Updates” EdgeFrontier system provides us with the ability to detect when data is missing, add any events with missing data to a queue, and then request event information for each event in the queue. The queue is implemented as a database table and therefore can be used from within EdgeFrontier and by applications which write events numbers directly to the queue.

Basic Design:

• Consists of:
  • An EdgeFrontier system which reads from a SQL Table  (queue) and then runs IFCAD commands to request event information from the CAD system
  • A database table which holds an event number, information on the requester of the event and the time the event was requested to be re-queued
  • Several SQL stored procedures which read, delete and insert rows into the queue
  • A web based application which can be used by support staff to add event numbers to the queue
  • A SQL job which runs a comparison to detect differences between a local data warehouse and the Hexagon CAD database.

The attached zip file includes the following:

ReQueue_Missed_Events_sqlObjects.SQL – This SQL script will create the database objects needed by the re-queue system. Note: We recommend using a different database for these objects, we do not have them in our CAD database.

SS911_ReQueue_Missed_Events_Or_Updates.XML  - Primary EdgeFrontier Re-queue system

SS911_Send_CAD_Event_ReQueue_Command.XML (Optional) – Secondary EdgeFrontier system which demonstrates the re-queue functionality in the primary system.

CADEventRequeue Visual Studio Solution (Optional) – Support Staff application which can be used to manually insert events into the queue based upon event number or agency ID and date / time.

Using this re-queue system will require some configuration and setup. Possible steps include:

[] Modifying the ReQueue_Missed_Events_sqlObjects.SQL script with any modifications needed for your environment. Note: We recommend using a different database for these objects, we do not have them in our CAD database.

[] Modifying SS911_ReQueue_Missed_Events_Or_Updates.xml– The EdgeFrontier system will need modifications done to many of the objects to connect to your database. Additionally, the following global variables will need to be created in EdgeFrontier

• @SS911_ReQueue_Missed_Events_Or_Updates_Database@ - The database that contains the event queue
• @SS911_ReQueue_Missed_Events_Or_Updates_Limit@ - The number of events that should be read each time events are “Re-queued”. This value should be used to prevent re-queue requests from overwhelming the CAD system

[] Modifying the CADEventRequeue visual studio solution by adding database read, write and connection methods. (Optional)

The SQL Job which runs an automated comparison of a local warehouse and the Hexagon database has not been included as it is very specific to our business logic. This portion of the process is not required for the system to operate, but does allow us to detect and re-queue any events that might be missing on an hourly schedule. Feel free to let us know if you run into any trouble implementing this for your CAD configuration.

We hope that you find this system useful! Please post below if you have any questions or feedback.

Thanks!

Dan Cox

ITS – II

South Sound 911

Dan.Cox@SouthSound911.org

We had a business need to automatically populate the "Talkgroup" field of the Dispatcher client application, with the Radio Talkgroup value.

A Radio Talkgroup is defined by 'X units':

An 'X' Unit is code word for:

"This is not a real unit, this is the radio talkgroup you real live units should be using on the call you are currently dispatched to".

The EdgeFrontier system below leverages the IFCADSQLUpdate command, and updates the following database column on the distinct row represented by the agency_event.num_1 value.

To verify:

[] - log in to the dispatcher client for the target environment

[] - create an event

[] - dispatch a unit that begins with 'X'. There is usually at least 1 available

[] - refresh the view on the event you created, by Event\Select, then search for it... or use the other many ways to 're-view' the event. This is required because the UI does not automatically refresh on its own.

[] - ensure the "talkgroup" field is populated with the Unit you dispatched, MINUS the X. Example, dispatching XC2A, would result in C2A being in the Talkgroup field

[] - verify correct value is fdound in target database as well (<Your Database Names>)

use LIVE_930

go

select aecust1 from agency_event

where num_1 = '<yourEventNumber>'

The psuedocode for what the IFCADSQLUpdate Command is doing would be:

UPDATE Live_930.agency_event

SET aecust1 = ReturnValue_from_Action_TrimStartingXs

WHERE num_1 = [EventArgument].IFCADUnitReport.AgencyEventID

TitleName/Agency Point of ContactVersion of EdgeFrontier

Adds a more detailed Ani/Ali remark to an event upon creation and when additional Ani/Ali data is associated with the event. The comment is added using the IFCADEventAddComments command. The Ani/Ali data is queried directly from the CAD database via a view that we added to our instance called vw_AniAliDataForRemark. 

If you want to customize what the ani/ali remark says or formatting you would change the view. You could also use this as an example for adding a comment to an event based off of data from a database (or other source). We were advised to put the views in a schema specific to our organization so that when database updates are made our objects would not be deleted/modified.

To set up you will have to enter the database connection information.

The format of the remark currently looks something like this:

Here is the view... I couldn't add multiple files.

CREATE VIEW [ss911].[vw_AniAliDataForRemark]  
(
     [num_1]
    ,[eid]
    ,[eventCallID]
    ,[aniAliString]
)
AS
    WITH cte AS(
    --Some of the data when returned contains carriage returns (CHAR(13)) and/or line feeds (CHAR(10)) from the source. Replace these characters with a space to ensure
    --AniAli data is not broken up when added to remarks.
    SELECT ae.[num_1], ae.[eid], cech.[event_call_id],
        REPLACE(REPLACE(
            'LOCATION:'    + ISNULL(aniali.[estnum],'') + ' ' + ISNULL(aniali.[efeanme],'') + coalesce(', ' + aniali.loc_id, '') + ', ' + ISNULL(aniali.[ccity],'') +
            ' \PHONE:'    + ISNULL(aniali.[clrnum],'')  +
            ' \COMP:'    + COALESCE(aniali.clname, cech.clname, '') +
            ' \SRC:'    + ISNULL(aniali.[service],'') +
            ' \PNUM:'    + ISNULL(aniali.[pilot_nnx],'')   +
            ' \ESN_TEXT:'    + ISNULL(aniali.[esn_text],'') + ISNULL(' ' + [y_lat],'') + ISNULL(' ' + [x_long],'') +
            ' \ESN:'    + ISNULL(aniali.[esn], '')
        ,CHAR(13), ' '), CHAR(10), ' ') as aniAliString
    FROM [dbo].[an_al] as aniali
    INNER JOIN [dbo].[common_event_call_history] AS cech ON cech.call_id = aniali.call_num
    INNER JOIN [dbo].[agency_event] AS ae ON ae.eid = cech.eid
    )
    --Do not show rows that have already been listed as a remark for this eid.
    SELECT * FROM cte
    WHERE aniAliString NOT IN (SELECT comm FROM evcom WHERE eid = cte.eid)
GO