Kibana Search Quick Reference for SQL-experienced Admins

This guide helps Relativity admins quickly transition from SQL to Kibana search by providing side-by-side examples of common queries. Use this as a quick reference when you need to find logs in Kibana.

Prerequisites: This guide assumes you're already familiar with the Kibana Discover UI basics. If not, review the Introduction to Kibana Discover UI first.

KQL vs ES|QL: Choosing the Right Tool

Kibana supports two query languages. Here's when to use each:

Use KQL (Kibana Query Language) for:

  • Simple searches: Finding logs containing specific text, errors, or field values
  • Quick filters: Searching by application name, log level, or host
  • Interactive exploration: Building queries with point-and-click filters
  • Pattern matching: Using wildcards to find partial matches

Think of KQL as: Your WHERE clause in SQL's simple filtering and pattern matching.

Use ES|QL (Elasticsearch Query Language) for:

  • Aggregations: Counting, grouping, and statistical analysis
  • Complex transformations: Calculations, derived fields, and data reshaping
  • Multi-step queries: Piping data through multiple operations
  • Advanced analytics: Top N queries, percentiles, and trending

Think of ES|QL as: Full SQL including GROUP BY, ORDER BY, LIMIT, and aggregate functions like COUNT(), AVG(), and SUM().

Quick Tips

  • Choose the right Data View: Select logs-* for application logs
  • Set an appropriate time range: Narrowing the range improves performance
  • Start simple: Begin with KQL for filtering, move to ES|QL when you need aggregations
  • Case sensitivity: KQL operators are case-insensitive, but quoted values must match exactly
  • Wildcards: Use * for pattern matching, but be specific to maintain performance

Section 1: Basic Searches with KQL

Use KQL for simple filtering and searching the equivalent of your SQL WHERE clause.

Find any log containing a word

How you'd think about it in SQL:

Copy 
SELECT * FROM [EDDSLogging].[eddsdbo].[RelativityLogs] WHERE Message LIKE '%error%'

How to do it in Kibana (KQL):

Copy 
error

Searches across all fields for the word "error". Case-insensitive by default.

Search for multiple terms (OR condition)

SQL approach:

Copy 
SELECT * FROM [EDDSLogging].[eddsdbo].[RelativityLogs] 
WHERE Message LIKE '%error%' OR Message LIKE '%warning%'

Kibana (KQL):

Copy 
error OR warning

Finds logs containing either "error" OR "warning".

Search for multiple conditions (AND)

SQL approach:

Copy 
SELECT * FROM [EDDSLogging].[eddsdbo].[RelativityLogs]
WHERE (Message LIKE '%error%' OR Message LIKE '%warning%')
  AND (Message LIKE '%document%' OR Message LIKE '%import%')

Kibana (KQL):

Copy 
(error OR warning) AND (document OR import)

Use parentheses to group conditions, just like SQL.

Search for an exact phrase

SQL approach:

Copy 
SELECT * FROM [EDDSLogging].[eddsdbo].[RelativityLogs] 
WHERE Message = 'Failed to retrieve favorites'

Kibana (KQL):

Copy 
"Failed to retrieve favorites"

Quotes ensure the exact phrase is matched.

Search a specific field

SQL approach:

Copy 
SELECT * FROM [EDDSLogging].[eddsdbo].[RelativityLogs] 
WHERE Level = 'Error' OR Level = 'Fatal'

Kibana (KQL):

Copy 
log.level:"Error" OR log.level:"Fatal"

Field names follow dot notation (e.g., log.level, labels.Application).

Use wildcards for pattern matching

SQL approach:

Copy 
SELECT * FROM [EDDSLogging].[eddsdbo].[RelativityLogs] 
WHERE Exception LIKE '%The application failed%'

Kibana (KQL):

Copy 
error.stack_trace:*The application failed*

The * wildcard matches any characters, similar to % in SQL.

Exclude results (NOT operator)

SQL approach:

Copy 
SELECT * FROM [EDDSLogging].[eddsdbo].[RelativityLogs] 
WHERE Exception LIKE '%The application failed%'
  AND Level != 'Information'

Kibana (KQL):

Copy 
error.stack_trace:*The application failed* AND NOT log.level:"Information"

Use NOT to exclude specific values.

Find errors for a specific application

SQL approach:

Copy 
SELECT * FROM [EDDSLogging].[eddsdbo].[RelativityLogs] 
WHERE (Level = 'Error' OR Level = 'Fatal')
  AND Properties.value('(//Application)[1]', 'nvarchar(max)') = 'Imaging'
  AND Properties.value('(//SubSystem)[1]', 'nvarchar(max)') = 'Import'

Kibana (KQL):

Copy 
(log.level:"Error" OR log.level:"Fatal") AND labels.Application:"Imaging" AND labels.SubSystem:"Import"

Combine multiple field filters to narrow down results.

Find all logs for a specific host

SQL approach:

