-- Useful extracts for faster querying -- This will provide data about discoveries CREATE TABLE extract_discoveries AS SELECT * FROM log_readable WHERE tag = 'BluetoothCore' AND c0 = 'Device found' AND c1 = 'participant'; CREATE TABLE extract_headers AS SELECT device, log, line, wallclock, uptime, tag, c0, c1, c2, c3, c4 FROM log_readable WHERE tag == 'LoggingManager' OR tag == 'APP' OR tag == 'CONSENT' OR tag == 'UptimeID'; -- the following query is expensive (requires lots of temporary disk space) CREATE TABLE tmp_logranges AS SELECT device, log, min(uptime) as min_uptime, max(uptime) as max_uptime, min(wallclock) as min_wallclock, max(wallclock) as max_wallclock FROM log_readable GROUP BY device, log; CREATE TABLE tmp_loginfo1 AS SELECT device, log, uptimeid, min_uptime, max_uptime, min_wallclock, max_wallclock FROM tmp_logranges JOIN (SELECT device, log, c0 as uptimeid FROM extract_headers WHERE tag = 'UptimeID') USING (device, log); -- tmp_logranges can now be dropped because tmp_loginfo1 contains the same information and more DROP TABLE tmp_logranges; CREATE TABLE helper_uptimelist AS SELECT DISTINCT device, uptimeid FROM tmp_loginfo1; CREATE TABLE tmp_uptimeinfo1 AS SELECT device, uptimeid, min(min_uptime) as min_uptime, max(max_uptime) as max_uptime FROM tmp_loginfo1 GROUP BY device, uptimeid; CREATE TABLE extract_ntptime_tmp AS SELECT device, log, line, wallclock, uptime, tag, CAST(c0 as integer) as ntptime, CAST(c1 as integer) as timeref, CAST(c2 as integer) as roundtrip, CAST(c0 as integer)- (wallclock - (uptime-CAST(c1 as integer))) as walldiff, CAST(c0 as integer)- CAST(c1 as integer) AS updiff FROM log_readable WHERE tag = 'NTPTIME' CREATE TABLE extract_ntptime AS SELECT extract_ntptime_tmp.*, uptimeid FROM extract_ntptime_tmp JOIN tmp_loginfo1 USING (device, log); DROP TABLE extract_ntptime_tmp; CREATE TABLE extract_protocol_nopayload AS SELECT * FROM log_readable WHERE tag = 'BroadcastProtocolConnection' AND c2 <> 'PAYLOAD'; -- 30c3 devices SELECT * FROM ( SELECT device, MIN(wallclock) AS first_seen_wallclock, DATETIME(MIN(wallclock)/1000,'unixepoch') AS readable FROM log_readable WHERE DATETIME(wallclock/1000,'unixepoch') > '2013-01-01' GROUP BY device ORDER BY first_seen_wallclock ) WHERE readable > '2013-12-27 00:00' AND readable < '2013-12-31 00:00'; -- Node degrees SELECT device as node, COUNT(DISTINCT c2) as out_count FROM extract_discoveries GROUP BY device ORDER BY out_count DESC; SELECT AVG(out_count) FROM (SELECT device as node, COUNT(DISTINCT c2) as out_count FROM extract_discoveries GROUP BY device); -- Information about connections and local/remote connection IDs. CREATE TABLE helper_connections_from_logs AS SELECT SUBSTR(device, 5, 16) as device1, c0 as connid1, c3 as device2, c4 as connid2 FROM extract_protocol_nopayload WHERE c1 = 'Received' AND c2 = 'HELLO'; -- Connections that were logged from both sides CREATE TABLE helper_connections_bidir_logged AS SELECT A.device1 as device1, A.connid1 as connid1, A.device2 as device2, A.connid2 as connid2 FROM helper_connections_from_logs A LEFT OUTER JOIN helper_connections_from_logs B ON (A.device1 = B.device2 AND A.device2 = B.device1 AND A.connid1 = B.connid2 AND A.connid2 = B.connid1) WHERE B.device1 IS NOT NULL; -- Or with more information preserved (log names and timestamps, as well as connections logged only on one end): CREATE TABLE helper_connections_from_logs_enhanced AS SELECT device, log, line, wallclock, uptime, SUBSTR(device, 5, 16) as device1, c0 as connid1, c3 as device2, c4 as connid2 FROM extract_protocol_nopayload WHERE c1 = 'Received' AND c2 = 'HELLO'; CREATE TABLE helper_connections_with_remote_info AS SELECT A.device1 as device1, A.connid1 as connid1, A.device2 as device2, A.connid2 as connid2, A.log AS device1_log, A.line AS device1_line, A.wallclock AS device1_wallclock, A.uptime AS device1_uptime, B.log AS device2_log, B.line AS device2_line, B.wallclock AS device2_wallclock, B.uptime AS device2_uptime FROM helper_connections_from_logs_enhanced A LEFT OUTER JOIN helper_connections_from_logs_enhanced B ON (A.device1 = B.device2 AND A.device2 = B.device1 AND A.connid1 = B.connid2 AND A.connid2 = B.connid1) ; -- Estimate wallclock accuracy by comparing both wallclocks in connections: CREATE TABLE tmp_connection_wallclocks AS SELECT device1, device2, device1_wallclock, device2_wallclock, device1_wallclock-device2_wallclock AS delta FROM helper_connections_with_remote_info WHERE device2_wallclock IS NOT NULL ORDER BY abs(delta) DESC; -- This can be used to estimate wallclock quality per device: SELECT device1, COUNT(DISTINCT device2) AS bad_pair_count, round(min(abs(delta))/60000) AS mindelta, round(max(abs(delta))/60000) AS maxdelta FROM tmp_connection_wallclocks WHERE abs(delta) > 3*60*1000 GROUP BY device1 ORDER BY bad_pair_count DESC; -- The results indicate that two wallclocks are massively off, with two more having slight differences: -- 26dfe630db590f7d is of by about 9 years, -- 11247f529a8665db by about one hour, -- 82b7ab26efa8e4df and d8a7812b2e2bc75e are off by a few minutes. -- Other devices have low but nonzero bad_pair_counts. To confirm that these are caused by the devices listed above: SELECT device1, COUNT(DISTINCT device2) AS bad_pair_count, round(min(abs(delta))/60000) AS mindelta, round(max(abs(delta))/60000) AS maxdelta FROM tmp_connection_wallclocks WHERE abs(delta) > 3*60*1000 AND device2 NOT IN ('26dfe630db590f7d','11247f529a8665db','82b7ab26efa8e4df','d8a7812b2e2bc75e') GROUP BY device1 ORDER BY bad_pair_count DESC; -- The result (showing only the four listed devices) indicates that these four devices are responsible for all wallclock discrepancies beyond 3 minutes in successful connections that were logged by both sides. -- Note that this does not provide information about wallclock discrepancies involving devices where one side did not provide logs. -- It also does not provide information about the wallclock accuracy of devices that never connected to another device (or only to devices with the same wrong clock setting). -- Since connection IDs are wallclock % INT_MAX, we can, to some extent, use them to compare system clocks. However, they are in hex, which is hard to handle in SQLite. -- The author exported helper_connections_from_logs to CSV and processed it using LibreOffice Calc. -- Except for one row where connid2 is 0, no additional bad wallclocks were found. -- Thus, the author recommends to use wallclock without corrections, except for the devices listed above. -- Corrections for these devices will be proposed separately. -- Only keep one connection pair from above table if connection was reported by both sides: CREATE TABLE helper_connections_with_remote_info_dedup AS SELECT * FROM ( SELECT t1.* FROM helper_connections_with_remote_info t1 LEFT JOIN helper_connections_with_remote_info t2 ON (t1.device1 = t2.device2 AND t1.connid1 = t2.connid2 AND t1.device2 = t2.device1 AND t1.connid2 = t2.connid1) WHERE t2.connid2 IS NULL OR t1.device1 < t1.device2 ); -- Get reliable timing information for these where available (will report NULL walltime for connections only logged by inaccurate devices): -- NOTE: An earlier version of this query provided device2_wallclock instead of NULL when both devices were inaccurate CREATE TABLE helper_connections_with_remote_info_dedup_times AS SELECT *, CASE WHEN device1 IN ('26dfe630db590f7d','11247f529a8665db','82b7ab26efa8e4df','d8a7812b2e2bc75e') THEN CASE WHEN device2 IN ('26dfe630db590f7d','11247f529a8665db','82b7ab26efa8e4df','d8a7812b2e2bc75e') THEN NULL ELSE device2_wallclock END ELSE device1_wallclock END AS walltime, abs(device1_wallclock - device2_wallclock) AS walldelta FROM helper_connections_with_remote_info_dedup; -- Results in a maximum walldelta of < 3 minutes unless one of the bad devices is involved. -- Turn this into a node connection table always going in the same direction: CREATE TABLE tmp_output_node_connections AS SELECT CASE WHEN device1 > device2 THEN device2 ELSE device1 END AS node1, CASE WHEN device1 > device2 THEN device1 ELSE device2 END AS node2, walltime, walldelta FROM helper_connections_with_remote_info_dedup_times; -- This table now contains each successfull connection establishment logged by at least one device. -- Each connection is included only once and assigned a timestamp that is likely accurate within 3 minutes. -- We can use this to prepare export files (note that connections with no reliable time are dropped - -- e.g. a connection between one of the four unreliable devices or a connection between an unreliable and a reliable device where the log from the reliable device was lost): CREATE TABLE output_node_connections AS SELECT node1, node2, GROUP_CONCAT(walltime) as connections FROM (SELECT node1, node2, walltime FROM tmp_output_node_connections WHERE walltime IS NOT NULL ORDER BY node1, node2, walltime) GROUP BY node1, node2; -- To verify the results, retrieve connection data from the original table with minimal postprocessing: SELECT SUBSTR(device, 5, 16) as device1, c3 as device2, wallclock FROM log_readable WHERE c1 = 'Received' AND c2 = 'HELLO' AND device1 NOT IN ('26dfe630db590f7d','11247f529a8665db','82b7ab26efa8e4df','d8a7812b2e2bc75e'); -- Connection speed (read thesis - this is a lower bound!) CREATE TABLE speeds AS SELECT device, c0, log, time, payloads, ROUND(CAST(payloads*1036 as FLOAT)/(time),1) as kbps FROM ( SELECT device, c0, max(uptime)-min(uptime) as time, sum(case when c1= 'Received' AND c2='PAYLOAD' then 1 else 0 end) as payloads, sum(case when c1= 'Received' AND c2='STATE' then 1 else 0 end) as offers FROM ( log_readable ) WHERE tag = 'BroadcastProtocolConnection' GROUP BY device, log, c0 HAVING offers > 0 ) WHERE payloads > 100 ORDER BY kbps DESC; -- Extract device information in one-line-per-log format CREATE TABLE extract_deviceinfo AS SELECT DISTINCT device, min(case when c0 = 'PhoneType' then c1 else NULL end) as PhoneType, min(case when c0 = 'Build.BOARD' then c1 else NULL end) as BOARD, min(case when c0 = 'Build.BRAND' then c1 else NULL end) as BRAND, min(case when c0 = 'Build.DEVICE' then c1 else NULL end) as DEVICE, min(case when c0 = 'Build.DISPLAY' then c1 else NULL end) as DISPLAY, min(case when c0 = 'Build.HARDWARE' then c1 else NULL end) as HARDWARE, min(case when c0 = 'Build.MANUFACTURER' then c1 else NULL end) as MANUFACTURER, min(case when c0 = 'Build.MODEL' then c1 else NULL end) as MODEL, min(case when c0 = 'Build.PRODUCT' then c1 else NULL end) as PRODUCT, min(case when c0 = 'Build.TAGS' then c1 else NULL end) as TAGS, min(case when c0 = 'Build.TYPE' then c1 else NULL end) as TYPE, min(case when c0 = 'Build.VERSION.CODENAME' then c1 else NULL end) as VERSION_CODENAME, min(case when c0 = 'Build.VERSION.INCREMENTAL' then c1 else NULL end) as VERSION_INCREMENTAL, min(case when c0 = 'Build.VERSION.RELEASE' then c1 else NULL end) as VERSION_RELEASE, min(case when c0 = 'Build.VERSION.SDK' then c1 else NULL end) as VERSION_SDK, min(case when c0 = 'Build.VERSION.SDK_INT' then c1 else NULL end) as VERSION_SDK_INT FROM log_readable WHERE tag='DEVICE' GROUP BY device, log; -- SQL queries from appendix -- Only for completeness, see above for queries that create a more complete extract_ntptime CREATE TABLE extract_ntptime AS SELECT device, log, line, wallclock, uptime, tag, CAST(c0 as integer) as ntptime, CAST(c1 as integer) as timeref, CAST(c2 as integer) as roundtrip, CAST(c0 as integer)- (wallclock - (uptime-CAST(c1 as integer))) as walldiff, CAST(c0 as integer)- CAST(c1 as integer) AS updiff FROM log_readable WHERE tag = 'NTPTIME'; CREATE TABLE helper_ntp_accuracy AS SELECT device, MIN(ntptime) as ntp_min_ntptime, MAX(ntptime) AS ntp_max_ntptime, MIN(walldiff) AS ntp_min_walldiff, MAX(walldiff) AS ntp_max_walldiff, COUNT(*) as ntp_count, MAX(ntptime)-MIN(ntptime) AS ntp_span, MAX(ABS(MIN(walldiff)),ABS(MAX(walldiff)))/60000 AS ntp_minutes_of_inaccuracy FROM extract_ntptime WHERE roundtrip > 0 AND roundtrip < 10000 GROUP BY device; CREATE TABLE extract_messaging AS SELECT * FROM log_readable WHERE (tag = 'BroadcastManager' AND c0 = 'creating message') OR (tag = 'BroadcastProtocolConnection' AND c1 = 'Received' AND c2 = 'PAYLOAD'); CREATE TABLE extract_messaging_with_timing AS SELECT *, (ntp_min_ntptime-60000) < wallclock AND wallclock < (ntp_max_ntptime+60000) AS within_ntp FROM extract_messaging LEFT JOIN helper_ntp_accuracy USING (device); CREATE TABLE tmp_messaging_receive AS SELECT wallclock, within_ntp, ntp_minutes_of_inaccuracy, device, c3 AS channel, c4 AS message FROM (SELECT * FROM extract_messaging_with_timing WHERE c3 IS NOT NULL); CREATE TABLE tmp_messaging_create AS SELECT wallclock, within_ntp, ntp_minutes_of_inaccuracy, device, SUBSTR(device, 5, 16) AS channel, c1 AS message FROM (SELECT * FROM extract_messaging_with_timing WHERE c3 IS NULL); CREATE TABLE results_messagespread AS SELECT datetime(wallclock/1000,'unixepoch') AS human_create_time, * FROM (SELECT channel, message, COUNT(DISTINCT device) AS carriers FROM tmp_messaging_receive GROUP BY channel, message) LEFT JOIN tmp_messaging_create USING (channel,message) ORDER BY human_create_time; CREATE TABLE results_messagespread_chartable AS SELECT wallclock/1000 AS unix_wallclock, carriers FROM results_messagespread WHERE within_ntp = 1 AND ntp_minutes_of_inaccuracy < 10; CREATE TABLE results_messagespread_daybuckets AS SELECT (unix_wallclock/(24*3600))*(24*3600) AS daystart, carriers AS number_of_carriers, COUNT(*) AS count FROM results_messagespread_chartable GROUP BY daystart, number_of_carriers; CREATE TABLE results_messagespread_hourbuckets AS SELECT (unix_wallclock/(3600))*(3600) AS hourstart, carriers AS number_of_carriers, COUNT(*) AS count FROM results_messagespread_chartable GROUP BY hourstart, number_of_carriers; CREATE TABLE tmp_messages_receive_with_create_date AS SELECT * FROM (SELECT channel, message, device AS recipient, wallclock AS receive_wallclock FROM tmp_messaging_receive WHERE within_ntp = 1 AND ntp_minutes_of_inaccuracy < 10) INNER JOIN (SELECT channel, message, wallclock AS create_wallclock FROM tmp_messaging_create WHERE within_ntp = 1 AND ntp_minutes_of_inaccuracy < 10) USING (channel,message); CREATE TABLE results_messagespread24_chartable AS SELECT create_wallclock/1000 AS unix_wallclock, COUNT(DISTINCT recipient) AS carriers FROM tmp_messages_receive_with_create_date WHERE receive_wallclock - create_wallclock < 24*3600*1000 GROUP BY channel, message; CREATE TABLE results_messagespread24_daybuckets AS SELECT (unix_wallclock/(24*3600))*(24*3600) AS daystart, carriers AS number_of_carriers, COUNT(*) AS count FROM results_messagespread24_chartable GROUP BY daystart, number_of_carriers; CREATE TABLE results_messagespread24_hourbuckets AS SELECT (unix_wallclock/(3600))*(3600) AS daystart, carriers AS number_of_carriers, COUNT(*) AS count FROM results_messagespread24_chartable GROUP BY daystart, number_of_carriers; CREATE TABLE tmp_selected_message AS SELECT datetime(receive_wallclock/1000,'unixepoch') AS human_receive_time, receive_wallclock, channel, message, recipient FROM tmp_messages_receive_with_create_date WHERE channel = 'a800ce7c9046ab8b' AND message = 7321; CREATE TABLE result_selected_message_spread AS SELECT receive_wallclock/1000 as unixtime, (SELECT COUNT(DISTINCT recipient) FROM (SELECT receive_wallclock AS rw, recipient FROM tmp_selected_message) WHERE rw <= receive_wallclock) AS recipientcount FROM tmp_selected_message ORDER BY receive_wallclock;