Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
70 lines (67 sloc) 1.96 KB
[Module Metadata]
AUTHOR=Sarah Edwards/mac4n6.com/@iamevltwin
MODULE_NOTES=Location Usage by App/Process Client
[Database Metadata]
DATABASE=CurrentPowerlog.PLSQL
PLATFORMS=IOS
VERSIONS=9,10,11,12
[Query Metadata]
QUERY_NAME=powerlog_location_client_status
ACTIVITY=Device Status
KEY_TIMESTAMP=ADJUSTED_TIMESTAMP
[SQL Query 9,10,11,12]
QUERY=
SELECT
DATETIME(LOCATIONAGENT_TIMESTAMP + SYSTEM, 'unixepoch') AS ADJUSTED_TIMESTAMP,
DATETIME(TIMESTAMPLOGGED+ SYSTEM, 'unixepoch') AS "TIMESTAMP LOGGED (ADJ)",
DATETIME(TIMESTAMPEND + SYSTEM, 'unixepoch') AS "TIMESTAMP END (ADJ)",
BUNDLEID AS "BUNDLE ID",
TYPE AS "TYPE",
LOCATIONDESIREDACCURACY AS "LOCATION DESIRED ACCURACY",
LOCATIONDISTANCEFILTER AS "LOCATION DISTANCE FILTER",
CLIENT AS "CLIENT",
EXECUTABLE AS "EXECUTABLE",
DATETIME(TIME_OFFSET_TIMESTAMP, 'unixepoch') AS OFFSET_TIMESTAMP,
SYSTEM AS TIME_OFFSET,
LOCATIONAGENT_ID AS "PLLOCATIONAGENT_EVENTFORWARD_CLIENTSTATUS TABLE ID"
FROM
(
SELECT
LOCATIONAGENT_ID,
LOCATIONAGENT_TIMESTAMP,
TIME_OFFSET_TIMESTAMP,
MAX(TIME_OFFSET_ID) AS MAX_ID,
TIMESTAMPEND,
TIMESTAMPLOGGED,
BUNDLEID,
TYPE,
LOCATIONDESIREDACCURACY,
LOCATIONDISTANCEFILTER,
CLIENT,
EXECUTABLE,
SYSTEM
FROM
(
SELECT
PLLOCATIONAGENT_EVENTFORWARD_CLIENTSTATUS.TIMESTAMP AS LOCATIONAGENT_TIMESTAMP,
TIMESTAMPEND,
TIMESTAMPLOGGED,
BUNDLEID,
TYPE,
LOCATIONDESIREDACCURACY,
LOCATIONDISTANCEFILTER,
CLIENT,
EXECUTABLE,
PLLOCATIONAGENT_EVENTFORWARD_CLIENTSTATUS.ID AS "LOCATIONAGENT_ID" ,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM
FROM
PLLOCATIONAGENT_EVENTFORWARD_CLIENTSTATUS
LEFT JOIN
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET
)
AS LOCATIONAGENT_STATE
GROUP BY
LOCATIONAGENT_ID
)