]>
Commit | Line | Data |
---|---|---|
5414dee4 | 1 | package PMG::Statistic; |
e350fb98 DM |
2 | |
3 | use strict; | |
5414dee4 | 4 | use warnings; |
e350fb98 | 5 | use DBI; |
e350fb98 DM |
6 | use Time::Local; |
7 | use Time::Zone; | |
8 | ||
5414dee4 DM |
9 | use PVE::SafeSyslog; |
10 | ||
9f67f5b3 | 11 | use PMG::ClusterConfig; |
5414dee4 DM |
12 | use PMG::RuleDB; |
13 | ||
e350fb98 DM |
14 | sub new { |
15 | my ($self, $start, $end, $advanced) = @_; | |
16 | ||
17 | $self = {}; | |
18 | ||
19 | bless($self); | |
20 | ||
21 | if ((defined($start))&&(defined($end))) { | |
22 | $self->timespan ($start, $end); | |
23 | } else { | |
24 | $self->timespan (time, time - 24*3600); | |
25 | } | |
26 | ||
27 | $self->{adv} = $advanced; | |
28 | ||
29 | return $self; | |
30 | } | |
31 | ||
32 | sub clear_stats { | |
33 | my ($dbh) = @_; | |
34 | ||
35 | eval { | |
36 | $dbh->begin_work; | |
37 | ||
38 | $dbh->do ("LOCK TABLE StatInfo"); | |
39 | $dbh->do ("LOCK TABLE ClusterInfo"); | |
40 | ||
41 | $dbh->do ("DELETE FROM Statinfo"); | |
42 | $dbh->do ("DELETE FROM DailyStat"); | |
43 | $dbh->do ("DELETE FROM DomainStat"); | |
44 | $dbh->do ("DELETE FROM VirusInfo"); | |
45 | $dbh->do ("DELETE FROM ClusterInfo WHERE name = 'lastmt_DomainStat'"); | |
46 | $dbh->do ("DELETE FROM ClusterInfo WHERE name = 'lastmt_DailyStat'"); | |
47 | $dbh->do ("DELETE FROM ClusterInfo WHERE name = 'lastmt_VirusInfo'"); | |
48 | ||
49 | $dbh->commit; | |
50 | }; | |
51 | if ($@) { | |
52 | $dbh->rollback; | |
53 | die $@; | |
54 | } | |
55 | } | |
56 | ||
57 | sub update_stats_generic { | |
58 | my ($dbh, $statinfoid, $select, $update, $insert) = @_; | |
59 | ||
60 | my $todo = 0; | |
61 | my $maxentries = 100000; | |
62 | ||
63 | ||
64 | eval { | |
65 | $dbh->begin_work; | |
66 | ||
67 | $dbh->do("LOCK TABLE StatInfo IN EXCLUSIVE MODE"); | |
68 | ||
69 | my $sth = $dbh->prepare("SELECT last_value FROM cstatistic_id_seq"); | |
70 | $sth->execute(); | |
71 | my $maxinfo = $sth->fetchrow_hashref(); | |
72 | goto COMMIT if !$maxinfo; | |
73 | my $last_value = $maxinfo->{last_value}; | |
74 | goto COMMIT if !defined ($last_value); | |
75 | ||
76 | $sth = $dbh->prepare("SELECT ivalue as value FROM StatInfo WHERE NAME = '$statinfoid'"); | |
77 | $sth->execute(); | |
78 | my $statinfo = $sth->fetchrow_hashref(); | |
79 | ||
80 | my $startid = $statinfo ? $statinfo->{value} : 0; | |
81 | goto COMMIT if $startid > $last_value; | |
82 | ||
83 | my $endid = $startid + $maxentries; | |
84 | $endid = $last_value + 1 if $endid > $last_value; | |
85 | $todo = $last_value + 1 - $endid; | |
86 | ||
87 | my $timezone = tz_local_offset();; | |
88 | ||
89 | $select =~ s/__timezone__/$timezone/g; | |
90 | $select =~ s/__startid__/$startid/g; | |
91 | $select =~ s/__endid__/$endid/g; | |
92 | ||
93 | $sth = $dbh->prepare($select); | |
94 | $sth->execute(); | |
95 | ||
96 | my $cmd = ""; | |
97 | #print "TEST:$last_value:$endid:$todo\n"; | |
98 | ||
99 | while (my $ref = $sth->fetchrow_hashref()) { | |
100 | if ($ref->{exists}) { | |
101 | $cmd .= &$update($ref); | |
102 | } else { | |
103 | $cmd .= &$insert($ref); | |
104 | } | |
105 | } | |
106 | ||
107 | $dbh->do ($cmd) if $cmd; | |
108 | ||
109 | $sth->finish(); | |
110 | ||
111 | if ($statinfo) { | |
112 | $dbh->do("UPDATE StatInfo SET ivalue = $endid WHERE NAME = '$statinfoid'"); | |
113 | } else { | |
114 | $dbh->do("INSERT INTO StatInfo VALUES ('$statinfoid', $endid)"); | |
115 | } | |
116 | ||
117 | COMMIT: | |
118 | $dbh->commit; | |
119 | }; | |
120 | ||
121 | if ($@) { | |
122 | $dbh->rollback; | |
123 | die $@; | |
124 | } | |
125 | ||
126 | return $todo; | |
127 | } | |
128 | ||
129 | sub update_stats_dailystat { | |
130 | my ($dbh, $cinfo) = @_; | |
131 | ||
9f67f5b3 DM |
132 | my $role = $cinfo->{local}->{type} // '-'; |
133 | return 0 if !(($role eq '-') || ($role eq 'master')); | |
e350fb98 DM |
134 | |
135 | my $select = "SELECT sub.*, dailystat.time IS NOT NULL as exists FROM " . | |
136 | "(SELECT COUNT (CASE WHEN direction THEN 1 ELSE NULL END) as count_in, " . | |
137 | "COUNT (CASE WHEN NOT direction THEN 1 ELSE NULL END) as count_out, " . | |
138 | "SUM (CASE WHEN direction THEN bytes ELSE NULL END) / (1024.0*1024) as bytes_in, " . | |
139 | "SUM (CASE WHEN NOT direction THEN bytes ELSE NULL END) / (1024.0*1024) as bytes_out, " . | |
140 | "COUNT (CASE WHEN virusinfo IS NOT NULL AND direction THEN 1 ELSE NULL END) AS virus_in, " . | |
141 | "COUNT (CASE WHEN virusinfo IS NOT NULL AND NOT direction THEN 1 ELSE NULL END) AS virus_out, " . | |
142 | "COUNT (CASE WHEN virusinfo IS NULL AND direction AND ptime > 0 AND spamlevel >= 3 THEN 1 ELSE NULL END) as spam_in, " . | |
143 | "COUNT (CASE WHEN virusinfo IS NULL AND NOT direction AND ptime > 0 AND spamlevel >= 3 THEN 1 ELSE NULL END) as spam_out, " . | |
144 | "COUNT (CASE WHEN virusinfo IS NULL AND direction AND sender = '' THEN 1 ELSE NULL END) as bounces_in, " . | |
145 | "COUNT (CASE WHEN virusinfo IS NULL AND NOT direction AND sender = '' THEN 1 ELSE NULL END) as bounces_out, " . | |
146 | "COUNT (CASE WHEN virusinfo IS NULL AND ptime = 0 AND spamlevel = 5 THEN 1 ELSE NULL END) as glcount, " . | |
147 | "COUNT (CASE WHEN virusinfo IS NULL AND ptime = 0 AND spamlevel = 4 THEN 1 ELSE NULL END) as spfcount, " . | |
148 | "sum (cstatistic.ptime) / 1000.0 as ptimesum, " . | |
149 | "((cstatistic.time + __timezone__) / 3600) * 3600 as hour " . | |
150 | "from cstatistic where id >= __startid__ and id < __endid__ group by hour) as sub " . | |
151 | "left join dailystat on (sub.hour = dailystat.time)"; | |
152 | ||
153 | my $update = sub { | |
154 | my $ref = shift; | |
155 | my @values = (); | |
156 | my $sql = ''; | |
157 | ||
158 | push @values, "CountIn = CountIn + $ref->{count_in}" if $ref->{count_in}; | |
159 | push @values, "CountOut = CountOut + $ref->{count_out}" if $ref->{count_out}; | |
160 | push @values, "BytesIn = BytesIn + $ref->{bytes_in}" if $ref->{bytes_in}; | |
161 | push @values, "BytesOut = BytesOut + $ref->{bytes_out}" if $ref->{bytes_out}; | |
162 | push @values, "VirusIn = VirusIn + $ref->{virus_in}" if $ref->{virus_in}; | |
163 | push @values, "VirusOut = VirusOut + $ref->{virus_out}" if $ref->{virus_out}; | |
164 | push @values, "SpamIn = SpamIn + $ref->{spam_in}" if $ref->{spam_in}; | |
165 | push @values, "SpamOut = SpamOut + $ref->{spam_out}" if $ref->{spam_out}; | |
166 | push @values, "BouncesIn = BouncesIn + $ref->{bounces_in}" if $ref->{bounces_in}; | |
167 | push @values, "BouncesOut = BouncesOut + $ref->{bounces_out}" if $ref->{bounces_out}; | |
168 | push @values, "GreylistCount = GreylistCount + $ref->{glcount}" if $ref->{glcount}; | |
169 | push @values, "SPFCount = SPFCount + $ref->{spfcount}" if $ref->{spfcount}; | |
170 | push @values, "PTimeSum = PTimeSum + $ref->{ptimesum}" if $ref->{ptimesum}; | |
171 | push @values, "MTime = EXTRACT(EPOCH FROM now())"; | |
172 | ||
173 | if (scalar (@values)) { | |
174 | $sql .= "UPDATE dailystat SET "; | |
175 | $sql .= join (',', @values); | |
176 | $sql .= " WHERE time = $ref->{hour};"; | |
177 | } | |
178 | return $sql; | |
179 | }; | |
180 | ||
181 | my $insert = sub { | |
182 | my $ref = shift; | |
183 | ||
184 | my $sql = "INSERT INTO dailystat " . | |
185 | "(Time,CountIn,CountOut,BytesIn,BytesOut,VirusIn,VirusOut,SpamIn,SpamOut," . | |
186 | "BouncesIn,BouncesOut,GreylistCount,SPFCount,RBLCount,PTimeSum,Mtime) " . | |
187 | "VALUES ($ref->{hour}," . ($ref->{count_in} || 0) . ',' . ($ref->{count_out} || 0) . ',' . | |
188 | ($ref->{bytes_in} || 0) . ',' . ($ref->{bytes_out} || 0) . ',' . | |
189 | ($ref->{virus_in} || 0) . ',' . ($ref->{virus_out} || 0) . ',' . | |
190 | ($ref->{spam_in} || 0) . ',' . ($ref->{spam_out} || 0) . ',' . | |
191 | ($ref->{bounces_in} || 0) . ',' . ($ref->{bounces_out} || 0) . ',' . | |
192 | ($ref->{glcount} || 0) . ',' . ($ref->{spfcount} || 0) . ',0,' . ($ref->{ptimesum} || 0) . | |
193 | ",EXTRACT(EPOCH FROM now()));"; | |
194 | ||
195 | return $sql; | |
196 | }; | |
197 | ||
198 | return update_stats_generic ($dbh, 'dailystat_index', $select, $update, $insert); | |
199 | ||
200 | } | |
201 | ||
202 | sub update_stats_domainstat_in { | |
203 | my ($dbh, $cinfo) = @_; | |
204 | ||
9f67f5b3 DM |
205 | my $role = $cinfo->{local}->{type} // '-'; |
206 | return 0 if !(($role eq '-') || ($role eq 'master')); | |
e350fb98 DM |
207 | |
208 | my $sub1 = "select distinct cstatistic_cid, cstatistic_rid, " . | |
209 | "lower(substring(receiver from position ('\@' in receiver) + 1)) as domain, " . | |
210 | "((cstatistic.time + __timezone__) / 86400) * 86400 as day " . | |
211 | "from CStatistic, CReceivers where cid = cstatistic_cid AND rid = cstatistic_rid AND " . | |
212 | "id >= __startid__ and id < __endid__ AND direction " . | |
213 | "group by cstatistic_cid, cstatistic_rid, day, domain"; | |
214 | ||
215 | ||
216 | my $select = "SELECT sub.*, domainstat.time IS NOT NULL as exists FROM " . | |
217 | "(SELECT day, domain, COUNT (id) as count_in, SUM (bytes) / (1024.0*1024) as bytes_in, " . | |
218 | "COUNT (CASE WHEN virusinfo IS NOT NULL THEN 1 ELSE NULL END) AS virus_in, " . | |
219 | "COUNT (CASE WHEN virusinfo IS NULL AND spamlevel >= 3 THEN 1 ELSE NULL END) as spam_in, " . | |
220 | "COUNT (CASE WHEN virusinfo IS NULL AND sender = '' THEN 1 ELSE NULL END) as bounces_in, " . | |
221 | "sum (cstatistic.ptime) / 1000.0 as ptimesum " . | |
222 | "from cstatistic, ($sub1) as ddb " . | |
223 | "WHERE ddb.cstatistic_cid = cstatistic.cid AND ddb.cstatistic_rid = cstatistic.rid GROUP BY day, domain) as sub " . | |
224 | "left join domainstat on (day = domainstat.time and sub.domain = domainstat.domain)"; | |
225 | ||
226 | my $update = sub { | |
227 | my $ref = shift; | |
228 | my @values = (); | |
229 | my $sql = ''; | |
230 | ||
231 | push @values, "CountIn = CountIn + $ref->{count_in}" if $ref->{count_in}; | |
232 | push @values, "BytesIn = BytesIn + $ref->{bytes_in}" if $ref->{bytes_in}; | |
233 | push @values, "VirusIn = VirusIn + $ref->{virus_in}" if $ref->{virus_in}; | |
234 | push @values, "SpamIn = SpamIn + $ref->{spam_in}" if $ref->{spam_in}; | |
235 | push @values, "BouncesIn = BouncesIn + $ref->{bounces_in}" if $ref->{bounces_in}; | |
236 | push @values, "PTimeSum = PTimeSum + $ref->{ptimesum}" if $ref->{ptimesum}; | |
237 | push @values, "MTime = EXTRACT(EPOCH FROM now())"; | |
238 | ||
239 | if (scalar (@values)) { | |
240 | $sql .= "UPDATE domainstat SET "; | |
241 | $sql .= join (',', @values); | |
242 | $sql .= " WHERE time = $ref->{day} and domain = " . $dbh->quote($ref->{domain}) . ';'; | |
243 | } | |
244 | return $sql; | |
245 | }; | |
246 | ||
247 | my $insert = sub { | |
248 | my $ref = shift; | |
249 | ||
250 | my $sql .= "INSERT INTO domainstat values ($ref->{day}, " . $dbh->quote($ref->{domain}) . ',' . | |
251 | ($ref->{count_in} || 0) . ',0,' . | |
252 | ($ref->{bytes_in} || 0) . ',0,' . | |
253 | ($ref->{virus_in} || 0) . ',0,' . | |
254 | ($ref->{spam_in} || 0) . ',0,' . | |
255 | ($ref->{bounces_in} || 0) . ',0,' . | |
256 | ($ref->{ptimesum} || 0) . | |
257 | ",EXTRACT(EPOCH FROM now()));"; | |
258 | ||
259 | return $sql; | |
260 | }; | |
261 | ||
262 | update_stats_generic ($dbh, 'domainstat_in_index', $select, $update, $insert); | |
263 | ||
264 | } | |
265 | ||
266 | sub update_stats_domainstat_out { | |
267 | my ($dbh, $cinfo) = @_; | |
268 | ||
9f67f5b3 DM |
269 | my $role = $cinfo->{local}->{type} // '-'; |
270 | return 0 if !(($role eq '-') || ($role eq 'master')); | |
e350fb98 DM |
271 | |
272 | my $select = "SELECT sub.*, domainstat.time IS NOT NULL as exists FROM " . | |
273 | "(SELECT COUNT (ID) as count_out, SUM (bytes) / (1024.0*1024) as bytes_out, " . | |
274 | "COUNT (CASE WHEN virusinfo IS NOT NULL THEN 1 ELSE NULL END) AS virus_out, " . | |
275 | "COUNT (CASE WHEN virusinfo IS NULL AND spamlevel >= 3 THEN 1 ELSE NULL END) as spam_out, " . | |
276 | "COUNT (CASE WHEN virusinfo IS NULL AND sender = '' THEN 1 ELSE NULL END) as bounces_out, " . | |
277 | "sum (cstatistic.ptime) / 1000.0 as ptimesum, " . | |
278 | "((cstatistic.time + __timezone__) / 86400) * 86400 as day, " . | |
279 | "lower(substring(sender from position ('\@' in sender) + 1)) as domain " . | |
280 | "from cstatistic where id >= __startid__ and id < __endid__ and not direction " . | |
281 | "group by day, domain) as sub " . | |
282 | "left join domainstat on (day = domainstat.time and sub.domain = domainstat.domain)"; | |
283 | ||
284 | my $update = sub { | |
285 | my $ref = shift; | |
286 | my @values = (); | |
287 | my $sql = ''; | |
288 | ||
289 | push @values, "CountOut = CountOut + $ref->{count_out}" if $ref->{count_out}; | |
290 | push @values, "BytesOut = BytesOut + $ref->{bytes_out}" if $ref->{bytes_out}; | |
291 | push @values, "VirusOut = VirusOut + $ref->{virus_out}" if $ref->{virus_out}; | |
292 | push @values, "SpamOut = SpamOut + $ref->{spam_out}" if $ref->{spam_out}; | |
293 | push @values, "BouncesOut = BouncesOut + $ref->{bounces_out}" if $ref->{bounces_out}; | |
294 | push @values, "PTimeSum = PTimeSum + $ref->{ptimesum}" if $ref->{ptimesum}; | |
295 | push @values, "MTime = EXTRACT(EPOCH FROM now())"; | |
296 | ||
297 | if (scalar (@values)) { | |
298 | $sql .= "UPDATE domainstat SET "; | |
299 | $sql .= join (',', @values); | |
300 | $sql .= " WHERE time = $ref->{day} and domain = " . $dbh->quote($ref->{domain}) . ';'; | |
301 | } | |
302 | return $sql; | |
303 | }; | |
304 | ||
305 | my $insert = sub { | |
306 | my $ref = shift; | |
307 | ||
308 | my $sql .= "INSERT INTO domainstat values ($ref->{day}, " . $dbh->quote($ref->{domain}) . | |
309 | ',0,' . ($ref->{count_out} || 0) . | |
310 | ',0,' . ($ref->{bytes_out} || 0) . | |
311 | ',0,' . ($ref->{virus_out} || 0) . | |
312 | ',0,' . ($ref->{spam_out} || 0) . | |
313 | ',0,' . ($ref->{bounces_out} || 0) . | |
314 | ','. ($ref->{ptimesum} || 0) . | |
315 | ",EXTRACT(EPOCH FROM now()));"; | |
316 | ||
317 | return $sql; | |
318 | }; | |
319 | ||
320 | update_stats_generic ($dbh, 'domainstat_out_index', $select, $update, $insert); | |
321 | ||
322 | } | |
323 | ||
324 | sub update_stats_virusinfo { | |
325 | my ($dbh, $cinfo) = @_; | |
326 | ||
9f67f5b3 DM |
327 | my $role = $cinfo->{local}->{type} // '-'; |
328 | return 0 if !(($role eq '-') || ($role eq 'master')); | |
e350fb98 DM |
329 | |
330 | my $select = "SELECT sub.*, virusinfo.time IS NOT NULL as exists FROM " . | |
331 | "(SELECT ((cstatistic.time + __timezone__) / 86400) * 86400 as day, " . | |
332 | "count (virusinfo) as count, virusinfo AS name " . | |
333 | "FROM cstatistic WHERE id >= __startid__ AND id < __endid__ AND virusinfo IS NOT NULL " . | |
334 | "group by day, name) as sub " . | |
335 | "left join VirusInfo on (day = virusinfo.time and sub.name = virusinfo.name)"; | |
336 | ||
337 | my $update = sub { | |
338 | my $ref = shift; | |
339 | my @values = (); | |
340 | my $sql = ''; | |
341 | ||
342 | push @values, "Count = Count + $ref->{count}" if $ref->{count}; | |
343 | push @values, "MTime = EXTRACT(EPOCH FROM now())"; | |
344 | ||
345 | if (scalar (@values)) { | |
346 | $sql .= "UPDATE VirusInfo SET "; | |
347 | $sql .= join (',', @values); | |
348 | $sql .= " WHERE time = $ref->{day} and Name = " . $dbh->quote($ref->{name}) . ';'; | |
349 | } | |
350 | return $sql; | |
351 | }; | |
352 | ||
353 | my $insert = sub { | |
354 | my $ref = shift; | |
355 | ||
356 | my $sql .= "INSERT INTO VirusInfo values ($ref->{day}, " . $dbh->quote($ref->{name}) . | |
357 | ',' . ($ref->{count} || 0) . | |
358 | ",EXTRACT(EPOCH FROM now()));"; | |
359 | ||
360 | return $sql; | |
361 | }; | |
362 | ||
363 | update_stats_generic ($dbh, 'virusinfo_index', $select, $update, $insert); | |
364 | ||
365 | } | |
366 | ||
367 | ||
368 | sub update_stats { | |
369 | my ($dbh, $cinfo) = @_; | |
370 | ||
371 | while (update_stats_dailystat ($dbh, $cinfo) > 0) {}; | |
372 | while (update_stats_domainstat_in ($dbh, $cinfo) > 0) {}; | |
373 | while (update_stats_domainstat_out ($dbh, $cinfo) > 0) {}; | |
374 | while (update_stats_virusinfo ($dbh, $cinfo) > 0) {}; | |
375 | } | |
376 | ||
377 | sub total_mail_stat { | |
378 | my ($self, $rdb) = @_; | |
379 | ||
380 | my ($from, $to) = $self->localdayspan(); | |
381 | ||
382 | my ($sth, $ref); | |
383 | my $glcount = 0; | |
384 | ||
385 | # this is to slow for high volume sites | |
386 | # $sth = $rdb->{dbh}->prepare("SELECT COUNT(DISTINCT Instance) AS GL FROM CGreylist " . | |
387 | # "WHERE passed = 0 AND rctime >= ? AND rctime < ? "); | |
388 | # $sth->execute($from, $to); | |
389 | # $ref = $sth->fetchrow_hashref(); | |
390 | # $glcount = $ref->{gl}; | |
391 | ||
392 | my $cmds = "SELECT sum(CountIn) + $glcount AS count_in, sum(CountOut) AS count_out, " . | |
393 | "sum (VirusIn) AS viruscount_in, sum (VirusOut) AS viruscount_out, " . | |
394 | "sum (SpamIn) AS spamcount_in, sum (SpamOut) AS spamcount_out, " . | |
395 | "sum (BytesIn) AS traffic_in, sum (BytesOut) AS traffic_out, " . | |
396 | "sum (BouncesIn) AS bounces_in, sum (BouncesOut) AS bounces_out, " . | |
397 | "sum (GreylistCount) + $glcount as glcount, " . | |
398 | "sum (SPFCount) as spfcount, " . | |
399 | "sum (RBLCount) as rblcount, " . | |
400 | "sum(PTimeSum)/(sum(CountIn) + $glcount + sum(CountOut)) AS avptime " . | |
401 | "FROM DailyStat where time >= $from and time < $to"; | |
402 | ||
403 | $sth = $rdb->{dbh}->prepare($cmds); | |
404 | $sth->execute(); | |
405 | $ref = $sth->fetchrow_hashref(); | |
406 | $sth->finish(); | |
407 | ||
408 | if (!$ref->{avptime}) { | |
409 | $ref->{count_in} = $ref->{count_out} = $ref->{viruscount_in} = $ref->{viruscount_out} = | |
410 | $ref->{spamcount_in} = $ref->{spamcount_out} = $ref->{glcount} = $ref->{spfcount} = | |
411 | $ref->{rblcount} = $ref->{bounces_in} = $ref->{bounces_out} = $ref->{traffic_in} = | |
412 | $ref->{traffic_out} = $ref->{avptime} = 0; | |
413 | } | |
414 | ||
415 | $ref->{count} = $ref->{count_in} + $ref->{count_out}; | |
416 | ||
417 | $ref->{count_in_per} = $ref->{count} > 0 ? ($ref->{count_in} * 100)/$ref->{count} : 0; | |
418 | $ref->{count_out_per} = 100 - $ref->{count_in_per}; | |
419 | ||
420 | $ref->{viruscount_in_per} = $ref->{count_in} > 0 ? ($ref->{viruscount_in} * 100)/$ref->{count_in} : 0; | |
421 | $ref->{viruscount_out_per} = $ref->{count_out} > 0 ? ($ref->{viruscount_out} * 100)/$ref->{count_out} : 0; | |
422 | ||
423 | $ref->{spamcount_in_per} = $ref->{count_in} > 0 ? ($ref->{spamcount_in} * 100)/$ref->{count_in} : 0; | |
424 | $ref->{spamcount_out_per} = $ref->{count_out} > 0 ? ($ref->{spamcount_out} * 100)/$ref->{count_out} : 0; | |
425 | ||
426 | $ref->{bounces_in_per} = $ref->{count_in} > 0 ? ($ref->{bounces_in} * 100)/$ref->{count_in} : 0; | |
427 | $ref->{bounces_out_per} = $ref->{count_out} > 0 ? ($ref->{bounces_out} * 100)/$ref->{count_out} : 0; | |
428 | ||
429 | $ref->{glcount_per} = $ref->{count_in} > 0 ? ($ref->{glcount} * 100)/$ref->{count_in} : 0; | |
430 | $ref->{spfcount_per} = $ref->{count_in} > 0 ? ($ref->{spfcount} * 100)/$ref->{count_in} : 0; | |
431 | $ref->{rblcount_per} = $ref->{count_in} > 0 ? ($ref->{rblcount} * 100)/$ref->{count_in} : 0; | |
432 | ||
433 | $ref->{junk_in} = $ref->{viruscount_in} + $ref->{spamcount_in} + $ref->{glcount} + | |
434 | $ref->{spfcount} + $ref->{rblcount}; | |
435 | ||
436 | $ref->{junk_out} = $ref->{viruscount_out} + $ref->{spamcount_out}; | |
437 | ||
438 | $ref->{junk_in_per} = $ref->{count_in} > 0 ? ($ref->{junk_in} * 100)/$ref->{count_in} : 0; | |
439 | $ref->{junk_out_per} = $ref->{count_out} > 0 ? ($ref->{junk_out} * 100)/$ref->{count_out} : 0; | |
440 | ||
441 | return $ref; | |
442 | } | |
443 | ||
444 | sub total_spam_stat { | |
445 | my ($self, $rdb) = @_; | |
446 | my ($from, $to) = $self->timespan(); | |
447 | ||
448 | my $sth = $rdb->{dbh}->prepare("SELECT spamlevel, COUNT(spamlevel) AS count FROM CStatistic " . | |
449 | "WHERE virusinfo IS NULL and time >= ? AND time < ? AND ptime > 0 AND spamlevel > 0 " . | |
450 | "GROUP BY spamlevel ORDER BY spamlevel LIMIT 10"); | |
451 | $sth->execute($from, $to); | |
452 | ||
453 | my $res = $sth->fetchall_arrayref({}); | |
454 | ||
455 | $sth->finish(); | |
456 | ||
457 | return $res; | |
458 | } | |
459 | ||
460 | sub total_virus_stat { | |
461 | my ($self, $rdb, $order) = @_; | |
462 | ||
463 | my ($from, $to) = $self->localdayspan(); | |
464 | ||
465 | $order = "count" if !$order; | |
466 | ||
467 | my @oa = split (',', $order); | |
468 | ||
469 | $order = join (' DESC, ', @oa); | |
470 | $order .= ' DESC'; | |
471 | ||
472 | my $sth = $rdb->{dbh}->prepare("SELECT Name, SUM (Count) as count FROM VirusInfo " . | |
473 | "WHERE time >= ? AND time < ? " . | |
474 | "GROUP BY name ORDER BY $order, name"); | |
475 | ||
476 | $sth->execute($from, $to); | |
477 | ||
478 | my $res = $sth->fetchall_arrayref({}); | |
479 | ||
480 | $sth->finish(); | |
481 | ||
482 | return $res; | |
483 | } | |
484 | ||
485 | sub rule_count { | |
486 | my ($self, $rdb) = @_; | |
487 | ||
488 | my $sth = $rdb->{dbh}->prepare("SELECT id, name, count from rule order by count desc, name"); | |
489 | $sth->execute(); | |
490 | ||
491 | my $res = $sth->fetchall_arrayref({}); | |
492 | $sth->finish(); | |
493 | ||
494 | return $res; | |
495 | } | |
496 | ||
497 | sub total_domain_stat { | |
498 | my ($self, $rdb, $orderby) = @_; | |
499 | ||
500 | $orderby || ($orderby = 'domain'); | |
501 | my $sortdir = sort_dir ($orderby); | |
502 | ||
503 | my ($from, $to) = $self->localdayspan(); | |
504 | ||
505 | my $query = "SELECT domain, SUM (CountIn) AS count_in, SUM (CountOut) AS count_out," . | |
506 | "SUM (BytesIn) AS bytes_in, SUM (BytesOut) AS bytes_out, " . | |
507 | "SUM (VirusIn) AS virus_in, SUM (VirusOut) AS virus_out," . | |
508 | "SUM (SpamIn) as spam_in, SUM (SpamOut) as spam_out " . | |
509 | "FROM DomainStat where time >= $from AND time < $to " . | |
510 | "GROUP BY domain ORDER BY $orderby $sortdir, domain ASC"; | |
511 | ||
512 | my $sth = $rdb->{dbh}->prepare($query); | |
513 | $sth->execute(); | |
514 | ||
515 | my $res = $sth->fetchall_arrayref({}); | |
516 | ||
517 | $sth->finish(); | |
518 | ||
519 | return $res; | |
520 | } | |
521 | ||
522 | sub clear_rule_count { | |
523 | my ($self, $rdb, $id) = @_; | |
524 | ||
525 | if (defined($id)) { | |
526 | $rdb->{dbh}->do ("UPDATE rule set count = 0 where id = ?", undef, $id); | |
527 | } else { | |
528 | $rdb->{dbh}->do("UPDATE rule set count = 0"); | |
529 | } | |
530 | } | |
531 | ||
532 | sub query_cond_good_mail { | |
533 | my ($self, $from, $to) = @_; | |
534 | return "time >= $from AND time < $to AND bytes > 0 AND sender IS NOT NULL"; | |
535 | } | |
536 | ||
537 | sub query_active_workers { | |
538 | my ($self) = @_; | |
539 | my ($from, $to) = $self->timespan(); | |
540 | ||
541 | my $start = $from - (3600*24)*90; # from - 90 days | |
542 | my $cond_good_mail = $self->query_cond_good_mail ($start, $to); | |
543 | ||
544 | return "SELECT DISTINCT sender as worker FROM CStatistic WHERE $cond_good_mail AND NOT direction"; | |
545 | } | |
546 | ||
547 | sub sort_dir { | |
548 | my ($orderby) = @_; | |
549 | ||
550 | my $sortdir = ($orderby eq "virusinfo" || $orderby eq 'sender' || $orderby eq 'domain' || $orderby eq 'receiver') ? 'ASC' : 'DESC'; | |
551 | ||
552 | return $sortdir; | |
553 | } | |
554 | ||
555 | sub user_stat_contact_details { | |
556 | my ($self, $rdb, $receiver, $limit, $orderby) = @_; | |
557 | my ($from, $to) = $self->timespan(); | |
558 | my $sth; | |
559 | my $res; | |
560 | ||
561 | $orderby || ($orderby = 'time'); | |
562 | my $sortdir = sort_dir ($orderby); | |
563 | ||
564 | my $cond_good_mail = $self->query_cond_good_mail ($from, $to); | |
565 | ||
566 | my $query = "SELECT * FROM CStatistic, CReceivers " . | |
567 | "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND $cond_good_mail AND NOT direction AND sender != '' AND receiver = ? " . | |
568 | "ORDER BY $orderby $sortdir, receiver limit $limit"; | |
569 | ||
570 | $sth = $rdb->{dbh}->prepare($query); | |
571 | ||
572 | $sth->execute ($receiver); | |
573 | ||
574 | while (my $ref = $sth->fetchrow_hashref()) { | |
575 | push @$res, $ref; | |
576 | } | |
577 | ||
578 | $sth->finish(); | |
579 | ||
580 | return $res; | |
581 | } | |
582 | ||
583 | sub user_stat_contact { | |
584 | my ($self, $rdb, $limit, $orderby) = @_; | |
585 | my ($from, $to) = $self->timespan(); | |
586 | my $sth; | |
587 | my $res; | |
588 | my $query; | |
589 | ||
590 | $orderby || ($orderby = 'count'); | |
591 | my $sortdir = sort_dir ($orderby); | |
592 | ||
593 | my $cond_good_mail = $self->query_cond_good_mail ($from, $to); | |
594 | ||
595 | if ($self->{adv}) { | |
596 | my $active_workers = $self->query_active_workers (); | |
597 | ||
598 | $query = "SELECT receiver, count(*) AS count, sum (bytes) AS bytes " . | |
599 | "FROM CStatistic, CReceivers WHERE cid = cstatistic_cid AND rid = cstatistic_rid " . | |
600 | "AND $cond_good_mail AND NOT direction AND sender != '' AND " . | |
601 | "receiver NOT IN ($active_workers) " . | |
602 | "GROUP BY receiver ORDER BY $orderby $sortdir, receiver limit $limit"; | |
603 | } else { | |
604 | $query = "SELECT receiver, count(*) AS count, sum (bytes) AS bytes " . | |
605 | "FROM CStatistic, CReceivers WHERE cid = cstatistic_cid AND rid = cstatistic_rid " . | |
606 | "AND $cond_good_mail AND NOT direction AND sender != '' " . | |
607 | "GROUP BY receiver ORDER BY $orderby $sortdir, receiver limit $limit"; | |
608 | } | |
609 | ||
610 | $sth = $rdb->{dbh}->prepare($query); | |
611 | ||
612 | $sth->execute(); | |
613 | ||
614 | while (my $ref = $sth->fetchrow_hashref()) { | |
615 | push @$res, $ref; | |
616 | } | |
617 | ||
618 | $sth->finish(); | |
619 | ||
620 | return $res; | |
621 | } | |
622 | ||
623 | sub user_stat_sender_details { | |
624 | my ($self, $rdb, $sender, $limit, $orderby) = @_; | |
625 | my ($from, $to) = $self->timespan(); | |
626 | my $sth; | |
627 | my $res; | |
628 | ||
629 | $orderby || ($orderby = 'time'); | |
630 | my $sortdir = sort_dir ($orderby); | |
631 | ||
632 | my $cond_good_mail = $self->query_cond_good_mail ($from, $to); | |
633 | ||
634 | $sth = $rdb->{dbh}->prepare("SELECT * FROM CStatistic, CReceivers WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND " . | |
635 | "$cond_good_mail AND NOT direction AND sender = ? " . | |
636 | "ORDER BY $orderby $sortdir, receiver limit $limit"); | |
637 | $sth->execute($sender); | |
638 | ||
639 | while (my $ref = $sth->fetchrow_hashref()) { | |
640 | push @$res, $ref; | |
641 | } | |
642 | ||
643 | $sth->finish(); | |
644 | ||
645 | return $res; | |
646 | } | |
647 | ||
648 | sub user_stat_sender { | |
649 | my ($self, $rdb, $limit, $orderby) = @_; | |
650 | my ($from, $to) = $self->timespan(); | |
651 | my $sth; | |
652 | my $res; | |
653 | my $query; | |
654 | ||
655 | $orderby || ($orderby = 'count'); | |
656 | my $sortdir = sort_dir ($orderby); | |
657 | ||
658 | my $cond_good_mail = $self->query_cond_good_mail ($from, $to); | |
659 | ||
660 | $query = "SELECT sender,count(*) AS count, sum (bytes) AS bytes, " . | |
661 | "count (virusinfo) as viruscount, " . | |
662 | "count (CASE WHEN spamlevel >= 3 THEN 1 ELSE NULL END) as spamcount " . | |
663 | "FROM CStatistic WHERE $cond_good_mail AND NOT direction AND sender != '' " . | |
664 | "GROUP BY sender ORDER BY $orderby $sortdir, sender limit $limit"; | |
665 | ||
666 | $sth = $rdb->{dbh}->prepare($query); | |
667 | $sth->execute(); | |
668 | ||
669 | while (my $ref = $sth->fetchrow_hashref()) { | |
670 | push @$res, $ref; | |
671 | } | |
672 | ||
673 | $sth->finish(); | |
674 | ||
675 | return $res; | |
676 | } | |
677 | ||
678 | sub user_stat_receiver_details { | |
679 | my ($self, $rdb, $receiver, $limit, $orderby) = @_; | |
680 | my ($from, $to) = $self->timespan(); | |
681 | my $sth; | |
682 | my $res; | |
683 | ||
684 | $orderby || ($orderby = 'time'); | |
685 | my $sortdir = sort_dir ($orderby); | |
686 | ||
687 | my $cond_good_mail = $self->query_cond_good_mail ($from, $to); | |
688 | ||
689 | $sth = $rdb->{dbh}->prepare("SELECT * FROM CStatistic, CReceivers " . | |
690 | "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND $cond_good_mail AND receiver = ? " . | |
691 | "ORDER BY $orderby $sortdir, sender limit $limit"); | |
692 | $sth->execute($receiver); | |
693 | ||
694 | while (my $ref = $sth->fetchrow_hashref()) { | |
695 | push @$res, $ref; | |
696 | } | |
697 | ||
698 | $sth->finish(); | |
699 | ||
700 | return $res; | |
701 | } | |
702 | ||
703 | sub user_stat_receiver { | |
704 | my ($self, $rdb, $limit, $orderby) = @_; | |
705 | my ($from, $to) = $self->timespan(); | |
706 | my $sth; | |
707 | my $res; | |
708 | my $query; | |
709 | ||
710 | $orderby || ($orderby = 'count'); | |
711 | my $sortdir = sort_dir ($orderby); | |
712 | ||
713 | my $cond_good_mail = $self->query_cond_good_mail ($from, $to) . " AND " . | |
714 | "receiver IS NOT NULL AND receiver != ''"; | |
715 | ||
716 | if ($self->{adv}) { | |
717 | my $active_workers = $self->query_active_workers (); | |
718 | ||
719 | $query = "SELECT receiver, count(*) AS count, sum (bytes) AS bytes, " . | |
720 | "count (virusinfo) as viruscount, " . | |
721 | "count (CASE WHEN spamlevel >= 3 THEN 1 ELSE NULL END) as spamcount " . | |
722 | "FROM CStatistic, CReceivers, ($active_workers) as workers " . | |
723 | "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND $cond_good_mail AND direction AND worker=receiver " . | |
724 | "GROUP BY receiver " . | |
725 | "ORDER BY $orderby $sortdir, receiver LIMIT $limit"; | |
726 | } else { | |
727 | $query = "SELECT receiver, count(*) AS count, sum (bytes) AS bytes, " . | |
728 | "count (virusinfo) as viruscount, " . | |
729 | "count (CASE WHEN spamlevel >= 3 THEN 1 ELSE NULL END) as spamcount " . | |
730 | "FROM CStatistic, CReceivers " . | |
731 | "WHERE cid = cstatistic_cid AND rid = cstatistic_rid AND $cond_good_mail and direction " . | |
732 | "GROUP BY receiver " . | |
733 | "ORDER BY $orderby $sortdir, receiver LIMIT $limit"; | |
734 | } | |
735 | ||
736 | $sth = $rdb->{dbh}->prepare($query); | |
737 | $sth->execute(); | |
738 | ||
739 | while (my $ref = $sth->fetchrow_hashref()) { | |
740 | push @$res, $ref; | |
741 | } | |
742 | ||
743 | $sth->finish(); | |
744 | ||
745 | return $res; | |
746 | } | |
747 | ||
748 | sub traffic_stat_graph { | |
749 | my ($self, $rdb, $span, $dir) = @_; | |
750 | my $res; | |
751 | ||
752 | my ($from, $to) = $self->localhourspan(); | |
753 | my $p = $dir ? "In" : "Out"; | |
754 | my $timezone = tz_local_offset();; | |
755 | ||
756 | my $spam = $dir ? "sum (SpamIn) + sum (GreylistCount) + sum (SPFCount) + sum (RBLCount)" : "sum (SpamOut)"; | |
757 | ||
758 | my $cmd = "SELECT sum(Count$p) as count, (time - $from) / $span AS index, " . | |
759 | "sum (Virus$p) as viruscount, $spam as spamcount, sum (Bounces$p) as bounces " . | |
760 | "FROM DailyStat WHERE time >= $from AND time < $to " . | |
761 | "GROUP BY index ORDER BY index"; | |
762 | ||
763 | my $sth = $rdb->{dbh}->prepare($cmd); | |
764 | ||
765 | $sth->execute (); | |
766 | ||
767 | while (my $ref = $sth->fetchrow_hashref()) { | |
768 | @$res[$ref->{index}] = $ref; | |
769 | } | |
770 | ||
771 | my $c = int (($to - $from) / $span); | |
772 | ||
773 | for (my $i = 0; $i < $c; $i++) { | |
774 | my $eref = {count => 0, index => $i, spamcount => 0, viruscount => 0, bounces => 0}; | |
775 | @$res[$i] = $eref if !@$res[$i]; | |
776 | @$res[$i]->{time} = $from + ($i+1)*$span - $timezone; | |
777 | } | |
778 | $sth->finish(); | |
779 | ||
780 | return $res; | |
781 | } | |
782 | ||
783 | sub traffic_stat_day_dist { | |
784 | my ($self, $rdb, $dir) = @_; | |
785 | my $res; | |
786 | ||
787 | my ($from, $to) = $self->localhourspan(); | |
788 | my $p = $dir ? "In" : "Out"; | |
789 | ||
790 | my $spam = $dir ? "sum (SpamIn) + sum (GreylistCount) + sum (SPFCount) + sum (RBLCount)" : "sum (SpamOut)"; | |
791 | ||
792 | my $cmd = "SELECT sum(Count$p) as count, ((time - $from) / 3600) % 24 AS index, " . | |
793 | "sum (Virus$p) as viruscount, $spam as spamcount, sum (Bounces$p) as bounces " . | |
794 | "FROM DailyStat WHERE time >= $from AND time < $to " . | |
795 | "GROUP BY index ORDER BY index"; | |
796 | ||
797 | my $sth = $rdb->{dbh}->prepare($cmd); | |
798 | ||
799 | $sth->execute (); | |
800 | ||
801 | while (my $ref = $sth->fetchrow_hashref()) { | |
802 | @$res[$ref->{index}] = $ref; | |
803 | } | |
804 | ||
805 | for (my $i = 0; $i < 24; $i++) { | |
806 | my $eref = {count => 0, index => $i, spamcount => 0, viruscount => 0, bounces => 0}; | |
807 | @$res[$i] = $eref if !@$res[$i]; | |
808 | } | |
809 | $sth->finish(); | |
810 | ||
811 | return $res; | |
812 | } | |
813 | ||
814 | sub timespan { | |
815 | my ($self, $from, $to) = @_; | |
816 | ||
817 | if (defined ($from) && defined ($to)) { | |
818 | $self->{from} = $from; | |
819 | $self->{to} = $to; | |
820 | } | |
821 | ||
822 | return ($self->{from}, $self->{to}); | |
823 | } | |
824 | ||
825 | sub localdayspan { | |
826 | my ($self) = @_; | |
827 | ||
828 | my ($from, $to) = $self->timespan(); | |
829 | ||
830 | my $timezone = tz_local_offset();; | |
831 | $from = (($from + $timezone)/86400) * 86400; | |
832 | $to = (($to + $timezone)/86400) * 86400; | |
833 | ||
834 | $to += 86400 if $from == $to; | |
835 | ||
836 | return ($from, $to); | |
837 | } | |
838 | ||
839 | sub localhourspan { | |
840 | my ($self) = @_; | |
841 | ||
842 | my ($from, $to) = $self->timespan(); | |
843 | ||
844 | my $timezone = tz_local_offset();; | |
845 | $from = (($from + $timezone)/3600) * 3600; | |
846 | $to = (($to + $timezone)/3600) * 3600; | |
847 | ||
848 | $to += 3600 if $from == $to; | |
849 | ||
850 | return ($from, $to); | |
851 | } | |
852 | ||
e350fb98 DM |
853 | |
854 | 1; |