How to create a Logtail configuration to collect SQL Server query results-Simple Log Service(SLS)-阿里云帮助中心

Prerequisites

Logtail must be installed. Use version 0.16.0 or later for Linux, or 1.0.0.8 or later for Windows. For more information, see Install Logtail on a Linux server or Install Logtail on a Windows server.

Important
Ensure that the Logtail server can connect to the target database.
Add the Logtail server's IP address to your SQL Server database whitelist.

For example, to configure the whitelist for an ApsaraDB RDS for SQL Server instance, see Configure an IP address whitelist.

Features

Collect query results from SQL Server databases.
Configure pagination.
Configure time zones.
Configure timeouts.
Save checkpoint states.
Limit the number of records collected per synchronization.

Procedure

Log on to the Simple Log Service console.
In the Import Data section, click Custom Data Plug-in.
Select the destination project and Logstore, and then click Next.
In the Machine Group Configurations step, configure a machine group.
1. Configure the Scenario and Installation Environment parameters as needed.
  Important
  You must configure the Scenario and Installation Environment parameters regardless of whether a machine group is available. The parameter settings affect subsequent configurations.
2. Ensure that a machine group is displayed in the Applied Server Groups section, and click Next.
  Machine group available
  Select a machine group from the Source Machine Group section.
  No machine group available
  Click Create Machine Group. In the Create Machine Group panel, configure the parameters. You can set the Machine Group Identifier parameter to IP Address or Custom Identifier. For more information, see Create a custom identifier-based machine group or Create an IP address-based machine group.
  Important
  If you apply a machine group immediately after you create the machine group, the heartbeat status of the machine group may be FAIL. This issue occurs because the machine group is not connected to Simple Log Service. To resolve this issue, you can click Automatic Retry. If the issue persists, see What do I do if no heartbeat connections are detected on Logtail?

On the Configure Data Source tab, set Configuration Name and Plug-in Configuration, and then click Next.

inputs is required and is used to configure the data source settings for the Logtail configuration.

Important
You can specify only one type of data source in inputs.
processors is optional and is used to configure the data processing settings for the Logtail configuration to parse data. You can specify one or more processing methods.

If your logs cannot be parsed based only on the setting of inputs, you can configure processors in the Plug-in Configuration field to add plugins for data processing. For example, extract fields, extract log time, mask data, and filter logs. For more information, see Logtail plugins for data processing.

{
  "inputs": [
    {
      "type": "service_mssql",
      "detail": {
        "Address": "rm-****.sqlserver.rds.aliyuncs.com",
        "User": "****",
        "Password": "*******",
        "DataBase": "****",
        "Limit": true,
        "PageSize": 100,
        "StateMent": "select * from LogtailTestTable where id > ? order by id",
        "CheckPoint": true,
        "CheckPointColumn": "id",
        "CheckPointStart": "0",
        "CheckPointSavePerPage": true,
        "CheckPointColumnType": "int",
        "IntervalMs": 1000
      }
    }
  ]
}

