# Okta 로그 쿼리

아래는 Okta 이벤트를 조사하고 학습하기 위해 시작할 수 있는 몇 가지 쿼리입니다. 이 쿼리들은 기존 로그 데이터를 조사하도록 설계되었습니다. 들어오는 새로운 데이터를 탐지하려면 여기를 활성화하는 것을 고려하세요 [Panther 관리 Okta 디텍션 여기](https://docs.panther.com/ko/data-onboarding/supported-logs/okta#panther-built-detections).

다음 쿼리들은 별도 표시가 없는 한 Snowflake SQL 문법으로 작성되었습니다.

#### 지난 7일간 사용자별 상위 로그인

```sql
-- 지난 7일간 사용자별 상위 로그인
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
```

#### 지난 1일간 시간대별 로그인

```sql
-- 지난 1일간 시간대별 로그인
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
```

#### 지난 7일간 상위 애플리케이션

```sql
-- 지난 7일간 상위 애플리케이션
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
```

#### 지난 7일간 실패가 많은 사용자

```sql
-- 지난 7일간 실패가 많은 사용자
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
```

#### 지난 7일간 사유별 로그인 실패

```sql
-- 사유별 로그인 실패
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
```

#### 지난 7일간 가짜 계정 로그인 시도

```sql
-- 가짜 계정 로그인 시도
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
```

**Okta 조직에 대한 Okta 지원 액세스 식별**

```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 
  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
```

**Okta에서 관리자 권한이 부여된 모든 사용자 식별**

```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 
  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
```