Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
57 lines (54 sloc) 1.76 KB
[Module Metadata]
AUTHOR=Sarah Edwards/mac4n6.com/@iamevltwin
MODULE_NOTES=Determine when the flashlight/torch was turned on/off and how (com.apple.springboard in the Bundle ID is using the control center).
[Database Metadata]
DATABASE=CurrentPowerlog.PLSQL
PLATFORMS=IOS
VERSIONS=9,10,11,12
[Query Metadata]
QUERY_NAME=powerlog_torch_state
ACTIVITY=Device Status
KEY_TIMESTAMP=ADJUSTED_TIMESTAMP
[SQL Query]
QUERY=
SELECT
DATETIME(TORCH_TIMESTAMP + SYSTEM, 'unixepoch') AS ADJUSTED_TIMESTAMP,
BUNDLEID AS BUNDLE_ID,
CASE LEVEL
WHEN "0" THEN "OFF"
WHEN "1" THEN "ON"
END AS STATUS,
DATETIME(TORCH_TIMESTAMP, 'unixepoch') AS ORIGINAL_TORCH_TIMESTAMP,
DATETIME(TIME_OFFSET_TIMESTAMP, 'unixepoch') AS OFFSET_TIMESTAMP,
SYSTEM AS TIME_OFFSET,
TORCH_ID
FROM
(
SELECT
BUNDLEID,
TORCH_ID,
TORCH_TIMESTAMP,
TIME_OFFSET_TIMESTAMP,
MAX(TIME_OFFSET_ID) AS MAX_ID,
SYSTEM,
LEVEL
FROM
(
SELECT
PLCAMERAAGENT_EVENTFORWARD_TORCH.TIMESTAMP AS TORCH_TIMESTAMP,
PLCAMERAAGENT_EVENTFORWARD_TORCH.BUNDLEID,
PLCAMERAAGENT_EVENTFORWARD_TORCH.LEVEL,
PLCAMERAAGENT_EVENTFORWARD_TORCH.ID as "TORCH_ID",
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM,
BUNDLEID
FROM
PLCAMERAAGENT_EVENTFORWARD_TORCH
LEFT JOIN
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET
)
AS TORCHESTATE
GROUP BY
TORCH_ID
)