Below are a few queries to get started with investigating and learning about your Okta events. These queries are meant to investigate existing log data. If you are interested in detecting new data that flows in, consider enabling the .
The following queries are written for Snowflake SQL syntax, unless noted otherwise.
Top logins by user last 7 days
-- Top logins by user last 7 days
SELECT actor:alternateId as actor, COUNT(*) as total
FROM panther_logs.public.okta_systemlog
WHERE eventtype = 'user.authentication.sso'
and outcome:result = 'SUCCESS'
and p_occurs_since(7d)
GROUP BY actor
ORDER BY total desc
Logins by hour last 1 day
-- Logins by hour last 1 day
SELECT
time_slice(p_event_time, 1, 'HOUR', 'START') as "start",
time_slice(p_event_time, 1, 'HOUR', 'END') as "end",
count(*) as "logins",
count(distinct(actor:alternateId)) as "users"
FROM panther_logs.public.okta_systemlog
WHERE eventtype = 'user.authentication.sso'
and outcome:result = 'SUCCESS'
and p_occurs_since(1d)
GROUP BY "start", "end"
ORDER BY "start" desc
Top applications last 7 days
-- Top applications last 7 days
SELECT GET(target, 0):displayName as application, count(*) as total
FROM panther_logs.public.okta_systemlog
WHERE eventtype = 'user.authentication.sso'
and p_occurs_since(7d)
GROUP BY Application
ORDER BY total desc
Top failing users last 7 days
-- Top failing users last 7 days
SELECT actor:alternateId as actor, COUNT(*) as total
FROM panther_logs.public.okta_systemlog
WHERE eventtype = 'user.session.start'
and outcome:result = 'FAILURE'
and outcome:reason = 'INVALID_CREDENTIALS'
and p_occurs_since(7d)
GROUP BY actor
ORDER BY total desc
Login failures by reason last 7 days
-- Login failures by reason
SELECT outcome:reason as reason, COUNT(*) as total
FROM panther_logs.public.okta_systemlog
WHERE eventtype = 'user.session.start'
and outcome:result = 'FAILURE'
and p_occurs_since(7d)
GROUP BY reason
ORDER BY total desc
Fake account login attempts last 7 days
-- Fake account login attempts
SELECT actor:alternateId as actor, COUNT(*) as total
FROM panther_logs.public.okta_systemlog
WHERE eventtype = 'user.session.start' and
outcome:result = 'FAILURE' and
outcome:reason = 'VERIFICATION_ERROR'
GROUP BY actor
ORDER BY total desc
Identify Okta Support access to your Okta organization
Snowflake SQL:
SELECT
p_event_time as event_time,
actor:alternateId as actor_email,
actor:displayName as actor_name,
client:ipAddress as src_ip,
client:geographicalContext:city as city,
client:geographicalContext:country as country,
client:userAgent:rawUserAgent as user_agent,
displayMessage,
eventType
FROM
panther_logs.public.okta_systemlog
WHERE
eventType = 'user.session.impersonation.grant'
OR
eventType = 'user.session.impersonation.initiate'
AND
p_occurs_between('YYYY-MM-DD','YYYY-MM-DD')
ORDER BY
event_time desc
Athena SQL:
SELECT
p_event_time as event_time,
actor.alternateid as actor_email,
actor.displayName as actor_name,
displayMessage,
eventType,
client.ipAddress as src_ip,
client.geographicalContext.city as city,
client.geographicalContext.country as country,
client.useragent.rawUserAgent as user_agent
FROM panther_logs.okta_systemlog
WHERE
(
eventType = 'user.session.impersonation.grant' OR
eventType = 'user.session.impersonation.initiate'
) and
p_occurs_between('YYYY-MM-DD','YYYY-MM-DD')
ORDER BY
event_time desc
Identify all users who have been granted Admin privileges in Okta
Snowflake SQL:
SELECT
p_event_time as event_time,
actor:alternateId as actor_email,
actor:displayName as actor_name,
displayMessage,
eventType,
debugContext:debugData:privilegeGranted as priv_granted,
target as target_user,
client:ipAddress as src_ip,
client:geographicalContext:city as city,
client:geographicalContext:country as country,
client:userAgent:rawUserAgent as user_agent
FROM
panther_logs.public.okta_systemlog
WHERE
( eventType = 'user.account.privilege.grant'
OR
eventType = 'group.privilege.grant'
AND
debugContext:debugData:privilegeGranted like '%Admin%'
)
AND
p_occurs_between(''YYYY-MM-DD','YYYY-MM-DD')
ORDER BY
event_time desc
Athena SQL:
SELECT
p_event_time as event_time,
actor.alternateid as actor_email,
actor.displayName as actor_name,
displayMessage,
eventType,
client.ipAddress as src_ip,
client.geographicalContext.city as city,
client.geographicalContext.country as country,
client.useragent.rawUserAgent as user_agent
FROM panther_logs.okta_systemlog
WHERE
(
eventType = 'user.account.privilege.grant' OR
eventType = 'group.privilege.grant'
) AND
p_occurs_between('YYYY-MM-DD','YYYY-MM-DD')
ORDER BY
event_time desc
