Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
106 lines (102 sloc) 2.88 KB
[Module Metadata]
AUTHOR=Sarah Edwards/mac4n6.com/@iamevltwin
MODULE_NOTES=Provides calling service details and status.
[Database Metadata]
DATABASE=CurrentPowerlog.PLSQL
PLATFORMS=IOS
VERSIONS=9,10,11,12
[Query Metadata]
QUERY_NAME=powerlog_incallserivice
ACTIVITY=Network Usage
KEY_TIMESTAMP=ADJUSTED_TIMESTAMP
[SQL Query 10,11,12]
QUERY=
SELECT
DATETIME(INCALLSERVICE_TIMESTAMP + SYSTEM, 'unixepoch') AS ADJUSTED_TIMESTAMP,
BUNDLEID AS "BUNDLE ID",
KCALLSUBTYPE AS "KCALL SUB TYPE",
PROVIDERIDENTIFIER AS "PROVIDER IDENTIFIER",
STATUS,
VIDEO,
DATETIME(INCALLSERVICE_TIMESTAMP, 'unixepoch') AS ORIGINAL_INCALLSERVICE_TIMESTAMP,
DATETIME(TIME_OFFSET_TIMESTAMP, 'unixepoch') AS OFFSET_TIMESTAMP,
SYSTEM AS TIME_OFFSET,
INCALLSERVICE_ID AS "PLXPCAGENT_EVENTFORWARD_INCALLSERVICE TABLE ID"
FROM
(
SELECT
INCALLSERVICE_ID,
INCALLSERVICE_TIMESTAMP,
TIME_OFFSET_TIMESTAMP,
MAX(TIME_OFFSET_ID) AS MAX_ID,
BUNDLEID,
KCALLSUBTYPE,
PROVIDERIDENTIFIER,
STATUS,
VIDEO,
SYSTEM
FROM
(
SELECT
PLXPCAGENT_EVENTFORWARD_INCALLSERVICE.TIMESTAMP AS INCALLSERVICE_TIMESTAMP,
BUNDLEID,
KCALLSUBTYPE,
PROVIDERIDENTIFIER,
STATUS,
VIDEO,
PLXPCAGENT_EVENTFORWARD_INCALLSERVICE.ID AS "INCALLSERVICE_ID" ,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM
FROM
PLXPCAGENT_EVENTFORWARD_INCALLSERVICE
LEFT JOIN
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET
WHERE
INCALLSERVICE_TIMESTAMP > TIME_OFFSET_TIMESTAMP
)
AS INCALLSERVICE_STATE
GROUP BY
INCALLSERVICE_ID
)
[SQL Query 9]
QUERY=
SELECT
DATETIME(INCALLSERVICE_TIMESTAMP + SYSTEM, 'unixepoch') AS ADJUSTED_TIMESTAMP,
BUNDLEID AS "BUNDLE ID",
STATUS,
DATETIME(INCALLSERVICE_TIMESTAMP, 'unixepoch') AS ORIGINAL_INCALLSERVICE_TIMESTAMP,
DATETIME(TIME_OFFSET_TIMESTAMP, 'unixepoch') AS OFFSET_TIMESTAMP,
SYSTEM AS TIME_OFFSET,
INCALLSERVICE_ID AS "PLXPCAGENT_EVENTFORWARD_INCALLSERVICE TABLE ID"
FROM
(
SELECT
INCALLSERVICE_ID,
INCALLSERVICE_TIMESTAMP,
TIME_OFFSET_TIMESTAMP,
MAX(TIME_OFFSET_ID) AS MAX_ID,
BUNDLEID,
STATUS,
SYSTEM
FROM
(
SELECT
PLXPCAGENT_EVENTFORWARD_INCALLSERVICE.TIMESTAMP AS INCALLSERVICE_TIMESTAMP,
BUNDLEID,
STATUS,
PLXPCAGENT_EVENTFORWARD_INCALLSERVICE.ID AS "INCALLSERVICE_ID" ,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.TIMESTAMP AS TIME_OFFSET_TIMESTAMP,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.ID AS TIME_OFFSET_ID,
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET.SYSTEM
FROM
PLXPCAGENT_EVENTFORWARD_INCALLSERVICE
LEFT JOIN
PLSTORAGEOPERATOR_EVENTFORWARD_TIMEOFFSET
WHERE
INCALLSERVICE_TIMESTAMP > TIME_OFFSET_TIMESTAMP
)
AS INCALLSERVICE_STATE
GROUP BY
INCALLSERVICE_ID
)