Table of Contents

Project Wifi Scanner: Data Scan 3a

Full scan of Feb 1st to April 10th 2020 - are people self isolating for COVID-19? Seems so.

I loaded data in as below. Then exported the basics to a CSV in MySQL:

SELECT DATE(seen_time),COUNT(DISTINCT mac) FROM simple_data WHERE DATE(seen_time) BETWEEN '2020-02-01' AND '2020-04-31' GROUP BY DATE(seen_time) INTO OUTFILE '/tmp/feb-apr-plot.csv'

Then use gnuplot to generate a table of this data:

set xdata time
set key off
set timefmt "%Y-%m-%d"
set boxwidth 0.9 relative
set format x "%Y-%m-%d"
set title "Distinct MACs seen"
set style fill solid 1
set label "Pubs are closed" at "2020-03-20", 10000 rotate by 90
plot "/tmp/feb-apr-plot.csv" using 1:2 with boxes lt rgb 'orange'

I had to manually colour the weekends (until I can figure out how to get gnuplot to do that for me.

Original Version (Scan 3)

Extract just March's data, then import it to the database:

grep -E "^2020-03" tcpdump.log > /tmp/march.wifi.log
php trim.php /tmp/march.wifi.log > march-trimmed.csv

Raw data for March was 21Gb, approx 168 million lines.

Then import to the mySQL database:

LOAD DATA INFILE 'march-trimmed.csv' INTO TABLE simple_data;

Basic Stats

SELECT DATE(seen_time), COUNT(mac) FROM simple_data WHERE DATE(seen_time) >= '2020-03-01' AND seen_time <= '2020-04-01' GROUP BY DATE(seen_time) INTO OUTFILE '/tmp/march-rawdata.csv';
DateHits
2020-03-01119994
2020-03-02122174
2020-03-03127180
2020-03-04138274
2020-03-05142102
2020-03-06150793
2020-03-07142417
2020-03-08135554
2020-03-09137481
2020-03-10125954
2020-03-11131901
2020-03-12135391
2020-03-13127303
2020-03-14118234
2020-03-15113126
2020-03-16106237
2020-03-17110425
2020-03-18106013
2020-03-19108127
2020-03-20115171
2020-03-21113031
2020-03-22108512
2020-03-23118570
2020-03-24110394
2020-03-25109092
2020-03-26113263
2020-03-27117891
2020-03-28114374
2020-03-29111808
2020-03-30116348
2020-03-31119926
SELECT DATE(seen_time),COUNT(DISTINCT mac) FROM simple_data WHERE DATE(seen_time) BETWEEN '2020-03-01' AND '2020-03-31' GROUP BY DATE(seen_time) INTO OUTFILE '/tmp/distinct-march.csv';
DateUnique MACs
2020-03-019372
2020-03-0212085
2020-03-0314876
2020-03-0417284
2020-03-0519644
2020-03-0621099
2020-03-0715523
2020-03-0812208
2020-03-0915426
2020-03-1013678
2020-03-1113240
2020-03-1213734
2020-03-1313742
2020-03-148752
2020-03-157683
2020-03-169916
2020-03-178676
2020-03-187949
2020-03-198166
2020-03-208624
2020-03-215615
2020-03-224818
2020-03-237242
2020-03-244621
2020-03-254568
2020-03-264451
2020-03-274819
2020-03-283527
2020-03-293645
2020-03-304900
2020-03-314283