SIM_SESSION_EVENTS

SIM session lifecycle events such as creation and deletion.

Public Contract

Rows are limited to the current SORACOM Query request context.
Rows are limited to the time range selected for the SORACOM Query request.
The request time range is applied to EVENT_TIME.

Data Freshness

Updated multiple times per day.

Columns

Column	Type	Description
APN	TEXT	Access Point Name used by the session.
CREATED_TIME	TIMESTAMP_NTZ	Record creation time.
EVENT_TIME	TIMESTAMP_NTZ	When the event occurred.
DNS0	TEXT	Primary DNS server.
DNS1	TEXT	Secondary DNS server.
EVENT	TEXT	Event type.
HPLMN	TEXT	Home PLMN.
ICCID	TEXT	SIM card identifier.
IMEI	TEXT	Device IMEI.
IMSI	TEXT	IMSI used by the session.
LAST_MODIFIED_TIME	TIMESTAMP_NTZ	Last modification time.
CELL	OBJECT	Cell tower attributes reported with the event.
MSISDN	TEXT	Phone number associated with the SIM when available.
OPERATOR_ID	TEXT	Operator identifier associated with the row.
PLACEMENT	TEXT	Placement information.
PRIMARY_IMSI	TEXT	Primary IMSI for multi-IMSI SIMs.
SESSION_ID	TEXT	Session identifier.
SIM_ID	TEXT	SIM identifier.
SPEED_CLASS	TEXT	Connection speed class.
SUBSCRIPTION	TEXT	Subscription plan code.
TIME	NUMBER	Event time as a Unix timestamp in milliseconds.
UE_IP_ADDRESS	TEXT	User equipment IP address.
VPLMN	TEXT	Visited PLMN.

JSON / VARIANT Fields

CELL contains cell information reported with a session event. Common fields include:

Field	Type	Description
`mcc`	number	Mobile Country Code; join with `NETWORKS` and `CELL_TOWERS`.
`mnc`	number	Mobile Network Code; join with `NETWORKS` and `CELL_TOWERS`.
`eci`	number	E-UTRAN Cell Identifier when available.
`lac`	number	Location Area Code when available.
`rac`	number	Routing Area Code when available.
`radioType`	string	Radio technology.
`tac`	number	Tracking Area Code when available.

Use CELL:mcc with NETWORKS.MCC to retrieve country information. Use CELL:mcc and CELL:mnc together when network operator or cell tower context is required.

Common Queries

Recent session event counts by SIM:

SELECT
  SIM_ID,
  COUNT(*) AS events,
  COUNT_IF(EVENT = 'Created') AS created_events,
  COUNT_IF(EVENT = 'Modified') AS modified_events,
  COUNT_IF(EVENT = 'Deleted') AS deleted_events
FROM SIM_SESSION_EVENTS
GROUP BY SIM_ID
ORDER BY events DESC
LIMIT 100;

When users ask for SIM information by country, join NETWORKS to retrieve MCC and MNC data by country name. Use NETWORKS.ORGNAME when users ask for network operator names. A SIM can have multiple sessions in a requested period, so use DISTINCT SIM_ID for SIM activity counts.

SIMs with activity in a country during the request time range:

SELECT DISTINCT
  se.SIM_ID,
  se.CELL:mcc::STRING AS mcc,
  se.CELL:mnc::STRING AS mnc
FROM SIM_SESSION_EVENTS se
WHERE se.CELL:mcc::STRING IN (
  SELECT MCC
  FROM NETWORKS
  WHERE COUNTRYNAME = 'Japan'
);

Latest country observed for each SIM from recent events. The mcc_country CTE deduplicates country rows per MCC to avoid join fan-out:

WITH recent_events AS (
  SELECT
    SIM_ID,
    IMSI,
    EVENT,
    EVENT_TIME,
    CELL:mcc::STRING AS mcc,
    ROW_NUMBER() OVER (
      PARTITION BY SIM_ID
      ORDER BY EVENT_TIME DESC
    ) AS rn
  FROM SIM_SESSION_EVENTS
  WHERE EVENT_TIME >= DATEADD(hour, -168, CURRENT_TIMESTAMP())
),
latest AS (
  SELECT
    SIM_ID,
    IMSI,
    EVENT AS most_recent_event,
    EVENT_TIME AS most_recent_event_time,
    mcc
  FROM recent_events
  WHERE rn = 1
),
mcc_country AS (
  SELECT
    MCC,
    ANY_VALUE(COUNTRYNAME) AS country
  FROM NETWORKS
  GROUP BY MCC
)
SELECT
  l.SIM_ID,
  l.IMSI,
  t.VALUE AS display_name,
  l.most_recent_event,
  l.most_recent_event_time,
  mc.country
