Admin Quick Stats

I don't use vBhacker so you will have to do this the old fashioned way. Also forgive me if something like this has already been released. I think everything is good but it is hard to code in vb2 style after working on the vb3 style so much.

This hack shows some quick stats on your Admin index page such as PHP version, server type, mysql version, Database usage, attachment usage, avatar usage, moderation and forum stats. I can add support for calculating attachments from PPN's attachments as files but I have to actually look at it first, which I will do tomorrow. In vb3 there is a filesize column in the attachment table so you don't need to do anything different if the attachments are currently in the file system (you can move them back and forth) but I assume I will have to run through the directory with filesize() to get an attachment total with the hack.

Anyway open admin/index.php and find:

if ($moderatenewmembers==1 or $usecoppa==1) {
  $waiting=$DB_site->query_first("SELECT COUNT(*) AS users FROM user WHERE usergroupid=4");
  if ($waiting[users]==0) {
    echo "<font size='1'>There are currently $waiting[users] user(s) awaiting <a href=\"user.php?s=$session[sessionhash]&action=moderate\">moderation</a>.</font>";
  } else {
    echo "<b><a href=\"user.php?s=$session[sessionhash]&action=moderate\">There are currently $waiting[users] user(s) awaiting moderation</a>.</b>";
  }
}

Delete that and replace it with:

function kbtomb($value) {
	if ($value == 'N/A') {
		return $value;
	} elseif (!$value) {
		return '0.0 MB';
	} else {
		return sprintf('%.2f', $value / 1024000) . ' MB';
	}
}

$starttime = mktime(0, 0, 0, date('m'), date('d'), date('Y'));

$attach = $DB_site->query_first("SELECT SUM(LENGTH(filedata)) AS size FROM attachment");
$avatar = $DB_site->query_first("SELECT SUM(LENGTH(avatardata)) AS size FROM customavatar");

$newusers = $DB_site->query_first("SELECT COUNT(*) AS count FROM user WHERE joindate >= $starttime");
$newthreads = $DB_site->query_first("SELECT COUNT(*) AS count FROM thread WHERE dateline >= $starttime");
$newposts = $DB_site->query_first("SELECT COUNT(*) AS count FROM post WHERE dateline >= $starttime");
$users = $DB_site->query_first("SELECT COUNT(*) AS count FROM user WHERE lastactivity >= $starttime");

$mysqlversion = $DB_site->query_first("SELECT VERSION() AS version");

$indexsize = 0;
$datasize = 0;
if ($mysqlversion['version'] >= '3.23') {
	$DB_site->reporterror = 0;
	$tables = $DB_site->query("SHOW TABLE STATUS");
	$errno = $DB_site->errno;
	$DB_site->reporterror = 1;
	if (!$errno) {
		while ($table = $DB_site->fetch_array($tables)) {
			$datasize += $table['Data_length'];
			$indexsize += $table['Index_length'];
		}
		if (!$indexsize) {
			$indexsize = 'N/A';
		}
		if (!datasize) {
			$datasize = 'N/A';
		}
	} else {
		$datasize = 'N/A';
		$indexsize = 'N/A';
	}
}

$attachcount = $DB_site->query_first("SELECT COUNT(*) AS count FROM attachment WHERE visible = 0");

$serverinfo = PHP_OS . ' / PHP v' . phpversion();

if (phpversion() >= '4.0.3') {
	$serverinfo .= iif(ini_get('safe_mode'), ' Safe Mode', '');
	$serverinfo .= iif(ini_get('file_uploads'), '', '<br />FILE_UPLOADS disabled');
}

doformheader('', '');
maketableheader('Quick Stats');
makelabelcode('Server Type', $serverinfo);
makelabelcode('MySQL', 'v' . $mysqlversion['version']);
makelabelcode('Database Data Usage:', kbtomb($datasize));
makelabelcode('Database Index Usage:', kbtomb($indexsize));
makelabelcode('Attachment Usage:', kbtomb($attach['size']));
makelabelcode('Custom Avatar Usage:', kbtomb($avatar['size']));

