SIM_STATS

SIM usage statistics including data transfer bytes and packet counts.

Use SIM_STATS to analyze SIM data usage over the request time range. Query examples should name the columns they need instead of relying on column position.

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 TIMESTAMP.

Data Freshness

Updated multiple times per day.

Columns

Column	Type	Description
TIMESTAMP	TIMESTAMP_NTZ	When the stats were recorded.
OPERATOR_ID	TEXT	Operator identifier associated with the row.
SIM_ID	TEXT	SIM identifier.
PRIMARY_IMSI	TEXT	Primary IMSI for multi-IMSI SIMs.
IMSI	TEXT	IMSI used for the session.
UE_IP_ADDRESS	TEXT	User equipment IP address.
VPLMN	TEXT	Visited PLMN.
COUNTRY_CODE	TEXT	Country code associated with the visited network.
DOWNLINK_BYTES	NUMBER	Total bytes downloaded.
DOWNLINK_DROPPED	NUMBER	Dropped downlink bytes.
DOWNLINK_PKTS	NUMBER	Total packets downloaded.
UPLINK_BYTES	NUMBER	Total bytes uploaded.
UPLINK_DROPPED	NUMBER	Dropped uplink bytes.
UPLINK_PKTS	NUMBER	Total packets uploaded.
VPG	BOOLEAN	Whether the SIM used VPG connectivity.
MISC	VARIANT	Additional usage attributes.

JSON / VARIANT Fields

MISC contains additional attributes that can vary by SIM and record. The object commonly includes timestamps, monthly counters, correction offsets, and optional service usage counters.

For non-VPG rows, common fields include:

Field	Type	Description
created_time	string	When the related usage record was created, commonly as epoch milliseconds.
last_active_time	string	Last observed activity time, commonly as epoch milliseconds.
timestamp	number	Timestamp value in milliseconds.
downlink_bytesYYYYMM	number	Monthly downlink byte counter for the indicated month, such as downlink_bytes202405.
uplink_bytesYYYYMM	number	Monthly uplink byte counter for the indicated month, such as uplink_bytes202405.
downlink_pktsYYYYMM	number	Monthly downlink packet counter for the indicated month, such as downlink_pkts202405.
uplink_pktsYYYYMM	number	Monthly uplink packet counter for the indicated month, such as uplink_pkts202405.
downlink_bytes_correction_diff	number	Correction offset for downlink bytes.
downlink_pkts_correction_diff	number	Correction offset for downlink packets.
uplink_bytes_correction_diff	number	Correction offset for uplink bytes.
uplink_pkts_correction_diff	number	Correction offset for uplink packets.
imei	string	Device IMEI when available.
idle_timeout_threshold	string	Session timeout setting when available.
session_lifetime_limit	string	Session lifetime limit when available.
funnel_in_http	number	Optional Funnel inbound HTTP counter.
funnel_out_*	number	Optional Funnel output counters.
harvest_in_http	number	Optional Harvest inbound HTTP counter.
harvest_out_plain	number	Optional Harvest outbound plain counter.

For VPG rows, common fields include:

Field	Type	Description
created_time	string	When the related usage record was created, commonly as epoch milliseconds.
last_active_time	string	Last observed activity time, commonly as epoch milliseconds.
timestamp	number	Timestamp value in milliseconds.
imei	string	Device IMEI when available.
version	string	Optional VPG version identifier, such as 202402.

Example:

SELECT
  SIM_ID,
  MISC:downlink_bytes202501::NUMBER AS jan_2025_downlink
FROM SIM_STATS;

Service usage counters are optional and appear only when the corresponding service usage exists in the record. Query optional fields with explicit casts and null checks.

Common Queries

Total usage per SIM:

SELECT
  SIM_ID,
  IMSI,
  SUM(DOWNLINK_BYTES) AS total_downlink,
  SUM(UPLINK_BYTES) AS total_uplink
FROM SIM_STATS
GROUP BY SIM_ID, IMSI
ORDER BY total_downlink DESC;

Usage by visited network:

SELECT
  VPLMN,
  SUM(DOWNLINK_BYTES + UPLINK_BYTES) AS total_bytes
FROM SIM_STATS
GROUP BY VPLMN
ORDER BY total_bytes DESC;

When a customer refers to a plan in SIM usage questions, use SUBSCRIPTION from SIM_SNAPSHOTS. Use SPEED_CLASS only when the customer specifically says speed plan or speed class.

SIM counts by country and plan for SIMs with recent traffic. Require SUBSCRIPTION IS NOT NULL to exclude subtotal or grouping-set rows:

WITH recent AS (
  SELECT
    st.SIM_ID,
    st.VPLMN,
    ANY_VALUE(ss.SUBSCRIPTION) AS subscription,
    SUM(st.DOWNLINK_BYTES + st.UPLINK_BYTES) AS total_bytes
  FROM SIM_STATS st
  LEFT JOIN SIM_SNAPSHOTS ss
    ON st.SIM_ID = ss.SIM_ID
  WHERE st.TIMESTAMP >= DATEADD(hour, -24, CURRENT_TIMESTAMP()::TIMESTAMP_NTZ)
  GROUP BY st.SIM_ID, st.VPLMN
),
active AS (
  SELECT
    SIM_ID,
    VPLMN,
    subscription,
    total_bytes
  FROM recent
  WHERE total_bytes > 0
),
with_country AS (
  SELECT
    a.SIM_ID,
    a.subscription,
    n.COUNTRYNAME AS country
  FROM active a
  LEFT JOIN NETWORKS n
    ON a.VPLMN = n.MCC || n.MNC
  WHERE a.subscription IS NOT NULL
)
SELECT
  country,
  subscription,
  COUNT(DISTINCT SIM_ID) AS sim_count
FROM with_country
WHERE country IS NOT NULL
GROUP BY country, subscription
ORDER BY country, subscription;