SQL Logs Query

The SQL Logs Query Source in Observo AI enables the retrieval and ingestion of log data from SQL databases, such as MySQL or PostgreSQL, for real-time analytics and observability using optimized SQL queries.

Purpose

The Observo AI SQL Logs Query Source enables users to pull log data from SQL databases into Observo AI for analysis and processing. This source supports executing SQL queries to retrieve log data, typically stored in relational databases in formats like JSON or structured tables, and is designed for integration with SIEM, observability, and analytics platforms. This integration helps organizations streamline their log data pipelines, enhance monitoring, and optimize data processing for security and operational insights.

Prerequisites

Before configuring the SQL Logs Query source in Observo AI, ensure the following requirements are met to facilitate seamless data ingestion:

  • Observo AI Platform Setup:

    • The Observo AI platform must be installed and operational, with support for SQL database sources.

    • Verify that the platform supports parsing data formats returned by the SQL query such as JSON, CSV, or structured rows. Additional parsers may be required for specific formats.

  • Database Access:

    • An active database instance such as MySQL, PostgreSQL, SQL Server, or Oracle containing the log data must be accessible.

    • Obtain the database connection details, including hostname, port, database name, and schema.

    • Ensure the database supports the SQL dialect used in your queries such as ANSI SQL, T-SQL.

  • Database Credentials:

    • Prepare database credentials with read access to the target tables containing log data.

    • Required permissions: SELECT on the relevant tables or views.

    • Store credentials securely within Observo AI’s secure storage or as environment variables.

  • Network and Connectivity:

    • Ensure Observo AI can communicate with the database server over the network, using the appropriate protocol and port such as TCP 3306 for MySQL, 5432 for PostgreSQL, 1433 for SQL Server.

    • Check for firewall rules, proxy settings, or network policies that may block connectivity to the database server.

    • If using SSL/TLS, ensure certificates are configured correctly.

  • SQL Query:

    • Prepare a SQL query to retrieve log data such as SELECT * FROM logs WHERE timestamp > ?.

    • Ensure the query is optimized to avoid performance issues and includes parameters for incremental data pulls such as timestamp-based filtering.

Prerequisite
Description
Notes

Observo AI Platform

Must be installed and support SQL database sources

Verify support for query result formats

Database Access

Active database with log data

Obtain hostname, port, database name, schema

Database Credentials

Read access to log tables

Store credentials securely; SELECT permission required

Network

Connectivity to database server

Ensure protocol/port access; verify SSL/TLS if used

SQL Query

Optimized query for log retrieval

Use parameters for incremental data pulls

Integration

The Integration section outlines configurations for the SQL Logs Query source. To configure SQL Logs Query as a source in Observo AI, follow these steps to set up and test the data flow:

  1. Log in to Observo AI:

    • Navigate to the Sources tab.

    • Click the Add Source button and select Create New.

    • Choose SQL Logs Query from the list of available sources to begin configuration.

  2. General Settings:

    • Name: A unique identifier for the source, such as sql-logs-query-1.

    • Description (Optional): Provide a description for the source.

    • DB Driver: The name of the database driver. Supported DBs: Postgres, Mysql. Snowflake, SAP HANA & Oracle DB.

      Options
      Description

      Postgres

      PostgreSQL database driver

      Mysql

      MySQL database driver

      Snowflake

      Snowflake database driver

      SAP HANA

      SAP HANA database driver

      Oracle DB

      Oracle database driver

    • Datasource (Connection String): Enter the database connection string. The datasource value passed to sql.Open. This is a driver-specific string usually consisting of at least a database name and connection information. This is sometimes referred to as the "connection string" in driver documentation.

      Example

      host=localhost port=5432 user=me password=s3cr3t sslmode=disable

    • Queries: A list of queries, where a query is a sql statement and one or more logs sections

    • SQL Query: The SQL query to run.

      Example

      SELECT * FROM my_table where log_id > $$1

    • Tracking Start Value: The value to start tracking from. This is used to track the last value of a column in the result set. This is used to track the last value of a column in the result set. The value should be a string that can be converted to the type of the column being tracked.

      Example

      10000

    • Logs: The value to start tracking from. This is used to track the last value of a column in the result set. This is used to track the last value of a column in the result set. The value should be a string that can be converted to the type of the column being tracked. (Add Logs entries as needed)

    • Body Column: The name of the column in the returned dataset used to set the body of the log.

    • Attribute Columns: The name of the column in the returned dataset used to set the body of the log. (Add Attribute Columns as needed)

  3. Advanced Settings:

    • Collection Interval: The interval at which to run the query. This is a duration string, such as 30s or 1m.

    • Storage: The ID of a storage extension to be used to track processed results.

  4. Parser Config:

    • Enable Source Log parser: (False)

    • Toggle Enable Source Log parser Switch to enable

      • Select appropriate Parser from the Source Log Parser dropdown

      • Add additional Parsers as needed

  5. Pattern Extractor:

    • Refer to Observo AI’s Pattern Extractor documentation for details on configuring pattern-based data extraction.

  6. Archival Destination:

    • Toggle Enable Archival on Source Switch to enable

    • Under Archival Destination, select from the list of Archival Destinations (Required)

  7. Save and Test Configuration:

    • Save the configuration settings in Observo AI.

    • Execute a test query and verify that log data is ingested into Observo AI, checking the Analytics tab for data flow.

Example Scenarios

Quantum Finance, a fictitious financial services company specializing in high-frequency trading, integrates transaction logs from a PostgreSQL database into Observo AI for real-time analytics, compliance monitoring, and anomaly detection. The configuration uses a PostgreSQL database at db.quantumfinance.com:5432 with a SQL query to retrieve JSON-formatted logs incrementally.

