System Check-in ODBC Configuration


Introduction

With System Check-in enabled, Quicklaunch will automatically log all Check-in events to a local database. This default database is named quicklaunch.store.sqlite and is located at the following location:


C:\ProgramData\IsolatedStorage\obo1sryr.o0h\ql1qigbe.vjz\Publisher.r0fqvo551sbk21ac0b1i5x51xcaafwdi\AssemFiles


This guide explains how to configure Quicklaunch Settings to log System Check-in events to an external database using Open Database Connectivity (ODBC).


Contents

  1. Configure External Database
  2. Configure Quicklaunch Settings



1) Configure External Database


1A) Create a table in the external database matching the required schema

  • You will need to create a table that matches the schema below. 
  • The table below does not include what is commonly referred to as a "Primary Key" field, this is due to how each database platform implements this concept. The data that Quicklaunch stores does not require this field. 
  • When implementing the table structure a "Primary Key" can be supplied. You will also need to map the ODBC data types to the proper data types supported for your database platform.
  • Quicklaunch filters the data primarily on the TimestampUtc (date ranges) and EventKind field for reporting and data retention deletion.


Table Name: Events

Table Columns:                                                                                                                                                                                                                                                                                  

Name
Data Type
Minimum Length
Nullable
EventKind
OdbcType.Int
-
False
EventKindText
OdbcType.NVarChar
255
False
Room
OdbcType.NVarChar
255
True
IPAddress
OdbcType.NVarChar
40
False
UserIdentifier
OdbcType.NVarChar
255
False
LicenseKey
OdbcType.NVarChar
50
False
ResultCode
OdbcType.NVarChar
-
False
TimestampUtc
OdbcType.DateTime
-
False
Data
OdbcType.NVarChar
Unlimited / Max value
True


1B) Create an ODBC Connection string

  • In order to have Quicklaunch connect to your database you will need to supply an ODBC connection string (if your database platform supports it). 
  • To help construct the connection string please visit https://www.connectionstrings.com and select your database platform. 
  • From there you will be able to see if your database platform supports ODBC and how to construct a valid connection string for it.


1C) Install a ODBC driver for your database

  • You may need to install an ODBC driver on the computer that runs Quicklaunch. 
  • You can either visit the vendor of the database platform you wish to target or visit https://www.connectionstrings.com and select the platform you use.
    • This site also has links to download ODBC drivers from the vendors own support site.


NOTE: By default Microsoft® supplies pre-installed ODBC driver(s) in Windows®. When using SQL Server the preferred ODBC driver to use with Quicklaunch is {SQL Server Native Client 11.0}.


1D) Azure Connection String

  • If you are using an Azure Sql Server instance you need to install the latest Sql Server ODBC driver. 
    • Do this via the Azure Portal https://portal.azure.com 
    • Log into your azure account and select the database you will be using. 
    • Under settings select the menu item Connection strings, then select the tab ODBC. 
    • From here you will be able to download the latest ODBC driver as well copy the ODBC connection string you wish to use. 



2) Configure Quicklaunch Settings

  • Open Quicklaunch Settings by pressing CTRL + ALT + S
  • Navigate to Settings > Account > System Check-in
  • Select ODBC Database from the dropdown menu beside "Type"
  • (Optional) Enter your security schema (see NOTE below)
  • Enter your connection string.

NOTE: Some database platforms support the concept of nesting database objects (tables, indexes, views) in a schema where all of the nested objects permissions can be managed by placing permissions on the schema. If your database platform supports this concept and you utilize them enter your schema into the relevant field in Quicklaunch Settings. Quicklaunch will then pre-pend the schema name when performing any SQL operations.