Analyze non-standard logs with custom SQL detection rules

Overview

Agentic SOC collects and analyzes standard Alibaba Cloud security logs by default. To analyze non-standard logs such as RDS SQL audit logs, configure extension fields and data integration first. The configuration process:

1. Add extension fields: Define custom fields in the RDS Database Audit Activity schema for use in detection rules.

2. Modify the standardization rule: Set extension field mapping to Keep Original in the RDS Database Audit Activity standardization rule.

3. Enable the data source: Add and enable the RDS SQL audit log data source.

4. Enable service integration: Enable the ApsaraDB RDS ingestion policy.

5. Create a custom SQL detection rule: Create a rule from a template.

6. View and handle alerts: Review and respond to alerts or events from the detection rule.

Before you begin

• Agentic SOC is enabled. For more information, see Purchase and activate Agentic SOC.

• An ApsaraDB RDS for MySQL instance is available and the SQL audit feature is enabled. For more information, see Collect RDS SQL audit logs.

Step 1: Add extension fields

Add extension fields to the RDS Database Audit Activity schema for use in custom SQL detection rules. For more information, see Standardized rules and datasets.

1. Access the Security Center console - Agentic SOC - Management - Access Settings. In the upper-left corner of the page, select the region where the assets to be protected are located: Chinese Mainland or Outside Chinese Mainland.

2. Click the Standard Fields page, and click the Standardized Field tab.

3. In the Standard Fields panel on the left side, expand Log Activity Category> Audit Category, and then select RDS Database Audit Activity.

4. In the right-side panel, click the Extension Fields tab, and click Field Management. Add the following extension fields. These are examples. Adjust based on your needs.

   Extension field name	Field type	Description
   APP_NAME	text	Application name.
   application_name	text	Application name.
   backend_type	text	Backend type.
   command_tag	text	Command tag.
   connection_from	text	Connection source.
   context	text	Context.
   database_name	text	Database name.
   detail	text	Details.
   env	text	Environment.
   error_severity	text	Error severity.

Step 2: Modify the standardization rule

Modify the RDS Database Audit Activity standardization rule to ingest the extension fields from Step 1.

1. On the Integration Settings page, click the Standardized Rule tab.

2. In the Service search box, search for ApsaraDB RDS.

3. In the Add Extended Fields column, click the drop-down list and select Keep Original.

Step 3: Enable the data source

Add and enable the RDS SQL audit log data source so that Agentic SOC can receive log data.

1. On the Integration Settings page, click the Data Source tab.

2. In the Data Source Name search box, search for RDS SQL audit log, and then click Edit in the Actions column.

3. On the Edit Data Source page, click Create Instance, and select the region and log store of the RDS SQL audit logs.

4. Click OK After the The data source is updated. Message appears, close the page. Refresh the connection status and verify it shows Normal.

5. (Optional) Turn on Auto Discovery to automatically synchronize new instances.

Step 4: Enable service integration

Enable the ApsaraDB RDS ingestion policy so Agentic SOC can receive and process RDS SQL audit logs.

1. On the Integration Settings page, click the Service Integration tab.

2. Find ApsaraDB RDS and click Ingestion Settings in the Actions column.

3. On the Access Policy page, enable the ingestion policies for RDS SQL audit logs and the Standardization Rule for Relational Database Audit Logs.

Step 5: Create a custom SQL detection rule

Create a custom SQL detection rule based on a rule template.

1. Access the Security Center console - Agentic SOC - Management - Detection Rules. In the upper-left corner of the page, select the region where the assets to be protected are located: Chinese Mainland or Outside Chinese Mainland.

2. Click the Rule Template tab and search for the Execution of Query Statements on Database Causes Errors template.

3. Click Create Rule in the Actions column.

4. Configure the following parameters and keep defaults for the rest. For more information, see Detection rules.

   Note

   In this example, APP_NAME and database_name are extension fields configured in Step 1.

   • Rule Body: Select SQL Syntax.

   • Log Range: Select Audit Logs > Relational Database Audit Logs.

   • SQL Query Statement: Modify the SQL statement as needed. The default template SQL:

     * |set session mode=scan;select distinct
     user_id,
     start_time,
     src_ip,
     db,
     user,
     table_name,
     sql,
     affect_rows
     from log
     where schema='RDS_DATABASE_AUDIT_ACTIVITY'
     and regexp_like(sql, '''\s*and\s+updatexml\s*\(') and APP_NAME='Test' and database_name='TestDatabase'

Step 6: View and handle alerts

After the detection rule takes effect, Agentic SOC generates alerts or events when matching log patterns are detected.

1. On the Agentic SOC page, click the Security Incidents or Alert tab.

2. View and handle alerts or events triggered by the detection rule.

FAQ

• Why does the detection rule not trigger alerts?

  Troubleshoot in this order:

  1. Service integration: Verify that the ingestion policy in Step 4 is enabled.

  2. Data source status: On the Data Source tab, verify that the RDS SQL audit log connection status is Normal.

  3. Standardization rule: Verify that the Add Extended Fields value for the RDS Database Audit Activity standardization rule is set to Keep Original.

  4. Detection rule: Verify that the detection rule is enabled and the log range is set to Relational Database Audit Logs.

• What is the difference between extension fields and standard fields?

  Standard fields are predefined fields in the Agentic SOC standardized schema, such as src_ip and user_id, applicable to all log types. Extension fields are user-defined fields for non-standard information specific to certain log types, such as database_name and command_tag. Both can be referenced in detection rule SQL queries.