Copy 
SELECT * FROM [EDDSLogging].[eddsdbo].[RelativityLogs] 
WHERE Properties.value('(//HostName)[1]', 'nvarchar(max)') = 'RELVM-WEB01'

Kibana (KQL):

Copy 
host.name:"RELVM-WEB01"

Find disabled agents

SQL approach:

Copy 
SELECT * FROM [EDDS].[eddsdbo].[Agent]
WHERE Enabled = 0

**Kibana (KQL):

Copy 
relsvr.agent.disabled:1

KQL Reference Summary

KQL works like your SQL WHERE clause:

Copy 
-- SQL pattern
SELECT * FROM [EDDSLogging].[eddsdbo].[RelativityLogs] WHERE [conditions]

-- Maps to KQL pattern
[field]:[value] AND/OR [field]:[value]

Common KQL patterns:

  • field:value | exact match on a field
  • field:*partial* | wildcard search
  • "exact phrase" | match complete phrase
  • AND, OR, NOT | logical operators
  • (...) | group conditions

KQL vs SQL Comparison

The screenshot above highlights a side-by-side comparison showing how SQL WHERE clauses translate to KQL field:value syntax. Notice how SQL's LIKE operator with % wildcards becomes * in KQL.

Section 2: Advanced Queries with ES|QL

Use ES|QL when you need aggregations, grouping, or calculations or the full power of SQL including GROUP BY, COUNT(), AVG(), and ORDER BY.

ES|QL uses a pipe-based syntax where data flows through transformations:

Copy 
FROM [data-view]
| WHERE [filter-conditions]
| STATS [aggregations] BY [group-by-fields]
| SORT [field] DESC
| LIMIT [number]

ES|QL Query Example

The screenshot above highlights an example of an ES|QL query showing the pipe-based syntax. Notice how each line starts with a pipe (|) and operations flow top to bottom: FROM | WHERE | STATS | SORT. The results table appears below the query.

Count total logs

SQL approach:

Copy 
SELECT COUNT(*) AS total_logs
FROM [EDDSLogging].[eddsdbo].[RelativityLogs]

Kibana (ES|QL):

Copy 
FROM logs-*
| STATS total_logs = count()

Count logs by log level

SQL approach:

Copy 
SELECT Level, COUNT(*) AS log_count
FROM [EDDSLogging].[eddsdbo].[RelativityLogs]
GROUP BY Level
ORDER BY log_count DESC

Kibana (ES|QL):

Copy 
FROM logs-*
| STATS log_count = count() BY log.level
| SORT log_count DESC

Count errors by application

SQL approach:

Copy 
SELECT 
    Properties.value('(//Application)[1]', 'nvarchar(max)') AS Application,
    COUNT(*) AS error_count
FROM [EDDSLogging].[eddsdbo].[RelativityLogs]
WHERE Level = 'Error'
GROUP BY Properties.value('(//Application)[1]', 'nvarchar(max)')
ORDER BY error_count DESC

Kibana (ES|QL):

Copy 
FROM logs-*
| WHERE log.level == "Error"
| STATS error_count = count() BY labels.Application
| SORT error_count DESC

Note: ES|QL uses == for equality in WHERE clauses (not single =).

Top 5 applications with most errors

SQL approach:

Copy 
SELECT TOP 5 
    Properties.value('(//Application)[1]', 'nvarchar(max)') AS Application,
    COUNT(*) AS error_count
FROM [EDDSLogging].[eddsdbo].[RelativityLogs]
WHERE Level = 'Error'
GROUP BY Properties.value('(//Application)[1]', 'nvarchar(max)')
ORDER BY error_count DESC

Kibana (ES|QL):

Copy 
FROM logs-*
| WHERE log.level == "Error"
| STATS error_count = count() BY labels.Application
| SORT error_count DESC
| LIMIT 5

Count logs by host

SQL approach:

Copy 
SELECT 
    Properties.value('(//HostName)[1]', 'nvarchar(max)') AS HostName,
    COUNT(*) AS log_count
FROM [EDDSLogging].[eddsdbo].[RelativityLogs]
GROUP BY Properties.value('(//HostName)[1]', 'nvarchar(max)')
ORDER BY log_count DESC

Kibana (ES|QL):

Copy 
FROM logs-*
| STATS log_count = count() BY host.name
| SORT log_count DESC

Count errors per hour for a specific application

SQL approach:

Copy 
SELECT 
    DATEPART(HOUR, TimeStamp) AS hour,
    COUNT(*) AS error_count
FROM [EDDSLogging].[eddsdbo].[RelativityLogs]
WHERE Level = 'Error' 
  AND Properties.value('(//Application)[1]', 'nvarchar(max)') = 'Imaging'
  AND TimeStamp >= DATEADD(DAY, -1, GETDATE())
GROUP BY DATEPART(HOUR, TimeStamp)
ORDER BY hour

