Soracom

SIM_SNAPSHOTS

Latest SIM attributes and status for the request context.

SIM_SNAPSHOTS returns the latest available daily snapshot for each SIM in the request context. SNAPSHOT_DATE is the snapshot date represented by the rows returned.

When counting or listing SIMs, use SIM_ID as the SIM identifier and use COUNT(DISTINCT SIM_ID) for counts. A SIM can be associated with multiple IMSI values over time.

Public Contract

  • Rows are limited to the current SORACOM Query request context.

SIM_SNAPSHOTS returns the latest daily snapshot of each SIM status as of SNAPSHOT_DATE. The view returns only the latest snapshot, so SNAPSHOT_DATE is expected to be the same for all rows in a single query result.

For historical trends or state changes over time, use SIM_SESSION_EVENTS instead.

A SIM_ID identifies the customer-facing SIM container. A SIM can be associated with multiple IMSI values over time, so customer questions such as "how many SIMs?" or "which SIMs?" should use SIM_ID as the authoritative SIM identifier. Use COUNT(DISTINCT SIM_ID) for SIM counts and key SIM lists by SIM_ID.

When a customer refers to a "plan" in SIM-related questions, they usually mean SUBSCRIPTION. Interpret "speed plan" as SPEED_CLASS only when the customer specifically asks for speed plan or speed class.

Data Freshness

Updated daily.

Because SIM_SNAPSHOTS refreshes daily, results may be up to 24 hours old.

Columns

Column Type Description
SNAPSHOT_DATE DATE Date of the SIM snapshot.
APN TEXT Access Point Name configured for the SIM.
BUNDLES ARRAY Bundle information for the SIM.
EXPIRY_ACTION TEXT Action configured for SIM expiry.
GROUP_ID TEXT Group identifier associated with the SIM.
ICCID TEXT Physical SIM card identifier.
IMEI_LOCK VARIANT IMEI lock configuration.
IMSI TEXT IMSI assigned to the SIM.
IP_ADDRESS TEXT IP address assigned to the current session when available.
MODULE_TYPE TEXT SIM module type.
MSISDN TEXT Phone number assigned to the SIM when available.
OPERATOR_ID TEXT Operator identifier associated with the row.
PACKET_CAPTURE_SESSIONS TEXT Packet capture session information.
PLAN NUMBER Plan identifier.
PREVIOUS_SESSION VARIANT Previous session attributes.
SERIAL_NUMBER TEXT SIM serial number.
SESSION_STATUS VARIANT Current session attributes.
SIM_ID TEXT SIM identifier.
SPEED_CLASS TEXT Connection speed class.
STATUS TEXT Current SIM status.
SUBSCRIPTION TEXT Subscription plan code.
TAGS VARIANT Key-value tags associated with the SIM.
TERMINATION_ENABLED NUMBER Whether termination is enabled.
TYPE TEXT SIM type.
VERSION NUMBER Record version.
CREATED_AT TIMESTAMP_NTZ When the SIM was created.
EXPIRED_AT TIMESTAMP_NTZ When the SIM expires or expired.
LAST_MODIFIED_AT TIMESTAMP_NTZ When SIM attributes were last modified.
LAST_PORT_MAPPING_CREATED_TIME TIMESTAMP_NTZ When the latest port mapping was created.
REGISTERED_TIME TIMESTAMP_NTZ When the SIM was registered.
RENEWAL_FEE_STATUS_SET_TIME TIMESTAMP_NTZ When renewal fee status was set.

JSON / VARIANT Fields

SESSION_STATUS contains the current session object when available. Common fields include:

Field Type Description
cell object Cell information for the current session when available.
dnsServers array DNS servers assigned to the session.
gtpcIpAddress string GTP-C IP address.
gtpcTeid number GTP-C Tunnel Endpoint Identifier.
imei string Device IMEI.
lastUpdatedAt string Last update time for the current session object.
online number Online flag, commonly 1 for online and 0 for offline.
operatorId string Operator identifier associated with the session object.
placement string Placement value for the session.
sessionId string Session identifier.
ueIpAddress string User equipment IP address.

PREVIOUS_SESSION contains the previous session object when available. Common fields include:

Field Type Description
cell object Cell information for the previous session when available.
createdTime string Previous session creation time.
deletedTime string Previous session deletion time.
dnsServers array DNS servers assigned to the previous session.
gtpcIpAddress string GTP-C IP address.
gtpcTeid number GTP-C Tunnel Endpoint Identifier.
imei string Device IMEI.
sessionId string Session identifier.
subscription string Subscription plan code for the previous session.
ueIpAddress string User equipment IP address.