// Only display if the admin has moderation enabled on an active postable forum.
if ($DB_site->query_first("SELECT forumid FROM forum WHERE moderatenew = 1 AND cancontainthreads = 1 AND active = 1 AND allowposting = 1")) {
	$postcount = $DB_site->query_first("SELECT COUNT(*) AS count FROM post WHERE visible=0");
	$threadcount = $DB_site->query_first("SELECT COUNT(*) AS count FROM thread WHERE visible=0");
	makelabelcode("Threads Awaiting <a href=\"../mod/moderate.php?s=$session[sessionhash]&action=posts\">Moderation</a>:", $threadcount['count']);
	makelabelcode("Posts Awaiting <a href=\"../mod/moderate.php?s=$session[sessionhash]&action=posts\">Moderation</a>:", $postcount['count']);
}
if ($moderatenewmembers==1 or $usecoppa==1) {
	$waiting=$DB_site->query_first("SELECT COUNT(*) AS users FROM user WHERE usergroupid=4");
	makelabelcode("Users Awaiting <a href=\"user.php?s=$session[sessionhash]&action=moderate\">Moderation</a>:", $waiting['users']);
}
makelabelcode("Attachments Awaiting <a href=\"../mod/moderate.php?s=$session[sessionhash]&action=attachments\">Moderation</a>:", $attachcount['count']);
makelabelcode('New Users Today:', $newusers['count']);
makelabelcode('Registered Visitors Today:', $users['count']);
makelabelcode('New Threads Today:', $newthreads['count']);
makelabelcode('New Posts Today:', $newposts['count']);
dotablefooter();

[FWC]

Cool. I'm glad it worked. It's a great hack!

[ryanhulce]

Here is what Opera Showed!

[inphinity]

nice hack freddy

on my board i've found that it slows the loading of the admin page by a few seconds and also adds to the load of the machine.

so i've moved it to a seperate file and added a link to my admin nav bar

Save Freddys hack to admin/adminquickstats.php at the top of the file add:

PHP Code:

<?php

error_reporting(7);

require("./global.php");

cpheader();

and the bottom of the file add

PHP Code:

cpfooter();

?> 


In admin/index.php find

PHP Code:

makenavoption("Back-up Database","backup.php?action=choose","<br>"); 


above it add

PHP Code:

makenavoption("Database Stats","adminquickstats.php?action=","<br>"); 


[Freddie Bingham]

Yes it will do a full scan of the post/thread/user tables so it can appear slow. I don't see any long term effects on load happening though unless you are reloading the Admin CP index often.

[Harvey]

Hi,

I don't reccoment if you don't call admin/index.php every few seconds, but if you want to try at your own risk, query some SQL-commands:

create index joindate on user (joindate)
create index dateline on thread (dateline)
create index dateline on post (dateline)
create index lastactivity on user (lastactivity)

If you don't get what you want, you can undo with:

alter table user drop index joindate
alter table thread drop index dateline
alter table post drop index dateline
alter table user drop index lastactivity

[Freddie Bingham]

Don't add those indeces to your forum as it will result in a general slowdown.

It will make these stats appear instantly but you will then have dateline indexed twice in thread and post since it is part of a multi-record index already. You will then have lastactivity indexed on the user table which will then have to be updated on every pageview which can be a bad thing.

It is important to devote optimization to the user side of the forum at the admin panel's cost.

[Harvey]

huh - did I miss something in the upgrades? I can't find any indices of dateline (checked with show create table post / thread)

But you're right, it would slow down update/insert/delete-statements.

[Freddie Bingham]

Your right. I am getting confused with vb3 tables I think. Just ignore me. I still woudn't put that index on the user.lastactivity though.

[Martz]

Excellent, works well apart from the slow admin/index.php - but that I can handle.

[Austin Dea]

Works bootyfully .