SIM_CDRS

SIM traffic records.

SIM_CDRS contains one row per SIM CDR event for the Global coverage. Use aggregate functions such as SUM() with GROUP BY for traffic totals.

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
CREATED_AT	TIMESTAMP_NTZ	When the CDR record was created.
S3_KEY	TEXT	Record key associated with the CDR.
OPERATOR_ID	TEXT	Operator identifier associated with the row.
IMSI	TEXT	IMSI used by the SIM.
IMEI	TEXT	Device IMEI.
MSISDN	TEXT	Phone number associated with the SIM when available.
PRIMARY_IMSI	TEXT	Primary IMSI for multi-IMSI SIMs.
SIM_ID	TEXT	SIM identifier.
GROUP_ID	TEXT	Group identifier associated with the SIM.
UE_IP_ADDRESS	TEXT	User equipment IP address.
GTPC_IP_ADDRESS	TEXT	GTP-C IP address.
GTPC_TEID	NUMBER	GTP-C Tunnel Endpoint Identifier.
SESSION_ID	TEXT	Session identifier.
MCC	NUMBER	Mobile Country Code.
MNC	NUMBER	Mobile Network Code.
TAC	NUMBER	Tracking Area Code.
ECI	NUMBER	E-UTRAN Cell Identifier.
CREATED_TIME	TIMESTAMP_NTZ	Record creation time.
TIMESTAMP	TIMESTAMP_NTZ	When the CDR was recorded.
LAST_ACTIVE_TIME	TIMESTAMP_NTZ	Last activity time.
APN	TEXT	Access Point Name.
RATING_GROUP	TEXT	Rating group for billing.
VPLMN	TEXT	Visited PLMN.
COUNTRY_CODE	TEXT	Country code.
FQDN	TEXT	Domain name associated with the traffic record when available.
DEST_IP_ADDRESS	TEXT	Destination IP address.
DEST_PORT	NUMBER	Destination port.
PROTOCOL	TEXT	Protocol used.
DOWNLINK_BYTES	NUMBER	Bytes downloaded in this period.
DOWNLINK_PKTS	NUMBER	Packets downloaded in this period.
UPLINK_BYTES	NUMBER	Bytes uploaded in this period.
UPLINK_PKTS	NUMBER	Packets uploaded in this period.
DUPLICATED_FQDN	TEXT	Duplicate domain name indicator.

Common Queries

The date filter for SIM_CDRS should use TIMESTAMP, which represents the CDR event time. SIM_CDRS contains one row per CDR event, so aggregate traffic queries must use SUM() and GROUP BY.

Traffic totals for selected IMSIs:

SELECT
  IMSI,
  SUM(DOWNLINK_BYTES) AS total_downlink_bytes,
  SUM(DOWNLINK_PKTS) AS total_downlink_packets,
  SUM(UPLINK_BYTES) AS total_uplink_bytes,
  SUM(UPLINK_PKTS) AS total_uplink_packets
FROM SIM_CDRS
WHERE IMSI IN ('440000000000001', '440000000000002')
  AND TIMESTAMP >= DATEADD(day, -7, CURRENT_DATE())
GROUP BY IMSI
ORDER BY IMSI;

All IMSIs with totals:

SELECT
  IMSI,
  SIM_ID,
  COUNT(*) AS cdr_count,
  SUM(DOWNLINK_BYTES) AS total_downlink_bytes,
  SUM(DOWNLINK_PKTS) AS total_downlink_packets,
  SUM(UPLINK_BYTES) AS total_uplink_bytes,
  SUM(UPLINK_PKTS) AS total_uplink_packets
FROM SIM_CDRS
WHERE TIMESTAMP >= DATEADD(day, -7, CURRENT_DATE())
GROUP BY IMSI, SIM_ID
ORDER BY total_downlink_bytes DESC;