The nested cell object commonly includes:

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

IMEI_LOCK contains the device IMEI lock configuration when set:

{
  "imei": "866667030129919"
}

The imei value may be a standard IMEI such as 866667030129919, or an IMEI with an anonymous flag such as 359418742599708|ANONYMOUS. The |ANONYMOUS suffix allows a session to be established even when the IMEI is temporarily not reported.

TAGS contains SIM tag key-value pairs for SIM or subscriber resources. The structure is a user-defined JSON object; common keys include name, owner, environment, and location.

{
  "name": "Production-Device-001"
}

For human-readable names or tag metadata for any supported resource type, use the TAGS table.

Common Queries

Count SIMs by current status. Use SIM_ID as the SIM identifier and use COUNT(DISTINCT SIM_ID) for SIM counts:

SELECT
  STATUS,
  COUNT(DISTINCT SIM_ID) AS sim_count
FROM SIM_SNAPSHOTS
GROUP BY STATUS
ORDER BY sim_count DESC;

When listing SIMs, key the result by SIM_ID. A SIM can have more than one IMSI over time:

SELECT
  SIM_ID,
  ANY_VALUE(ICCID) AS iccid,
  ANY_VALUE(STATUS) AS status,
  ANY_VALUE(SUBSCRIPTION) AS subscription,
  MAX(LAST_MODIFIED_AT) AS last_modified_at
FROM SIM_SNAPSHOTS
GROUP BY SIM_ID
ORDER BY SIM_ID;

When users ask about purchased SIMs, use CREATED_AT or REGISTERED_TIME; purchasing a SIM does not mean it is activated. Use STATUS for the current operational state, such as active, ready, inactive, or suspended:

SELECT
  SIM_ID,
  ICCID,
  STATUS,
  CREATED_AT,
  REGISTERED_TIME,
  SUBSCRIPTION
FROM SIM_SNAPSHOTS
WHERE CREATED_AT >= '2024-01-01'
  AND CREATED_AT < '2024-02-01'
ORDER BY CREATED_AT;

When users ask about renewal fees or renewal charges, use RENEWAL_FEE_STATUS_SET_TIME, not EXPIRED_AT. RENEWAL_FEE_STATUS_SET_TIME is when a SIM was marked as subject to renewal fees. EXPIRED_AT is when a SIM expired or will expire based on its expiry action setting. The renewal period varies by customer and is typically 1 or 2 years; if the customer does not know the period, default to 1 year.

SELECT
  SIM_ID,
  SUBSCRIPTION,
  STATUS,
  RENEWAL_FEE_STATUS_SET_TIME,
  DATEADD(year, 1, RENEWAL_FEE_STATUS_SET_TIME) AS renewal_fee_date
FROM SIM_SNAPSHOTS
WHERE DATEADD(year, 1, RENEWAL_FEE_STATUS_SET_TIME) >= '2026-01-01'
  AND DATEADD(year, 1, RENEWAL_FEE_STATUS_SET_TIME) < '2027-01-01'
ORDER BY renewal_fee_date;

Join current session cell information to tower coordinates:

SELECT
  ss.SIM_ID,
  ss.ICCID,
  ss.SESSION_STATUS:cell:mcc::NUMBER AS mcc,
  ss.SESSION_STATUS:cell:mnc::NUMBER AS net,
  ANY_VALUE(ct.LAT) AS lat,
  ANY_VALUE(ct.LON) AS lon
FROM SIM_SNAPSHOTS ss
JOIN CELL_TOWERS ct
  ON ss.SESSION_STATUS:cell:mcc::NUMBER = ct.MCC
  AND ss.SESSION_STATUS:cell:mnc::NUMBER = ct.NET
WHERE ss.SESSION_STATUS IS NOT NULL
  AND ct.LAT IS NOT NULL
  AND ct.LON IS NOT NULL
GROUP BY
  ss.SIM_ID,
  ss.ICCID,
  ss.SESSION_STATUS:cell:mcc::NUMBER,
  ss.SESSION_STATUS:cell:mnc::NUMBER;

When a customer refers to a plan, use SUBSCRIPTION. Use SPEED_CLASS only when the customer specifically says speed plan or speed class. Marketing plan codes that map to SUBSCRIPTION include:

planV1
planX3
plan07
plan01-low_data_volume
planJPK1
planArc01
planAP1
planP1
plan06
planGLK1
planX1
plan05
plan01s-low_data_volume
planP2
planNT1
planX2
plan03
planM1
planX3-EU
plan04
plan01
plan02
planJPKM2
planFX1
plan-NA1-package
plan-US-max
plan01s
plan-US
plan-US-NA