Tracing Malicious Attacks with Alibaba Cloud SLS SQL and Automated Multi‑Cloud Defense

SLS SQL User Story Series – Episode 1

Alibaba Cloud Log Service (SLS) stores, queries, and analyzes massive log data for various business scenarios. Using SLS SQL, users can extract useful information from any log structure—numeric, JSON, or unstructured text—to support operations, insights, and decision‑making.

While we often highlight SQL's generic capabilities, high‑performance, and low cost, we rarely share concrete user stories that demonstrate real business value. This series presents such stories, focusing on problems, solutions, and practical implementations.

Each story is short, light‑hearted, and designed to be read in under five minutes.

Large Platform Customer Malicious Attack Trace Analysis

"Oh no, we’re under a malicious attack..."

One afternoon the customer’s online service was hit by a massive traffic surge from IPs worldwide, causing a sharp load increase and affecting normal user requests. The business team urgently called the operations lead to identify the attack source and block it.

Detecting High‑Frequency Access IPs

Log entries with the pattern "rate limit for true-client-ip" indicate high‑frequency behavior. Example log line:

...rate limit for true-client-ip: 139.135.241.87 trace_id: ...

Using SLS SQL, we can quickly retrieve these high‑frequency logs:

"rate limit for true-client-ip" | select regexp_extract(msg, 'true-client-ip: (\d+\.\d+\.\d+\.\d+)', 1) as ip, count(1) as pv, __time__%120 as t from log group by ip, t order by pv desc

However, the log is unstructured text without an explicit IP column, so we need to extract the IP using regular expressions.

We use the regexp_extract function to pull the IP from the msg field and then aggregate by IP in two‑minute windows.

Correlating Malicious Accounts

High‑frequency IPs are numerous, making manual blocking impractical. By linking logs via trace_id, we can associate IPs with user accounts (UID) recorded in SMS verification logs.

select date_trunc('minute', __time__) t, uuid, count(1) cnt, array_agg(distinct ip) ip FROM ( select __time__, regexp_extract(msg, '"true-client-ip": (\d+\.\d+\.\d+\.\d+)', 1) as ip, trace_id FROM log where msg has '"rate limit for true-client-ip": ') t1 left join ( select trace_id, regexp_extract(msg, '"uid":"([^"]*)"', 1) uuid FROM log where msg has 'player.getSMScode' and msg has 'Received') t2 on t1.trace_id = t2.trace_id group by t, t2.uuid having cnt > 100 order by cnt desc

This query extracts high‑frequency IPs, joins them with UID via trace_id, and filters accounts that generated more than 100 requests per minute, indicating suspicious activity.

The result shows a small set of malicious accounts and their associated IPs.

Geolocation of IPs

We can further enrich the analysis by converting IPs to countries using the ip_to_country function: ... array_agg(distinct ip_to_country(ip)) ... Images illustrate the aggregated data (omitted here).

Automated Multi‑Cloud Blocking

After identifying malicious IPs, the customer, who runs services on both Alibaba Cloud and AWS, implemented an automated workflow:

Use SLS to detect high‑frequency IPs.

Apply a whitelist to filter out internal or partner IPs.

Store the final blacklist in Redis with a TTL of three days.

Periodically update Alibaba Cloud WAF and AWS WAF IP sets using their respective SDKs.

Sample Python SDK code for Alibaba Cloud SLS:

def get_logs_from_sls():
    query = "\"rate limit for true-client-ip\":|select ... limit all"
    request = GetLogsRequest(project_name, logstore_name, from_time, to_time, query=query)
    response = client.get_logs(request)
    logs_data = {}
    for log in response.get_logs():
        ip = None
        # extract ip and count
        if ip and cnt:
            if not is_in_whitelist(ip):
                logs_data[ip] = int(cnt)
    return logs_data

Sample boto3 code to create an AWS WAF Web ACL:

def create_web_acl(waf_acl_name, waf_rules):
    try:
        response = waf_client.create_web_acl(
            Name=waf_acl_name,
            Scope=region_scope,
            DefaultAction={'Allow': {}},
            Rules=waf_rules,
            VisibilityConfig={
                'SampledRequestsEnabled': True,
                'CloudWatchMetricsEnabled': True,
                'MetricName': waf_acl_name + 'ACLMetric'
            }
        )
        return response['Summary']['Id']
    except ClientError as e:
        mylog("error", f"Error creating Web ACL: {e}")
        return None

The workflow reads IPs from Redis, formats them as CIDR blocks, splits them into groups of up to 5,000 (max 10,000 per IP set), creates/updates the corresponding AWS IP sets, and attaches them to the Web ACL. Existing Web ACL rules are preserved, and only the auto‑generated rules are updated.

Final screenshots show the updated WAF configurations and the overall architecture diagram.

Conclusion

The case demonstrates that, regardless of data format, Alibaba Cloud SLS combined with powerful SQL queries can perform complex security investigations—extracting high‑frequency IPs, correlating them with user identifiers, applying geographic analysis, and automatically enforcing protection across multi‑cloud environments.

By leveraging SLS’s high‑performance search and flexible analysis, customers can quickly locate issues, pinpoint malicious sources, and mitigate risks.