Где можно получить все события аутентификации в альфе за запрашиваемый период?
Вот таким запросом можно собрать информацию о сеансах работы пользователей:
with
events_1 as (
select “Type” = ‘Вход’ as is_signon, “Login” as login, “DateTime” as ts
from runtime.security_sessionopenclosefact
),
events_2 as (
select false as is_signon, “Login” as login, “ExpiresAt” as ts
from runtime.auth_token
where “ExpiresAt” < timestamp ‘9999-12-31 00:00:00’
),
events as (
select * from events_1
union all
select * from events_2
order by ts desc
),
event_pairs as (
select
ts as ts,
lead(ts) over (partition by login order by ts) as ts_next,
login,
is_signon
from events
),
sessions as (
select ts as ts_start, ts_next as ts_end, login
from event_pairs
where is_signon
)
select * from sessions order by ts_start desc