Spider Watcher

Spider Watcher
Author: Mikel Beck (mikel.beck@elite-computing.net)

This hack keeps track of the spiders (Search Engine robots) that visit your fourm. Every time a guest visits a page, the guest's IP address, user agent and the page they visited are logged to the database.

When somebody views the spider statistics page, this data is "rolled up", meaning the raw data is collated, the spider's name is determined by comparing the user agent to data contained in the spiders_bulletin.xml file, and the number of pages and visits is summarized and writted back to the database. In addition, and data from non-bots is removed.

The data is then displayed in a easy to read format for your viewing pleasure.

If the user viewing the report has permissions to view IP addresses, these are displayed as well.

A live version of the report from one of my sites can be seen here: http://www.happyhourpub.com/spiders.php

Also see the attached screenshot for an exmaple.


Revision History:
1.0.0 Beta 1 - 01/05/2006
- Initial Release

1.0.0 Beta 2 - 01/06/2006
- Included templates for spiders.php
- Removed text from templates, added them as phrases

1.0.0 Beta 3 - 01/07/2006
- Split up the display of "known" and "unknown" spiders

1.0.0 Beta 4 - 01/25/2006
- Corrected potentional SQL injection issue in plug-in
- Reduced the number of SQL queries required to display statistics
- Corrected date/time display issue

1.0.0 Beta 5 - 02/01/2006
- Reduced the number of SQL queries required to display statistics

1.0.0 Beta 6 - 02/08/2006
- No release

1.0.0 Beta 7 - 02/11/2006
- Corrected issue with "unknown" spiders not being displayed properly.
- Added tracking of the type of spider (searchspider, link checker, etc)

1.0.0 Beta 8 - 02/19/2006
- Change the display of IP addresses to be a pop-up so they're all not displayed on the main page.
- Combined the spiders that have the same name but different user agents.

1.0.0 Beta 9 - 03/10/2006
- Changed the display to group similar spiders together (search spiders, http check spiders, etc)

1.0.0 Beta 10 - 08/08/2006
- Changed how the rollup functions. Instead of rolling up every time somebody views the spider page, it rolls up once per hour.
- Corrected a few bugs here and there, mostly related to removing entries from the database.

Installation Instructions
1. Upload spiders.php to the root of your forum.
2. Upload spiders_rollup.php to the includes/cron directory.
3. Import the file product-spiderwatcher.xml using the Manage Products module.
4. Add a link to spiders.php on your navbar or footer.
5. Add a cron job with the following information:
Title: Spider Watcher Rollup
Day of the Week: *
Day of the Month: *
Hour: *
Minute: 0 - - -
Log entries: Yes
Filename: ./includes/cron/spiders_rollup.php

Upgrade Instructions
1. Upload (and overwrite) spiders to the root of your forum.
2. Upload spiders_rollup.php to the includes/cron directory.
3. Import the file product-spiderwatcher.xml using the Manage Products module. Make sure the "Allow Overwrite" option is set to "Yes".
4. Add a link to spiders.php on your navbar or footer.
5. Add a cron job with the following information:
Title: Spider Watcher Rollup
Day of the Week: *
Day of the Month: *
Hour: *
Minute: 0 - - -
Log entries: Yes
Filename: ./includes/cron/spiders_rollup.php

***UPGRADE NOTE***
When you upgrade from version 1.0.0 Beta 7 to 1.0.0 Beta 8 your existing spider data will be lost!

To make sure that you can decode the maximum amount of spiders, you should grab the latest spiderlist.xml and replace the spiders_vbulletin.xml file in your forumhome/includes/xml/ directory with the one from this thread: http://www.vbulletin.com/forum/showthread.php?t=76662

[Totti]

ye, would be cool ... as i added google-feedfetcher to my spiderlist (wasn't in it )yesterday
and now i have an unkown entry with the old visits and a new known entry with the visits since yesterday

[Megareus Rex]

Now its down to like 1350 or so. (# of queries)

[Robbed]

Database error in vBulletin 3.5.3:

Invalid SQL:
START TRANSACTION;

MySQL Error : You have an error in your SQL syntax near 'START TRANSACTION' at line 1
Error Number : 1064
Date : Thursday, January 26th 2006 @ 06:10:48 PM
Script :
Referrer :
IP Address :
Username :
Classname :

Any fix for this?

[XtremeOffroad]

Originally Posted by mikelbeck

XtremeOffroad: That sounds like there's nothing to be sorted, so the multisort is failing. I will put a check in there in the next version. As for no spiders... Do they visit your site? Are you sure that the plug-in is installed and active?

Yea they do visit quite often, Yes the plugin is active, I've had the issue since the first release of this hack.
Kept it installed in hopes of a fix.

Thanks for the reply, Fingers crossed for a fix :nervous:

[mikelbeck]

Originally Posted by XtremeOffroad

Yea they do visit quite often, Yes the plugin is active, I've had the issue since the first release of this hack.
Kept it installed in hopes of a fix.

Thanks for the reply, Fingers crossed for a fix :nervous:

Can you take a look in your vb_bots table and see if there's any data in it?

[XtremeOffroad]

Originally Posted by mikelbeck

Can you take a look in your vb_bots table and see if there's any data in it?

Where is the vb_bots table?

[Brandon Sheley]

that would be in your MySQL

[XtremeOffroad]

Originally Posted by Loco.M

that would be in your MySQL

I thaught so but I didnt even see it there.

Edit: I do not have vb_bots but do have a Bots which contains

Field Type Collation Attributes Null Default Extra Action
id int(11) No auto_increment
bot varchar(255) latin1_swedish_ci No
nice_bot varchar(255) latin1_swedish_ci No
timestamp datetime Yes 0000-00-00 00:00:00
ip_address varchar(255) latin1_swedish_ci No
page varchar(255) latin1_swedish_ci No
rollup int(11)


Space usage:
Type Usage
Data 0 Bytes
Index 1,024 Bytes
Total 1,024 Bytes

If this is what you were referring to.

[mikelbeck]

Originally Posted by XtremeOffroad

I thaught so but I didnt even see it there.

Edit: I do not have vb_bots but do have a Bots which contains

Field Type Collation Attributes Null Default Extra Action
id int(11) No auto_increment
bot varchar(255) latin1_swedish_ci No
nice_bot varchar(255) latin1_swedish_ci No
timestamp datetime Yes 0000-00-00 00:00:00
ip_address varchar(255) latin1_swedish_ci No
page varchar(255) latin1_swedish_ci No
rollup int(11)


Space usage:
Type Usage
Data 0 Bytes
Index 1,024 Bytes
Total 1,024 Bytes

If this is what you were referring to.

Yes.

Do the rest of your tables have any sort of prefix? (like vb_ or something similar)

There's no data in that table, that's why it's not showing you any data.

[XtremeOffroad]

Originally Posted by mikelbeck

Yes.

Do the rest of your tables have any sort of prefix? (like vb_ or something similar)

There's no data in that table, that's why it's not showing you any data.

No There is no (VB_) prefix on any of the tables.

How would I go about getting data to show?
And thank you very much for the replies to help.
Hopefully there is an easy fix.