Skip to main content
Version: Next

Setting up a generic database archive

Cortex can connect to any SQL Server, PostgreSQL, MariaDB or SQLite database as a generic Database archive data source. It gives you relationship-aware browsing and search over that database without requiring a pre-defined integration for a specific system.

Prerequisites​

  • A database with the data you want to archive, on one of the supported engines.
  • A login with read-only access to that database.
  • For SQLite, a path to the database file that the Cortex server can read.

Connection String​

Add a Database data source under Admin → Data Sources and set the engine option for the database you are connecting to. All connection strings are treated as SQL Server unless engine says otherwise.

SQL Server (the default, so the engine option can be omitted):

server=server-hostname,1433;uid=username;password=the-password;database=YourDatabase;MultipleActiveResultSets=true

PostgreSQL:

engine=postgres;server=server-hostname,5432;uid=username;password=the-password;database=your_database

MariaDB:

engine=mariadb;server=server-hostname,3306;uid=username;password=the-password;database=your_database

SQLite:

engine=sqlite;Data Source=/path/to/your_database.db

The key fields are:

  • engine — mssql, postgres, mariadb or sqlite. Omit it for SQL Server.
  • server — the hostname or IP address and port of the database server.
  • uid / password — the login Cortex reads the database with.
  • database — the name of the database.
  • Data Source (SQLite only) — the path to the database file on the Cortex server.
  • MultipleActiveResultSets — must be set to true for SQL Server, and must be left out for the other engines.

See Database Connections for the full set of connection options.

How a generic database archive works​

Table discovery​

Tables are read live from the database, so a table added to the database later appears in Cortex without reconfiguring the data source. Tables are presented grouped by schema.

On PostgreSQL, every user schema is discovered rather than only public, so a database whose tables live in their own schema is browsed normally.

Relationship discovery​

Cortex reads the database's own foreign key constraints and turns them into relationships you can follow from a record, the same way dedicated archive types do. Composite (multi-column) foreign keys are followed as a unit.

Relationships you configure by hand on the data source are honoured as well.

  • Browse lists the tables in the database, organised by schema.
  • Search uses Cortex's structured filters over the columns of the selected table, with the search count and timed search behaviour used elsewhere in Cortex.
  • The quick-search bar is not shown for a generic database archive, because a source database has no full-text index for Cortex to use. Structured filters do the same job over any column.

Columns that are not shown exactly as stored​

Some column types are projected into something readable rather than loaded raw, so that browsing a table never pulls large payloads into the page:

  • Binary columns (bytea on PostgreSQL, binary/blob on MariaDB, BLOB on SQLite) are shown as their hexadecimal value. While browsing only the start of a long value is read; the whole value is shown when you open the record.
  • JSON columns on PostgreSQL and MariaDB are shown as text.
  • Spatial (geometry) columns on MariaDB are shown using the engine's text representation.

Columns of these types are not used for default ordering.

Use Cases​

Generic database archives are useful for:

  • Custom applications — archive data from in-house or third-party applications that don't have a dedicated Cortex integration
  • Data migration projects — temporarily connect to a legacy database to browse and search its contents
  • Cross-system analysis — connect to auxiliary databases that support your main ITSM processes

What Cortex Can Do With a Generic Database Archive​

  • Browse — navigate tables organized by schema
  • Search — structured, filtered search across the columns of a table
  • Record viewing — view individual records with their field values
  • Relationships — navigate related records via the database's foreign keys

Troubleshooting​

  • Verify the database server is running and accessible from the Cortex server
  • Confirm the login credentials are correct, and that the engine value matches the database you are connecting to
  • Check that the login has read-only (SELECT) permissions on the database
  • On SQLite, confirm the file path is reachable from the Cortex server and readable by the account Cortex runs under
  • See Troubleshooting for more help