Permalink
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
66 lines (62 sloc) 3.22 KB
[Module Metadata]
AUTHOR=Sarah Edwards/mac4n6.com/@iamevltwin
MODULE_NOTES=Network Usage Traffic by Network.
[Database Metadata]
DATABASE=netusage.sqlite
PLATFORMS=IOS
VERSIONS=8,9,10,11
[Query Metadata]
QUERY_NAME=netusage_zliverouteperf
ACTIVITY=Network Usage
KEY_TIMESTAMP=LIVEROUTEPREF TIMESTAMP
[SQL Query 11]
QUERY=
SELECT
DATETIME(ZLIVEROUTEPERF.ZTIMESTAMP + 978307200, 'unixepoch') AS "LIVEROUTEPREF TIMESTAMP",
DATETIME(ZNETWORKATTACHMENT.ZFIRSTTIMESTAMP + 978307200, 'unixepoch') AS "FIRST NETWORK ATTACHMENT",
DATETIME(ZNETWORKATTACHMENT.ZTIMESTAMP + 978307200, 'unixepoch') AS "NETWORK ATTACHMENT TIMESTAMP",
CASE ZLIVEROUTEPERF.ZKIND
WHEN 1 THEN "WIFI"
WHEN 2 THEN "CELLULAR"
END "NETWORK TYPE",
ZLIVEROUTEPERF.ZBYTESIN AS "BYTES IN",
ZLIVEROUTEPERF.ZBYTESOUT AS "BYTES OUT",
ZLIVEROUTEPERF.ZPACKETSIN AS "PACKETS IN",
ZLIVEROUTEPERF.ZPACKETSOUT AS "PACKETS OUT",
ZLIVEROUTEPERF.ZCONNATTEMPTS AS "CONNECTION ATTEMPTS",
ZLIVEROUTEPERF.ZCONNSUCCESSES AS "CONNECTION SUCCESSES",
ZNETWORKATTACHMENT.ZIDENTIFIER AS "NETWORK IDENTIFIER",
ZNETWORKATTACHMENT.ZISKNOWNGOOD AS "IS KNOWN GOOD",
HEX(ZNETWORKATTACHMENT.ZNETSIGNATURE) AS "NET SIGNATURE (HEX)",
ZNETWORKATTACHMENT.ZOVERALLSTAYMEAN AS "OVERALL STAY MEAN (NETWORK ATTACHMENT)",
ZNETWORKATTACHMENT.ZOVERALLSTAYVAR AS "OVERALL STAY VAR (NETWORK ATTACHMENT)",
ZLIVEROUTEPERF.ZOVERALLSTAY AS "OVERALL STAY MEAN (LIVE ROUTE PERF)",
ZLIVEROUTEPERF.ZOVERALLSTAYM2 AS "OVERALL STAY M2 (LIVE ROUTE PERF)",
ZLIVEROUTEPERF.Z_PK AS "ZLIVEROUTEPERF TABLE ID"
FROM ZLIVEROUTEPERF
LEFT JOIN ZNETWORKATTACHMENT ON ZLIVEROUTEPERF.ZHASNETWORKATTACHMENT = ZNETWORKATTACHMENT.Z_PK
[SQL Query 8,9,10]
QUERY=
SELECT
DATETIME(ZLIVEROUTEPERF.ZTIMESTAMP + 978307200, 'unixepoch') AS "LIVEROUTEPREF TIMESTAMP",
DATETIME(ZNETWORKATTACHMENT.ZFIRSTTIMESTAMP + 978307200, 'unixepoch') AS "FIRST NETWORK ATTACHMENT",
DATETIME(ZNETWORKATTACHMENT.ZTIMESTAMP + 978307200, 'unixepoch') AS "NETWORK ATTACHMENT TIMESTAMP",
CASE ZLIVEROUTEPERF.ZKIND
WHEN 1 THEN "WIFI"
WHEN 2 THEN "CELLULAR"
END "NETWORK TYPE",
ZLIVEROUTEPERF.ZBYTESIN AS "BYTES IN",
ZLIVEROUTEPERF.ZBYTESOUT AS "BYTES OUT",
ZLIVEROUTEPERF.ZPACKETSIN AS "PACKETS IN",
ZLIVEROUTEPERF.ZPACKETSOUT AS "PACKETS OUT",
ZLIVEROUTEPERF.ZCONNATTEMPTS AS "CONNECTION ATTEMPTS",
ZLIVEROUTEPERF.ZCONNSUCCESSES AS "CONNECTION SUCCESSES",
ZNETWORKATTACHMENT.ZIDENTIFIER AS "NETWORK IDENTIFIER",
ZNETWORKATTACHMENT.ZISKNOWNGOOD AS "IS KNOWN GOOD",
ZNETWORKATTACHMENT.ZOVERALLSTAYMEAN AS "OVERALL STAY MEAN (NETWORK ATTACHMENT)",
ZNETWORKATTACHMENT.ZOVERALLSTAYVAR AS "OVERALL STAY VAR (NETWORK ATTACHMENT)",
ZLIVEROUTEPERF.ZOVERALLSTAY AS "OVERALL STAY MEAN (LIVE ROUTE PERF)",
ZLIVEROUTEPERF.ZOVERALLSTAYM2 AS "OVERALL STAY M2 (LIVE ROUTE PERF)",
ZLIVEROUTEPERF.Z_PK AS "ZLIVEROUTEPERF TABLE ID"
FROM ZLIVEROUTEPERF
LEFT JOIN ZNETWORKATTACHMENT ON ZLIVEROUTEPERF.ZHASNETWORKATTACHMENT = ZNETWORKATTACHMENT.Z_PK