$dbh->do ("DELETE FROM Statinfo");
$dbh->do ("DELETE FROM DailyStat");
+ $dbh->do ("DELETE FROM LocalStat");
$dbh->do ("DELETE FROM DomainStat");
$dbh->do ("DELETE FROM VirusInfo");
$dbh->do ("DELETE FROM ClusterInfo WHERE name = 'lastmt_DomainStat'");
$dbh->do ("DELETE FROM ClusterInfo WHERE name = 'lastmt_DailyStat'");
+ $dbh->do ("DELETE FROM ClusterInfo WHERE name = 'lastmt_LocalStat'");
$dbh->do ("DELETE FROM ClusterInfo WHERE name = 'lastmt_VirusInfo'");
$dbh->commit;
my $cmds = "SELECT sum(CountIn) + $glcount AS count_in, sum(CountOut) AS count_out, " .
"sum (VirusIn) AS viruscount_in, sum (VirusOut) AS viruscount_out, " .
"sum (SpamIn) AS spamcount_in, sum (SpamOut) AS spamcount_out, " .
- "sum (BytesIn) AS bytes_in, sum (BytesOut) AS bytes_out, " .
+ "sum (BytesIn)*1024*1024 AS bytes_in, sum (BytesOut)*1024*1024 AS bytes_out, " .
"sum (BouncesIn) AS bounces_in, sum (BouncesOut) AS bounces_out, " .
"sum (GreylistCount) + $glcount as glcount, " .
"sum (SPFCount) as spfcount, " .
- "sum (RBLCount) as rblcount, " .
+ "sum (RBLCount) as rbl_rejects, " .
"sum(PTimeSum)/(sum(CountIn) + $glcount + sum(CountOut)) AS avptime " .
"FROM DailyStat where time >= $from and time < $to";
if (!$ref->{avptime}) {
$ref->{count_in} = $ref->{count_out} = $ref->{viruscount_in} = $ref->{viruscount_out} =
$ref->{spamcount_in} = $ref->{spamcount_out} = $ref->{glcount} = $ref->{spfcount} =
- $ref->{rblcount} = $ref->{bounces_in} = $ref->{bounces_out} = $ref->{bytes_in} =
+ $ref->{rbl_rejects} = $ref->{bounces_in} = $ref->{bounces_out} = $ref->{bytes_in} =
$ref->{bytes_out} = $ref->{avptime} = 0;
}
$ref->{count} = $ref->{count_in} + $ref->{count_out};
$ref->{junk_in} = $ref->{viruscount_in} + $ref->{spamcount_in} + $ref->{glcount} +
- $ref->{spfcount} + $ref->{rblcount};
+ $ref->{spfcount} + $ref->{rbl_rejects};
$ref->{junk_out} = $ref->{viruscount_out} + $ref->{spamcount_out};
}
sub total_domain_stat {
- my ($self, $rdb, $orderby) = @_;
-
- $orderby || ($orderby = 'domain');
- my $sortdir = sort_dir ($orderby);
+ my ($self, $rdb) = @_;
my ($from, $to) = $self->localdayspan();
my $query = "SELECT domain, SUM (CountIn) AS count_in, SUM (CountOut) AS count_out," .
- "SUM (BytesIn) AS mbytes_in, SUM (BytesOut) AS mbytes_out, " .
+ "SUM (BytesIn)*1024*1024 AS bytes_in, SUM (BytesOut)*1024*1024 AS bytes_out, " .
"SUM (VirusIn) AS viruscount_in, SUM (VirusOut) AS viruscount_out," .
"SUM (SpamIn) as spamcount_in, SUM (SpamOut) as spamcount_out " .
"FROM DomainStat where time >= $from AND time < $to " .
- "GROUP BY domain ORDER BY $orderby $sortdir, domain ASC";
+ "GROUP BY domain ORDER BY domain ASC";
my $sth = $rdb->{dbh}->prepare($query);
$sth->execute();
return "SELECT DISTINCT sender as worker FROM CStatistic WHERE $cond_good_mail AND NOT direction";
}
-sub sort_dir {
- my ($orderby) = @_;
-
- my $sortdir = ($orderby eq "virusinfo" || $orderby eq 'sender' || $orderby eq 'domain' || $orderby eq 'receiver') ? 'ASC' : 'DESC';
-
- return $sortdir;
-}
-
my $compute_sql_orderby = sub {
my ($sorters, $sort_default, $sort_always_prop) = @_;
return $res;
}
+sub postscreen_stat {
+ my ($self, $rdb) = @_;
+
+ my ($from, $to) = $self->localhourspan();
+ my $timezone = tz_local_offset();;
+
+ my $cmd = "SELECT " .
+ "sum(rblcount) as rbl_rejects, " .
+ "sum(PregreetCount) as pregreet_rejects " .
+ "FROM LocalStat WHERE time >= $from AND time < $to";
+
+ my $sth = $rdb->{dbh}->prepare($cmd);
+ $sth->execute();
+ my $res = $sth->fetchrow_hashref();
+ $sth->finish();
+
+ return $res;
+}
+
+sub postscreen_stat_graph {
+ my ($self, $rdb, $span) = @_;
+ my $res;
+
+ my ($from, $to) = $self->localhourspan();
+ my $timezone = tz_local_offset();;
+
+ my $cmd = "SELECT " .
+ "(time - $from) / $span AS index, " .
+ "sum(rblcount) as rbl_rejects, " .
+ "sum(PregreetCount) as pregreet_rejects " .
+ "FROM LocalStat WHERE time >= $from AND time < $to " .
+ "GROUP BY index ORDER BY index";
+
+ my $sth = $rdb->{dbh}->prepare($cmd);
+ $sth->execute ();
+
+ my $max_entry = int(($to - $from) / $span);
+ while (my $ref = $sth->fetchrow_hashref()) {
+ my $i = $ref->{index};
+ $res->[$i] = $ref;
+ $max_entry = $i if $i > $max_entry;
+ }
+
+ for my $i (0..$max_entry) {
+ $res->[$i] //= { index => $i, rbl_rejects => 0, pregreet_rejects => 0};
+
+ my $d = $res->[$i];
+ $d->{time} = $from + $i*$span - $timezone;
+ }
+ $sth->finish();
+
+ return $res;
+}
+
+sub recent_mailcount {
+ my ($self, $rdb, $span) = @_;
+ my $res;
+
+ my ($from, $to) = $self->timespan();
+
+ my $cmd = "SELECT".
+ "(time - $from) / $span AS index, ".
+ "COUNT (CASE WHEN direction THEN 1 ELSE NULL END) as count_in, ".
+ "COUNT (CASE WHEN NOT direction THEN 1 ELSE NULL END) as count_out, ".
+ "SUM (CASE WHEN direction THEN bytes ELSE 0 END) as bytes_in, ".
+ "SUM (CASE WHEN NOT direction THEN bytes ELSE 0 END) as bytes_out, ".
+ "SUM (ptime) / 1000.0 as ptimesum, ".
+ "COUNT (CASE WHEN virusinfo IS NOT NULL AND direction THEN 1 ELSE NULL END) as virus_in, ".
+ "COUNT (CASE WHEN virusinfo IS NOT NULL AND NOT direction THEN 1 ELSE NULL END) as virus_out, ".
+ "COUNT (CASE WHEN virusinfo IS NULL AND direction AND spamlevel >= 3 THEN 1 ELSE NULL END) as spam_in, ".
+ "COUNT (CASE WHEN virusinfo IS NULL AND NOT direction AND spamlevel >= 3 THEN 1 ELSE NULL END) as spam_out ".
+ "FROM cstatistic ".
+ "WHERE time >= $from AND time < $to ".
+ "GROUP BY index ORDER BY index";
+
+ my $sth = $rdb->{dbh}->prepare($cmd);
+
+ $sth->execute ();
+
+ while (my $ref = $sth->fetchrow_hashref()) {
+ @$res[$ref->{index}] = $ref;
+ }
+
+ $sth->finish();
+
+ my $c = int(($to - $from) / $span);
+
+ for (my $i = 0; $i < $c; $i++) {
+ @$res[$i] //= {
+ index => $i,
+ count => 0, count_in => 0, count_out => 0,
+ spam => 0, spam_in => 0, spam_out => 0,
+ virus => 0, virus_in => 0, virus_out => 0,
+ bytes => 0, bytes_in => 0, bytes_out => 0,
+ };
+
+ my $d = @$res[$i];
+
+ $d->{time} = $from + $i*$span;
+ $d->{count} = $d->{count_in} + $d->{count_out};
+ $d->{spam} = $d->{spam_in} + $d->{spam_out};
+ $d->{virus} = $d->{virus_in} + $d->{virus_out};
+ $d->{bytes} = $d->{bytes_in} + $d->{bytes_out};
+ $d->{timespan} = $span+0;
+ $d->{ptimesum} += 0;
+ }
+
+ return $res;
+}
+
+sub recent_receivers {
+ my ($self, $rdb, $limit) = @_;
+ my $res = [];
+
+ my ($from, $to) = $self->timespan();
+
+ my $cmd = "SELECT ".
+ "COUNT(receiver) as count, receiver ".
+ "FROM CStatistic, CReceivers ".
+ "WHERE time >= ? ".
+ "AND cid = cstatistic_cid ".
+ "AND rid = cstatistic_rid ".
+ "AND blocked = false ".
+ "AND direction = true ".
+ "GROUP BY receiver ORDER BY count DESC LIMIT ?;";
+
+ my $sth = $rdb->{dbh}->prepare($cmd);
+
+ $sth->execute ($from, $limit);
+
+ while (my $ref = $sth->fetchrow_hashref()) {
+ push @$res, $ref;
+ }
+ $sth->finish();
+
+ return $res;
+}
+
sub traffic_stat_graph {
my ($self, $rdb, $span) = @_;
my $res;
my $d = @$res[$i];
- $d->{time} = $from + ($i+1)*$span - $timezone;
+ $d->{time} = $from + $i*$span - $timezone;
$d->{count} = $d->{count_in} + $d->{count_out};
}
$sth->finish();
my ($from, $to) = $self->timespan();
my $timezone = tz_local_offset();;
- $from = (($from + $timezone)/86400) * 86400;
- $to = (($to + $timezone)/86400) * 86400;
+ $from = int(($from + $timezone)/86400) * 86400;
+ $to = int(($to + $timezone)/86400) * 86400;
$to += 86400 if $from == $to;
my ($from, $to) = $self->timespan();
my $timezone = tz_local_offset();;
- $from = (($from + $timezone)/3600) * 3600;
- $to = (($to + $timezone)/3600) * 3600;
+ $from = int(($from + $timezone)/3600) * 3600;
+ $to = int(($to + $timezone)/3600) * 3600;
$to += 3600 if $from == $to;