> ## Documentation Index
> Fetch the complete documentation index at: https://docs.germainux.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Query

> SQL Query-Based Insights enable GermainUX to periodically run a customer-defined SQL query and turn the returned data into real-time KPIs.

Service
`Analytics`

Availability
2014.1 or later

> [card] [Benefits](#benefits)
>
> Business value of query KPIs

> [card] [Measures and pivots](#measures-and-pivots)
>
> Choose values and dimensions

> [card] [Create a KPI for a query](#create-a-kpi-for-a-query)
>
> From query to KPI

> [card] [Deployment and configuration](#deployment-and-configuration)
>
> Set up query-based insights

## SQL Query-Based Insights

SQL Query-Based Insights enable GermainUX to periodically execute a customer-defined SQL query and turn the returned data into real-time business or technical KPIs.

Use this capability when the insight you need is stored in a database but is not available through another GermainUX data source.

## Overview

For example, GermainUX can query a database to monitor:

| | Use case |
| - | - |
| 1 | Failed or incomplete business transactions |
| 2 | Orders awaiting processing |
| 3 | Cases missing required information |
| 4 | Business-process backlogs |
| 5 | Integration failures recorded in database tables |
| 6 | Records remaining in an unexpected state |
| 7 | Data-quality issues |
| 8 | Application or batch-processing status |
| 9 | Transaction volumes |
| 10 | Customer-specific operational conditions |

The query results become GermainUX facts that can be analyzed using KPIs, measures, pivots, SLAs, rules, dashboards, alerts, reports, and approved automated actions.

## Benefits

SQL Query-Based Insights help organizations:

* Convert database records into actionable real-time insights.
* Monitor business conditions that are not exposed through application telemetry.
* Detect failed, delayed, incomplete, or unusual transactions.
* Measure business-process volume, performance, and backlog.
* Identify data-quality and operational issues.
* Analyze database-derived insights alongside user-experience, application, infrastructure, and integration data.
* Notify responsible teams when an important condition is detected.
* Initiate approved diagnostic or corrective actions.

## How it works

**1. The GermainUX Engine connects to an authorized database**

**2. It executes a configured SQL query at the defined interval**

**3. The returned data is mapped to a GermainUX KPI**

**4. GermainUX stores and analyzes the resulting facts**

**5. Rules, SLAs, dashboards, alerts, reports, or automation can use the insight**

The SQL query defines what information GermainUX retrieves. It does not, by itself, profile every SQL statement executed by the monitored application.

## Examples

| Area | Query-based insight | Possible outcome |
| - | - | - |
| eCommerce | Orders with a failed payment status | Detect revenue-impacting payment failures |
| CRM | Cases with a missing customer email address | Identify data-quality issues affecting service |
| Business process | Transactions remaining in the same stage beyond the permitted duration | Detect workflow delays and backlogs |
| Integration | Records awaiting processing in an integration table | Detect a stopped or delayed integration |
| Customer service | Open requests exceeding their response SLA | Prioritize overdue customer requests |
| Operations | Failed batch jobs recorded in a control table | Notify the operations team |
| Data quality | Duplicate, incomplete, or invalid records | Reduce downstream processing errors |
| Application health | Heartbeat or status record not updated within the expected period | Detect a failed application component |

![SQL Query: SQL Query-Based Insights](https://mintcdn.com/germain-ux/A5V4ExbdsH-e9Odt/images/main/query-monitoring/image-20260909-022253.png?fit=max\&auto=format\&n=A5V4ExbdsH-e9Odt\&q=85\&s=997b504149d2157b70ddb43bcc1e43e5)

![SQL Query: SQL Query-Based Insights, image 2](https://mintcdn.com/germain-ux/A5V4ExbdsH-e9Odt/images/main/query-monitoring/image-20260909-022232.png?fit=max\&auto=format\&n=A5V4ExbdsH-e9Odt\&q=85\&s=ee98fbca8f535508d9241fda0005c212)

## Query results and KPIs

Each query is associated with a GermainUX KPI that defines how its results are stored and analyzed.

Depending on the use case, the KPI can represent:

| Type | Example |
| - | - |
| An event | An event, such as a failed integration |
| A transaction | A transaction, such as an order or service request |
| A measurement | A measurement, such as backlog size |
| A business condition | A business condition, such as an overdue case |
| An availability condition | An availability condition, such as a missing heartbeat |
| A data-quality issue | A data-quality issue, such as an incomplete record |

A query can use an existing KPI or a customer-defined KPI.

See [Custom Insights](https://docs.germainux.com/main/custom-insights.md) and [KPIs, Pivots & Measures](https://docs.germainux.com/main/kpis.md).

## Measures and pivots

The columns returned by a query can provide values and dimensions for analyzing the resulting KPI.

Measures can include:

* Record count
* Transaction duration
* Processing delay
* Backlog size
* Transaction value
* Error count
* Completion rate
* Customer-specific numeric values

Pivots can include:

* Application
* Environment
* Database
* Business process
* Transaction type
* Status

> [accordion] Show all 11
>
> * Error type
> * Customer
> * Product
> * Region
> * Assigned team

For example, an `Overdue Service Request` KPI could be measured by count and processing delay, then segmented by priority, assigned team, request type, and region.

## Rules and SLAs

Apply GermainUX rules to categorize, enrich, filter, or correlate the facts generated by a query.

Examples include:

* Categorize failures using a returned error code.
* Assign a severity according to transaction value.
* Associate a database record with a business process.
* Detect when the result exceeds a defined threshold.
* Correlate the result with related application or integration activity.

An SLA can define when a query-based KPI requires attention. The SLA can evaluate values such as:

* Number of matching records
* Transaction age
* Processing duration
* Backlog size
* Business value
* Time since the last successful update

See [Rules: Advanced Automated Analytics](https://docs.germainux.com/main/rules-advanced-automated-analytics.md) and [SLAs](https://docs.germainux.com/main/slas.md).

## Configure a Query Monitor

### Prerequisites

Before configuring the monitor, ensure that:

* A GermainUX Engine can connect to the database.
* The database type is supported and its driver is available.
* Authorized database credentials are configured.
* Network and firewall access are available.
* The SQL query has been tested.
* The database account has access only to the required data.
* The corresponding GermainUX KPI exists or can be created.

### Create a Query Monitor component

1. Sign in to the GermainUX Workspace.
2. Go to **System > Component Types**.
3. Add a **Database Query Monitor Component**.

   ![SQL Query: Create a Query Monitor component](https://mintcdn.com/germain-ux/A5V4ExbdsH-e9Odt/images/main/query-monitoring/image-20260902-194838.png?fit=max\&auto=format\&n=A5V4ExbdsH-e9Odt\&q=85\&s=7e14e1772d6349d68995b2669fb77524)
4. Enter a name for the component.

   ![SQL Query: Create a Query Monitor component, image 2](https://mintcdn.com/germain-ux/A5V4ExbdsH-e9Odt/images/main/query-monitoring/image2020-11-17_16-47-48.png?fit=max\&auto=format\&n=A5V4ExbdsH-e9Odt\&q=85\&s=230f9a4546cb02042fb398860da6c5da)
5. Select the database connection.
6. Select the authorized database credentials.
7. Enter a descriptive name for the query.
8. Enter and validate the SQL query.
9. Select the GermainUX KPI that will receive the results.
10. Configure a custom rule when returned values require mapping or transformation.
11. Configure the execution interval and other available settings.
12. Add an SLA when the result must be evaluated against a threshold.
13. Save the component.
14. Confirm that the Engine executes the query and that the resulting facts appear under the selected KPI.

## Add a query to an existing component

1. Go to **System > Component Types**.
2. Open the existing Database Query Monitor component.
3. In the **Queries** section, select **Add**.

   ![SQL Query: Add a query to an existing component](https://mintcdn.com/germain-ux/A5V4ExbdsH-e9Odt/images/main/query-monitoring/image-20260902-195043.png?fit=max\&auto=format\&n=A5V4ExbdsH-e9Odt\&q=85\&s=592420e8f483f23171871ff67cb31d00)
4. Select the database connection and credentials.
5. Enter a name for the query.
6. Enter and validate the SQL statement.
7. Select the destination KPI.
8. Configure a custom rule when required.
9. Save the query.

From the component configuration, authorized users can enable, disable, edit, delete, or reorder configured queries.

## Create a KPI for a query

**1. Go to Analytics > KPIs**

**2. Select Add**

**3. Enter a clear KPI name**

**4. Select the appropriate fact class, such as Generic Event or Generic Transaction**

**5. Enter a unique fact category, such as `Query: Overdue Service Requests`**

**6. Configure the relevant measures and pivots**

**7. Add an SLA when required**

**8. Configure relationships, rules, alerts, or automation as needed**

**9. Save the KPI**

**10. Select the KPI in the Query Monitor configuration**

## Analyze query-based insights

After data collection begins, use GermainUX to:

* Measure the frequency and business impact of the detected condition.
* Analyze historical trends.
* Segment results using KPI pivots.
* Compare environments or periods.
* Identify abnormal volumes or durations.
* Correlate database findings with user, application, API, integration, or infrastructure activity.
* Drill down to individual database-derived facts.
* Apply alerts, Watches, reports, or approved automated actions.

## Security and performance

Queries execute against customer databases and should follow the organization’s database-security and operational-governance requirements.

Recommended practices include:

* Use a dedicated database account.
* Grant only the permissions required by the configured queries.
* Prefer read-only access.
* Avoid collecting sensitive data unless it is required and authorized.
* Select only the columns needed for the insight.
* Filter results as narrowly as possible.

> [accordion] Show all 12
>
> * Avoid unbounded or unnecessarily expensive queries.
> * Use indexed columns when filtering large tables.
> * Configure an execution interval appropriate for the business need.
> * Test queries outside production before enabling them.
> * Monitor the query’s execution time and database impact.
> * Review retention and access requirements for the collected results.

Do not configure SQL statements that modify business data unless the use case has been explicitly designed, tested, secured, and authorized as an automation.

## Troubleshooting

If no facts are generated:

**1. Confirm that the GermainUX Engine is running**

**2. Verify network access from the Engine to the database**

**3. Validate the database connection and credentials**

**4. Execute the SQL query using an authorized database client**

**5. Confirm that the query returns the expected columns and values**

**6. Verify that the query is enabled**

**7. Confirm that the destination KPI and fact category are correct**

**8. Review Engine logs for connection, SQL, mapping, or processing errors**

**9. Confirm that the selected time range and filters include the generated facts**

## Deployment and configuration

* Deploy and configure the [GermainUX Engine](https://docs.germainux.com/main/germain-ux-engine.md).
* Configure an authorized connection to the target database.
* Create or select a [KPI](https://docs.germainux.com/main/kpis.md) for the query results.
* Configure rules through [Rules: Advanced Automated Analytics](https://docs.germainux.com/main/rules-advanced-automated-analytics.md) when results require filtering, categorization, enrichment, or correlation.
* Configure [SLAs](https://docs.germainux.com/main/slas.md) when the resulting insight must be evaluated against a threshold.
* Add dashboards, alerts, Watches, reports, or approved actions according to the operational requirement.

## Get Help

> [card] [Contact GermainUX Support](https://docs.germainux.com/main/support.md)
>
> The GermainUX team helps you get up and running faster, empower your team, turn data into actionable insights, and optimize performance to deliver measurable business outcomes.

> [card] Can't find what you need?
>
> Search all GermainUX docs with **Ctrl + K**, or use the feedback buttons at the bottom of this page to tell us what is missing.

The content of this document is proprietary to GERMAIN SOFTWARE.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.