Doubt in XQL Queries

[N.B238890]

Hi Community,

Can anyone please help me correct these two XQL queries in both objectives?
Each objective uses a different query. If you feel the existing queries are completely incorrect, I kindly request your help in creating new queries from scratch based on the given objectives.

Hunt Mission1 

Vishing‑Based Initial Access 

Objective 

Detect potentially suspicious identity activity indicative of social-engineering attacks, such as vishing, by identifying users who perform password resets, MFA resets, or account unlock actions followed by a successful authentication within a short time window, and surface these cases for further investigation by analyzing associated context such as timing, IP addresses, and user behavior, aligning to MITRE ATT&CK techniques including Phishing for Information (T1598), Vishing (T1566.004), Valid Accounts (T1078), and Credential Manipulation (T1110). 

Query 

dataset = msft_azure_ad_raw 

// Step 1: Identify potential reset events 

| alter is_reset_indicator = if( 

    (clientAppUsed != null and lowercase(clientAppUsed) contains "password") // password reset 

    or (clientAppUsed != null and lowercase(clientAppUsed) contains "mfa") // MFA reset 

    or (appDisplayName != null and lowercase(appDisplayName) contains "unlock") // account unlock 

    or (resourceDisplayName != null and lowercase(resourceDisplayName) contains "unlock"), 

    1, 0 

) 

// Step 2: Identify successful login events 

| alter is_login = if( 

    _raw_json != null and json_extract_scalar(_raw_json, "$.errorCode") = "0", 

    1, 0 

) 

// Step 3: Keep only relevant events 

| filter is_reset_indicator = 1 or is_login = 1 

// Step 4: Aggregate per user to correlate reset → login 

| comp 

    min(if(is_reset_indicator = 1, _time, null)) as first_reset_time, 

    min(if(is_login = 1, _time, null)) as first_login_time, 

    values(ipAddress) as ips, 

    values(userAgent) as user_agents 

    by userPrincipalName 

// Step 5: Ensure login happened after the reset 

| filter first_reset_time != null 

    and first_login_time != null 

    and first_login_time >= first_reset_time 

// Step 6: Calculate time difference in minutes 

| alter time_diff_min = timestamp_diff(first_login_time, first_reset_time, "MINUTE") 

// Step 7: Flag suspicious rapid login (vishing pattern) 

| filter time_diff_min <= 10 

// Step 8: Output context for investigation 

| fields userPrincipalName, first_reset_time, first_login_time, time_diff_min, ips, user_agents 

| sort desc first_login_time 

Hunt Mission2 

AiTM-Based Session Interception 

Objective 

This hunt Mission identifies potentially suspicious authentication activity indicative of adversary-in-the-middle (AiTM) or session hijacking attempts by detecting users who log in from multiple IP addresses, exhibit changes in browser User-Agent, or use headless browsers within the observed timeframe. These anomalies may indicate stolen session tokens, MFA codes, or automated phishing tools, and the results should be investigated further by analyzing login context, IP/geolocation changes, and session behavior. This hunt aligns with MITRE ATT&CK techniques including Valid Accounts (T1078), Credential Access via Phishing (T1566), and Credential Manipulation (T1110). 

Query 

dataset = login_logs 

| alter username = user_id, ts = serverTime 

// Step 1: Flag headless UAs per event 

| alter ua_headless_event = if(lowercase(http_req_user_agent_header) contains "headless", 1, 0) 

// Step 2: Aggregate per user 

| comp  

    values(actor_remote_ip) as ip_list, 

    values(http_req_user_agent_header) as ua_list, 

    max(ua_headless_event) as ua_headless_flag, 

    min(ts) as first_login_time, 

    max(ts) as last_login_time 

    by username 

// Step 3: Count distinct IPs and UAs 

| alter ip_count = array_length(ip_list), 

        ua_count = array_length(ua_list) 

// Step 4: Filter suspicious behavior 

| filter ip_count > 1 

    or ua_count > 1 

    or ua_headless_flag = 1 

// Step 5: Output results 

| fields username, first_login_time, last_login_time, ip_list, ua_list, ip_count, ua_count, ua_headless_flag 

| sort desc last_login_time