FROM latest l
LEFT JOIN mcc_country mc
  ON mc.MCC = l.mcc
LEFT JOIN TAGS t
  ON l.IMSI = t.RESOURCE_ID
  AND t.RESOURCE_TYPE = 'subscriber'
  AND t.NAME = 'name'
ORDER BY l.most_recent_event_time DESC;

A SIM may be online without a recent session event. For "currently online or recently active in a country" questions, combine recent SIM_SESSION_EVENTS rows with currently online SIM_SNAPSHOTS rows:

WITH mccs AS (
  SELECT MCC
  FROM NETWORKS
  WHERE COUNTRYNAME = 'Japan'
),
combined_online_sims AS (
  SELECT SIM_ID
  FROM SIM_SESSION_EVENTS se
  WHERE se.EVENT_TIME >= DATEADD(day, -7, CURRENT_DATE())
    AND se.CELL:mcc::STRING IN (SELECT MCC FROM mccs)

  UNION

  SELECT SIM_ID
  FROM SIM_SNAPSHOTS ss
  WHERE ss.SESSION_STATUS:online = 1
    AND ss.SESSION_STATUS:cell:mcc::STRING IN (SELECT MCC FROM mccs)
)
SELECT COUNT(DISTINCT SIM_ID) AS sim_count
FROM combined_online_sims;

Break down recent or currently online SIMs by PLMN:

WITH mccs AS (
  SELECT MCC
  FROM NETWORKS
  WHERE COUNTRYNAME = 'Japan'
),
combined_online_sims AS (
  SELECT
    SIM_ID,
    CELL:mcc::STRING AS mcc,
    CELL:mnc::STRING AS mnc
  FROM SIM_SESSION_EVENTS se
  WHERE se.EVENT_TIME >= DATEADD(day, -7, CURRENT_DATE())
    AND CELL:mcc::STRING IN (SELECT MCC FROM mccs)

  UNION

  SELECT
    SIM_ID,
    SESSION_STATUS:cell:mcc::STRING AS mcc,
    SESSION_STATUS:cell:mnc::STRING AS mnc
  FROM SIM_SNAPSHOTS ss
  WHERE ss.SESSION_STATUS:online = 1
    AND ss.SESSION_STATUS:cell:mcc::STRING IN (SELECT MCC FROM mccs)
)
SELECT
  COUNT(DISTINCT SIM_ID) AS sim_count,
  CONCAT(mcc, mnc) AS plmn
FROM combined_online_sims
GROUP BY plmn
ORDER BY sim_count DESC;

Filter by network operator name using NETWORKS.ORGNAME:

WITH mccs AS (
  SELECT MCC
  FROM NETWORKS
  WHERE COUNTRYNAME = 'Japan'
    AND LOWER(ORGNAME) LIKE '%kddi%'
),
combined_online_sims AS (
  SELECT
    SIM_ID,
    CELL:mcc::STRING AS mcc,
    CELL:mnc::STRING AS mnc
  FROM SIM_SESSION_EVENTS se
  WHERE se.EVENT_TIME >= DATEADD(day, -7, CURRENT_DATE())
    AND CELL:mcc::STRING IN (SELECT MCC FROM mccs)

  UNION

  SELECT
    SIM_ID,
    SESSION_STATUS:cell:mcc::STRING AS mcc,
    SESSION_STATUS:cell:mnc::STRING AS mnc
  FROM SIM_SNAPSHOTS ss
  WHERE ss.SESSION_STATUS:online = 1
    AND ss.SESSION_STATUS:cell:mcc::STRING IN (SELECT MCC FROM mccs)
)
SELECT
  COUNT(DISTINCT SIM_ID) AS sim_count,
  CONCAT(mcc, mnc) AS plmn
FROM combined_online_sims
GROUP BY plmn
ORDER BY sim_count DESC;