Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
142 lines (138 sloc) 3.17 KB
[Module Metadata]
AUTHOR=Sarah Edwards/mac4n6.com/@iamevltwin
MODULE_NOTES=Technology used to determine location.
[Database Metadata]
DATABASE=CurrentPowerlog.PLSQL
PLATFORMS=IOS
VERSIONS=9,10,11,12
[Query Metadata]
QUERY_NAME=powerlog_location_tech_status
ACTIVITY=Device Status
KEY_TIMESTAMP=ADJUSTED_TIMESTAMP
[SQL Query 11,12]
QUERY=
SELECT
DATETIME(LOCATIONTECH_TIMESTAMP + SYSTEM, 'unixepoch') AS ADJUSTED_TIMESTAMP,
ACCESSORY AS "ACCESSORY",
CELL AS "CELL",
GPS AS "GPS",
GPS_COARSE AS "GPS COARSE",
LAC AS "LAC",
MCC AS "MCC",
NMEA AS "NMEA",
PIPELINE AS "PIPELINE",
SKYHOOK AS "SKYHOOK",
WIFI AS "WIFI",
WIFI2 AS "WIFI2",
DATETIME(TIME_OFFSET_TIMESTAMP, 'unixepoch') AS OFFSET_TIMESTAMP,
SYSTEM AS TIME_OFFSET,
LOCATIONTECH_ID AS "PLLOCATIONAGENT_EVENTFORWARD_TECHSTATUS TABLE ID"
FROM
(
SELECT
LOCATIONTECH_ID,
LOCATIONTECH_TIMESTAMP,
TIME_OFFSET_TIMESTAMP,
MAX(TIME_OFFSET_ID) AS MAX_ID,
ACCESSORY,
CELL,
GPS,
GPS_COARSE,
LAC,
MCC,
NMEA,
PIPELINE,
SKYHOOK,
WIFI,
WIFI2,
SYSTEM
FROM
(
SELECT
PLLOCATIONAGENT_EVENTFORWARD_TECHSTATUS.TIMESTAMP AS LOCATIONTECH_TIMESTAMP,
ACCESSORY,
CELL,
GPS,
GPS_COARSE,
LAC,
MCC,
NMEA,
PIPELINE,
SKYHOOK,
WIFI,
WIFI2,
PLLOCATIONAGENT_EVENTFORWARD_TECHSTATUS.ID AS "LOCATIONTECH_ID" ,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM
FROM
PLLOCATIONAGENT_EVENTFORWARD_TECHSTATUS
LEFT JOIN
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET
)
AS LOCATIONTECH_STATE
GROUP BY
LOCATIONTECH_ID
)
[SQL Query 9,10]
QUERY=
SELECT
DATETIME(LOCATIONTECH_TIMESTAMP + SYSTEM, 'unixepoch') AS ADJUSTED_TIMESTAMP,
ACCESSORY AS "ACCESSORY",
CELL AS "CELL",
GPS AS "GPS",
GPS_COARSE AS "GPS COARSE",
LAC AS "LAC",
MCC AS "MCC",
NMEA AS "NMEA",
PIPELINE AS "PIPELINE",
SKYHOOK AS "SKYHOOK",
WIFI AS "WIFI",
DATETIME(TIME_OFFSET_TIMESTAMP, 'unixepoch') AS OFFSET_TIMESTAMP,
SYSTEM AS TIME_OFFSET,
LOCATIONTECH_ID AS "PLLOCATIONAGENT_EVENTFORWARD_TECHSTATUS TABLE ID"
FROM
(
SELECT
LOCATIONTECH_ID,
LOCATIONTECH_TIMESTAMP,
TIME_OFFSET_TIMESTAMP,
MAX(TIME_OFFSET_ID) AS MAX_ID,
ACCESSORY,
CELL,
GPS,
GPS_COARSE,
LAC,
MCC,
NMEA,
PIPELINE,
SKYHOOK,
WIFI,
SYSTEM
FROM
(
SELECT
PLLOCATIONAGENT_EVENTFORWARD_TECHSTATUS.TIMESTAMP AS LOCATIONTECH_TIMESTAMP,
ACCESSORY,
CELL,
GPS,
GPS_COARSE,
LAC,
MCC,
NMEA,
PIPELINE,
SKYHOOK,
WIFI,
PLLOCATIONAGENT_EVENTFORWARD_TECHSTATUS.ID AS "LOCATIONTECH_ID" ,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM
FROM
PLLOCATIONAGENT_EVENTFORWARD_TECHSTATUS
LEFT JOIN
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET
)
AS LOCATIONTECH_STATE
GROUP BY
LOCATIONTECH_ID
)