anlässlich hannes seines
skripts zur abwehr von referrer-spam [
details] einmal paar referrer-daten von twoday.net der letzten 24 stunden. schlußfolgerungen kommen erst später.
Top-Referrer Domains sortiert nach Request-Count
mysql> select substr(ACCESSLOG_REFERRER, 8, locate('/', ACCESSLOG_REFERRER, 8)-8) as host, count(*) as cnt, count(distinct ACCESSLOG_IP) as ips, count(distinct substr(ACCESSLOG_REFERRER, locate('/', ACCESSLOG_REFERRER, 8))) as paths, count(distinct ACCESSLOG_F_SITE) as sites, count(distinct ACCESSLOG_F_TEXT) as sto
ries from AV_ACCESSLOG where ACCESSLOG_DATE > SUBDATE(NOW(), 24) group by host order by cnt desc limit 50;
+----------------------------+-------+-------+-------+-------+---------+
| host | cnt | ips | paths | sites | stories |
+----------------------------+-------+-------+-------+-------+---------+
| www.google.de | 44914 | 35726 | 29662 | 444 | 10650 |
| | 25967 | 12964 | 52 | 322 | 3475 |
| www.google.at | 18279 | 12951 | 13231 | 360 | 6087 |
| www.twoday.net | 13782 | 3576 | 50 | 273 | 2154 |
| images.search.yahoo.com | 11918 | 7191 | 6055 | 142 | 718 |
| www.google.com | 9575 | 7868 | 7767 | 320 | 3928 |
| www.galstown.ne.jp | 8197 | 431 | 4264 | 12 | 18 |
| de.search.yahoo.com | 6291 | 4754 | 4491 | 262 | 2203 |
| www.medianetjapan.com | 5585 | 305 | 2942 | 12 | 18 |
| www.google.ch | 4622 | 3636 | 3280 | 241 | 2090 |
| www.crossspot.net | 4572 | 173 | 1474 | 12 | 17 |
| www.rogue-slayer.org | 4374 | 174 | 1194 | 12 | 17 |
| www.pornspaces.com | 4202 | 182 | 2094 | 12 | 17 |
| globals.stormpages.com | 3981 | 175 | 1977 | 12 | 17 |
| pussyx.sultryserver.com | 3805 | 177 | 2393 | 12 | 17 |
| freexxx.erotic-place.org | 3773 | 175 | 2340 | 12 | 17 |
| www.adultshare.com | 3563 | 291 | 1700 | 12 | 18 |
| www.suomifutis.net | 2572 | 218 | 1210 | 12 | 18 |
| zusex.freesexyhouse.com | 2518 | 236 | 1461 | 11 | 16 |
| zoofi.free-websites.com | 2301 | 223 | 1627 | 11 | 16 |
| www.stormpages.com | 2241 | 217 | 1482 | 11 | 16 |
| sexibo.sex-photos.org | 2144 | 211 | 1087 | 11 | 16 |
| hardxxx.liveadulthost.com | 2017 | 152 | 1389 | 12 | 17 |
| musite.porkyhost.com | 1999 | 190 | 1107 | 11 | 16 |
| adultxxx.liveadulthost.com | 1954 | 154 | 1361 | 12 | 17 |
| xxxpost.sexushost.com | 1766 | 148 | 872 | 12 | 17 |
| www.aschost.com | 1636 | 110 | 893 | 11 | 16 |
| www.citedusexe.com | 1585 | 215 | 1097 | 12 | 18 |
| www.pornhome.com | 1539 | 147 | 919 | 12 | 17 |
| de.wikipedia.org | 1327 | 1199 | 119 | 34 | 61 |
| sexico.liveadulthost.com | 1303 | 162 | 940 | 11 | 16 |
| freeweb.siol.net | 1268 | 2 | 472 | 2 | 0 |
| sucheaol.aol.de | 1266 | 92 | 874 | 138 | 590 |
| www.topwebs.de | 1245 | 73 | 746 | 12 | 17 |
| itsthezoo.com | 1223 | 161 | 4 | 12 | 17 |
| www.altavista.com | 1177 | 791 | 421 | 109 | 317 |
| billsex.servik.com | 1146 | 139 | 831 | 11 | 16 |
| male.porkyhost.com | 1146 | 137 | 769 | 11 | 16 |
| fx3.org | 1107 | 834 | 1 | 1 | 1 |
| sexiboy.analloverz.com | 1058 | 132 | 779 | 11 | 16 |
| www.hostones.com | 1054 | 94 | 313 | 12 | 17 |
| suche.lycos.de | 990 | 819 | 388 | 97 | 236 |
| motorcyclecity.com | 953 | 151 | 3 | 12 | 17 |
| www.temiscamingue.net | 938 | 150 | 3 | 12 | 17 |
| www.qualityhost.be | 912 | 154 | 646 | 2 | 2 |
| www.datum.at | 901 | 253 | 204 | 21 | 248 |
| www.oxfordmuse.com | 897 | 121 | 32 | 12 | 17 |
| news.google.de | 895 | 757 | 696 | 1 | 68 |
| www.ballhosting.com | 890 | 145 | 729 | 2 | 2 |
| www.6space.org | 884 | 158 | 629 | 2 | 2 |
+----------------------------+-------+-------+-------+-------+---------+
50 rows in set (20.72 sec)
Top-Referrer Domains sortiert nach dem Ratio ip-count/rqust-count
mysql> select substr(ACCESSLOG_REFERRER, 8, locate('/', ACCESSLOG_REFERRER, 8)-8) as host, count(*) as cnt, count(distinct ACCESSLOG_IP) as ips, count(distinct substr(ACCESSLOG_REFERRER, locate('/', ACCESSLOG_REFERRER, 8))) as paths, count(distinct ACCESSLOG_F_SITE) as sites, count(distinct ACCESSLOG_F_TEXT) as stories, count(distinct ACCESSLOG_IP)/count(*) as ratio from AV_ACCESSLOG where ACCESSLOG_DATE > SUBDATE(NOW(), 24) group by host having cnt > 100 order by ratio asc limit 50;
+----------------------------+------+-----+-------+-------+---------+--------+
| host | cnt | ips | paths | sites | stories | ratio |
+----------------------------+------+-----+-------+-------+---------+--------+
| freeweb.siol.net | 1268 | 2 | 472 | 2 | 0 | 0.0016 |
| www.feelor.com | 107 | 2 | 18 | 3 | 62 | 0.0187 |
| www.crossspot.net | 4581 | 173 | 1474 | 12 | 17 | 0.0378 |
| www.rogue-slayer.org | 4385 | 174 | 1194 | 12 | 17 | 0.0397 |
| www.pornspaces.com | 4214 | 182 | 2097 | 12 | 17 | 0.0432 |
| globals.stormpages.com | 3993 | 175 | 1980 | 12 | 17 | 0.0438 |
| freexxx.erotic-place.org | 3787 | 175 | 2345 | 12 | 17 | 0.0462 |
| pussyx.sultryserver.com | 3814 | 177 | 2396 | 12 | 17 | 0.0464 |
| www.galstown.ne.jp | 8224 | 432 | 4272 | 12 | 18 | 0.0525 |
| www.medianetjapan.com | 5601 | 305 | 2946 | 12 | 18 | 0.0545 |
| www.topwebs.de | 1245 | 73 | 746 | 12 | 17 | 0.0586 |
| www.aschost.com | 1643 | 110 | 894 | 11 | 16 | 0.0670 |
| sucheaol.aol.de | 1267 | 92 | 874 | 138 | 590 | 0.0726 |
| hardxxx.liveadulthost.com | 2020 | 152 | 1390 | 12 | 17 | 0.0752 |
| adultxxx.liveadulthost.com | 1959 | 154 | 1364 | 12 | 17 | 0.0786 |
| www.adultshare.com | 3573 | 293 | 1702 | 12 | 18 | 0.0820 |
| xxxpost.sexushost.com | 1772 | 148 | 872 | 12 | 17 | 0.0835 |
| www.suomifutis.net | 2576 | 218 | 1211 | 12 | 18 | 0.0846 |
| www.hostones.com | 1060 | 94 | 313 | 12 | 17 | 0.0887 |
| zusex.freesexyhouse.com | 2526 | 236 | 1463 | 11 | 16 | 0.0934 |
| musite.porkyhost.com | 2003 | 190 | 1107 | 11 | 16 | 0.0949 |
| www.pornhome.com | 1540 | 147 | 919 | 12 | 17 | 0.0955 |
| www.stormpages.com | 2245 | 217 | 1483 | 11 | 16 | 0.0967 |
| zoofi.free-websites.com | 2304 | 223 | 1630 | 11 | 16 | 0.0968 |
| sexibo.sex-photos.org | 2150 | 212 | 1088 | 11 | 16 | 0.0986 |
| male.porkyhost.com | 1147 | 137 | 770 | 11 | 16 | 0.1194 |
| billsex.servik.com | 1151 | 139 | 833 | 11 | 16 | 0.1208 |
| sexico.liveadulthost.com | 1304 | 162 | 940 | 11 | 16 | 0.1242 |
| sexiboy.analloverz.com | 1061 | 133 | 780 | 11 | 16 | 0.1254 |
| itsthezoo.com | 1226 | 161 | 4 | 12 | 17 | 0.1313 |
| www.adultdreamhost.com | 813 | 107 | 685 | 2 | 2 | 0.1316 |
| www.oxfordmuse.com | 903 | 121 | 33 | 12 | 17 | 0.1340 |
| www.citedusexe.com | 1589 | 215 | 1100 | 12 | 18 | 0.1353 |
| www.bestpornhost.com | 797 | 109 | 678 | 2 | 2 | 0.1368 |
| www.freazer.com | 845 | 117 | 701 | 11 | 16 | 0.1385 |
| www.albergue3x.com | 774 | 110 | 528 | 11 | 16 | 0.1421 |
| userpage.fu-berlin.de | 839 | 120 | 2 | 12 | 17 | 0.1430 |
| essa.nerdcamp.net | 655 | 101 | 274 | 12 | 17 | 0.1542 |
| fobos.nerdcamp.net | 670 | 104 | 306 | 12 | 17 | 0.1552 |
| www.mom.at | 883 | 137 | 622 | 2 | 2 | 0.1552 |
| motorcyclecity.com | 958 | 151 | 3 | 12 | 17 | 0.1576 |
| www.temiscamingue.net | 941 | 150 | 3 | 12 | 17 | 0.1594 |
| homepages.ccharity.com | 881 | 141 | 638 | 2 | 2 | 0.1600 |
| members.moto.ru | 356 | 57 | 93 | 12 | 17 | 0.1601 |
| www.ballhosting.com | 888 | 145 | 728 | 2 | 2 | 0.1633 |
| zoomy.home.sapo.pt | 849 | 140 | 476 | 11 | 16 | 0.1649 |
| www.xgalleryhost.net | 872 | 145 | 661 | 2 | 2 | 0.1663 |
| www.gauhs.com | 881 | 147 | 733 | 2 | 2 | 0.1669 |
| pop.nerdcamp.net | 672 | 113 | 287 | 12 | 17 | 0.1682 |
| silos.nerdcamp.net | 671 | 113 | 310 | 12 | 17 | 0.1684 |
+----------------------------+------+-----+-------+-------+---------+--------+
50 rows in set (27.45 sec)
mysql> select substring_index(ACCESSLOG_IP, '.', 3) as ip, count(*) as cnt, count(distinct substring_index(substr(ACCESSLOG_REFERRER, 8, locate('/', ACCESSLOG_REFERRER, 8)-8), '.', -2)) as hosts, count(distinct substr(ACCESSLOG_REFERRER, locate('/', ACCESSLOG_REFERRER, 8))) as paths, count(distinct ACCESSLOG_F_SITE) as sites, count(distinct ACCESSLOG_F_TEXT) as stories from AV_ACCESSLOG where ACCESSLOG_DATE > SUBDATE(NOW(), 24) group by ip order by cnt desc;
mysql> select substring_index(substr(ACCESSLOG_REFERRER, 8, locate('/', ACCESSLOG_REFERRER, 8)-8), '.', -2) as host, count(*) as cnt, count(distinct ACCESSLOG_IP) as ips, count(distinct substr(ACCESSLOG_REFERRER, locate('/', ACCESSLOG_REFERRER, 8))) as paths, count(distinct ACCESSLOG_F_SITE) as sites, count(distinct ACCESSLOG_F_TEXT) as stories from AV_ACCESSLOG where ACCESSLOG_DATE > SUBDATE(NOW(), 24) group by host order by cnt desc;
michi - 29.May 2006 10:34 -
technisches
Michi a.k.a. 'Michael Platzer' is one of the Knallgraus, a Vienna-based New Media Agency, that deals more and more with 'stuff' that is commonly termed as Social Software.