Parameter	Type	Required	Description
type	string	Yes	The data source type is set to service_mssql.
Address	string	No	The SQL Server address. For example: `rm-****.sqlserver.rds.aliyuncs.com`. If this parameter is not configured, the default is 127.0.0.1:1433.
User	string	No	The username for logging on to the SQL Server database. If not configured, root is used by default.
Password	string	No	The password for the user. This parameter is empty by default. If you have high security requirements, we recommend that you set the username and password to `xxx`. After the Logtail configuration is synchronized to your local machine, find the corresponding configuration in the /usr/local/ilogtail/user_log_config.json file and modify it. For more information, see Modify local configurations. Important If you modify this parameter in the console, the console overwrites the local configuration upon synchronization.
DataBase	string	No	The name of the SQL Server database.
DialTimeOutMs	int	No	The timeout for connecting to the SQL Server database. Unit: milliseconds (ms). Default value: 5000.
ReadTimeOutMs	int	No	The timeout for reading query results from the SQL Server database. Unit: ms. Default value: 5000.
StateMent	string	No	The SELECT statement. When you set CheckPoint to true, the `WHERE` clause of the `SELECT` statement in StateMent must contain the column specified by the CheckPointColumn parameter, and the value of this column must be set to ?. For example: set CheckPointColumn to id and StateMent to `SELECT * from ... where id > ?`.
Limit	boolean	No	Specifies whether to use pagination with a LIMIT clause. If this parameter is not specified, the default value is false, which indicates that Limit-based pagination is not used. We recommend that you use Limit for pagination. If you set Limit to true, a LIMIT statement is automatically appended to the Statement when you perform an SQL query.
PageSize	int	No	The page size. This parameter is required when Limit is set to true.
MaxSyncSize	int	No	The maximum number of records to synchronize at a time. If you do not configure this parameter, the default value is 0, which indicates no limit.
CheckPoint	boolean	No	Specifies whether to use a checkpoint for incremental collection. If not configured, the default value is false, which indicates that Checkpoint is not used.
CheckPointColumn	string	No	The name of the checkpoint column. Required if CheckPoint is set to true. Warning Values in the checkpoint column must be incremental. Otherwise, data loss may occur because Logtail uses the maximum value from the last query as the starting point for the next.
CheckPointColumnType	string	No	The data type of the checkpoint column. Valid values: `int` and `time`. Logtail stores the `int` type as int64. The `time` type supports SQL Server's `date`, `datetime`, and `time` types. This parameter is required if CheckPoint is set to true.
CheckPointStart	string	No	The initial value of the checkpoint column. This parameter is required if CheckPoint is set to true.
CheckPointSavePerPage	boolean	No	Specifies whether to save the checkpoint after collecting each page. true: Saves a Checkpoint each time pagination occurs. false: Saves a Checkpoint after each synchronization is complete.
IntervalMs	int	Yes	The synchronization interval, in ms.

Create indexes and preview data. Then, click Next. By default, full-text indexing is enabled in Simple Log Service. You can also manually create field indexes for the collected logs or click Automatic Index Generation. Then, Simple Log Service generates field indexes. For more information, see Create indexes.
Important
If you want to query all fields in logs, we recommend that you use full-text indexes. If you want to query only specific fields, we recommend that you use field indexes. This helps reduce index traffic. If you want to analyze fields, you must create field indexes. You must include a SELECT statement in your query statement for analysis.
Click Query Log to go to the query and analysis page for the Logstore.

Wait about one minute for the indexes to take effect. Then, you can view the collected logs on the Raw Logs tab. For more information, see Quick start for log query and analysis.

Modify the local configuration

If you did not enter the actual values for parameters such as Address, User, and Password in the Plug-in Configuration section, you can modify them manually after the Logtail configuration is delivered.

Linux

Log on to the server where Logtail is installed.
Open the /usr/local/ilogtail/user_log_config.json file, find the service_mssql keyword, and modify fields such as Address, User, and Password.

Run the following command to restart Logtail.

sudo /etc/init.d/ilogtaild stop; sudo /etc/init.d/ilogtaild start

Windows

Log on to the server where Logtail is installed.
Open the C:\Program Files\Alibaba\Logtail\user_log_config.json file, find the service_mssql keyword, and modify the Address, User, and Password fields.

The file path is different for 64-bit and 32-bit Windows operating systems. For more information, see File paths.
Restart Logtail.
1. Choose Start Menu > Control Panel > Administrative Tools > Services.
2. In the Services dialog box, select the service that you want to manage.
  - For Logtail V0.x.x.x, select LogtailWorker.
  - For Logtail V1.0.0.0 or later, select LogtailDaemon.
3. Right-click the service and select Start, Stop, or Restart.

Troubleshooting

If no data appears on the preview page or the query page after you use Logtail to collect logs, see Troubleshoot Logtail log collection failures.

Sample database table and logs

This section provides a sample schema for an SQL Server database table and sample logs collected by Logtail.

Sample table schema

IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = 'LogtailTest')
BEGIN
CREATE DATABASE [LogtailTest]
END
GO
USE [LogtailTest]
GO

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='LogtailTestTable' and xtype='U')
BEGIN
CREATE TABLE LogtailTestTable (
      id INT PRIMARY KEY IDENTITY (1, 1),
      name NVARCHAR(50),
      quantity INT
    )
END
GO

INSERT INTO LogtailTestTable (name, quantity) values('banana', 1);
INSERT INTO LogtailTestTable (name, quantity) values('banana', 2);
INSERT INTO LogtailTestTable (name, quantity) values('banana', 3);
INSERT INTO LogtailTestTable (name, quantity) values('banana', 4);
SELECT * FROM LogtailTestTable;
GO

Sample logs