Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
60 lines (57 sloc) 1.5 KB
[Module Metadata]
AUTHOR=Sarah Edwards/mac4n6.com/@iamevltwin
MODULE_NOTES=Airdrop Connection infomation
[Database Metadata]
DATABASE=CurrentPowerlog.PLSQL
PLATFORMS=IOS
VERSIONS=9,10,11,12
[Query Metadata]
QUERY_NAME=powerlog_airdrop
ACTIVITY=Network Usage
KEY_TIMESTAMP=ADJUSTED_TIMESTAMP
[SQL Query 9,10,11,12]
QUERY=
SELECT
DATETIME(AIRDROP_TIMESTAMP + SYSTEM, 'unixepoch') AS ADJUSTED_TIMESTAMP,
STATE,
SUBEVENT,
BUNDLEID AS BUNDLE_ID,
PID,
DATETIME(AIRDROP_TIMESTAMP, 'unixepoch') AS ORIGINAL_AIRDROP_TIMESTAMP,
DATETIME(TIME_OFFSET_TIMESTAMP, 'unixepoch') AS OFFSET_TIMESTAMP,
SYSTEM AS TIME_OFFSET,
AIRDROP_ID AS "PLXPCAGENT_EVENTFORWARD_AIRDROP TABLE ID"
FROM
(
SELECT
BUNDLEID,
AIRDROP_ID,
AIRDROP_TIMESTAMP,
TIME_OFFSET_TIMESTAMP,
MAX(TIME_OFFSET_ID) as max_id,
SYSTEM,
PID,
SUBEVENT,
STATE
FROM
(
SELECT
PLXPCAGENT_EVENTFORWARD_AIRDROP.TIMESTAMP AS AIRDROP_TIMESTAMP,
PLXPCAGENT_EVENTFORWARD_AIRDROP.BUNDLEID,
PLXPCAGENT_EVENTFORWARD_AIRDROP.PID,
PLXPCAGENT_EVENTFORWARD_AIRDROP.SUBEVENT,
PLXPCAGENT_EVENTFORWARD_AIRDROP.STATE,
PLXPCAGENT_EVENTFORWARD_AIRDROP.ID as "AIRDROP_ID",
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM,
BUNDLEID
FROM
PLXPCAGENT_EVENTFORWARD_AIRDROP
LEFT JOIN
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET
)
AS AIRDROPSTATE
GROUP BY
AIRDROP_ID
)