How to Configure the Data Shipping Application (DSA)

The Intterra Data Shipping Application (DSA) is a small C# utility created to help simplify the client Analytics data export and ingest data into the Intterra Data Center API

Overview

The application uses a SQL server connection to connect to the client database incident and unit data tables. It reads any updated data records, packages the data into simple text delimited CSV files, and then submits them to the Data Center API for processing.

The application is installed on a client Windows system with direct or network access to the client database and analytics data tables. The setup process creates and installs the application into the "C:\Program Files (x86)\Intterra\Data Shipping App" directory and also creates an 'Intterra' directory in the 'C:\Program Data' directory which contains the application's configuration 'settings.json' file. The application reads this file when it is started and any changes made to the application settings are saved to this file automatically when the application is closed.

The 'settings.json' file contains the API connection authorization values, the SQL database connection string and authentication values, the incident and unit data query definitions, and it also provides the capability to manually submit a data update to the API or create a task within the Windows 'scheduled tasks' settings.

The 'scheduled task' settings determines the interval at which the application is executed to query the client database and transmit any new data to the Data Center API.

Application Tabs

Profile

The applications 'Profile' tab contains the profile 'Name', 'Type' (Analytics/SitStat), 'Agency', as well as a few additional options. The functionality allows for multiple profiles to be created within the application if necessary.

Authentication

The application's 'Authentication' tab contains the client's DSA API Key and API Key Secret used to authenticate to the Intterra API. The API Key and API Key Secret will be provided by Intterra through secure means. The 'Test API Connectivity' button sends a test request to the API to confirm that the credentials are valid and either returns a message indicating success or provides an error code to assist in troubleshooting the connection.

Database

The 'Database' tab contains the server connection string to connect to the client's SQL database containing the incident and unit data tables. From the dropdown, select whether the Driver is MSSQL or ODBC.

The SQL connection string uses the format: "Data Source:my_server; Initial Catalog=my_db; User Id:my_uid; Password: my_password"
The ODBC connection string uses the format: "Driver= {ODBC_driver_name}; Server= my_server; Port= my_port; Database=mydb; Uid=report; Password= my_password"

The simplest method to connect the DSA to the data source uses a SQL login with data_reader permissions to access the source database. If a separate database is desired to store the queries\views - which usually makes it simpler to create and manage the data queries within the SQL Server Management Tools - an account with data_writer permissions is required to work on the queries or views. A Windows network domain account login is not supported through the SQL or ODBC drivers' connections, although a domain-based Group Managed Service Account (gMSA) will also work in a Windows 2012, or higher, network and SQL database environment. For gMSA account configuration, there are several helpful articles published here: https://techcommunity.microsoft.com/t5/Core-Infrastructure-and-Security/bg-p/CoreInfrastructureandSecurityBlog

The 'Test Data Connectivity' button checks the connection string's ability to connect to the target database and either returns a message indicating success or provides an error code to assist in troubleshooting the connection.

Where to run the query? (two options)

In the DSA
In view on client database.

why? faster and client has control
how? select * from view

Incidents Query

The 'Incidents Query' tab contains the SQL query code used to query the incident data from the client's SQL database. Optimally, all of the incident data would already be collected and formatted into the Intterra API data table definitions to keep the query as simple as possible. The DSA dynamically builds the query string from the provided input fields and submits the request to the client database. Although the DSA is capable of utilizing semi-complex WHERE and JOIN statements in the query clause, the preferred method is not to use this capability and, instead, keep the data processing within the client procedures to load the data tables and keep the DSA's 'select' query as simple as possible. The DSA's execution of the 'Select' statement includes a call to the Intterra API that returns the most recent timestamp, the 'last_modified' field in the client's API data, which can be used to limit the selection query to only those records that were updated since the last time the data was successfully exported. The returned datetime value is most effective if it is used to compare to a self-populating datetime value that timestamps each record as it is written into the data table.

The 'Test Incident Query' button allows for checking of the select query without actually sending an update request to the API to confirm the selection results. The max number of records specified in the database tab included in the query and the results window will indicate the total number of requests required to submit the selected data records to the API. It either returns the results of the query or provides an error code to assist in troubleshooting the query.

Units Query

The 'Units Query' tab functions in the same manner as the 'Incidents Query', but is used to select the collected unit data associated with the selected incidents. The 'Where' clause execution allows the same 'last_modified' timestamp filtering capability to select only those unit records that were updated within the unit data, or the filter can be left out if it is desired to always send all associated unit records for every updated incident.

The 'Test Unit Query' button allows for checking of the select query without actually sending an update request to the API to confirm the selection results. The results window will either return the results of the query or provide an error code to assist in troubleshooting the query.

Run All

The 'Run All' tab provides a single button, 'Upload Data', to manually execute the API data request submission to the API. The results window either displays the batch id's returned by the API server for each successfully submitted batch or provides an error code to assist in troubleshooting the request submission.

Task Scheduler

The 'Task Scheduler' tab provides a button, 'Schedule Task', to create a scheduled task called 'Intterra Data Shipping App' in the Windows Task Scheduler Library.

The process sets the task 'Action' to execute the DSA application in silent mode (-s) with the configuration settings in the 'settings.json' file.


Action: Start a Program "C:\Program Files (x86)\Intterra\Data Shipping App\DSA.App.exe -s"

'Interval in Minutes' is used to configure a 'Daily' trigger event schedule to continuously start the task at the provided interval from the time it is submitted.


Trigger: Daily "At hh:mm AM/PM every day - After triggered, repeat every 1 hour for a duration of one day"

The application initially attempts to create the task using the Windows 'system' account and thus requires administrative permission to create the task. The task will ask to restart in 'Administrator' mode; the user access control popup needs to be confirmed using an account login with administrative rights on the system, and the 'Scheduled Task' button needs to be clicked again with the task running in administrative mode to create the scheduled task. The task can then be modified to suit the client preferences to the task schedule or the task user assignment. The specified user account, however, still requires the 'login as a batch job' right on the machine to allow the task to 'Run whether a user is logged in or not'.