SQL Query | Germain Documentation

📊 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:

Benefit
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

The GermainUX Engine connects to an authorized database.
It executes a configured SQL query at the defined interval.
The returned data is mapped to a GermainUX KPI.
GermainUX stores and analyzes the resulting facts.
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

📈 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 and KPIs, Pivots & Measures.

🔁 Measures and pivots

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

Measures can include:

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

Pivots can include:

Pivot
Application
Environment
Database
Business process
Transaction type
Status
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:

Rule example
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:

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

See Rules: Advanced Automated Analytics and SLAs.

🔧 Configure a Query Monitor

✅ Prerequisites

Before configuring the monitor, ensure that:

Requirement
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

Sign in to the GermainUX Workspace.
Go to System > Component Types.
Add a Database Query Monitor Component.
Enter a name for the component.
Select the database connection.
Select the authorized database credentials.
Enter a descriptive name for the query.
Enter and validate the SQL query.
Select the GermainUX KPI that will receive the results.
Configure a custom rule when returned values require mapping or transformation.
Configure the execution interval and other available settings.
Add an SLA when the result must be evaluated against a threshold.
Save the component.
Confirm that the Engine executes the query and that the resulting facts appear under the selected KPI.

☑️ Add a query to an existing component

Go to System > Component Types.
Open the existing Database Query Monitor component.
In the Queries section, select Add.
Select the database connection and credentials.
Enter a name for the query.
Enter and validate the SQL statement.
Select the destination KPI.
Configure a custom rule when required.
Save the query.

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

🚀 Create a KPI for a query

Go to Analytics > KPIs.
Select Add.
Enter a clear KPI name.
Select the appropriate fact class, such as Generic Event or Generic Transaction.
Enter a unique fact category, such as Query: Overdue Service Requests.
Configure the relevant measures and pivots.
Add an SLA when required.
Configure relationships, rules, alerts, or automation as needed.
Save the KPI.
Select the KPI in the Query Monitor configuration.

📊 Analyze query-based insights

After data collection begins, use GermainUX to:

Action
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:

Practice
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.
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:

Confirm that the GermainUX Engine is running.
Verify network access from the Engine to the database.
Validate the database connection and credentials.
Execute the SQL query using an authorized database client.
Confirm that the query returns the expected columns and values.
Verify that the query is enabled.
Confirm that the destination KPI and fact category are correct.
Review Engine logs for connection, SQL, mapping, or processing errors.
Confirm that the selected time range and filters include the generated facts.

📦 Deployment and configuration

For SQL Query-Based Insights
Deploy and configure the GermainUX Engine.
Configure an authorized connection to the target database.
Create or select a KPI for the query results.
Configure rules through Rules: Advanced Automated Analytics when results require filtering, categorization, enrichment, or correlation.
Configure SLAs 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

The Germain Team can help you set this up. Contact GermainUX Support.

Service: Analytics

Feature Availability: 2014.1 or later