Standard SQL Logs Query Source Setup

Here is a standard SQL Logs Query Source configuration example. Only the required sections and their associated field updates are displayed in the table below:

General Settings

Field
Value
Notes

Name

sql-logs-quantum-1

Unique identifier for the SQL Logs Query source, indicating Quantum Finance’s transaction log ingestion.

Description

Ingest transaction logs from PostgreSQL for Quantum Finance compliance and analytics

Optional, provides context for the source’s purpose.

DB Driver

Postgres

Specifies the PostgreSQL database driver, matching the database type.

Datasource (Connection String)

host=db.quantumfinance.com port=5432 user=quantum_user password=FinSecure2025! dbname=trading_logs sslmode=require

Connection string for the PostgreSQL database, using SSL for secure access.

Queries

Field
Value
Notes

SQL Query

SELECT log_id, timestamp, log_data, trade_type, amount FROM transaction_logs WHERE log_id > $$1 AND timestamp >= $$2 ORDER BY log_id

Retrieves transaction logs incrementally, filtering by log_id and timestamp for efficient data pulls.

Tracking Start Value

10000

Starting value for the log_id column, ensuring all relevant logs are captured from a known point.

Logs: Body Column

log_data

Column containing the JSON-formatted log data such as trade details, used as the log body.

Logs: Attribute Columns

log_id, timestamp, trade_type, amount

Columns to include as metadata attributes, providing context like trade type and amount.

Advanced Settings

Field
Value
Notes

Collection Interval

1m

Default duration (1 minute) between query executions, suitable for real-time monitoring.

Storage

(Empty)

Default, no specific storage extension specified for tracking processed results.

Parser Config

Field
Value
Notes

Enable Source Log Parser

True

Enabled to parse JSON data from the log_data column.

Source Log Parser

JSON Parser

Selected from the dropdown to match the JSON format of the log_data column.

Additional Parsers

(None)

Not needed unless specific transformations are required.

Test Configuration:

  • Click “Save” to store the configuration settings in Observo AI.

  • Execute a test query with sample transaction log data such as simulated trades. Verify ingestion by monitoring the Analytics tab in the Observo AI pipeline for event counts and throughput.

Notes:

  • Database Access: The connection string uses a fictional hostname (db.quantumfinance.com) and credentials (quantum_user, FinSecure2025!). Quantum Finance must provide actual database details, with the password stored securely in Observo AI.

  • SQL Query: The query filters by log_id and timestamp for incremental pulls, using 1and1 and2 as placeholders for tracking values. The ORDER BY log_id ensures consistent processing.

  • Network: Ensure firewall rules allow TCP traffic on port 5432 to db.quantumfinance.com. SSL (sslmode=require) is enabled for secure communication.

  • Troubleshooting: If issues occur such as “Connection refused” or “No data returned”, verify the connection string, query syntax, and SELECT permissions. Use psql to test the query directly and check Observo AI logs for errors, as per the Troubleshooting section.

  • Resources: Refer to PostgreSQL Documentation and SQL Best Practices for guidance on query optimization and database configuration.

  • Quantum Finance Context: This configuration supports real-time ingestion of transaction logs, enabling compliance monitoring and fraud detection, complementing other Observo AI sources for a unified financial observability pipeline.

Troubleshooting

If issues arise with the SQL Logs Query source in Observo AI, use the following steps to diagnose and resolve them:

  • Verify Configuration Settings:

    • Ensure all fields, such as Connection String, SQL Query, and Username, are correctly entered and match the database setup.

    • Validate that the SQL query syntax is compatible with the database type and returns the expected log data.

  • Check Authentication:

    • Verify that the database username and password are valid and have not expired.

    • Ensure the credentials have SELECT permissions on the target tables or views.

    • If using secure storage, confirm the password is accessible in Observo AI.

  • Validate Network Connectivity:

    • Check for firewall rules, proxy settings, or network policies that may block access to the database server’s hostname and port.

    • Test connectivity using database client tools such as mysql CLI, psql, or SQL Server Management Studio with similar configurations.

  • Common Error Messages:

    • "Connection refused": Indicates network issues or incorrect hostname/port. Verify the connection string and network access.

    • "Invalid credentials": Check that the username and password are correct and have the necessary permissions.

    • "Query timeout": The query may be too complex or the database overloaded. Optimize the query or increase the Query Timeout setting.

    • "No data returned": Ensure the SQL query matches the table schema and includes valid filtering conditions such as timestamp range.

  • Monitor Logs and Data:

    • Verify that log data is being ingested by monitoring Observo AI logs for errors or warnings related to query execution.

    • Use the Analytics tab in the targeted Observo AI pipeline to monitor data volume and ensure expected throughput.

    • Execute the SQL query directly on the database to confirm it returns the expected log data.

Issue
Possible Cause
Resolution

Data not ingested

Incorrect connection string or query syntax

Verify connection string and query syntax

Authentication errors

Invalid or expired credentials

Check username/password and permissions

Connectivity issues

Firewall or proxy blocking access

Test network connectivity to database server

"Connection refused"

Incorrect hostname/port or network issues

Verify connection string and network access

"Query timeout"

Complex query or database overload

Optimize query or increase timeout

"No data returned"

Incorrect query or empty table

Validate query and table schema

Resources

For additional guidance and detailed information, refer to the following resources:

PreviousSplunk TCPNextSyslog

Last updated

Was this helpful?