DX Infrastructure Management

#!/usr/bin/perl

my $inputfile = @ARGV[0];my $i = 0;my @result;

open (INFILE, $inputfile);

$inputfile =~ s/.*\\(.*)-loggedflows\.txt/$1/g; #grab the harvester name

while () {chomp;if ($_ =~ m!.*(..../../.. ..:..:..).*Flows/Minute : (.*)!){@result[$i] = (["$1", $2]);$i += 1;}} #get the timestamp and fpm from each line of the file

close (INFILE);

open (OUTFILE, ">fpm.sql");

foreach $row (0..@result-1) {    print OUTFILE "replace into flows values ('$inputfile',unix_timestamp(str_to_date('$result[$row][0]','%Y/%c/%d %T')),$result[$row][1]);\n"; } #prepares a sql file for insertion into custom db.flows table

close (OUTFILE);

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

echo Analyzing Harvesters by flow count...

set hrvbyflows=analyze_harvesters_byflows

(

echo select content from ^(select concat^(

echo '[\'',date_format^(from_unixtime^(sp_start^),'%%b-%%e %%H:00'^),'\','

echo ,round^(avg^(case when ^(name like 'hrv1%%'^) then fpm else 0 end^)^),','

echo ,round^(avg^(case when ^(name like 'hrv2%%'^) then fpm else 0 end^)^),','

echo ,round^(avg^(case when ^(name like 'hrv3%%'^) then fpm else 0 end^)^),','

echo ,round^(avg^(case when ^(name like 'hrv4%%'^) then fpm else 0 end^)^),','

echo ,round^(avg^(case when ^(name like 'hrv5%%'^) then fpm else 0 end^)^),','

echo ,round^(avg^(case when ^(name like 'hrv6%%'^) then fpm else 0 end^)^),','

echo ,round^(avg^(case when ^(name like 'hrv7%%'^) then fpm else 0 end^)^),','

echo ,round^(avg^(case when ^(name like 'hrv8%%'^) then fpm else 0 end^)^),','

echo ,round^(avg^(case when ^(name like 'hrv9%%'^) then fpm else 0 end^)^),'],'^) as content

echo ,sp_start

echo from nfaparser.flows group by date_format^(from_unixtime^(sp_start^),'%%b-%%e %%H:00'^)

echo order by sp_start desc limit %historyhours%^) tmp

echo  order by sp_start asc;

) > %hrvbyflows%.sql

(

echo ^<div class="fullwidth widget" id="hrvbyflowsdiv1"^>

echo ^<H1 class="inline"^>Harvester %historydays% Day Flow Count History^</H1^>

echo ^<script type="text/javascript"^>

echo google.load^("visualization", "1", {packages:["corechart","table"]}^);google.setOnLoadCallback^(drawChart^);

echo function drawChart^(^) {

echo var fpm = google.visualization.arrayToDataTable^([

echo ['Harvester','HRV1','HRV2','HRV3','HRV4','HRV5','HRV6','HRV7','HRV8','HRV9'],

%querynfap% --skip-column-names --batch < %hrvbyflows%.sql

echo ]^);

echo var fpm_options = {title: 'Total Flows During Sample Period per Harvester',legend:{position: 'top'},isStacked: true,titlePosition: 'in'};

echo var chart = new google.visualization.LineChart^(document.getElementById^('fpm'^)^);

::echo var chart = new google.visualization.AreaChart^(document.getElementById^('fpm'^)^);

echo chart.draw^(fpm, fpm_options^);

echo var fpm_data = new google.visualization.DataTable^(^);

echo fpm_data.addColumn^('string','Date'^);

echo fpm_data.addColumn^('number','HRV1'^);

echo fpm_data.addColumn^('number','HRV2'^);

echo fpm_data.addColumn^('number','HRV3'^);

echo fpm_data.addColumn^('number','HRV4'^);

echo fpm_data.addColumn^('number','HRV5'^);

echo fpm_data.addColumn^('number','HRV6'^);

echo fpm_data.addColumn^('number','HRV7'^);

echo fpm_data.addColumn^('number','HRV8'^);

echo fpm_data.addColumn^('number','HRV9'^);

echo fpm_data.addRows^([

) >> %analysisoutfile%

(

echo select content from ^(select

echo concat_ws^(','

echo ,concat^('[\'',date_format^(from_unixtime^(sp_start^),'%%b-%%e'^),'\''^)

echo ,round^(avg^(case when ^(name like 'hrv1%%'^) then fpm else 0 end^)^)

echo ,round^(avg^(case when ^(name like 'hrv2%%'^) then fpm else 0 end^)^)

echo ,round^(avg^(case when ^(name like 'hrv3%%'^) then fpm else 0 end^)^)

echo ,round^(avg^(case when ^(name like 'hrv4%%'^) then fpm else 0 end^)^)

echo ,round^(avg^(case when ^(name like 'hrv5%%'^) then fpm else 0 end^)^)

echo ,round^(avg^(case when ^(name like 'hrv6%%'^) then fpm else 0 end^)^)

echo ,round^(avg^(case when ^(name like 'hrv7%%'^) then fpm else 0 end^)^)

echo ,round^(avg^(case when ^(name like 'hrv8%%'^) then fpm else 0 end^)^)

echo ,concat^(round^(avg^(case when ^(name like 'hrv9%%'^) then fpm else 0 end^)^),'],'^)^) as content

echo ,sp_start

echo from nfaparser.flows

echo group by date_format^(from_unixtime^(sp_start^),'%%b-%%e'^)

echo order by sp_start desc limit %historydays%^) tmp

echo order by sp_start desc;

) > %hrvbyflows%.sql

(

%querynfap% -N < %hrvbyflows%.sql

echo ]^);

echo var table = new google.visualization.Table^(document.getElementById^('fpm_data_table'^)^);

echo table.draw^(fpm_data, {alternatingRowStyle: true, page: 'enable', pageSize: 10, cssClassNames: {oddTableRow:'alternatetabrow', tableRow:'tabrow', hoverTableRow: 'hovertabrow', selectedTableRow: 'hovertabrow'}}^);

echo }

echo ^</script^>

echo ^<div id="fpm" class="google_chart"^>^</div^>

echo ^<div id='fpm_data_table' class='datatable'^>^</div^>

echo ^</div^>

) >> %analysisoutfile%