add comptage, a acces_log parser for RPM download stat

1 files changed, 212 insertions, 0 deletions

diff --git a/comptage b/comptage
new file mode 100755
index 0000000..0b33cbb
--- /dev/null
+++ b/comptage
@@ -0,0 +1,212 @@
+#!/usr/bin/php
+<?php
+/*
+	Analyse log from web server to count rpm usage
+
+	Version: 0.6.1
+	Author:  Remi Collet
+	License: GPL
+	URL:     http://blog.famillecollet.com/
+
+	Usage:   comptage --help
+
+	=== Changelog === 
+
+	23/05/2010	0.6.1	- generate stats.csv
+				- bug when access_log have vhost column
+				- add --comment option for CSV file
+	29/04/2010	0.6	- generate stats.xml
+	24/09/2008	0.5	- post %{dist} number
+	14/09/2008	0.4	- ppc64
+	14/08/2008	0.3	- minor fixes
+	13/02/2008	0.2	- enhanced regex for kwizart
+	10/02/2008	0.1	- initial
+*/
+define ("VERSION", "0.6.1");
+@include 'Console/Getargs.php';
+if (!class_exists("Console_Getargs")) {
+	echo "PEAR Console_Getargs extension is required\n";
+	die ("=> yum install php-pear-Console-Getargs\n");
+}
+
+function TraiteFile ($db, $table, $path, $Verb) {
+	$fic=fopen($path, "r");
+	if ($fic) {
+		while ($tab=fgetcsv($fic, 4000, ' ', '"')) {
+                   	if (count($tab)==10 && $tab[6]==200) {
+				# 0:IP, 3:Date, 5:URL, 6:Status, 7:Size, 9:Agent
+				list($ordre,$name,$prot)=explode(' ', $tab[5]);
+				$time=substr($tab[3],1)." ".substr($tab[4],0,-1);
+				$time=date("Y-m-d H:i:s", strtotime($time));
+
+		   	} else if (count($tab)==11 && $tab[7]==200) {
+				# 0:IP, 3:Date, 5:Host, 6:URL, 7:Status, 8:Size, 10:Agent
+				list($ordre,$name,$prot)=explode(' ', $tab[6]);
+				$time=substr($tab[3],1)." ".substr($tab[4],0,-1);
+				$time=date("Y-m-d H:i:s", strtotime($time));
+		   	} else {
+				continue;
+                   	} 
+			if ($Verb>3 && substr($name,-4)==".rpm") echo "+ $ordre: ".$name."\n";
+
+			# 1:name, 2:version, 3:release, 4:distro, 6:repotag 7:arch
+			/// if (preg_match('@^.*/(.*)-(.*)-(.*)\.((fc|el)[0-9]*)\.([a-z]*)\.(i386|x86_64|ppc|noarch)\.rpm$@',$name,$regs)) {
+
+			$ok=0;
+			if ($ordre != "GET") {
+				$ok=0;
+			} else if (preg_match('@^.*/(.*)-(.*)-(.*)\.((fc|el)[0-9]*)\.[a-z\.0-9]*\.(i.86|x86_64|ppc64|ppc|noarch|src)\.rpm$@',$name,$regs)) {
+			        $ok=1;
+			        $rpm=$regs[1];
+			        $ver=$regs[2];
+			        $rel=$regs[3];
+			        $dis=$regs[4];
+			        $arc=$regs[6];
+			} else if (preg_match('@^.*/(.*)-(.*)-(.*)\.[a-z]*\.((fc|el)[0-9]*)\.(i.86|x86_64|ppc64|ppc|noarch|src)\.rpm$@',$name,$regs)) {
+			        $ok=2;
+			        $rpm=$regs[1];
+			        $ver=$regs[2];
+			        $rel=$regs[3];
+			        $dis=$regs[4];
+			        $arc=$regs[6];
+			} else if (preg_match('@^.*/(.*)-(.*)-(.*)\.((fc|el)[0-9]*)\.(i.86|x86_64|ppc64|ppc|noarch|src)\.rpm$@',$name,$regs)) {
+			        $ok=3;
+			        $rpm=$regs[1];
+			        $ver=$regs[2];
+			        $rel=$regs[3];
+			        $dis=$regs[4];
+			        $arc=$regs[6];
+			} else if (preg_match('@^.*/(.*)-(.*)-(.*)\.(i.86|x86_64|ppc64|ppc|noarch|src)\.rpm$@',$name,$regs)) {
+			        $ok=4;
+			        $rpm=$regs[1];
+			        $ver=$regs[2];
+			        $rel=$regs[3];
+			        $dis="all";
+			        $arc=$regs[4];
+			} else if ($Verb>3  && substr($name,-4)==".rpm") 
+				echo "+ rejected \n";
+
+			if ($ok) {
+
+				$sql = sprintf("SELECT ref FROM %s WHERE `name`='%s' AND `version`='%s' AND `release`='%s' AND `distro`='%s' AND `arch`='%s'", 
+					$table, $rpm, $ver, $rel, $dis, $arc);
+				if ($Verb>3) echo "+ SQL: $sql\n";
+				$Res=$db->query($sql) or die ("** SQL error\n** ".$db->error."\n");	
+				
+				if ($Row=$Res->fetch_object()) {
+					$sql = sprintf("UPDATE %s SET cpt=cpt+1, last='%s', size=size+%d WHERE ref=%d", $table, $time, $tab[7], $Row->ref);
+					if ($Verb>3) echo "+ SQL: $sql\n";
+					$db->query($sql) or die ("** SQL error\n** ".$db->error."\n");	
+				} else {
+					$sql = sprintf("INSERT INTO %s SET `name`='%s', `version`='%s', `release`='%s', distro='%s', arch='%s', cpt=1, first='%s', last='%s', size=%d", 
+						$table, $rpm, $ver, $rel, $dis, $arc, $time, $time, $tab[7]);
+					if ($Verb>3) echo "+ SQL: $sql\n";
+					$db->query($sql) or die ("** SQL error\n** ".$db->error."\n");	
+				}
+			}
+		}
+		fclose($fic);
+	} else {
+		echo "Cannot read $path !\n";
+	}
+}
+if (isset($_SERVER["USERNAME"])) $defuser=$_SERVER["USERNAME"]; else $defuser='root';
+$config = array(
+     "database" => array('short' => 'b', 'max' =>  1, 'min' => 1, 'desc' => "Database name", 'default' => "stats"),
+     "comment"  => array('short' => 'x', 'max' =>  1, 'min' => 1, 'desc' => "Comment for CSV file", 'default' => ""),
+     "create"   => array('short' => 'c', 'max' =>  0, 'desc' => "Create table"),
+     "debug"    => array('short' => 'd', 'max' =>  0, 'desc' => "Very-verbose output"),
+     "password" => array('short' => 'p', 'max' =>  1, 'min' => 1, 'desc' => "Password for user on server", 'default' => ""),
+     "quiet"    => array('short' => 'q', 'max' =>  0, 'desc' => "Minimal output"),
+     "server"   => array('short' => 's', 'max' =>  1, 'min' => 1, 'desc' => "MySQL server name", 'default' => "localhost"),
+     "table"    => array('short' => 't', 'max' =>  1, 'min' => 1, 'desc' => "Table name", 'default' => "rpms"),
+     "user"     => array('short' => 'u', 'max' =>  1, 'min' => 1, 'desc' => "User login on server", 'default' => $defuser),
+     "verbose"  => array('short' => 'v', 'max' =>  0, 'desc' => "Verbose output"),
+     CONSOLE_GETARGS_PARAMS => array('min' => 1, 'max' => -1, 'desc' => 'files to analyse.', 'default' => "php://stdin")
+     );
+$args =& Console_Getargs::factory($config);
+
+if (PEAR::isError($args)) {
+	echo "comptage version " . VERSION . "\n";
+	if ($args->getCode() === CONSOLE_GETARGS_ERROR_USER)
+		echo Console_Getargs::getHelp($config, null, $args->getMessage())."\n";
+	else if ($args->getCode() === CONSOLE_GETARGS_HELP)
+		echo Console_Getargs::getHelp($config)."\n";
+	exit;
+}
+$Verb=1;
+if ($args->getValue('quiet'))   $Verb=0;
+if ($args->getValue('verbose')) $Verb=2;
+if ($args->getValue('debug'))   $Verb=4;
+
+$Server  = $args->getValue('server');
+$User    = $args->getValue('user');
+$Pass    = $args->getValue('password');
+$Base    = $args->getValue('database');
+$Table   = $args->getValue('table');
+$Comment = $args->getValue('comment');
+
+if ($Verb>1) {
+	echo "comptage version " . VERSION . "\n";
+	echo "+ Server   = $Server\n";
+	echo "+ User     = $User\n";
+	echo "+ Database = $Base\n";
+	echo "+ Table    = $Table\n";
+}
+
+$db=new mysqli($Server, $User, $Pass, $Base);
+if (mysqli_connect_errno()) {
+	die ("** Connection failed\n** ".mysqli_connect_error()."\n");
+}
+if ($args->getValue('create')){
+	if ($Verb>1) echo "+ Create table $Base.$Table\n";
+	
+	$sql="CREATE TABLE IF NOT EXISTS `rpms` (
+	  `ref` int(11) NOT NULL AUTO_INCREMENT,
+	  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
+	  `version` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
+	  `release` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
+	  `distro` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
+	  `arch` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
+	  `first` datetime DEFAULT NULL,
+	  `last` datetime DEFAULT NULL,
+	  `cpt` int(11) NOT NULL DEFAULT '0',
+	  `size` int(11) NOT NULL DEFAULT '0',
+	  PRIMARY KEY (`ref`),
+	  UNIQUE KEY `name` (`name`,`version`,`release`,`distro`,`arch`),
+	  KEY `first` (`first`),
+	  KEY `last` (`last`),
+	  KEY `distro` (`distro`,`arch`),
+	  KEY `arch` (`arch`)
+	) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
+	$db->query($sql) or die ("** SQL error\n** ".$db->error."\n");	
+}
+
+$db->autocommit(TRUE);
+
+$files=$args->getValue('parameters');
+if (is_array($files)) foreach ($files as $file) {
+	if ($Verb>1) echo "+ File     = $file\n";
+	TraiteFile($db, $Table, $file, $Verb);
+} else {
+	if ($Verb>1) echo "+ File     = $files\n";
+	TraiteFile($db, $Table, $files, $Verb);
+}
+
+// Compute total number of RPM download
+if ($res = $db->query("SELECT SUM( cpt ) AS RPM, NOW( ) AS Date FROM `rpms`")) {
+	$msg ="<?xml version='1.0' standalone='yes'?>\n<stats>\n";
+	if ($row = $res->fetch_assoc()) {
+		$msg .= "\t<RPM type='counter' subtype='small'>".$row['RPM']."</RPM>\n";
+		$msg .= "\t<Date type='datetime'>".$row['Date']."</Date>\n";
+	}
+	$msg .= "</stats>\n";
+
+	// Record last value to XML file
+	file_put_contents('stats.xml', $msg);
+
+	// Record value to CSV file (history)
+	error_log($row['Date'].';'.$row['RPM'].";$Comment\n",3,'stats.csv');
+}
+$db->close();
+?>