Tracing Malicious Attacks on Large Platforms with Alibaba Cloud SLS SQL

Alibaba Cloud Log Service (SLS) provides scalable storage, query, and analysis for massive log data. Leveraging SLS SQL, users can extract actionable information from both structured and unstructured logs to support operations, monitoring, and security investigations.

Attack Scenario

A customer’s online service was hit by a large‑scale traffic attack originating from many global IPs, causing a sharp increase in load and disrupting normal user requests. The business team urgently asked the operations lead to identify the attack source and apply immediate blocks.

Step 1 – Identify High‑Frequency IPs

Logs contain a characteristic pattern when rate limiting occurs:

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

Using SLS SQL, the high‑frequency IPs are extracted with regexp_extract on the msg field and aggregated per two‑minute window:

"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 limit all

Step 2 – Correlate with Account Information

Because the raw logs lack a dedicated IP column, the regexp_extract function creates one on‑the‑fly. The same query also extracts uid and trace_id from SMS receipt logs, then joins the two sub‑queries on trace_id to link IPs with user accounts:

select * 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) as 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 count(1) > 100
order by count(1) desc

This yields accounts that generated more than 100 requests within a two‑minute window, indicating likely malicious actors.

Step 3 – Geographic Enrichment

To pinpoint the origin of the IPs, the built‑in function ip_to_country replaces the plain IP aggregation: array_agg(distinct ip_to_country(ip)) The result shows the distribution of attacking IPs by country.

Step 4 – Automation with Python SDK

A Python script uses the Alibaba Cloud SLS SDK to run the above queries, filter out whitelisted IPs, and store the final blacklist in Redis with a TTL of three days:

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 = extract_ip(log)
        cnt = extract_count(log)
        if ip and cnt and not is_in_whitelist(ip):
            logs_data[ip] = int(cnt)
    return logs_data

The script then updates both Alibaba Cloud WAF and AWS WAF using their respective Python SDKs (boto3 for AWS). Example code creates a Web ACL and IP set in AWS:

# Create 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

IP sets are populated in batches of up to 5,000 entries (max 10,000 per set) and linked to the Web ACL. The same blacklist is pushed to Alibaba Cloud WAF via its SDK.

Outcome

The combined analysis accurately traced the malicious account and its IP sources. After the security team blocked the single identified account and the associated IP ranges, the attack ceased. The automated pipeline now continuously detects abnormal IPs, updates firewalls in both clouds, and expires stale entries, providing a robust, multi‑cloud defensive posture.