Hello all,
I'm here again with new trouble. I need to find any possible way to apply excel macro on excel file in SLES server, or to convert that macro somehow.
Could you please share your ideas, and help me with this? Thank you in advance
ps (attached macro is with changed extension, because xlsm not allowed)
Or maybe it is possible to do this without macro. If column C (ringnoanswer) is equal with column C (exitwithtimeout), then write in new sheet Columns F,G,I from RingNoAnswer
It seems converting some EXCEL macro to a shell script is not too attractive in these forums. May I propose a different approach: If you post the two sheets as .csv files (in fact, text files) and care- and detailfully describe the check/compare actions needed, chances are someone comes up with a shell/ sed / awk script that does what you need...
Or, in short, show the data you have, and then, show the data you want, and explain how they're related.
I have two sheets:
sheet one - ringnoanswer event:
Day Start Date Start Time End Date End Time Queue Operator Issue WaitTime
Mon 07 Nov 2016 09:12:48 EST 07 Nov 2016 09:13:10 EST 2041 Djamila Millien RINGNOANSWER 20000
Mon 07 Nov 2016 09:32:19 EST 07 Nov 2016 09:32:41 EST 2041 Djamila Millien RINGNOANSWER 20000
Mon 07 Nov 2016 09:35:48 EST 07 Nov 2016 09:36:11 EST 2021 Kimberly Paz RINGNOANSWER 20000
Mon 07 Nov 2016 09:39:15 EST 07 Nov 2016 09:39:40 EST 2041 Djamila Millien RINGNOANSWER 20000
Mon 07 Nov 2016 10:17:11 EST 07 Nov 2016 10:17:41 EST 2081 Deidra Wright RINGNOANSWER 20000
sheet two - exitwithtimeout event:
Day Start Date Start Time End Date End Time Queue Issue
Mon 07 Nov 2016 09:12:48 EST 07 Nov 2016 09:13:41 EST 2041 EXITWITHTIMEOUT 30
Mon 07 Nov 2016 09:16:42 EST 07 Nov 2016 09:17:41 EST 2081 EXITWITHTIMEOUT 30
Mon 07 Nov 2016 09:39:15 EST 07 Nov 2016 09:40:11 EST 2041 EXITWITHTIMEOUT 30
Mon 07 Nov 2016 10:17:11 EST 07 Nov 2016 10:17:41 EST 2081 EXITWITHTIMEOUT 30
I need to check column (start time) in both sheets. If there is match, I need to have output like this:
RNA Queue RNA Operator RNA WaitTime
2041 Djamila Millien 20000
2041 Djamila Millien 20000
2081 Deidra Wright 20000
Currently I have this script that I'm using to generate excel file in SLES
#!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Formula;
use Excel::Template;
use Text::CSV_XS;
my $name = 'call_report.xls';
my $file_ring = "/var/log/asterisk_agents_log/ringnoanswer_strip.log";
my $file_exitempty = "/var/log/asterisk_agents_log/exitempty_strip.log";
my $file_exitempty_of = "/var/log/asterisk_agents_log/exitempty-of_strip.log";
my $file_exittimeout = "/var/log/asterisk_agents_log/exitwithtimeout_strip.log";
my $file_exittimeout_of = "/var/log/asterisk_agents_log/exitwithtimeout-of_strip.log";
my $workbook = Spreadsheet::WriteExcel->new("/var/log/asterisk_agents_log/$name");
my $ringnoanswer = $workbook->addworksheet("RingNoAnswer");
my $exitempty = $workbook->addworksheet("ExitEmpty");
my $exitempty_of = $workbook->addworksheet("ExitEmpty-OF");
my $exitwithtimeout = $workbook->addworksheet("ExitWithTimeout");
my $exitwithtimeout_of = $workbook->addworksheet("ExitWithTimeout-OF");
my $calc = $workbook->addworksheet("Calc");
my @array_ring = ( 'Day','Start Date','Start Time', 'End Date','End Time', 'Queue', 'Operator', 'Issue', 'WaitTime' );
my @array_exittimeout = ( 'Day','Start Date','Start Time', 'End Date','End Time', 'Queue', 'Issue', '' );
my @array_exittimeout_of = ( 'Day','Start Date','Start Time', 'End Date','End Time', 'Queue', 'Issue', '' );
my @array_exitempty = ( 'Day','Start Date','Start Time', 'End Date','End Time', 'Queue', 'Issue' );
my @array_exitempty_of = ( 'Day','Start Date', 'Start Time', 'End Date', 'End Time', 'Queue', 'Issue' );
my @calc = ('RNA Queue','RNA Operator','RNA WaitTime', '', 'RNA Operator', 'Count');
my %cf = (
font => 'Arial',
border => 1,
border_color => 'black'
);
my %bold = (
bold => 1
);
my %ch = (
align => 'center',
valign => 'center',
font => 'Arial',
size => 11,
border => 1,
border_color => 'black',
bg_color => 'yellow'
);
my $ch = $workbook->addformat(%ch, %bold); # header
my $cn = $workbook->addformat(%cf, size => 10); # normal text
my $cs = $workbook->addformat(%cf, size => 8); # small text
$ringnoanswer->autofilter('A1:I1');
$exitempty->autofilter('A1:G1');
$exitempty_of->autofilter('A1:G1');
$exitwithtimeout->autofilter('A1:H1');
$exitwithtimeout_of->autofilter('A1:H1');
$calc->autofilter('A1:F1');
set_columns($ringnoanswer, "A",
[5, 16, 13, 16, 13, 45, 15, 15, 12],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($exitempty, "A",
[5, 16, 13, 16, 13, 45, 13],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($exitempty_of, "A",
[5, 16, 13, 16, 13, 25, 13],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($exitwithtimeout, "A",
[5, 16, 13, 16, 13, 45, 20, 3],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($exitwithtimeout_of, "A",
[5, 16, 13, 16, 13, 45, 20, 3],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($calc, "A",
[15, 25, 20, 40, 25, 5],
[$cn, $cn, $cn, $cn, $cn, $cn]);
$ringnoanswer->write('A1', [ @array_ring ], $ch);
$exitempty->write('A1', [ @array_exitempty ], $ch);
$exitempty_of->write('A1', [ @array_exitempty_of ], $ch);
$exitwithtimeout->write('A1', [ @array_exittimeout ], $ch);
$exitwithtimeout_of->write('A1', [ @array_exittimeout ], $ch);
$calc->write('A1', [ @calc ], $ch);
$calc->write('A3', 1.2345);
$calc->write('A4', '=SIN(PI()/4)');
$calc->write('C2', 'internal:ringnoanswer!B1');
$calc->write('D5', '=SUM(1, 2, 3)'); # Okay
$calc->write('D6', 1, '=SUM(1, 2, 3)');
write_from_log($ringnoanswer, $file_ring);
write_from_log($exitempty, $file_exitempty);
write_from_log($exitempty_of, $file_exitempty_of);
write_from_log($exitwithtimeout, $file_exittimeout);
write_from_log($exitwithtimeout_of, $file_exittimeout_of);
$ringnoanswer->activate(); # I'd like the second worksheet to be active
$workbook->close(); # play nice
exit(0);
print_setup($ringnoanswer);
print_setup($exitempty);
print_setup($exitempty_of);
print_setup($exitwithtimeout);
print_setup($exitwithtimeout_of);
print_setup($calc);
sub print_setup {
my $exitempty = shift;
$exitempty->set_landscape();
$exitempty->center_horizontally();
$exitempty->set_margins_LR(0.25);
$exitempty->set_margin_top(0.5);
$exitempty->set_margin_bottom(0.36);
$exitempty->set_footer('&L&D&R&P of &N', 0.17);
$exitempty->hide_gridlines();
}
sub set_columns {
my ($ws, $col, $width, $format, $hidden) = @_;
for (my $i=0; $i < @$width; $i++) {
my $column = "$col:$col";
$ws->set_column($column, $$width[$i], $$format[$i], $$hidden[$i]);
$col++;
}
}
sub write_from_log {
my ($worksheet, $file) = @_;
my $csv = Text::CSV_XS->new({
'quote_char' => '', # what? no quote character? you got it!
'escape_char' => '\\', # a backslash
'sep_char' => '|',
'binary' => 0
});
my $row = 1;
open (CSVFILE, "<", $file) || die "Unable to open $file for reading: $!, stopped";
while (<CSVFILE>) {
if ($csv->parse($_)) {
my @Fld = $csv->fields;
my $col = 0;
my $keep = 1;
if ($keep) {
foreach my $token (@Fld) {
if ($token =~ /^<img/) {
$token =~ /<img src="(.*)" loc="(.*)">/i;
my ($img, $loc) = ($1, $2);
$worksheet->insert_bitmap($loc, $img);
} else {
$worksheet->write($row, $col, $token);
}
$col++;
}
}
$row++ if ($keep);
} else {
my $err = $csv->error_input;
print "Text::CSV_XS parse() failed on argument: ", $err, "\n";
}
}
}
Those are not .csv - files. Howsoever, try
awk '
NR == FNR {T[$2,$3,$4,$5]
next
}
FNR == 1 {print "RNA Queue RNA Operator RNA WaitTime"
next
}
($2,$3,$4,$5) in T {print $12, $13, $14, $16
}
' file2 file1
RNA Queue RNA Operator RNA WaitTime
2041 Djamila Millien 20000
2041 Djamila Millien 20000
2081 Deidra Wright 20000
This works very well.
How I can make it write output if there is RingNoAnswer between Start-End time of ExitWithTimeout?
Like this:
EXITWITHTIMEOUT
17 Nov 2016,10:06:07 EST,17 Nov 2016,10:06:59 EST,2031,EXITWITHTIMEOUT,30
RINGNOANSWER
17 Nov 2016,10:05:53 EST,17 Nov 2016,10:06:29 EST,2021,Traci Dingman,RINGNOANSWER,20000
Output:
2021 Traci Dingman 20000
This is way more complex as it would require a full blown date time handling. Some awk versions offer functions for this, mine doesn't. You might be luckier with yours.
it it possible to use unix time?
Sorry?
Perl modules could be used to perform date arithmetic/comparison.
Here's a possible solution.
$
$
$ cat exitwithtimeout.txt
Day,Start Date,Start Time,End Date,End Time,Queue,Issue
Mon,07 Nov 2016,09:12:48 EST,07 Nov 2016,09:13:41 EST,2041,EXITWITHTIMEOUT,30
Mon,07 Nov 2016,09:16:42 EST,07 Nov 2016,09:17:41 EST,2081,EXITWITHTIMEOUT,30
Mon,07 Nov 2016,09:39:15 EST,07 Nov 2016,09:40:11 EST,2041,EXITWITHTIMEOUT,30
Mon,07 Nov 2016,10:17:11 EST,07 Nov 2016,10:17:41 EST,2081,EXITWITHTIMEOUT,30
Thu,17 Nov 2016,10:06:07 EST,17 Nov 2016,10:06:59 EST,2031,EXITWITHTIMEOUT,30
$
$ cat ringnoanswer.txt
Day,Start Date,Start Time,End Date,End Time,Queue,Operator,Issue,WaitTime
Mon,07 Nov 2016,09:12:48 EST,07 Nov 2016,09:13:10 EST,2041,Djamila Millien,RINGNOANSWER,20000
Mon,07 Nov 2016,09:32:19 EST,07 Nov 2016,09:32:41 EST,2041,Djamila Millien,RINGNOANSWER,20000
Mon,07 Nov 2016,09:35:48 EST,07 Nov 2016,09:36:11 EST,2021,Kimberly Paz,RINGNOANSWER,20000
Mon,07 Nov 2016,09:39:15 EST,07 Nov 2016,09:39:40 EST,2041,Djamila Millien,RINGNOANSWER,20000
Mon,07 Nov 2016,10:17:11 EST,07 Nov 2016,10:17:41 EST,2081,Deidra Wright,RINGNOANSWER,20000
Thu,17 Nov 2016,10:05:53 EST,17 Nov 2016,10:06:29 EST,2021,Traci Dingman,RINGNOANSWER,20000
$
$ cat -n compare_datetimes.pl
1 #!/usr/bin/perl -w
2 use strict;
3 use DateTime;
4
5 my %mth = qw(Jan 1 Feb 2 Mar 3 Apr 4 May 5 Jun 6
6 Jul 7 Aug 8 Sep 9 Oct 10 Nov 11 Dec 12);
7 my (@ewt_arr, @token, @sdt, @sts, @edt, @ets);
8 my $ewt_file = $ARGV[0];
9 my $rna_file = $ARGV[1];
10
11 # Read the exitwithtimeout file, parse and store the start and
12 # end datetimes in the ewt_arr array.
13 open(FH, "<", $ewt_file) or die "Can't open $ewt_file : $!";
14 while (<FH>) {
15 next if $. == 1;
16 chomp(@token = split(/,/, $_));
17 @sdt = split(/ /, $token[1]);
18 $sdt[1] = $mth{$sdt[1]};
19 @sts = split(/[: ]/, $token[2]);
20
21 @edt = split(/ /, $token[3]);
22 $edt[1] = $mth{$edt[1]};
23 @ets = split(/[: ]/, $token[4]);
24
25 push (@ewt_arr, [ DateTime->new(
26 year => $sdt[2],
27 month => $sdt[1],
28 day => $sdt[0],
29 hour => $sts[0],
30 minute => $sts[1],
31 second => $sts[2]
32 ),
33 DateTime->new(
34 year => $edt[2],
35 month => $edt[1],
36 day => $edt[0],
37 hour => $ets[0],
38 minute => $ets[1],
39 second => $ets[2]
40 )
41 ]);
42
43 }
44 close(FH) or die "Can't close $ewt_file : $!";
45
46 # Read the ringnoanswer file, capture the start and end datetimes
47 # and compare with each element of ewt_arr array. If there is an
48 # overlap, then print Queue, Operator and WaitTime values.
49 printf("%-10s %-20s %s\n", "RNA Queue","RNA Operator","RNA WaitTime");
50 open(FH, "<", $rna_file) or die "Can't open $rna_file : $!";
51 while (<FH>) {
52 next if $. == 1;
53 chomp(@token = split(/,/, $_));
54 @sdt = split(/ /, $token[1]);
55 $sdt[1] = $mth{$sdt[1]};
56 @sts = split(/[: ]/, $token[2]);
57
58 @edt = split(/ /, $token[3]);
59 $edt[1] = $mth{$edt[1]};
60 @ets = split(/[: ]/, $token[4]);
61
62 my $sdate = DateTime->new(
63 year => $sdt[2],
64 month => $sdt[1],
65 day => $sdt[0],
66 hour => $sts[0],
67 minute => $sts[1],
68 second => $sts[2]
69 );
70 my $edate = DateTime->new(
71 year => $edt[2],
72 month => $edt[1],
73 day => $edt[0],
74 hour => $ets[0],
75 minute => $ets[1],
76 second => $ets[2]
77 );
78 foreach my $item (@ewt_arr) {
79 if ( DateTime->compare($edate, $item->[0]) >= 0 and
80 DateTime->compare($sdate, $item->[1]) <= 0 ) {
81 printf("%-10s %-20s %s\n", $token[5], $token[6], $token[8]);
82 }
83 }
84 }
85 close(FH) or die "Can't close $rna_file $!";
86
$
$ perl compare_datetimes.pl exitwithtimeout.txt ringnoanswer.txt
RNA Queue RNA Operator RNA WaitTime
2041 Djamila Millien 20000
2041 Djamila Millien 20000
2081 Deidra Wright 20000
2021 Traci Dingman 20000
$
$
I should've added script comments for lines 79 and 80.
Basically, if we have two date intervals like so:
[sd1, ed1] ==> date interval with start date sd1 and end date ed1
[sd2, ed2] ==> date interval with start date sd2 and end date ed2
then an "overlap" occurs if the following condition is true:
ed2 >= sd1 and sd2 <= ed1
I capture [sd2, ed2] in each line of ringnoanswer.txt and compare that interval with
the one stored in ewt_arr array. Each element of ewt_arr is an array reference with the
first element being sd1 and the second element being ed1.
Maybe I can use these:
1479585616|1479585585.136581|2041|NONE|EXITWITHTIMEOUT|1|1|30
1479585687|1479585657.136613|2031|NONE|EXITWITHTIMEOUT|1|1|30
1479586178|1479586148.136659|2081|NONE|EXITWITHTIMEOUT|1|1|30
1479587086|1479587055.136842|2011|NONE|EXITWITHTIMEOUT|1|1|30
1479586178|1479586148.136659|2081|Mary Ellen Buckley|RINGNOANSWER|5000
1479587070|1479587055.136842|2011|Nashid Ali|RINGNOANSWER|14000
1479587073|1479587055.136842|2011|Mary Ellen Buckley|RINGNOANSWER|17000
1479587086|1479587055.136842|2011|Nashid Ali|RINGNOANSWER|8000
1479587717|1479587697.136936|2021|Traci Dingman|RINGNOANSWER|20000
OK, that should be much easier. How come the start time is later than the end time in all cases above? And, which time (start or end) of
should be considered?
Let's assume $1 is the epoch time to be considered - try
awk -F"|" '
NR == FNR {EN[$1] = $2
next
}
BEGIN {print "RNA Queue RNA Operator RNA WaitTime"
}
{for (e in EN) if ($1 <= e && $1 >= EN[e]) print $3, $4, $6
}
' file2 file1
RNA Queue RNA Operator RNA WaitTime
2081 Mary Ellen Buckley 5000
2011 Nashid Ali 14000
2011 Mary Ellen Buckley 17000
2011 Nashid Ali 8000
Hi all,
I'd like to thank RudiC, and durden_tyler for help. I'm wondering how to improve those scripts, also how to add formulas in perl script...
#!/bin/bash
mysql -u select -D zabbix -e "select clock,value from history_log where itemid=25152;" |
grep -v -e endpoint -e ADDMEMBER -e REMOVEMEMBER -e '|020' |
grep EXITWITHTIMEOUT > "/tmp/exitwithtimeout.txt"
mysql -u select -D zabbix -e "select clock,value from history_log where itemid=25152;" |
grep -v -e endpoint -e ADDMEMBER -e REMOVEMEMBER -e '|20[0-9]' -e '|100' -e '|300' -e '|400' |
grep EXITWITHTIMEOUT > "/tmp/exitwithtimeout-of.txt"
mysql -u select -D zabbix -e "select clock,value from history_log where itemid=25143;" |
grep -v -e endpoint -e ADDMEMBER -e REMOVEMEMBER |
grep RINGNOANSWER > "/tmp/ringnoanswer.txt"
mysql -u select -D zabbix -e "select clock,value from history_log where itemid=25161;" |
grep -v -e endpoint -e ADDMEMBER -e REMOVEMEMBER -e '|020' |
grep EXITEMPTY > "/tmp/exitempty.txt"
mysql -u select -D zabbix -e "select clock,value from history_log where itemid=25161;" |
grep -v -e endpoint -e ADDMEMBER -e REMOVEMEMBER -e '|20[0-9]' -e '|100' -e '|300' -e '|400' |
grep EXITEMPTY > "/tmp/exitempty-of.txt"
sleep 2
cat /tmp/exitwithtimeout.txt |
awk -F '[|]' 'BEGIN {OFS="|"} {print strftime("%a| %d %b %Y| %H:%M:%S %Z|",$2) strftime("%d %b %Y| %H:%M:%S %Z|",$1) $3,$5,$8; }' > /var/log/asterisk_agents_log/exitwithtimeout.log
cat /tmp/exitwithtimeout-of.txt |
awk -F '[|]' 'BEGIN {OFS="|"} {print strftime("%a| %d %b %Y| %H:%M:%S %Z|",$2) strftime("%d %b %Y| %H:%M:%S %Z|",$1) $3,$5,$8; }' > /var/log/asterisk_agents_log/exitwithtimeout-of.log
cat /tmp/ringnoanswer.txt |
awk -F '[|]' 'BEGIN {OFS="|" } {print strftime("%a| %d %b %Y| %H:%M:%S %Z|",$2) strftime(" %d %b %Y| %H:%M:%S %Z|",$1) $3,$4,$5,$6;'} |
awk -F '[|]' -v x=14000 '$9 > x' > /var/log/asterisk_agents_log/ringnoanswer.log
cat /tmp/exitempty.txt |
awk -F '[|]' 'BEGIN {OFS="|"} { print strftime("%a| %d %b %Y| %H:%M:%S %Z|",$2) strftime("%d %b %Y| %H:%M:%S %Z|",$1) $3,$5; }' > /var/log/asterisk_agents_log/exitempty.log
cat /tmp/exitempty-of.txt |
awk -F '[|]' 'BEGIN {OFS="|"} { print strftime("%a| %d %b %Y| %H:%M:%S %Z|",$2) strftime("%d %b %Y| %H:%M:%S %Z|",$1) $3,$5; }' > /var/log/asterisk_agents_log/exitempty-of.log
f1=`tail -n 400 /tmp/exitwithtimeout.txt | awk -F '[|\t]' 'BEGIN {OFS="|"} { print $2,$3,$4,$5,$6,$7 }' > /tmp/exitwithtimeout_100lines.txt`;
f2=`tail -n 400 /tmp/ringnoanswer.txt | awk -F '[|\t]' 'BEGIN {OFS="|"} { print $2,$3,$4,$5,$6,$7 }' > /tmp/ringnoanswer_100lines.txt`;
file1="/tmp/ringnoanswer_100lines.txt";
file2="/tmp/exitwithtimeout_100lines.txt";
awk -F"|" '
NR == FNR {EN[$1] = $2
next
}
BEGIN {OFS="|"}
{for (e in EN) if ($1 <= e && $1 >= EN[e]) print $2,$3,$4,$5,$6,$7
}
' $file2 $file1 > /tmp/rna.log
cat /tmp/rna.log | awk -F '[|]' 'BEGIN {OFS="|" } {print strftime(" %d %b %Y| %H:%M:%S %Z|",$1) $2,$3,$4,$5,$6;'} |
awk -F '[|]' -v x=14000 '$6 > x' |
sort -u > /var/log/asterisk_agents_log/rna.log
#!/bin/bash
cat /var/log/asterisk_agents_log/ringnoanswer.log |
grep "$(date --date '-1 days' +'%d %b')" |
sed 's/|1001|/|1001-Name1|/g;
s/|1011|/|1011-Name2|/g;
s/|03000|/|03000-Name3|/g;
s/|04001|/|04001-Name4|/g' > /var/log/asterisk_agents_log/ringnoanswer_strip.log
cat /var/log/asterisk_agents_log/exitempty.log |
grep "$(date --date '-1 days' +'%d %b')"|
sed 's/|1001|/|1001-Name1|/g;
s/|1011|/|1011-Name2|/g;
s/|03000|/|03000-Name3|/g;
s/|04001|/|04001-Name4|/g' > /var/log/asterisk_agents_log/exitempty_strip.log
cat /var/log/asterisk_agents_log/exitempty-of.log |
grep "$(date --date '-1 days' +'%d %b')" |
sed 's/|1001|/|1001-Name1|/g;
s/|1011|/|1011-Name2|/g;
s/|03000|/|03000-Name3|/g;
s/|04001|/|04001-Name4|/g' > /var/log/asterisk_agents_log/exitempty-of_strip.log
cat /var/log/asterisk_agents_log/exitwithtimeout.log |
grep "$(date --date '-1 days' +'%d %b')" |
sed 's/|1001|/|1001-Name1|/g;
s/|1011|/|1011-Name2|/g;
s/|03000|/|03000-Name3|/g;
s/|04001|/|04001-Name4|/g' > /var/log/asterisk_agents_log/exitwithtimeout_strip.log
cat /var/log/asterisk_agents_log/exitwithtimeout-of.log |
grep "$(date --date '-1 days' +'%d %b')" |
sed 's/|1001|/|1001-Name1|/g;
s/|1011|/|1011-Name2|/g;
s/|03000|/|03000-Name3|/g;
s/|04001|/|04001-Name4|/g' > /var/log/asterisk_agents_log/exitwithtimeout-of_strip.log
cat /var/log/asterisk_agents_log/rna.log |
grep "$(date --date '-1 days' +'%d %b')" |
sed 's/|1001|/|1001-Name1|/g;
s/|1011|/|1011-Name2|/g;
s/|03000|/|03000-Name3|/g;
s/|04001|/|04001-Name4|/g' > /var/log/asterisk_agents_log/rna_strip.log
#!/usr/bin/perl
use strict;
use warnings;
use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Formula;
use Excel::Template;
use Text::CSV_XS;
my $name = 'call_report.xls';
# other files we are reading
my $file_ring = "/var/log/asterisk_agents_log/ringnoanswer_strip.log";
my $file_exitempty = "/var/log/asterisk_agents_log/exitempty_strip.log";
my $file_exitempty_of = "/var/log/asterisk_agents_log/exitempty-of_strip.log";
my $file_exittimeout = "/var/log/asterisk_agents_log/exitwithtimeout_strip.log";
my $file_exittimeout_of = "/var/log/asterisk_agents_log/exitwithtimeout-of_strip.log";
my $file_rna = "/var/log/asterisk_agents_log/rna_strip.log";
my $workbook = Spreadsheet::WriteExcel->new("/var/log/asterisk_agents_log/$name");
# worksheets
my $ringnoanswer = $workbook->addworksheet("RingNoAnswer");
my $exitempty = $workbook->addworksheet("ExitEmpty");
my $exitempty_of = $workbook->addworksheet("ExitEmpty-OF");
my $exitwithtimeout = $workbook->addworksheet("ExitWithTimeout");
my $exitwithtimeout_of = $workbook->addworksheet("ExitWithTimeout-OF");
my $rna = $workbook->addworksheet("RNA");
my @array_ring = ( 'Day','Start Date','Start Time', 'End Date','End Time', 'Queue', 'Operator', 'Issue', 'WaitTime' );
my @array_exittimeout = ( 'Day','Start Date','Start Time', 'End Date','End Time', 'Queue', 'Issue', '' );
my @array_exittimeout_of = ( 'Day','Start Date','Start Time', 'End Date','End Time', 'Queue', 'Issue', '' );
my @array_exitempty = ( 'Day','Start Date','Start Time', 'End Date','End Time', 'Queue', 'Issue' );
my @array_exitempty_of = ( 'Day','Start Date', 'Start Time', 'End Date', 'End Time', 'Queue', 'Issue' );
my @array_rna = ('Date', 'Time', 'Queue', 'RNA Operator', 'RNA Event', 'RNA WaitTime', '', 'RNA Operator', 'Count');
my %cf = (
font => 'Arial',
border => 1,
border_color => 'black'
);
my %bold = (
bold => 1
);
my %ch = (
align => 'center',
valign => 'center',
font => 'Arial',
size => 11,
border => 1,
border_color => 'black',
bg_color => 'yellow'
);
my $ch = $workbook->addformat(%ch, %bold); # header
my $cn = $workbook->addformat(%cf, size => 10); # normal text
my $cs = $workbook->addformat(%cf, size => 8); # small text
$ringnoanswer->autofilter('A1:I1');
$exitempty->autofilter('A1:G1');
$exitempty_of->autofilter('A1:G1');
$exitwithtimeout->autofilter('A1:H1');
$exitwithtimeout_of->autofilter('A1:H1');
$rna->autofilter('A1:H1');
set_columns($ringnoanswer, "A",
[8, 16, 13, 16, 13, 45, 15, 15, 12],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($exitempty, "A",
[8, 16, 13, 16, 13, 45, 13],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($exitempty_of, "A",
[8, 16, 13, 16, 13, 25, 13],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($exitwithtimeout, "A",
[8, 16, 13, 16, 13, 45, 20, 3],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($exitwithtimeout_of, "A",
[8, 16, 13, 16, 13, 45, 20, 3],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn]);
set_columns($rna, "A",
[15, 15, 45, 20, 15, 15, ,50, 25, 5],
[$cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn, $cn]);
$ringnoanswer->write('A1', [ @array_ring ], $ch);
$exitempty->write('A1', [ @array_exitempty ], $ch);
$exitempty_of->write('A1', [ @array_exitempty_of ], $ch);
$exitwithtimeout->write('A1', [ @array_exittimeout ], $ch);
$exitwithtimeout_of->write('A1', [ @array_exittimeout ], $ch);
$rna->write('A1', [ @array_rna ], $ch);
# I'm trying to use formula for those columns below. Unfortunately not working
#$rna->write_formula('H2', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H1, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H3', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H2, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H4', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H3, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H5', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H4, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H6', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H5, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H7', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H6, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H8', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H7, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H9', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H8, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H10', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H9, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H11', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H10, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H12', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H11, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H13', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H12, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H14', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H13, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H15', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H14, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H16', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H15, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H17', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H16, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H18', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H17, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H19', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H18, $D$3:$D$150)), 0)),"")');
#$rna->write_string('H20', '=IFERROR(INDEX($D$3:$D$150, MATCH(0,IF(ISBLANK($D$3:$D$150),1,COUNTIF($H$1:H19, $D$3:$D$150)), 0)),"")');
#$rna->write_string('I2', '=COUNTIFS($D$1:$D$150,$H2)');
#$rna->write_string('I3', '=COUNTIFS($D$1:$D$150,$H3)');
#$rna->write_string('I4', '=COUNTIFS($D$1:$D$150,$H4)');
#$rna->write_string('I5', '=COUNTIFS($D$1:$D$150,$H5)');
#$rna->write_string('I6', '=COUNTIFS($D$1:$D$150,$H6)');
#$rna->write_string('I7', '=COUNTIFS($D$1:$D$150,$H7)');
#$rna->write_string('I8', '=COUNTIFS($D$1:$D$150,$H8)');
#$rna->write_string('I9', '=COUNTIFS($D$1:$D$150,$H9)');
#$rna->write_string('I10', '=COUNTIFS($D$1:$D$150,$H10)');
#$rna->write_string('I11', '=COUNTIFS($D$1:$D$150,$H11)');
#$rna->write_string('I12', '=COUNTIFS($D$1:$D$150,$H12)');
#$rna->write_string('I13', '=COUNTIFS($D$1:$D$150,$H13)');
#$rna->write_string('I14', '=COUNTIFS($D$1:$D$150,$H14)');
#$rna->write_string('I15', '=COUNTIFS($D$1:$D$150,$H15)');
#$rna->write_string('I16', '=COUNTIFS($D$1:$D$150,$H16)');
#$rna->write_string('I17', '=COUNTIFS($D$1:$D$150,$H17)');
#$rna->write_string('I18', '=COUNTIFS($D$1:$D$150,$H18)');
#$rna->write_string('I19', '=COUNTIFS($D$1:$D$150,$H19)');
#$rna->write_string('I20', '=COUNTIFS($D$1:$D$150,$H20)');
write_from_log($ringnoanswer, $file_ring);
write_from_log($exitempty, $file_exitempty);
write_from_log($exitempty_of, $file_exitempty_of);
write_from_log($exitwithtimeout, $file_exittimeout);
write_from_log($exitwithtimeout_of, $file_exittimeout_of);
write_from_log($rna, $file_rna);
$ringnoanswer->activate();
$workbook->close();
exit(0);
print_setup($ringnoanswer);
print_setup($exitempty);
print_setup($exitempty_of);
print_setup($exitwithtimeout);
print_setup($exitwithtimeout_of);
print_setup($rna);
sub print_setup {
my $exitempty = shift;
$exitempty->set_landscape();
$exitempty->center_horizontally();
$exitempty->set_margins_LR(0.25);
$exitempty->set_margin_top(0.5);
$exitempty->set_margin_bottom(0.36);
$exitempty->set_footer('&L&D&R&P of &N', 0.17);
$exitempty->hide_gridlines();
}
sub set_columns {
my ($ws, $col, $width, $format, $hidden) = @_;
for (my $i=0; $i < @$width; $i++) {
my $column = "$col:$col";
$ws->set_column($column, $$width[$i], $$format[$i], $$hidden[$i]);
$col++;
}
}
sub write_from_log {
my ($worksheet, $file) = @_;
# Create a new CSV parsing ojbect with the CSV options that I needed
my $csv = Text::CSV_XS->new({
'quote_char' => '', # what? no quote character? you got it!
'escape_char' => '\\', # a backslash
'sep_char' => '|',
'binary' => 0
});
# Row and column are zero indexed!
my $row = 1;
open (CSVFILE, "<", $file) || die "Unable to open $file for reading: $!, stopped";
while (<CSVFILE>) {
if ($csv->parse($_)) {
my @Fld = $csv->fields;
my $col = 0;
my $keep = 1;
if ($keep) {
foreach my $token (@Fld) {
if ($token =~ /^<img/) {
$token =~ /<img src="(.*)" loc="(.*)">/i;
my ($img, $loc) = ($1, $2);
$worksheet->insert_bitmap($loc, $img);
} else {
$worksheet->write($row, $col, $token);
}
$col++;
}
}
$row++ if ($keep);
} else {
my $err = $csv->error_input;
print "Text::CSV_XS parse() failed on argument: ", $err, "\n";
}
}
}
/tmp/exitwithtimeout.txt
1480420104 1480420084|1480420054.441|2082|NONE|EXITWITHTIMEOUT|1|1|30
1480420886 1480420859|1480420829.500|2051|NONE|EXITWITHTIMEOUT|1|1|30
1480422627 1480422601|1480422571.690|2051|NONE|EXITWITHTIMEOUT|1|1|30
1480422988 1480422985|1480422954.728|2081|NONE|EXITWITHTIMEOUT|1|1|30
1480429716 1480429713|1480429683.2041|2051|NONE|EXITWITHTIMEOUT|1|1|30
1480431067 1480431051|1480430991.2388|2061|NONE|EXITWITHTIMEOUT|1|1|60
1480431667 1480431649|1480431619.2528|2051|NONE|EXITWITHTIMEOUT|1|1|30
1480432387 1480432358|1480432328.2700|2041|NONE|EXITWITHTIMEOUT|1|1|30
1480432598 1480432571|1480432510.2761|2021|NONE|EXITWITHTIMEOUT|1|1|60
1480432748 1480432749|1480432718.2794|2071|NONE|EXITWITHTIMEOUT|1|1|30
1480432899 1480432875|1480432845.2830|2031|NONE|EXITWITHTIMEOUT|1|1|30
1480432899 1480432875|1480432845.2829|2041|NONE|EXITWITHTIMEOUT|1|1|30
1480432929 1480432920|1480432859.2839|2021|NONE|EXITWITHTIMEOUT|1|1|60
1480432959 1480432935|1480432905.2858|2041|NONE|EXITWITHTIMEOUT|1|1|30
1480433020 1480433005|1480432975.2876|2031|NONE|EXITWITHTIMEOUT|1|1|30
1480433170 1480433152|1480433092.2895|2021|NONE|EXITWITHTIMEOUT|1|1|60
1480433620 1480433595|1480433565.3025|2051|NONE|EXITWITHTIMEOUT|1|1|30
1480434253 1480434224|1480434193.3245|2071|NONE|EXITWITHTIMEOUT|1|1|30
/tmp/exitwithtimeout-of.txt
1472176907 1472176891|1472176889.350477|02071|NONE|EXITWITHTIMEOUT|1|1|1
1472179642 1472179635|1472179625.350873|02041|NONE|EXITWITHTIMEOUT|1|1|10
1472211002 1472210984|1472210973.352670|02031|NONE|EXITWITHTIMEOUT|1|1|10
1472211391 1472211363|1472211353.352721|02031|NONE|EXITWITHTIMEOUT|1|1|10
1472212203 1472212199|1472212188.352837|02031|NONE|EXITWITHTIMEOUT|1|1|10
1472214965 1472214963|1472214962.353192|02071|NONE|EXITWITHTIMEOUT|1|1|1
1472214995 1472214979|1472214977.353196|02071|NONE|EXITWITHTIMEOUT|1|1|1
1472216170 1472216166|1472216156.353389|02031|NONE|EXITWITHTIMEOUT|1|1|10
1472216889 1472216880|1472216870.353537|02041|NONE|EXITWITHTIMEOUT|1|1|10
1472217940 1472217933|1472217922.353742|02051|NONE|EXITWITHTIMEOUT|1|1|10
1472218029 1472218023|1472218013.353763|02051|NONE|EXITWITHTIMEOUT|1|1|10
1472218690 1472218686|1472218676.353889|02031|NONE|EXITWITHTIMEOUT|1|1|10
1472218720 1472218693|1472218683.353896|02031|NONE|EXITWITHTIMEOUT|1|2|10
1472218929 1472218915|1472218905.353952|02051|NONE|EXITWITHTIMEOUT|1|1|10
1472219020 1472219007|1472218996.353991|02051|NONE|EXITWITHTIMEOUT|1|1|10
1472219140 1472219112|1472219101.354026|02041|NONE|EXITWITHTIMEOUT|1|1|10
1472219321 1472219297|1472219287.354093|02041|NONE|EXITWITHTIMEOUT|1|1|10
1472219501 1472219472|1472219462.354161|02031|NONE|EXITWITHTIMEOUT|1|1|10
1472219801 1472219787|1472219776.354239|02051|NONE|EXITWITHTIMEOUT|1|1|10
1472219861 1472219848|1472219837.354255|02041|NONE|EXITWITHTIMEOUT|1|1|10
1472219951 1472219926|1472219916.354280|02031|NONE|EXITWITHTIMEOUT|1|1|10
1472220281 1472220266|1472220256.354356|02051|NONE|EXITWITHTIMEOUT|1|1|10
1472220520 1472220520|1472220510.354426|02041|NONE|EXITWITHTIMEOUT|1|1|10
1472220550 1472220550|1472220540.354433|02041|NONE|EXITWITHTIMEOUT|1|1|10
1474204951 1474204937|1474204862.28350|1011|NONE|EXITWITHTIMEOUT|1|1|70
1474559430 1474559430|1474559335.41591|1011|NONE|EXITWITHTIMEOUT|1|1|70
/tmp/ringnoanswer.txt
1480414158 1480414145|1480414124.129|2001|Tennille Rivas|RINGNOANSWER|20000
1480420886 1480420859|1480420829.500|2051|Traci Dingman|RINGNOANSWER|8000
1480422025 1480422017|1480421997.639|2021|Traci Dingman|RINGNOANSWER|20000
1480422988 1480422975|1480422954.728|2081|Tennille Rivas|RINGNOANSWER|20000
1480422988 1480422985|1480422954.728|2081|Nikita Hill|RINGNOANSWER|5000
1480426230 1480426229|1480426209.1197|2001|Nikita Hill|RINGNOANSWER|20000
1480427673 1480427654|1480427633.1569|2051|Traci Dingman|RINGNOANSWER|20000
1480428212 1480428208|1480428187.1686|2001|Tennille Rivas|RINGNOANSWER|20000
1480429716 1480429703|1480429683.2041|2051|Traci Dingman|RINGNOANSWER|20000
1480429716 1480429713|1480429683.2041|2051|Traci Dingman|RINGNOANSWER|5000
1480430796 1480430779|1480430758.2301|2021|Traci Dingman|RINGNOANSWER|20000
1480431667 1480431639|1480431619.2528|2051|Traci Dingman|RINGNOANSWER|20000
1480431667 1480431649|1480431619.2528|2051|Conrad Arizaga|RINGNOANSWER|4000
1480432358 1480432348|1480432328.2700|2041|Traci Dingman|RINGNOANSWER|20000
1480432358 1480432352|1480432345.2705|2081|Djamila Millien|RINGNOANSWER|7000
1480432387 1480432367|1480432355.2715|2011|Djamila Millien|RINGNOANSWER|6000
1480433109 1480433086|1480433068.2884|2011|Nikita Hill|RINGNOANSWER|17000
1480434253 1480434224|1480434193.3245|2071|Traci Dingman|RINGNOANSWER|25000
1480434793 1480434772|1480434747.3425|2061|Nikita Hill|RINGNOANSWER|20000
1480435995 1480435979|1480435954.3826|2061|Djamila Millien|RINGNOANSWER|20000
1480436145 1480436140|1480436120.3891|2081|Djamila Millien|RINGNOANSWER|20000
1480436325 1480436317|1480436297.3951|2021|Irvlyne Mellon|RINGNOANSWER|20000
1480436354 1480436330|1480436310.3959|2081|Djamila Millien|RINGNOANSWER|20000
1480436354 1480436340|1480436310.3959|2081|Nikita Hill|RINGNOANSWER|5000
1480436384 1480436362|1480436335.3976|2011|Nikita Hill|RINGNOANSWER|17000
1480436415 1480436411|1480436380.4001|2071|Irvlyne Mellon|RINGNOANSWER|30000
1480437616 1480437599|1480437579.4382|2041|Irvlyne Mellon|RINGNOANSWER|20000
1480438186 1480438173|1480438153.4598|2061|Emily Jackson|RINGNOANSWER|20000
/tmp/exitempty.txt
1480362658 1480362639|1480362639.237190|2091|NONE|EXITEMPTY|1|1|0
1480362658 1480362639|1480362639.237190|2091|NONE|EXITEMPTY|1|1|0
1480363018 1480362991|1480362991.237286|2091|NONE|EXITEMPTY|1|1|0
1480363018 1480362991|1480362991.237286|2091|NONE|EXITEMPTY|1|1|0
1480363108 1480363083|1480363083.237332|2091|NONE|EXITEMPTY|1|1|0
1480363108 1480363083|1480363083.237332|2091|NONE|EXITEMPTY|1|1|0
1480363138 1480363123|1480363123.237345|2091|NONE|EXITEMPTY|1|1|0
1480363138 1480363123|1480363123.237345|2091|NONE|EXITEMPTY|1|1|0
1480363318 1480363309|1480363309.237391|2091|NONE|EXITEMPTY|1|1|0
1480363318 1480363309|1480363309.237391|2091|NONE|EXITEMPTY|1|1|0
1480363738 1480363710|1480363709.237483|2091|NONE|EXITEMPTY|1|1|0
1480363738 1480363710|1480363709.237483|2091|NONE|EXITEMPTY|1|1|0
1480363738 1480363720|1480363720.237487|2091|NONE|EXITEMPTY|1|1|0
1480363738 1480363720|1480363720.237487|2091|NONE|EXITEMPTY|1|1|0
1480363859 1480363842|1480363842.237499|2091|NONE|EXITEMPTY|1|1|0
1480363859 1480363842|1480363842.237499|2091|NONE|EXITEMPTY|1|1|0
1480384732 1480384717|1480384717.241597|2011|NONE|EXITEMPTY|1|1|0
1480384732 1480384717|1480384717.241597|2011|NONE|EXITEMPTY|1|1|0
1480384762 1480384752|1480384752.241605|2061|NONE|EXITEMPTY|1|1|0
1480384762 1480384752|1480384752.241605|2061|NONE|EXITEMPTY|1|1|0
/tmp/exitempty-of.txt
1480436985 1480436971|1480436970.4198|02041|NONE|EXITEMPTY|1|1|0
1480436985 1480436971|1480436970.4198|02041|NONE|EXITEMPTY|1|1|0
1480437406 1480437389|1480437389.4323|02021|NONE|EXITEMPTY|1|1|0
1480437406 1480437389|1480437389.4323|02021|NONE|EXITEMPTY|1|1|0
1480437406 1480437402|1480437402.4335|02082|NONE|EXITEMPTY|1|1|0
1480437406 1480437402|1480437402.4335|02082|NONE|EXITEMPTY|1|1|0
1480438155 1480438149|1480438149.4588|02051|NONE|EXITEMPTY|1|1|0
1480438155 1480438149|1480438149.4588|02051|NONE|EXITEMPTY|1|1|0
1480438155 1480438153|1480438152.4594|02041|NONE|EXITEMPTY|1|1|0
1480438155 1480438153|1480438152.4594|02041|NONE|EXITEMPTY|1|1|0
1480438186 1480438178|1480438178.4602|02051|NONE|EXITEMPTY|1|1|0
1480438186 1480438178|1480438178.4602|02051|NONE|EXITEMPTY|1|1|0
1480438305 1480438279|1480438279.4628|02041|NONE|EXITEMPTY|1|1|0
1480438305 1480438279|1480438279.4628|02041|NONE|EXITEMPTY|1|1|0
1480438366 1480438357|1480438357.4662|02021|NONE|EXITEMPTY|1|1|0
1480438366 1480438357|1480438357.4662|02021|NONE|EXITEMPTY|1|1|0
1480438366 1480438358|1480438358.4666|02021|NONE|EXITEMPTY|1|1|0
1480438366 1480438358|1480438358.4666|02021|NONE|EXITEMPTY|1|1|0