I got the idea of writing some perl code that slices and dices the TeamQuest .RPT files that can be generated by TeamQuest View and then passing on the queries to tqharvc to a graphing routine to generate both plots and .CSV files for later processing if need be.
For the graphing of data I utilize GD::Graph for generating a scatter plot.
For the example in this blog entry I have a very simple .RPT file by the name of physc.RPT and as the name implies, it simply reports physc usage from a LPAR:
[General]
Report Name = "physc"
View = Line
Format = Date+Time,
Parameter
[dParm1]
System = "[?]"
Category Group = "CPU"
Category = "by LPAR"
Subcategory = ""
Statistic = "physc"
Value Types = "Average"
Simple enough, isn't it?
My perl routine builds up a query for tqharvc by slicing and dicing the .RPT file and iterates through all the sliced and diced metrics, collects the output data and generates the .CSV output and plot image.
The tqharvc command line query is formed as:
tqharvc.exe -m [hostName] -s [startDate]:[startTime] -e [endDate]:[endTime] -a 10-minute -k [metric]Say that I want to query "serverA" for the metric "CPU:by LPAR:physc" between 11/1/2011 midnight to 11/07/2011 11:59:59 PM with metrics from every 10 minutes?
I call tqharvc with the following command line:
tqharvc.exe -m serverA -s 11012011:000000 -e 11072011:235959 -a 10-minute -k "CPU:by LPAR:physc"My routine takes several command line parameters:
tqGatherMetrics.pl server.list physc.rpt 11/01/2011 00:00:00 11/06/2011 23:59:59server.list is simply a text file with a list of servers to query:
serverA
serverB
serverC
serverD
serverE
serverF
phsyc.rpt is the aforementioned .RPT file. 11/01/2011 is the start date and 00:0:000 is the start time. 11/07/2011 is the end date and 23:59:59 is the end time of the queries.
Below is the output to STDERR from the perl routine as it crunches through the data from the servers:
Running query against serverA.
Executing TQ Query for serverA:CPU:by LPAR.
Processing query results.
Extracted 864 lines of data from query.
Processing results for serverA : CPU:by LPAR : 7
Generating graph for "serverA_CPU_by_LPAR_physc.gif"
Running query against serverB.
Executing TQ Query for serverB:CPU:by LPAR.
Processing query results.
Extracted 864 lines of data from query.
Processing results for serverB : CPU:by LPAR : 7
Generating graph for "serverB_CPU_by_LPAR_physc.gif"
Running query against serverC.
Executing TQ Query for serverC:CPU:by LPAR.
Processing query results.
Extracted 864 lines of data from query.
Processing results for serverC : CPU:by LPAR : 7
Generating graph for "serverC_CPU_by_LPAR_physc.gif"
Running query against serverD.
Executing TQ Query for serverD:CPU:by LPAR.
Processing query results.
Extracted 864 lines of data from query.
Processing results for serverD : CPU:by LPAR : 7
Generating graph for "serverD_CPU_by_LPAR_physc.gif"
Running query against serverE.
Executing TQ Query for serverE:CPU:by LPAR.
Processing query results.
Extracted 864 lines of data from query.
Processing results for serverE : CPU:by LPAR : 7
Generating graph for "serverE_CPU_by_LPAR_physc.gif"
Running query against serverF.
Executing TQ Query for serverF:CPU:by LPAR.
Processing query results.
Extracted 864 lines of data from query.
Processing results for serverF : CPU:by LPAR : 7
Generating graph for "serverF_CPU_by_LPAR_physc.gif"
The output of the plot appears as:
With the above command line I was able to generate plots for six servers in the server.list file.
If the .RPT file had listed multiple metrics, there would have been one plot generated per server for each metric. I chose the single metric physc as a simple example.
The perl code to generate the plot follows:
1: use GD::Graph::points;
2: use Statistics::Descriptive;
3: use Time::Local;
4: use POSIX qw/ceil/;
5:
6: use strict;
7:
8: my $sourceList = shift;
9: my $tqReport = shift;
10: my $startDate = shift;
11: my $startTime = shift;
12: my $endDate = shift;
13: my $endTime = shift;
14:
15: $startDate =~ s/\///g;
16: $endDate =~ s/\///g;
17:
18: $startTime =~ s/\://g;
19: $endTime =~ s/\://g;
20:
21: if (length($sourceList) == 0) {
22: die "Usage:tqGatherMerics.pl <sourceList> <tqReport> <startDate> <stareTime> <endDate> <endTime>\n";
23: };
24:
25: my $tqHarvestBinary = "C:\\Program Files\\TeamQuest\\manager\\bin\\tqharvc.exe";
26:
27: if ( -f "$tqHarvestBinary" ) {
28: if ( -f "$sourceList" ) {
29: if ( -f "$tqReport" ) {
30:
31: my @hostNames = ();
32: my %metricHash = ();
33:
34: open(HOSTNAMES, "$sourceList") || die "$!";
35: while (<HOSTNAMES>) {
36: chomp($_);
37: if ($_ !~ /^#/) {
38: push(@hostNames, $_);
39: };
40: };
41: close(HOSTNAMES);
42:
43: open(REPORT, "$tqReport") || die "$!";
44:
45: my $catGroup = "::";
46: my $catName = "::";
47: my $subCat = "::";
48:
49: while (<REPORT>) {
50:
51: my @statArray = ();
52:
53: chomp($_);
54:
55: if ($_ =~ /Category Group = \"(.+?)\"/) {
56: $catGroup = $1;
57: };
58:
59: if ($_ =~ /Category = \"(.+?)\"/) {
60: $catName = $1;
61: };
62:
63: if ($_ =~ /Subcategory = \"(.+?)\"/) {
64: $subCat = $1;
65: };
66:
67: if ($_ =~ /Statistic =/) {
68: my $tmpString = "";
69: $_ =~ s/Statistic =//g;
70: $tmpString = $_;
71: do {
72: $_ = <REPORT>;
73: chomp($_);
74: if ($_ !~ /^Resource|^Value Types/) {
75: $tmpString .= $_;
76: };
77: } until ($_ =~ /^Resource|^Value Types/);
78: my @statArray = split(/\,/, $tmpString);
79: $metricHash{"${catGroup}:${catName}"} = \@statArray;
80: };
81:
82: };
83:
84: close(REPORT);
85:
86: foreach my $hostName (@hostNames) {
87: print STDERR "Running query against $hostName.\n";
88: my %metricData = ();
89: foreach my $paramName (sort(keys(%metricHash))) {
90: my %columnHash = ();
91: my $linesExtracted = 0;
92: my $shellCmd = "\"$tqHarvestBinary\" -m $hostName -s $startDate:$startTime -e $endDate:$endTime -a 10-minute -k \"$paramName\"";
93: # my $shellCmd = "\"$tqHarvestBinary\" -m $hostName -s $startDate:$startTime -e $endDate:$endTime -a 1-minute -k \"$paramName\"";
94: print STDERR "\t\tExecuting TQ Query for $hostName:$paramName.\n";
95:
96: open(OUTPUT, "$shellCmd |") || die "$!";
97:
98: print STDERR "\t\tProcessing query results.\n";
99:
100: my $totalColumns = 0;
101:
102: while (<OUTPUT>) {
103: chomp($_);
104: if ($_ =~ /^Time:/) {
105: my @columns = split(/\,/, $_);
106: my $statName = "";
107: for (my $index = 0; $index < $#columns; $index++) {
108: foreach $statName (@{$metricHash{$paramName}}) {
109: $statName =~ s/^\s+//g; # ltrim
110: $statName =~ s/\s+$//g; # rtrim
111: $statName =~ s/\"//g;
112:
113: my $columnName = $columns[$index];
114: if (index($columnName, $statName, 0) >= 0) {
115: $columnHash{$index} = $columns[$index];
116: $totalColumns++;
117: };
118: };
119: };
120: } else {
121: if ($_ =~ /^[0-9]/) {
122: chomp($_);
123: my @columns = split(/\,/, $_);
124: foreach my $index (sort(keys(%columnHash))) {
125: $metricData{"$columns[0] $columns[1]"}{$columnHash{$index}} = $columns[$index];
126: };
127: $linesExtracted++;
128: };
129: };
130: };
131:
132: close(OUTPUT);
133:
134: if (($linesExtracted > 0) && ($totalColumns > 0)) {
135: print STDERR "\tExtracted $linesExtracted lines of data from query.\n";
136: my @domainData = ();
137:
138: foreach my $timeStamp (sort dateSort keys(%metricData)) {
139: push(@domainData, $timeStamp);
140: };
141:
142: foreach my $metricIndex (sort(keys(%columnHash))) {
143: print STDERR "\t\tProcessing results for $hostName : $paramName : $metricIndex\n";
144:
145: my $metricName = $columnHash{$metricIndex};
146: my @rangeData = ();
147: my $stat = Statistics::Descriptive::Full->new();
148:
149: foreach my $timeStamp (@domainData) {
150: push(@rangeData, $metricData{$timeStamp}{$columnHash{$metricIndex}});
151: $stat->add_data($metricData{$timeStamp}{$columnHash{$metricIndex}});
152: };
153:
154: my $graphName = "${hostName}_${paramName}_${metricName}";
155: my $csvName = "";
156:
157: $graphName =~ s/\\/_/g;
158: $graphName =~ s/\//_/g;
159: $graphName =~ s/\%/_/g;
160: $graphName =~ s/\:/_/g;
161: $graphName =~ s/\s/_/g;
162:
163: $csvName = $graphName;
164: $graphName .= ".gif";
165: $csvName .= ".csv";
166:
167: print STDERR "\t\tGenerating graph for \"$graphName\"\n";
168:
169: open(CSVOUTPUT, ">$csvName");
170: print CSVOUTPUT "Timestamp,$paramName:$metricName\n";
171:
172: my $i = 0;
173: foreach my $timeStamp (@domainData) {
174: print CSVOUTPUT "$domainData[$i],$rangeData[$i]\n";
175: $i++;
176: };
177:
178: close(CSVOUTPUT);
179:
180: my $dataMax = $stat->max();
181: my $dataMin = $stat->min();
182:
183: if ($dataMax < 1) {
184: $dataMax = 0.5;
185: };
186:
187: if ($dataMin > 0) {
188: $dataMin = 0;
189: };
190:
191: for (my $rangeIndex = 0; $rangeIndex < $#rangeData; $rangeIndex++) {
192: if ($rangeData[$rangeIndex] == 0) {
193: $rangeData[$rangeIndex] = $dataMax * 5;
194: };
195: };
196:
197: my @data = (\@domainData, \@rangeData);
198: my $tqGraph = GD::Graph::points->new(1024, int(768/2));
199: my $totalMeasurements = $#{$data[0]} + 1;
200:
201: $tqGraph->set(x_label_skip => int($#domainData/40),
202: x_labels_vertical => 1,
203: markers => [6],
204: marker_size => 2,
205: y_label => "$metricName",
206: y_min_value => $dataMin,
207: y_max_value => ceil($dataMax * 1.1),
208: title => "${hostName}:${paramName}:${metricName}, N = " . $stat->count(). "; avg = " . $stat->mean() . "; SD = " . $stat->standard_deviation() . "; 90th = " . $stat->percentile(90) . ".",
209: line_types => [1],
210: line_width => 1,
211: dclrs => ['red'],
212: ) or warn $tqGraph->error;
213:
214: $tqGraph->set_legend("TQ Measurement");
215: $tqGraph->set_legend_font(GD::gdMediumBoldFont);
216: $tqGraph->set_x_axis_font(GD::gdMediumBoldFont);
217: $tqGraph->set_y_axis_font(GD::gdMediumBoldFont);
218:
219: my $tqImage = $tqGraph->plot(\@data) or die $tqGraph->error;
220:
221: open(PICTURE, ">$graphName");
222: binmode PICTURE;
223: print PICTURE $tqImage->gif;
224: close(PICTURE);
225:
226: };
227:
228: } else {
229: print STDERR "#################### Nothing extracted for Hostname \"$hostName\" and metric \"$paramName\" ####################\n";
230: };
231:
232: };
233: };
234:
235: } else {
236: print STDERR "Could not find the TeamQuest Report file.\n";
237: };
238: } else {
239: print STDERR "Could not find the list of hostnames to run against.\n";
240: };
241: } else {
242: print STDERR "Could not find the TeamQuest Manager TQHarvest binary at \"$tqHarvestBinary\". Cannot continue.\n";
243: };
244:
245: sub dateSort {
246: my $a_value = dateToEpoch($a);
247: my $b_value = dateToEpoch($b);
248:
249: if ($a_value > $b_value) {
250: return 1;
251: } else {
252: if ($b_value > $a_value) {
253: return -1;
254: } else {
255: return 0;
256: };
257: };
258:
259: };
260:
261: sub dateToEpoch {
262: my ($timeStamp) = @_;
263: my ($dateString, $timeString) = split(/ /, $timeStamp);
264: my ($month, $day, $year) = split(/\//, $dateString);
265: my ($hour, $min, $sec) = split(/:/, $timeString);
266:
267: $year += 2000;
268: $month -= 1;
269:
270: return timegm($sec,$min,$hour,$day,$month,$year);
271:
272: };
273:
