이것은 관리자 로그인 대시보드 리포트의 SQL 버전입니다.
이 리포트는 지정된 날짜 범위 내의 관리자 로그인 활동을 개요로 제공하며, 관리자가 언제, 어떤 IP 주소에서 Discourse에 로그인했는지를 추적하는 데 도움이 됩니다.
--[params]
--date :start_date
--date :end_date
SELECT
u.id user_id,
u.username username,
t1.client_ip as location,
t1.created_at as login_at
FROM (
SELECT DISTINCT ON (t.client_ip, t.user_id) t.client_ip, t.user_id, t.created_at
FROM user_auth_token_logs t
INNER JOIN users u ON u.id = t.user_id
WHERE u.admin = TRUE
AND t.created_at >= :start_date
AND t.created_at <= :end_date
ORDER BY t.client_ip, t.user_id, t.created_at DESC
) t1
JOIN users u ON u.id = t1.user_id
ORDER BY login_at DESC
SQL 쿼리 설명
이 SQL 쿼리는 관리자의 고유한 로그인 이벤트 목록을 검색하도록 설계되었습니다. 데이터는 다음 기준에 따라 필터링되고 표시됩니다:
- 파라미터 :: 쿼리는 로그인 데이터를 필요로 하는 기간을 지정하기 위해
:start_date와:end_date라는 두 가지 파라미터를 받습니다. 두 날짜 파라미터 모두YYYY-MM-DD형식의 날짜를 받아들입니다. - 고유한 로그인:
SELECT DISTINCT ON을 사용하는 서브쿼리(t1)를 통해client_ip와user_id의 각 조합이 고유하도록 보장합니다. 이를 통해 날짜 범위 내에서 각 IP 주소별 관리자의 가장 최근 로그인 이벤트를 효과적으로 가져올 수 있습니다. - 관리자 필터: 쿼리는
admin플래그가TRUE로 설정된 사용자를 특정하여 관리자 계정에만 초점을 맞춥니다. - Users 테이블 조인: 서브쿼리는 사용자 ID에 해당하는 사용자명을 가져오기 위해
users테이블과 다시 조인됩니다. - 정렬: 결과는
login_at타임스탬프 기준으로 내림차순으로 정렬되어 가장 최근의 관리자 로그인 이벤트가 먼저 표시됩니다.
출력 열은 다음과 같습니다:
user_id: 로그인한 관리자의 고유 ID.username: 로그인한 관리자의 사용자명.location: 관리자가 로그인한 IP 주소.login_at: 관리자가 로그인한 타임스탬프.
이 리포트는 모든 관리자 로그인이 모니터링되고 기록되도록 보장하여 보안 및 감사 목적으로 가치가 있습니다.
예시 결과
| user | username | location | login_at |
|---|---|---|---|
| 1 | admin_user_1 | 123.45.67.89 | 2023-12-13T23:59:55.733Z |
| 2 | admin_user_2 | 123.45.67.89 | 2023-12-14T23:59:45.685Z |
| 3 | admin_user_3 | 123.45.67.89 | 2023-12-15T23:59:43.033Z |
| … | … | … | … |