Need help in validating XQL query to identify suspicious MFA registration from new GEO location + new device

MohanrajaE — Tue, 06 Oct 2026 17:55:41 GMT

dataset = okta_sso_raw
| filter (debugContext contains "New Geo-Location=POSITIVE" and debugContext contains "New Device=POSITIVE") and (eventType in("user.mfa.factor.activate", "user.lifecycle.create", "system.import.user.create"))
| alter user_id = if(eventType = "user.lifecycle.create" or eventType = "system.import.user.create", json_extract_scalar(`target`, "$.0.alternateId"), json_extract_scalar(actor, "$.alternateId")), user_name = json_extract_scalar(actor, "$.displayName"), TIME = _time
| alter
suspicious_login_time = if(debugContext contains "New Geo-Location=POSITIVE" and debugContext contains "New Device=POSITIVE", _time, null),
suspicious_login_ip = if(debugContext contains "New Geo-Location=POSITIVE" and debugContext contains "New Device=POSITIVE", json_extract_scalar(client, "$.ipAddress"), null),
suspicious_login_city = if(debugContext contains "New Geo-Location=POSITIVE" and debugContext contains "New Device=POSITIVE", json_extract_scalar(client, "$.geographicalContext.city"),null),
suspicious_login_state = if(debugContext contains "New Geo-Location=POSITIVE"and debugContext contains "New Device=POSITIVE",json_extract_scalar(client, "$.geographicalContext.state"),null),
suspicious_login_country = if(debugContext contains "New Geo-Location=POSITIVE"and debugContext contains "New Device=POSITIVE", json_extract_scalar(client, "$.geographicalContext.country"), null),
suspicious_login_device = if(debugContext contains "New Geo-Location=POSITIVE" and debugContext contains "New Device=POSITIVE",json_extract_scalar(client, "$.device"),null),
suspicious_login_os = if(debugContext contains "New Geo-Location=POSITIVE" and debugContext contains "New Device=POSITIVE",json_extract_scalar(client, "$.userAgent.os"),null),
suspicious_login_browser = if(debugContext contains "New Geo-Location=POSITIVE" and debugContext contains "New Device=POSITIVE", json_extract_scalar(client, "$.userAgent.browser"),null),
suspicious_login_user_agent = if(debugContext contains "New Geo-Location=POSITIVE"and debugContext contains "New Device=POSITIVE",json_extract_scalar(client, "$.userAgent.rawUserAgent"),null),
mfa_activation_time = if(eventType = "user.mfa.factor.activate", _time,null),
mfa_factor = if(eventType = "user.mfa.factor.activate",json_extract_scalar(debugContext,"$.debugData.factor"),null),
mfa_factor_type = if(eventType = "user.mfa.factor.activate",json_extract_scalar(debugContext,"$.debugData.factorType"),null),
mfa_device_name = if(eventType = "user.mfa.factor.activate",json_extract_scalar(debugContext,"$.debugData.deviceName"),null),
account_creation_time = if(eventType = "user.lifecycle.create" or eventType = "system.import.user.create", _time,null)
| windowcomp max(suspicious_login_time) by user_id sort asc _time between null and -1 as previous_suspicious_login,
last_value(suspicious_login_ip) by user_id sort asc _time between null and -1 as previous_suspicious_login_ip,
last_value(suspicious_login_city) by user_id sort asc _time between null and -1 as previous_suspicious_login_city,
last_value(suspicious_login_state) by user_id sort asc _time between null and -1 as previous_suspicious_login_state,
last_value(suspicious_login_country) by user_id sort asc _time between null and -1 as previous_suspicious_login_country,
last_value(suspicious_login_device) by user_id sort asc _time between null and -1 as previous_suspicious_login_device,
last_value(suspicious_login_os) by user_id sort asc _time between null and -1 as previous_suspicious_login_os,
last_value(suspicious_login_browser) by user_id sort asc _time between null and -1 as previous_suspicious_login_browser,
last_value(suspicious_login_user_agent) by user_id sort asc _time between null and -1 as previous_suspicious_login_user_agent,
min(account_creation_time) by user_id as first_account_creation
| filter mfa_activation_time != null
| filter previous_suspicious_login != null
| filter timestamp_diff(previous_suspicious_login, mfa_activation_time,"HOUR") >= 0 and timestamp_diff(previous_suspicious_login,mfa_activation_time,"HOUR") <= 4
| filter first_account_creation = null or timestamp_diff(first_account_creation,previous_suspicious_login,"DAY") > 30
| dedup user_id, previous_suspicious_login, mfa_activation_time
| fields user_id, user_name, previous_suspicious_login, previous_suspicious_login_ip, previous_suspicious_login_city, previous_suspicious_login_state, previous_suspicious_login_country, previous_suspicious_login_device, previous_suspicious_login_os, previous_suspicious_login_browser, previous_suspicious_login_user_agent, mfa_activation_time, mfa_device_name, mfa_factor, mfa_factor_type, first_account_creation
| sort desc mfa_activation_time

Re: Need help in validating XQL query to identify suspicious MFA registration from new GEO location + new device

MohanrajaE — Wed, 07 Oct 2026 16:49:48 GMT

just to add few pointers, the above query is successfully running by changing below bold
dataset = okta_sso_raw
| filter (debugContext contains "New Geo-Location=POSITIVE" and debugContext contains "New Device=POSITIVE") and (eventType in("user.mfa.factor.activate", "user.lifecycle.create", "system.import.user.create"))

Challenge: Query is successfully running with results but when add it in correlation use-case, the count of the cases and events are not same.
Use-case condition, query should run every 1hr to look for last 4hrs of events, no suppression is enabled as we don't see duplicate cases.
Appreciate the response. thank you.

topic Re: Need help in validating XQL query to identify suspicious MFA registration from new GEO location + new device in Cortex XSIAM Discussions

Need help in validating XQL query to identify suspicious MFA registration from new GEO location + new device

Re: Need help in validating XQL query to identify suspicious MFA registration from new GEO location + new device