Security Center:Detect dormant account logins with custom SQL rules

Define recent activity

(
  select
    user_id,
    src_ip,
    username,
    uuid,
    start_time
  from
    log
  where
    cast(start_time as bigint) >= cast(to_unixtime (current_timestamp) as bigint) - 20 * 60
    and cast(start_time as bigint) < cast(to_unixtime (current_timestamp) as bigint)
) a

• Syntax analysis:

  • from log: Queries data from the log table.

  • to_unixtime(current_timestamp): Returns the current Unix timestamp (seconds).

  • cast(... as bigint): Converts the timestamp to bigint for arithmetic operations and comparisons.

  • where ...: Defines a 20-minute sliding window ending at the current time.

    • >= ... - 20 * 60: start_time must be at or after current time minus 20 minutes.

    • < ...: start_time must be before the current time.

• Semantic analysis:

  • Purpose: Defines a result set named a for "recent activity" — the primary objects of analysis.

  • Result: A temporary result set of users active in the last 20 minutes, including user_id and src_ip.

  • Key point: The "recent" period is a 20-minute sliding window ending at the current time.

Define a historical baseline

(
  select
    user_id,
    username,
    uuid
  from
    log
  where
    schema='HOST_LOGIN_ACTIVITY' and
    cast(start_time as bigint) >= cast(to_unixtime (current_timestamp) as bigint) - 24 * 3600
    and cast(start_time as bigint) < cast(to_unixtime (current_timestamp) as bigint) - 20 * 60
) b

• Syntax analysis:

  • schema='HOST_LOGIN_ACTIVITY': Filters to host login activity logs only, improving baseline accuracy.

  • where ...: Defines a time range from 24 hours ago to 20 minutes ago.

    • >= ... - 24 * 3600: Start time must be at or after current time minus 24 hours.

    • < ... - 20 * 60: Start time must be before current time minus 20 minutes.

• Semantic analysis:

  • Purpose: Builds a "historical baseline" set named b as a reference for identifying abnormal activity.

  • Result: A temporary result set of users who logged in within the last 24 hours, excluding the most recent 20 minutes.

  • Key point: The window spans from 24 hours ago to 20 minutes ago, ensuring no overlap with recent activity data and preventing self-comparison errors.

Use LEFT JOIN for difference comparison

... a
left join
... b on a.username = b.username and a.uuid = b.uuid and a.user_id = b.user_id

• Syntax analysis:

  • LEFT JOIN: Uses the left table (a, recent users) as the base and matches each record against the right table (b, historical users).

  • on a.username = b.username and a.uuid = b.uuid and a.user_id=b.user_id: Joins on username, uuid, and user_id together to uniquely identify a user entity.

• Semantic analysis:

  • Purpose: Correlates "recent activity" (a) with the "historical baseline" (b) to find historical login records for each recent user.

  • Result: A combined result set with all records from table a and matched records from table b.

  • Key point: A LEFT JOIN is asymmetric: if a user from table a has no match in table b, all columns from table b return NULL. This is the core mechanism for identifying new activity.

Filter the final result

where
  (
    b.username is null
    or b.username = ''
  )

• Syntax analysis:

  • b.username is null: Leverages the LEFT JOIN behavior. When a recent user (table a) has no match in the historical baseline (table b), b.username is NULL.

  • or b.username = '': A defensive condition for cases where a log field is an empty string '' instead of NULL.

• Semantic analysis:

  • Purpose: Filters the joined results to isolate activities that appeared recently but have no historical record.

  • Result: Only unmatched records from the LEFT JOIN remain — the abnormal events you are looking for.

  • Key point: b.username is null is the core decision point of the detection logic. It uses the NULL values from the join to separate "present recently, absent historically" records from the dataset.

SELECT DISTINCT: Output alerts

select distinct
     a.user_id,
     a.src_ip,
     a.username,
     a.uuid

• Syntax analysis:

  • SELECT DISTINCT: Selects the specified fields and removes duplicates, ensuring one alert per abnormal event.

• Semantic analysis:

  • Purpose: Formats and outputs a final list of abnormal events for alerting.

  • Result: A deduplicated alert list. Each record contains the core tracing information: user ID and source IP.

  • Key point: DISTINCT deduplicates results so the same user's abnormal behavior within the 20-minute detection window triggers only one alert.

Final SQL query

*|set session mode=scan;
select distinct
     a.user_id,
     a.src_ip,
     a.username,
     a.uuid
   from
     (
       select
         user_id,
         src_ip,
         username,
         uuid,
         start_time
       from
         log
       where
         cast(start_time as bigint) >= cast(to_unixtime (current_timestamp) as bigint) -20 * 60
         and cast(start_time as bigint) < cast(to_unixtime (current_timestamp) as bigint)
     ) a
     left join (
       select
         user_id,
         username,
         uuid
       from
         log
       where
         schema='HOST_LOGIN_ACTIVITY' and
         cast(start_time as bigint) >= cast(to_unixtime (current_timestamp) as bigint) - 24 * 3600
         and cast(start_time as bigint) < cast(to_unixtime (current_timestamp) as bigint) -20 * 60
     ) b on a.username = b.username
     and a.uuid = b.uuid
     and a.user_id=b.user_id
   where
     (
       b.username is null
       or b.username = ''
     )

Configure the rule in Agentic SOC

1. Purchase and enable Agentic SOC

   Refer to Purchase and enable for purchasing options. To access all custom threat detection features, we recommend purchasing both Log Ingestion Traffic and Log Storage Capacity.

2. Log on to the console and navigate to the Create Custom Rule page

   1. Log on to the .

   2. In the left-side navigation pane, choose Agentic SOC > Detection Rules. In the upper-left corner of the Console, select the Region where your assets are located: Chinese Mainland or Outside Chinese Mainland.

   3. On the Custom tab, click Create Custom Rule.

3. Configure the alert generation rule

   1. In the Create Custom Rule panel, on the Basic Information tab, enter a rule name and description, then click Next to go to the Alert Settings page.

   2. Configure the SQL detection rule with the following settings:

      Parameter	Value
      Rule Body	SQL
      Log Scope	Logon Logs - Host Logon Success Log.
      SQL Query	Copy the code from the Final SQL query section.
      Scheduling Interval	Fixed Interval - 20 minutes.
      SQL Time Window	24 hours.
      Start Time	When the rule is enabled.
      Generation Structure	Other Alert Logs.
      Alarm Metric	Abnormal Logon.
      Alert Severity	Medium.
      ATT&CK Tactic	Persistence - T1136 Create Account.
      Entity Mapping	Network Address is_malware: 1 ip: $src_ip net_connect_dir: in Host is_asset: 1 uuid: $uuid

4. Configure the Incident generation rule

   1. On the Alert Settings page, after you complete the configuration, click Next to go to the Incident Generation Settings page.

   2. Configure the time rule with the following settings:

      • Generate Event: Yes.

      • Incident Generation Method: Aggregate by type.

      • Aggregation Window: 20 minutes.

5. Validate the rule

   New rules are Disabled by default. Test them first to evaluate effectiveness. During testing, the system automatically calibrates alert fields. Use the calibration suggestions to optimize the rule's SQL or playbook before enabling.

   1. Change the Enabling Status of the target rule to Testing.

   2. In the actions column for the target rule, click View Alert Test Result.

   3. View the alert trend chart and the list of generated alerts on the test result details page.

   4. In the Actions column for an alert, click Details to view its calibration results.

6. Enable the custom rule

   After the rule passes testing, set its Enabling Status to Enabled.

   Important

   Test the rule before enabling it.