Hello,
I attach the CGI script stats_short.c which
generates some useful statistics.
Read the source for more information.
There is a section name
/* Edit from here ... */ until /* Do not edit from here ... */
where you can change database connection parameters (and
some German strings, if you like).
It will compile with this Makefile (on my Linux box), change
the path variables to suit your installation:
# Makefile
LIB=-L/usr/local/mysql/lib/mysql -lmysqlclient -lm -lc
INCLUDE=-I/usr/local/mysql/include/mysql
CC=g++
CGIBIN=/usr/local/apache/cgi-bin
all: stats_short
.c:
$(CC) $(LIB) $(INCLUDE) -o $* $<
install:
cp stats_short $(CGIBIN)/stats_short.cgi
---
Call it using:
http://<your server>/cgi-bin/stats_short.cgi
Frank
/******************************************************************************
stats.cgi:
This CGI script extracts useful statistics information
from the MySQL-based Aspseek database and outputs
formatted HTML tables, suitable for viewing in a WWW browser.
It does not work for Oracle-based Aspseek.
http://your_server/cgi-bin/stats.cgi
Options:
You can use files named header.txt and footer.txt
in the same directory (or elsewhere, if configured)
to add some nice header (<html> ...) and footer ( ... </html> text.
You can easily add more queries.
Do not forget to update the counter N (number_queries),
if adding more queries.
Authors:
(c) University of Osnabrueck, Computing Center
Christian Lindemann
[EMAIL PROTECTED]
Frank Elsner
[EMAIL PROTECTED]
Release: 2001-10-01, V1.0
(This is the short output version.)
TO DO:
- Add (and translate ;:) more queries.
- Add some nice header and footer files.
- Show history of indexer runs, e.g. how many documents
have changed, were deleted or added since previous indexer run.
- More hooks for internationalization (error messages, titles, anchors, ...),
but an experienced C programmer will easily find the strings which
must be changed ;:)
- Put all config parameters into an external file (config.h ?)
******************************************************************************/
#include <time.h>
#include <sys/stat.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql.h>
#include <ctype.h>
/*
Edit from here ...
*/
#define GERMAN
/** #define ENGLISH **/
/*
Define footer.txt and header.txt to make the HTML output
look like the rest of your site.
These files must be created and copied into the same directory
as stats.cgi.
**/
#define file_header "header.txt"
#define file_footer "footer.txt"
#define max_suffix_lenght 10
/*
Define your files and connection parameters here.
Change to suit to your installation.
*/
#define pathtototal "/data/aspseek/aspseek12/total"
#define database_host "localhost"
#define database_user ""
#define database_pw ""
#define database_name "aspseek12"
/*
Define some useful queries,
you may easily add more queries or other languages,
using a #ifdef NEW_LANGUAGE ... #endif
and defining this language via #define NEW_LANGUAGE above.
*/
#ifdef GERMAN
/* Number of queries */
#define N 6
struct _query
{
int hide; /* 0: show output, 1: hide output */
char anchor[100]; /* anchor name for query */
char title[1024]; /* title for query */
char formstr[100]; /* format string: c(enter), l(eft), r(ight) */
char select_statement[1024]; /* SQL statement */
} queries[N] =
{
0,
"queries",
"Abfragen insgesamt:",
"cccc",
"SELECT COUNT(*) AS \"Anzahl:\", ROUND(AVG(urls), 1) AS \"Treffer: (�)\",
ROUND(AVG(sites), 1) as \"Seiten: (�)\",
AVG(finish-start) AS \"Suchdauer: (�)\"
FROM stat;",
0,
"last_queries",
"Die letzten 20 Abfragen:",
"lrrcl",
"SELECT query AS \"Anfrage:\", urls AS \"Treffer:\", sites AS \"Seiten:\",
DATE_FORMAT(FROM_UNIXTIME(start),
\"%H:%i - %d.%b %Y\") as \"Zeitpunkt:\",
addr AS \"Computer\"
FROM stat
ORDER BY start DESC
LIMIT 20;",
0,
"noresults_queries",
"Abfragen ohne Treffer:",
"lr",
"SELECT query AS \"Anfrage:\", COUNT(*) AS \"Anzahl:\"
FROM stat
WHERE sites=0
GROUP BY query
ORDER BY \"Anzahl:\" DESC
LIMIT 20;",
0,
"mostresults_queries",
"Abfragen mit den meisten Ergebnissen",
"lr",
"SELECT query AS \"Anfrage:\", MAX(urls) AS \"Treffer:\"
FROM stat
GROUP BY query
ORDER BY \"Treffer:\" DESC
LIMIT 20;",
1,
"mostqueries_ip",
"Computer mit den meisten Anfragen",
"lr",
"SELECT addr AS \"IP-Adresse\", COUNT(*) AS \"Anzahl\"
FROM stat
GROUP BY addr
ORDER BY \"Anzahl\" DESC
LIMIT 20;",
0,
"mostliked_queries",
"H�ufigste Anfragen, die ein Ergebniss hatten",
"lrrrr",
"SELECT query AS \"Anfrage:\", COUNT(*) AS \"Anzahl:\",
MAX(sites) as \"Treffer:(max)\", MIN(sites) as \"Treffer:(min)\",
ROUND(AVG(sites),1) as \"Treffer:(�)\"
FROM stat
WHERE sites!=0
GROUP BY query
ORDER BY \"Anzahl:\" DESC
LIMIT 20;"
};
#endif
/** endif GERMAN **/
/**
Do not edit from here.
**/
void printnow(char *s)
{
printf("%s<br>\n", s);
fflush(stdout);
}
void print_header()
{
FILE *fp;
int i;
struct stat statbuf;
struct tm *time;
time_t t;
char strbuf[80];
printf("Content-type: text/html\n\n");
if ((fp = fopen(file_header, "rb")) != NULL)
{
while((i = fgetc(fp)) != EOF)
printf("%c", i);
}
else
{
printf("<html><head></head><body><h1>Statistics</h1>\n");
}
printf("<ul>");
for (i = 0; i <N; i++) {
if (queries[i].hide == 0 ) {
printf("<li><a href =\"#%s\">%s</a></li>\n",
queries[i].anchor,
queries[i].title);
}
}
printf("</ul>\n\n");
if ((fp = fopen(pathtototal, "r")) != NULL)
{
if (stat(pathtototal, &statbuf) >= 0)
{
t = statbuf.st_mtime;
time = (struct tm *)localtime(&t);
strftime(strbuf, 79, "%d.%m.%Y", time);
printf("Letzte Aktualisierung der Suchmaschine: %s\n", strbuf);
}
fclose(fp);
}
fflush(stdout);
}
void print_footer()
{
FILE *fp;
int i;
if ((fp = fopen(file_footer, "rb")) != NULL)
{
while((i = fgetc(fp)) != EOF)
printf("%c", i);
}
else
{
printf("</body></html>\n");
}
}
void write_table(MYSQL *connection, _query q)
{
MYSQL_RES *result;
MYSQL_ROW row;
MYSQL_FIELD *field;
int state, fields, i;
state = mysql_query(connection, q.select_statement);
if (state != 0)
{
printf(mysql_error(connection));
print_footer();
exit(1);
}
result = mysql_store_result(connection);
fields = mysql_num_fields(result);
printf("<table border=1 width=\"100%\">\n");
for (i = 0; i < fields; i++)
{
field = mysql_fetch_field(result);
printf("<th>%s</th>", field->name);
}
printf("\n");
while ((row = mysql_fetch_row(result)) != NULL)
{
printf("<tr>");
for (i = 0; i < fields; i++)
{
printf("<td");
if (strlen(q.formstr) >= i)
switch (q.formstr[i])
{
case 'l': printf(" align=\"left\""); break;
case 'r': printf(" align=\"right\""); break;
case 'c': printf(" align=\"center\""); break;
}
printf("> %s </td>", (row[i] ? row[i] : "NULL"));
}
printf("</tr>\n");
}
printf("</table>\n");
mysql_free_result(result);
fflush(stdout);
}
int main(int argc, char *args[])
{
MYSQL *connection, mysql;
int state, i = 1;
char c;
print_header();
mysql_init(&mysql);
connection = mysql_real_connect(&mysql,
database_host,
database_user,
database_pw,
database_name,
0, NULL, 0);
if (connection == NULL)
{
printf(mysql_error(&mysql));
print_footer();
exit(1);
}
for (i = 0; i < N; i++)
if (queries[i].hide == 0)
{
printf("<a name=\"%s\">\n<h2>%s</h2></a>\n", queries[i].anchor,
queries[i].title);
write_table(connection, queries[i]);
}
mysql_close(connection);
print_footer();
}
#-------------------------------------------------------#
Dipl.-Math. Frank Elsner
Universitaet Osnabrueck (University of Osnabrueck)
- Rechenzentrum - (Computing Center)
Albrechstrasse 28, AVZ
D-49076 Osnabrueck
Deutschland (Germany)
Tel. (Phone): ++49 (0)541/969-2343 Fax: -2470
E-Mail: [EMAIL PROTECTED]
#-------------------------------------------------------#