Kibana (ES|QL):

Copy 
FROM logs-*
| WHERE log.level == "Error" AND labels.Application == "Imaging"
| STATS error_count = count() BY hour = DATE_TRUNC(1 hour, @timestamp)
| SORT hour

Find applications with more than 100 errors

SQL approach:

Copy 
SELECT 
    Properties.value('(//Application)[1]', 'nvarchar(max)') AS Application,
    COUNT(*) AS error_count
FROM [EDDSLogging].[eddsdbo].[RelativityLogs]
WHERE Level = 'Error'
GROUP BY Properties.value('(//Application)[1]', 'nvarchar(max)')
HAVING COUNT(*) > 100
ORDER BY error_count DESC

Kibana (ES|QL):

Copy 
FROM logs-*
| WHERE log.level == "Error"
| STATS error_count = count() BY labels.Application
| WHERE error_count > 100
| SORT error_count DESC

Note: In ES|QL, the second WHERE acts like SQL's HAVING clause.

Multiple aggregations in one query

SQL approach:

Copy 
SELECT 
    Properties.value('(//Application)[1]', 'nvarchar(max)') AS Application,
    COUNT(*) AS total_logs,
    SUM(CASE WHEN Level = 'Error' THEN 1 ELSE 0 END) AS error_count,
    SUM(CASE WHEN Level = 'Warning' THEN 1 ELSE 0 END) AS warning_count
FROM [EDDSLogging].[eddsdbo].[RelativityLogs]
GROUP BY Properties.value('(//Application)[1]', 'nvarchar(max)')

Kibana (ES|QL):

Copy 
FROM logs-*
| STATS 
    total_logs = count(),
    error_count = count_if(log.level == "Error"),
    warning_count = count_if(log.level == "Warning")
  BY labels.Application

Group by multiple fields

SQL approach:

Copy 
SELECT 
    Properties.value('(//Application)[1]', 'nvarchar(max)') AS Application,
    Properties.value('(//SubSystem)[1]', 'nvarchar(max)') AS SubSystem,
    COUNT(*) AS error_count
FROM [EDDSLogging].[eddsdbo].[RelativityLogs]
WHERE Level = 'Error'
GROUP BY Properties.value('(//Application)[1]', 'nvarchar(max)'), Properties.value('(//SubSystem)[1]', 'nvarchar(max)')
ORDER BY error_count DESC

Kibana (ES|QL):

Copy 
FROM logs-*
| WHERE log.level == "Error"
| STATS error_count = count() BY labels.Application, labels.SubSystem
| SORT error_count DESC

ES|QL Reference Summary

ES|QL provides full SQL-like querying:

Copy 
-- SQL pattern
SELECT [fields], COUNT(*), AVG(field)
FROM [table]
WHERE [conditions]
GROUP BY [fields]
HAVING [aggregate-conditions]
ORDER BY [field] DESC
LIMIT [number]

-- Maps to ES|QL pattern
FROM [data-view]
| WHERE [conditions]
| STATS [aggregations] BY [group-fields]
| WHERE [aggregate-conditions]
| SORT [field] DESC
| LIMIT [number]

Key ES|QL Commands:

  • FROM | Select data source (like SQL FROM)
  • WHERE | Filter rows (like SQL WHERE and HAVING)
  • STATS | Aggregate data (like SQL SELECT with aggregates)
  • BY | Group results (like SQL GROUP BY)
  • SORT | Order results (like SQL ORDER BY)
  • LIMIT | Restrict results (like SQL TOP or LIMIT)
  • EVAL | Create calculated fields (like SQL calculated columns)

Important differences from SQL:

  • Use == for equality (not =) in WHERE clauses
  • Use | pipes to chain operations instead of commas
  • Aggregation aliases come before the function: error_count = count()

Quick Reference: Common Admin Queries

Copy and paste these frequently-used queries:

All disabled agents

Copy 
relsvr.agent.disabled:1

Specific disabled agent (e.g., Billing Agent)

Copy 
relsvr.agent.disabled:1 AND labels.relsvr_agent_type:"Billing Agent"

Service discovery failures

Copy 
labels.message_template_text:"GetServiceLocationAsync lookup for {ServiceIdentifier} {ServiceVersion} failed!"

Database connection errors

Copy 
error.stack_trace:*SqlException* OR error.stack_trace:*connection*

Timeout errors across all applications

Copy 
error.stack_trace:*timeout* OR error.message:*timeout*
Copy 
labels.rabbitmq_node_name:*

Import job failures

Copy 
(log.level:"Error" OR log.level:"Fatal") AND labels.SubSystem:"Import"

Agent errors in the last 15 minutes

Copy 
(log.level:"Error" OR log.level:"Fatal") AND labels.relsvr_agent_type:*

Set time range to "Last 15 minutes" in the Kibana UI.

Additional Resources

Official Documentation

Community Cheat Sheets

Return to top of the page
Feedback