Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
71 lines (68 sloc) 1.83 KB
[Module Metadata]
AUTHOR=Sarah Edwards/mac4n6.com/@iamevltwin
MODULE_NOTES=Records telephony registration details such as carrier and service.
[Database Metadata]
DATABASE=CurrentPowerlog.PLSQL
PLATFORMS=IOS
VERSIONS=9,10,11,12
[Query Metadata]
QUERY_NAME=powerlog_device_telephony_registration
ACTIVITY=Device Status
KEY_TIMESTAMP=ADJUSTED_TIMESTAMP
[SQL Query 9,10,11,12]
QUERY=
SELECT
DATETIME(TELEPHONYREG_TIMESTAMP + SYSTEM, 'unixepoch') AS ADJUSTED_TIMESTAMP,
DATAIND AS "SERVICE",
OPERATOR AS "OPERATOR",
STATUS AS "STATUS",
CELLID AS "CELL ID",
DATAACTIVE AS "DATA ACTIVE",
DATAATTACHED AS "DATA ATTACHED",
HOME AS "HOME",
LAC AS "LAC",
DATETIME(TELEPHONYREG_TIMESTAMP, 'unixepoch') AS ORIGINAL_TELEPHONYREG_TIMESTAMP,
DATETIME(TIME_OFFSET_TIMESTAMP, 'unixepoch') AS OFFSET_TIMESTAMP,
SYSTEM AS TIME_OFFSET,
TELEPHONYREG_ID AS "PLBBAGENT_EVENTFORWARD_TELEPHONYREGISTRATION TABLE ID"
FROM
(
SELECT
TELEPHONYREG_ID,
TELEPHONYREG_TIMESTAMP,
TIME_OFFSET_TIMESTAMP,
MAX(TIME_OFFSET_ID) AS MAX_ID,
DATAIND,
OPERATOR,
STATUS,
CELLID,
DATAACTIVE,
DATAATTACHED,
HOME,
LAC,
SYSTEM
FROM
(
SELECT
PLBBAGENT_EVENTFORWARD_TELEPHONYREGISTRATION.TIMESTAMP AS TELEPHONYREG_TIMESTAMP,
DATAIND,
OPERATOR,
STATUS,
CELLID,
DATAACTIVE,
DATAATTACHED,
HOME,
LAC,
PLBBAGENT_EVENTFORWARD_TELEPHONYREGISTRATION.ID AS "TELEPHONYREG_ID" ,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM
FROM
PLBBAGENT_EVENTFORWARD_TELEPHONYREGISTRATION
LEFT JOIN
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET
)
AS TELEPHONYREG_STATE
GROUP BY
TELEPHONYREG_ID
)