Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
71 lines (68 sloc) 2.02 KB
[Module Metadata]
AUTHOR=Sarah Edwards/mac4n6.com/@iamevltwin
MODULE_NOTES=Keeps track of Bluetooth state on device, discoverability, connection, etc.
[Database Metadata]
DATABASE=CurrentPowerlog.PLSQL
PLATFORMS=IOS
VERSIONS=9,10,11,12
[Query Metadata]
QUERY_NAME=powerlog_bluetooth_device_state
ACTIVITY=Device Status
KEY_TIMESTAMP=ADJUSTED_TIMESTAMP
[SQL Query 9,10,11,12]
QUERY=
SELECT
DATETIME(BLUETOOTHSTATE_TIMESTAMP + SYSTEM, 'unixepoch') AS ADJUSTED_TIMESTAMP,
CASE DEVICECONNECTABLE
WHEN "0" THEN "NO"
WHEN "1" THEN "YES"
END AS "DEVICE CONNECTABLE",
CASE DEVICECONNECTED
WHEN "0" THEN "NO"
WHEN "1" THEN "YES"
END AS "DEVICE CONNECTED",
CASE DEVICEDISCOVERABLE
WHEN "0" THEN "NO"
WHEN "1" THEN "YES"
END AS "DEVICE DISCOVERABLE",
CASE DEVICEPOWERED
WHEN "0" THEN "NO"
WHEN "1" THEN "YES"
END AS "DEVICE POWERED",
DATETIME(BLUETOOTHSTATE_TIMESTAMP, 'unixepoch') AS ORIGINAL_BLUETOOTHSTATE_TIMESTAMP,
DATETIME(TIME_OFFSET_TIMESTAMP, 'unixepoch') AS OFFSET_TIMESTAMP,
SYSTEM AS TIME_OFFSET,
BLUETOOTHSTATE_ID AS "PLBLUETOOTHAGENT_EVENTFORWARD_DEVICESTATE TABLE ID"
FROM
(
SELECT
BLUETOOTHSTATE_ID,
BLUETOOTHSTATE_TIMESTAMP,
TIME_OFFSET_TIMESTAMP,
MAX(TIME_OFFSET_ID) AS MAX_ID,
DEVICECONNECTABLE,
DEVICECONNECTED,
DEVICEDISCOVERABLE,
DEVICEPOWERED,
SYSTEM
FROM
(
SELECT
PLBLUETOOTHAGENT_EVENTFORWARD_DEVICESTATE.TIMESTAMP AS BLUETOOTHSTATE_TIMESTAMP,
DEVICECONNECTABLE,
DEVICECONNECTED,
DEVICEDISCOVERABLE,
DEVICEPOWERED,
PLBLUETOOTHAGENT_EVENTFORWARD_DEVICESTATE.ID AS "BLUETOOTHSTATE_ID" ,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM
FROM
PLBLUETOOTHAGENT_EVENTFORWARD_DEVICESTATE
LEFT JOIN
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET
)
AS BLUETOOTHSTATE_STATE
GROUP BY
BLUETOOTHSTATE_ID
)