The following data was compiled using the MySQL Dump of MediaDefender data leaked to the public. See also: http://thepiratebay.org/tor/3808220/Gnutella.Tracking.Database.Leak.INDEPENDENT If you have anything to add or found a mistake visit #mediadefender-defenders on EFnet. Kindly hosted by JRWR / CCTSM. -- Let's look how many entries the table decoy_uploads has: SELECT COUNT(*) FROM decoy_uploads; +----------+ | count(*) | +----------+ | 13631110 | +----------+ That's 13.6 million in case you got the number of digits wrong :) What is the structure of the data? DESC decoy_uploads; +------------+--------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+-------------------+-------+ | timestamp | timestamp | NO | MUL | CURRENT_TIMESTAMP | | | filename | varchar(255) | YES | MUL | NULL | | | hash_value | char(33) | NO | PRI | | | | IP | varchar(16) | NO | PRI | | | +------------+--------------+------+-----+-------------------+-------+ What keys did they put in the table? SHOW KEYS FROM decoy_uploads; +---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | decoy_uploads | 0 | PRIMARY | 1 | hash_value | A | NULL | NULL | NULL | | BTREE | | | decoy_uploads | 0 | PRIMARY | 2 | IP | A | NULL | NULL | NULL | | BTREE | | | decoy_uploads | 1 | timestamp | 1 | timestamp | A | NULL | NULL | NULL | | BTREE | | | decoy_uploads | 1 | timestamp | 2 | filename | A | NULL | NULL | NULL | YES | BTREE | | | decoy_uploads | 1 | timestamp_2 | 1 | timestamp | A | NULL | NULL | NULL | | BTREE | | | decoy_uploads | 1 | filename | 1 | filename | A | NULL | NULL | NULL | YES | BTREE | | | decoy_uploads | 1 | hash_value | 1 | hash_value | A | NULL | NULL | NULL | | BTREE | | | decoy_uploads | 1 | IP | 1 | IP | A | NULL | NULL | NULL | | BTREE | | +---------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ i'm no mysql expert, but putting hash_value and IP into the primary key and then using both colums again as seperated keys makes no sense to me, at least for hash_value as it's at first place in the multi primary-key. Lets have a look on how a dataset looks like: SELECT * FROM decoy_uploads LIMIT 10; +---------------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------------+-----------------+ | timestamp | filename | hash_value | IP | +---------------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------------+-----------------+ | 2007-05-16 03:20:17 | C:\syncher\rcv\Gnutella_Decoys\05-01-2007\funny banned animated clothes video (havaianas).mpg | DLPAUX23QFZ7QDUPQBY6MI76KIBY7YZR | 70.101.214.145 | | 2007-05-16 03:20:17 | C:\syncher\rcv\Gnutella_Decoys\05-01-2007\hysterical outrageous animated anime thongs ad (havaianas).mpg | 6B2MPOUK3CLWWUUN33EJG43735FZPL7R | 70.101.214.145 | | 2007-05-16 03:20:17 | C:\syncher\rcv\Gnutella_Decoys\black rebel motorcycle club\black rebel motorcycle club - baby 81 - 666 conducer.mp3 | MUN2OCDTHPSOIH3QWOUTKMN77DC2O2WA | 68.201.16.215 | | 2007-05-16 03:20:17 | C:\syncher\rcv\Gnutella_Decoys\black rebel motorcycle club\black rebel motorcycle club - baby 81 - it's not what you wanted.mp3 | VZAOT2ETSYJ7NQLIJGLXGRM5P6P65RXC | 68.201.16.215 | | 2007-05-16 03:20:17 | C:\syncher\rcv\Gnutella_Decoys\black rebel motorcycle club\black rebel motorcycle club - baby 81 - lien on your dreams.mp3 | 4KLRVMS26RYFAXKCL4BLWJJDADDXASL7 | 68.201.16.215 | | 2007-05-16 03:20:17 | C:\syncher\rcv\Gnutella_Decoys\gretchen wilson sony\gretchen wilson - one of the boys - if you want a mother.mp3 | YM6JATSE27JAVOSRQK4KWYLMNVCTC5FB | 209.214.200.105 | | 2007-05-16 03:20:17 | C:\syncher\rcv\Gnutella_Decoys\miranda lambert crazy ex-girlfriend sony\miranda lambert - crazy ex-girlfriend - down.mp3 | 4HXWXNWY4CRNOCEVLZPU36FXONR4XOFT | 24.176.160.190 | | 2007-05-16 03:20:17 | C:\syncher\rcv\Gnutella_Decoys\miranda lambert crazy ex-girlfriend sony\miranda lambert - crazy ex-girlfriend - down.mp3 | 4HXWXNWY4CRNOCEVLZPU36FXONR4XOFT | 71.86.41.52 | | 2007-05-16 03:20:17 | C:\syncher\rcv\Gnutella_Decoys\miranda lambert crazy ex-girlfriend sony\miranda lambert - crazy ex-girlfriend - love letters.mp3 | 75ZLQ45WCZE7FLDHWWUHJTY7VGJZMDCU | 69.6.175.213 | | 2007-05-16 03:20:17 | C:\syncher\rcv\Gnutella_Decoys\unilever 05-04-2007\dress up and be sexy .mpg | HD6IFH2HVNEPCXCENW4OOQCRIBOXRTLT | 189.163.101.202 | +---------------------+----------------------------------------------------------------------------------------------------------------------------------+----------------------------------+-----------------+ The data in this table comprises almost 4 months: SELECT timestamp FROM decoy_uploads ORDER BY timestamp ASC LIMIT 1; +---------------------+ | timestamp | +---------------------+ | 2007-05-16 03:20:17 | +---------------------+ SELECT timestamp FROM decoy_uploads ORDER BY timestamp DESC LIMIT 1; +---------------------+ | timestamp | +---------------------+ | 2007-09-15 18:53:23 | +---------------------+ Do MediaDefender employees work on weekends or at least do their servers upload fake material? (1 = Sunday, 2 = Monday, ... 7 = Saturday) SET time_zone = 'US/Eastern'; SELECT DAYOFWEEK(timestamp) AS day_of_week, COUNT(*) AS uploads FROM decoy_uploads GROUP BY day_of_week ORDER BY uploads DESC; +-------------+---------+ | day_of_week | uploads | +-------------+---------+ | 6 | 2232235 | | 7 | 2083913 | | 3 | 1981627 | | 4 | 1934930 | | 1 | 1848687 | | 5 | 1829404 | | 2 | 1720314 | +-------------+---------+ For the people on the eastcoast of the USA Friday and Saturday are the days where you could download most likely fake material from MediaDefender Servers while using gnutella although uploads are close together over the week. Same analysis based on daytime: SET time_zone = 'US/Eastern'; SELECT HOUR(timestamp) AS hour, COUNT(*) AS uploads FROM decoy_uploads GROUP BY hour ORDER BY uploads DESC; +------+---------+ | hour | uploads | +------+---------+ | 21 | 775614 | | 22 | 760332 | | 20 | 751994 | | 23 | 733636 | | 18 | 706009 | | 19 | 705445 | | 0 | 699672 | | 1 | 690664 | | 17 | 672455 | | 4 | 661882 | | 2 | 660340 | | 3 | 656323 | | 16 | 627386 | | 5 | 600081 | | 15 | 548505 | | 6 | 528307 | | 14 | 463657 | | 7 | 459712 | | 13 | 400894 | | 8 | 388100 | | 12 | 336568 | | 9 | 282365 | | 11 | 279524 | | 10 | 241645 | +------+---------+ The IPs i suppose are the ones used to upload these fake-files. so let's have a look how many different IPs MediaDefender used to upload fake material: SELECT COUNT(*) FROM (SELECT DISTINCT ip FROM decoy_uploads) AS foo; +----------+ | count(*) | +----------+ | 5329403 | +----------+ The /24 netblocks they used the most: SELECT COUNT(*) AS occurance, SUBSTRING_INDEX(ip, '.', 3) AS netblock_24 FROM decoy_uploads GROUP BY netblock_24 ORDER BY occurance DESC LIMIT 20; +-----------+-------------+ | occurance | netblock_24 | +-----------+-------------+ | 2193 | 41.221.16 | | 2119 | 125.60.241 | | 2001 | 41.221.17 | | 1589 | 125.60.243 | | 1271 | 196.40.10 | | 1139 | 41.221.19 | | 1110 | 41.221.18 | | 1083 | 208.54.94 | | 1053 | 81.208.83 | | 1035 | 85.18.14 | | 960 | 85.18.136 | | 851 | 62.101.126 | | 826 | 208.54.14 | | 804 | 208.0.231 | | 742 | 203.111.234 | | 735 | 75.111.128 | | 714 | 213.140.6 | | 685 | 212.76.37 | | 638 | 71.42.147 | | 631 | 213.167.96 | +-----------+-------------+ Some statistics about the file extensions of fake-files they uploaded: CREATE VIEW fake_uploads AS SELECT COUNT(*) AS fake_uploads, LOWER(SUBSTRING_INDEX(filename, '.', -1)) AS file_extension FROM decoy_uploads GROUP BY file_extension ORDER BY fake_uploads DESC; SELECT * FROM fake_uploads; +--------------+----------------+ | fake_uploads | file_extension | +--------------+----------------+ | 10372169 | mp3 | | 2022435 | mov | | 590409 | avi | | 473176 | mpg | | 105575 | wma | | 53987 | wmv | | 7359 | rar | | 6000 | zip | +--------------+----------------+ Since they uploaded the same file more than once here is the list of file extensions of unique filenames: CREATE TEMPORARY TABLE fake_unique_files (fake_unique_files INT, file_extension CHAR(3)); INSERT INTO fake_unique_files SELECT COUNT(*) AS fake_unique_files, LOWER(SUBSTRING_INDEX(filename, '.', -1)) AS file_extension FROM (SELECT DISTINCT filename FROM decoy_uploads) AS foo GROUP BY file_extension ORDER BY fake_unique_files DESC; SELECT * FROM fake_unique_files; +-------------------+----------------+ | fake_unique_files | file_extension | +-------------------+----------------+ | 5091 | mov | | 2352 | wmv | | 1812 | mp3 | | 1470 | avi | | 1029 | mpg | | 266 | wma | | 40 | rar | | 3 | zip | +-------------------+----------------+ so you can see they had only 1812 different mp3 files in their database which comes at third place behind mov and wmv but they uploaded a shitload of times the mp3s: SELECT file_extension, fake_unique_files, fake_uploads, fake_uploads/fake_unique_files AS times_uploaded FROM fake_uploads INNER JOIN fake_unique_files USING (file_extension); +----------------+-------------------+--------------+----------------+ | file_extension | fake_unique_files | fake_uploads | times_uploaded | +----------------+-------------------+--------------+----------------+ | mov | 5091 | 2022435 | 397.2569 | | wmv | 2352 | 53987 | 22.9537 | | mp3 | 1812 | 10372169 | 5724.1551 | | avi | 1470 | 590409 | 401.6388 | | mpg | 1029 | 473176 | 459.8406 | | wma | 266 | 105575 | 396.8985 | | rar | 40 | 7359 | 183.9750 | | zip | 3 | 6000 | 2000.0000 | +----------------+-------------------+--------------+----------------+ Fakeuploads based on the day of the week and the file extension: (1 = Sunday, 2 = Monday, ... 7 = Saturday) SELECT LOWER(SUBSTRING_INDEX(filename, '.', -1)) AS file_extension, DAYOFWEEK(timestamp) AS day_of_week, COUNT(*) AS uploads FROM decoy_uploads GROUP BY file_extension, day_of_week; +----------------+-------------+---------+ | file_extension | day_of_week | uploads | +----------------+-------------+---------+ | avi | 1 | 92987 | | avi | 2 | 82043 | | avi | 3 | 78630 | | avi | 4 | 75430 | | avi | 5 | 81002 | | avi | 6 | 86332 | | avi | 7 | 93985 | | mov | 1 | 274072 | | mov | 2 | 271549 | | mov | 3 | 276396 | | mov | 4 | 287002 | | mov | 5 | 274600 | | mov | 6 | 328799 | | mov | 7 | 310017 | | mp3 | 1 | 1387616 | | mp3 | 2 | 1278136 | | mp3 | 3 | 1540691 | | mp3 | 4 | 1468142 | | mp3 | 5 | 1393463 | | mp3 | 6 | 1716473 | | mp3 | 7 | 1587648 | | mpg | 1 | 68432 | | mpg | 2 | 66602 | | mpg | 3 | 62557 | | mpg | 4 | 77949 | | mpg | 5 | 55321 | | mpg | 6 | 74828 | | mpg | 7 | 67487 | | rar | 1 | 941 | | rar | 2 | 930 | | rar | 3 | 1143 | | rar | 4 | 1221 | | rar | 5 | 906 | | rar | 6 | 1035 | | rar | 7 | 1183 | | wma | 1 | 15679 | | wma | 2 | 13061 | | wma | 3 | 13465 | | wma | 4 | 16210 | | wma | 5 | 16122 | | wma | 6 | 16655 | | wma | 7 | 14383 | | wmv | 1 | 8084 | | wmv | 2 | 7217 | | wmv | 3 | 7795 | | wmv | 4 | 7985 | | wmv | 5 | 7300 | | wmv | 6 | 7297 | | wmv | 7 | 8309 | | zip | 1 | 876 | | zip | 2 | 776 | | zip | 3 | 950 | | zip | 4 | 991 | | zip | 5 | 690 | | zip | 6 | 816 | | zip | 7 | 901 | +----------------+-------------+---------+ Fake files they uploaded the most: SELECT COUNT(*) AS uploads, filename FROM decoy_uploads GROUP BY filename ORDER BY uploads DESC LIMIT 20; +---------+--------------------------------------------------------------------------------------------------------------------------------------------+ | uploads | filename | +---------+--------------------------------------------------------------------------------------------------------------------------------------------+ | 174804 | C:\syncher\rcv\Gnutella_Decoys\beyonce\beyonce - ring the alarm.mp3 | | 171824 | C:\syncher\rcv\Gnutella_Decoys\new 50 cent\50 cent - 07 - ayo technology.mp3 | | 130982 | C:\syncher\rcv\Gnutella_Decoys\beyonce\beyonce - deja_vu.mp3 | | 109138 | C:\syncher\rcv\Gnutella_Decoys\miranda lambert crazy ex-girlfriend sony\miranda lambert - crazy ex-girlfriend - famous in a small town.mp3 | | 106637 | C:\syncher\rcv\Gnutella_Decoys\velvet revolver - she builds quick machines.mp3 folder\velvet revolver - she builds quick machines.mp3 | | 97489 | C:\syncher\rcv\Gnutella_Decoys\brad paisley sony\brad paisley-ticks.mp3 | | 95190 | C:\syncher\rcv\Gnutella_Decoys\kelly clarkson - never again\01 - kelly clarkson - never again.mp3 | | 86737 | C:\syncher\rcv\Gnutella_Decoys\velvet revolver - she builds quick machines.mp3 folder\01-velvet revolver-she builds quick machines.mp3 | | 83639 | C:\syncher\rcv\Gnutella_Decoys\kelly clarkson - never again\kelly clarkson - never again.mp3 | | 83511 | C:\syncher\rcv\Gnutella_Decoys\miranda lambert crazy ex-girlfriend sony\miranda lambert - crazy ex-girlfriend - crazy ex-girlfriend.mp3 | | 75109 | C:\syncher\rcv\Gnutella_Decoys\velvet revolver - she builds quick machines.mp3 folder\velvet revolver--she builds quick machines.mp3 | | 72966 | C:\syncher\rcv\Gnutella_Decoys\new kanye west\kanye west - graduation - good life.mp3 | | 72548 | C:\syncher\rcv\Gnutella_Decoys\new 50 cent\50 cent - 16 - curtis 187.mp3 | | 72535 | C:\syncher\rcv\Gnutella_Decoys\new kanye west\03 kanye west stronger.mp3 | | 69097 | C:\syncher\rcv\Gnutella_Decoys\gretchen wilson sony\gretchen wilson - one of the boys - one of the boys.mp3 | | 68453 | C:\syncher\rcv\Gnutella_Decoys\velvet revolver - she builds quick machines.mp3 folder\velvet revolver-she builds quick machines.mp3 | | 66303 | C:\syncher\rcv\Gnutella_Decoys\tokio hotel-zimmer 483- new\tokio hotel-zimmer 483-spring nicht.mp3 | | 63034 | C:\syncher\rcv\Gnutella_Decoys\new kanye west\kanye west - graduation - champion.mp3 | | 62953 | C:\syncher\rcv\Gnutella_Decoys\new kanye west\kanye west - graduation - stronger.mp3 | | 62002 | C:\syncher\rcv\Gnutella_Decoys\gretchen wilson sony\gretchen wilson - one of the boys - come to bed.mp3 | +---------+--------------------------------------------------------------------------------------------------------------------------------------------+