User talk:Kotepho

.

Admin list

Hi. Could you please tell me how to make a list of administrators, like the one you made? It will be greatly appreciated. / Fred Chess 08:03, 18 August 2006 (UTC)

I've managed to install mySql, downloaded the commons dump called "logging" and load it into mysql. Which queries should I make? Gratfully, Fred Chess 15:38, 18 August 2006 (UTC)

The logging table has a number of collumns, but it is pretty straightforward. You will be interested in log_type of protect, delete, block and log_actions of delete, restore, protect, unprotect, block, and unblock. log_timestamp is a mediawiki timestamp as a varchar 14 (YYYYMMDDhhmmss), log_user is the user id of the admin, log_namespace and log_title are the targets, log_comment is the 'edit summary', etc. If you want a count of all deletions in a month, per user:

SELECT log_user, COUNT(log_user) as num FROM logging WHERE LEFT(log_timestamp, 6) = '200608' AND log_type = 'delete' AND log_action = 'delete' GROUP BY log_user;

User ids aren't quite as useful for names though, and there isn't a table that is dumped that provides them easily. You can either find them by hand (go through each user id and see what they have deleted/when, look it up in special:log) or go diving through revision (stub-meta-history.xml is great for this--download the dump, run it through xml2sql, sort -t" " -u -k5,5 revision.txt | awk 'BEGIN { FS="\t" } {printf "%s\t%s\n",$5, $6; }' - > uid.txt, then make a table <code>create table uid ( user_id int(5) unsigned NOT NULL, user_name varchar(255) binary NOT NULL default '', PRIMARY KEY user_id (user_id), INDEX user_name (user_name)) ENGINE=InnoDB;</code> (I ran into some troubles with non-unique usernames because of renames and stuff on enwiki, you can probaby use UNIQUE INDEX or better, but I never join on user_name so it doesn't bother me...), load the data LOAD DATA INFILE 'uid.txt' INTO TABLE uid;)

Now we can do it by names:

SELECT user_name, COUNT(log_user) as num FROM logging INNER JOIN uid ON log_user = user_id WHERE LEFT(log_timestamp, 6) = '200608' AND log_type = 'delete' AND log_action = 'delete' GROUP BY log_user; (I actually use ...INTO OUTFILE 'foo.txt' FROM...)

breakdown of deletes per month (for all months) by admin: select left(log_timestamp,6), user_name, COUNT(log_action) into outfile '/tmp/name-delete.txt' from logging INNER JOIN uid ON log_user = user_id where log_type = 'delete' AND log_action = 'delete' group by left(log_timestamp,6), user_name with rollup having COUNT(log_action) > 0;

The all-time totals: select user_name, log_type, log_action, COUNT(log_action) into outfile '/tmp/all-type-action-test.txt' from logging INNER JOIN uid ON log_user = user_id where log_type in ('protect', 'block', 'delete') group by user_name, log_type, log_action with rollup having COUNT(log_action) > 0;

Deletions per day: select left(log_timestamp, 8) as day, count(log_action) as num from logging group by day with rollup;

Kotepho 22:49, 18 August 2006 (UTC)

Thank you very much. / Fred Chess 11:51, 19 August 2006 (UTC)

I just found your list of users - user Id in my spam-folder. Thanks. Lets see what I can make of it. / Fred Chess 14:00, 21 August 2006 (UTC)

Permission for statistics

Hi Kotepho,

Just so you know, we now have Commons:Administrator permission for statistics, so if you ever want to publish stats, just refer to this list and there should be no problems at all. :)

cheers, pfctdayelise (translate?) 07:52, 11 September 2006 (UTC)

Danke. Kotepho 10:33, 11 September 2006 (UTC)

Javascript

I don0t use any javascript, nor have a custom monobook. There's a "nominate for deletion" link on the sidebar at image pages, and I thought that, if it's a default thing (as I don't hav customization), then it would work properly, I see it isn't the case. -- Drini 13:58, 30 May 2007 (UTC)