Okta logs queries

This page contains example Panther queries for Okta log data

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 Panther-managed Okta detections here.

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