Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
58 lines (55 sloc) 1.88 KB
[Module Metadata]
AUTHOR=Sarah Edwards/mac4n6.com/@iamevltwin
MODULE_NOTES=Accessory Connections - Accessories may be bluetooth devices like headphones or connections to devices like CarPlay enabled vehicles.
[Database Metadata]
DATABASE=CurrentPowerlog.PLSQL
PLATFORMS=IOS
VERSIONS=9,10,11,12
[Query Metadata]
QUERY_NAME=powerlog_accessory_connection
ACTIVITY=Network Usage
KEY_TIMESTAMP=ADJUSTED_TIMESTAMP
[SQL Query 9,10,11,12]
QUERY=
SELECT
DATETIME(ACCESSORYCONNECTION_TIMESTAMP + SYSTEM, 'unixepoch') AS ADJUSTED_TIMESTAMP,
ACCESSORYUSAGETIMEINSECONDS AS "ACCESSORY USAGE TIME IN SECONDS",
DATETIME(IAPAPPACCESSORYCONNECTIONTIMEINSECKEY + SYSTEM, 'unixepoch') AS "CONNECTION TIME (ADJ)",
CASE ISCONNECTED
WHEN '0' THEN 'DISCONNECTED'
WHEN '1' THEN 'CONNECTED'
END "CONNECTION STATUS",
DATETIME(TIME_OFFSET_TIMESTAMP, 'unixepoch') AS OFFSET_TIMESTAMP,
SYSTEM AS TIME_OFFSET,
ACCESSORYCONNECTION_ID AS "PLXPCAGENT_EVENTFORWARD_ACCESSORY TABLE ID"
FROM
(
SELECT
ACCESSORYCONNECTION_ID,
ACCESSORYCONNECTION_TIMESTAMP,
TIME_OFFSET_TIMESTAMP,
MAX(TIME_OFFSET_ID) AS MAX_ID,
ACCESSORYUSAGETIMEINSECONDS,
IAPAPPACCESSORYCONNECTIONTIMEINSECKEY,
ISCONNECTED,
SYSTEM
FROM
(
SELECT
PLXPCAGENT_EVENTFORWARD_ACCESSORY.TIMESTAMP AS ACCESSORYCONNECTION_TIMESTAMP,
ACCESSORYUSAGETIMEINSECONDS,
IAPAPPACCESSORYCONNECTIONTIMEINSECKEY,
ISCONNECTED,
PLXPCAGENT_EVENTFORWARD_ACCESSORY.ID AS "ACCESSORYCONNECTION_ID" ,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM
FROM
PLXPCAGENT_EVENTFORWARD_ACCESSORY
LEFT JOIN
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET
)
AS ACCESSORYCONNECTION_STATE
GROUP BY
ACCESSORYCONNECTION_ID
)