Elastic® SQL inputs (metricbeat module and input package) allows the user to execute SQL queries against many supported databases in a flexible way and ingest the resulting metrics to Elasticsearch®. This blog dives into the functionality of generic SQL and provides various use cases for advanced users to ingest custom metrics to Elastic®, for database observability. The blog also introduces the fetch from all database new capability, released in 8.10.
Why “Generic SQL”?
Elastic already has metricbeat and integration packages targeted for specific databases. One example is metricbeat for MySQL — and the corresponding integration package. These beats modules and integrations are customized for a specific database, and the metrics are extracted using pre-defined queries from the specific database. The queries used in these integrations and the corresponding metrics are not available for modification.
Whereas the Generic SQL inputs (metricbeat or input package) can be used to scrape metrics from any supported database using the user's SQL queries. The queries are provided by the user depending on specific metrics to be extracted. This enables a much more powerful mechanism for metrics ingestion, where users can choose a specific driver and provide the relevant SQL queries and the results get mapped to one or more Elasticsearch documents, using a structured mapping process (table/variable format explained later).
Generic SQL inputs can be used in conjunction with the existing integration packages, which already extract specific database metrics, to extract additional custom metrics dynamically, making this input very powerful. In this blog, Generic SQL input and Generic SQL are used interchangeably.
Functionalities details
This section covers some of the features that would help with the metrics extraction. We provide a brief description of the response format configuration. Then we dive into the merge_results functionality, which is used to combine results from multiple SQL queries into a single document.
The next key functionality users may be interested in is to collect metrics from all the custom databases, which is now possible with the fetch_from_all_databases feature.
Now let's dive into the specific functionalities:
Different drivers supported
The generic SQL can fetch metrics from the different databases. The current version has the capability to fetch metrics from the following drivers: MySQL, PostgreSQL, Oracle, and Microsoft SQL Server(MSSQL).
Response format
The response format in generic SQL is used to manipulate the data in either table or in variable format. Here’s an overview of the formats and syntax for creating and using the table and variables.
Syntax:
Response format table
This mode generates a single event for each row. The table format has no restrictions on the number of columns in the response. This format can have any number of columns.
Example:
driver: "mssql"
sql_queries:
- query: "SELECT counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE counter_name= 'User Connections'"
response_format: table
This query returns a response similar to this:
"sql":{
"metrics":{
"counter_name":"User Connections ",
"cntr_value":7
},
"driver":"mssql"
}
The response generated above adds the counter_name as a key in the document.
Response format variables
The variable format supports key:value pairs. This format expects only two columns to fetch in a query.
Example:
driver: "mssql"
sql_queries:
- query: "SELECT counter_name, cntr_value FROM sys.dm_os_performance_counters WHERE counter_name= 'User Connections'"
response_format: variables
The variable format takes the first variable in the query above as the key:
"sql":{
"metrics":{
"user connections ":7
},
"driver":"mssql"
}
In the above response, you can see the value of counter_name is used to generate the key in variable format.
Response optimization: merge_results
We are now supporting merging multiple query responses into a single event. By enabling merge_results , users can significantly optimize the storage space of the metrics ingested to Elasticsearch. This mode enables an efficient compaction of the document generated, where instead of generating multiple documents, a single merged document is generated wherever applicable. The metrics of a similar kind, generated from multiple queries, are combined into a single event.
Syntax:
In the below example, you can see how the data is loaded into Elasticsearch for the below query when the merge_results is disabled.
Example:
In this example, we are using two different queries to fetch metrics from the performance counter.
merge_results: false
driver: "mssql"
sql_queries:
- query: "SELECT cntr_value As 'user_connections' FROM sys.dm_os_performance_counters WHERE counter_name= 'User Connections'"
response_format: table
- query: "SELECT cntr_value As 'buffer_cache_hit_ratio' FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio' AND object_name like '%Buffer Manager%'"
response_format: table
As you can see, the response for the above example generates a single document for each query.
The resulting document from the first query:
"sql":{
"metrics":{
"user_connections":7
},
"driver":"mssql"
}
And resulting document from the second query:
"sql":{
"metrics":{
"buffer_cache_hit_ratio":87
},
"driver":"mssql"
}
When we enable the merge_results flag in the query, both the above metrics are combined together and the data gets loaded in a single document.
You can see the merged document in the below example:
"sql":{
"metrics":{
"user connections ":7,
“buffer_cache_hit_ratio”:87
},
"driver":"mssql"
}
However, such a merge is possible only if the table queries are merged, and each produces a single row. There is no restriction on variable queries being merged.
Introducing a new capability: fetch_from_all_databases
This is a new functionality to fetch all the database metrics automatically from the system and user databases of the Microsoft SQL Server, by enabling the fetch_from_all_databases flag.
Keep an eye out for the 8.10 release version where you can start using the fetch all database feature. Prior to the 8.10 version, users had to provide the database names manually to fetch metrics from custom/user databases.
Syntax:
Below is the sample query with fetch all databases flag as disabled:
fetch_from_all_databases: false
driver: "mssql"
sql_queries:
- query: "SELECT @@servername AS server_name, @@servicename AS instance_name, name As 'database_name', database_id FROM sys.databases WHERE name='master';"
The above query fetches metrics only for the provided database name. Here the input database is master, so the metrics are fetched only for the master.
Below is the sample query with the fetch all databases flag as enabled:
fetch_from_all_databases: true
driver: "mssql"
sql_queries:
- query: SELECT @@servername AS server_name, @@servicename AS instance_name, DB_NAME() AS 'database_name', DB_ID() AS database_id;
response_format: table
The above query fetches metrics from all available databases. This is useful when the user wants to get data from all the databases.
Please note: currently this feature is supported only for Microsoft SQL Server and will be used by MS SQL integration internally, to support extracting metrics for all user DBs by default.
Using generic SQL: Metricbeat
The generic SQL metricbeat module provides flexibility to execute queries against different database drivers. The metricbeat input is available as GA for any production usage. Here, you can find more information on configuring the generic SQL for different drivers with various examples.
Using generic SQL: Input package
The input package provides a flexible solution to advanced users for customizing their ingestion experience in Elastic. Generic SQL is now also available as an SQLinput package. The input package is currently available for early users as a beta release. Let's take a walk through how users can use generic SQL via the input package.
Configurations of generic SQL input package:
The configuration options for the generic SQL input package are as below:
- Driver** :** This is the SQL database for which you want to use the package. In this case, we will take mysql as an example.
- Hosts: Here the user enters the connection string to connect to the database. It would vary depending on which database/driver is being used. Refer here for examples.
- SQL Queries: Here the user writes the SQL queries they want to fire and the response_format is specified.
- Data set: The user specifies a data set name to which the response fields get mapped.
- Merge results** :** This is an advanced setting, used to merge queries into a single event.
Metrics extensibility with customized SQL queries
Let's say a user is using MYSQL Integration, which provides a fixed set of metrics. Their requirement now extends to retrieving more metrics from the MYSQL database by firing new customized SQL queries.
This can be achieved by adding an instance of SQL input package, writing the customized queries and specifying a new data set name as shown in the screenshot below.
This way users can get any metrics by executing corresponding queries. The resultant metrics of the query will be indexed to the new data set, sql_second_dataset.
When there are multiple queries, users can club them into a single event by enabling the Merge Results toggle.
Customizing user experience
Users can customize their data by writing their own ingest pipelines and providing their customized mappings. Users can also build their own bespoke dashboards.
As we can see above, the SQL input package provides the flexibility to get new metrics by running new queries, which are not supported in the default MYSQL integration (the user gets metrics from a predetermined set of queries).
The SQL input package also supports multiple drivers: mssql, postgresql and oracle. So a single input package can be used to cater to all these databases.
Note: The fetch_from_all_databases feature is not supported in the SQL input package yet.
Try it out!
Now that you know about various use cases and features of generic SQL, get started with Elastic Cloud and try using the SQL input package for your SQL database and get customized experience and metrics. If you are looking for newer metrics for some of our existing SQL based integrations — like Microsoft SQL Server, Oracle, and more — go ahead and give the SQL input package a swirl.
The release and timing of any features or functionality described in this post remain at Elastic's sole discretion. Any features or functionality not currently available may not be delivered on time or at all.