Debian DokuWiki

#!/usr/bin/perl -w
#Jason Thomas
use strict;
use lib "/home/jthomas/lib/perl5/site_perl/5.8.8/";
use Mysql;
use Statistics::Descriptive;
 
 
my $host = "hostname";
my $database = "keytest";
my $user = "admin";
my $pw = "admin";
my $beginDate = "2009-07-01";
my $endDate = "2009-07-31";
my @slotId = ("731147","731148","799084","799086","799088","799090","799091","698549");
 
my $connect = Mysql -> connect ($host, $database, $user, $pw);
 
	findTextId($beginDate, $endDate);
 
 
sub findTextId{
	my ($beginDate, $endDate) = @_; 
 
	for ( my $i = 0; $i < scalar @slotId; $i++){
 
	my @textId;
 
	my $findTextIdQuery = "SELECT 
	a.slot_id,
	concat(c.trans_element_conn,
	b.trans_element_text) full_element_name,
	a.text_id,
	a.coverage,
	a.response_time,
	a.conn_id
	FROM 
		(SELECT element_text_id text_id,slot_id,
		count(element_text_id)/(SELECT count(*) c FROM base_trans WHERE date_time>=\'$beginDate\' AND date_time<=\'$endDate\' AND slot_id=\'$slotId[$i]\')*100 coverage, 
	avg(measurement_element+time_to_first_byte+base_page_or_content_download+client_time) response_time,
       	max(element_conn_id) conn_id
	FROM base_element
	WHERE measurement_element>0
	AND date_time >= \'$beginDate\' and date_time <=\'$endDate\'
	AND slot_id = \'$slotId[$i]\'
	GROUP BY element_text_id) a,
	global_trans_element_text b,
	global_trans_element_conn c
	WHERE a.text_id = b.trans_element_text_id
	AND a.conn_id = c.trans_element_conn_id
	AND a.coverage > 95
	ORDER BY a.response_time desc 
	LIMIT 12;";
 
	print $findTextIdQuery."\n";
	my $execute = $connect->query($findTextIdQuery); 
##	print $execute."test";
		while (my @results = $execute->fetchrow()){
        	       print $results[2]."\n";
			push(@textId, $results[2]);
		}
	getDataSet($slotId[$i], @textId);
	}
}
 
sub getDataSet{
 
	my ($slotIdSingle, @textId) = @_;
	my @dataSet;
	for ( my $i = 0; $i < scalar @textId; $i++){
 
	my $selectElements = "SELECT a.responsetime 
	FROM (select avg(base_page_or_content_download+measurement_element+time_to_first_byte+client_time) responsetime
	FROM base_element
	WHERE date_time>=\'$beginDate\'
	AND date_time<\'$endDate\' AND element_text_id=\'$textId[$i]\'
	AND slot_id = \'$slotIdSingle\'
	GROUP BY trans_id) a;";
	print $selectElements."\n";
	my $execute = $connect->query($selectElements);
	while (my @results = $execute->fetchrow()){
##		print $results[0]."\n";
		push(@dataSet, $results[0]);
	}
 
	my $responseTime85 = calc85th(@dataSet);
	writeDB($slotIdSingle, $textId[$i], "85", $responseTime85);
	my $responseTime95 = calc95th(@dataSet);
	writeDB($slotIdSingle, $textId[$i], "95", $responseTime95);
	}
 
}
 
sub calc85th{
 
	my (@dataSet) = @_;
	my $stat = Statistics::Descriptive::Full->new();
	$stat->add_data (@dataSet);
	##print $stat->percentile(85)."\n";
	return $stat->percentile(85);
 
}
 
 
sub calc95th{
 
	my (@dataSet) = @_;
	my $stat = Statistics::Descriptive::Full->new();
	$stat->add_data (@dataSet);
	##print $stat->percentile(95)."\n";
	return $stat->percentile(95)."\n";
 
}
 
sub writeDB{
 
	my $connectNew = Mysql -> connect ($host, $database, $user, $pw);
	my ($slotId, $textId, $percentile, $responseTime) = @_; 
	my $tableName = "percentile_tmp"; 
 
	my $writeQuery = "INSERT INTO $tableName (slot_id, element_text_id, percentile, response_time)
		VALUES (\'$slotId\', \'$textId\', \'$percentile\', \'$responseTime\');";
	my $execute = $connectNew->query($writeQuery);
 
 
}