Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
53 lines (50 sloc) 1.47 KB
[Module Metadata]
AUTHOR=Sarah Edwards/mac4n6.com/@iamevltwin
MODULE_NOTES=Records when the device is locked or unlocked.
[Database Metadata]
DATABASE=CurrentPowerlog.PLSQL
PLATFORMS=IOS
VERSIONS=9,10,11,12
[Query Metadata]
QUERY_NAME=powerlog_device_lock_state
ACTIVITY=Device State
KEY_TIMESTAMP=ADJUSTED_TIMESTAMP
[SQL Query]
QUERY=
SELECT
DATETIME(LOCKSTATE_TIMESTAMP + SYSTEM, 'unixepoch') AS ADJUSTED_TIMESTAMP,
CASE LOCKED
WHEN "0" THEN "DEVICE UNLOCKED"
WHEN "1" THEN "DEVICE LOCKED"
END AS "LOCK STATUS",
DATETIME(LOCKSTATE_TIMESTAMP, 'unixepoch') AS ORIGINAL_LOCKSTATE_TIMESTAMP,
DATETIME(TIME_OFFSET_TIMESTAMP, 'unixepoch') AS OFFSET_TIMESTAMP,
SYSTEM AS TIME_OFFSET,
LOCKSTATE_ID AS "PLSPRINGBOARDAGENT_EVENTFORWARD_SBLOCK TABLE ID"
FROM
(
SELECT
LOCKSTATE_ID,
LOCKSTATE_TIMESTAMP,
TIME_OFFSET_TIMESTAMP,
MAX(TIME_OFFSET_ID) AS MAX_ID,
LOCKED,
SYSTEM
FROM
(
SELECT
PLSPRINGBOARDAGENT_EVENTFORWARD_SBLOCK.TIMESTAMP AS LOCKSTATE_TIMESTAMP,
LOCKED,
PLSPRINGBOARDAGENT_EVENTFORWARD_SBLOCK.ID AS "LOCKSTATE_ID" ,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM
FROM
PLSPRINGBOARDAGENT_EVENTFORWARD_SBLOCK
LEFT JOIN
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET
)
AS LOCKSTATE_STATE
GROUP BY
LOCKSTATE_ID
)