Skip to main content

ibm_verify_events_raw.application_usage

One row per IBM Verify SSO event, when someone reaches an application.

Every IBM Verify sso event: one row each time someone who is signed in reaches an integrated application. Prefer the daily summary, events_app_login_counts.

Keywords: SSO events, application usage, applications, relying parties, idle accounts, raw events

Provenance

Pulled hourly from IBM Verify by the streaming job. See IBM Verify Events for what is hashed and how to filter by day.

  • Updated: Hourly
  • Retention: 3 years
  • Steward: EDCP Data and Research Team
  • Contact: Yinlin Zhang
  • Location: s3://<raw-bucket>/ibm_verify_events_raw/application_usage/

Fields

One row per event.

Field Name Type Description
time string When the event happened, in UTC (for example, “2026-09-15 00:00:05 UTC”)
userid string Hashed user ID. Can be "UNKNOWN"
username string Hashed username
applicationid string IBM Verify’s numeric ID for the application. Stable when the application is renamed. Join to rp.alias on this
applicationname string Application name (for example, “IRIS - PRC - Prod”). It changes if the application is renamed, so do not group or join on it
result string “success” or “failure”
realm string IBM Verify directory. “cloudIdentityRealm”, or “UNKNOWN” when the account is not found
origin string Hashed client IP address
geoip__country_iso_code string Country from the IP address, as ISO 3166-1 alpha-3 (for example, “CAN”)
geoip__country_name string Country name from the IP address
geoip__region_name string Province, territory, or state from the IP address
year string Partition key. UTC year, for example “2026”
month string Partition key. UTC month, for example “09”
day string Partition key. UTC day of the month, for example “15”

Notes

Failed events rarely have a user. Almost all failures have userid = "UNKNOWN".

Flow Application is CanadaLogin’s own. Almost every new account reaches it right after sign-up, which suggests it is the sign-up flow. It is an internal CanadaLogin component in rp.service, so exclude internal CanadaLogin components when the question is about partner services.

Join to relying parties after, not before, aggregating. See Joining through the lookup.

Example. Distinct users who reached each application on an ET day:

tbl(con, in_schema("ibm_verify_events_raw", "application_usage")) |>
  # partitions are UTC days, so read the next one too for the ET evening
  filter(year == "2026", month == "09", day %in% c("15", "16")) |>
  mutate(et_day = sql("date(with_timezone(CAST(substr(time, 1, 19) AS timestamp), 'UTC') AT TIME ZONE 'America/Toronto')")) |>
  filter(
    et_day == as.Date("2026-09-15"), result == "success",
    !is.na(userid), !userid %in% c("", "UNKNOWN")
  ) |>
  group_by(applicationid) |>
  summarise(unique_users = n_distinct(userid))