Oh, sorry, examples are for blacklist, for whitelist here:
### getting totalcount:
my $sth = DBSelect("
SELECT
Count(*) AS TotalCount
FROM
greylisting_tracking
WHERE
TrackKey =
".DBQuote($key)."
AND FirstSeen >=
".DBQuote($addedTime)."
");
...
my $totalCount =
defined($row->{'totalcount'}) ? $row->{'totalcount'} : 0;
### getting failcount:
$sth = DBSelect("
SELECT
Count(*) AS
PassCount
FROM
greylisting_tracking
WHERE
TrackKey =
".DBQuote($key)."
AND FirstSeen
>= ".DBQuote($addedTime)."
AND Count != 0
");
my $percentage = (
$passCount / $totalCount ) * 100;
modify to:
### getting totalcount:
my $sth = DBSelect("
SELECT
SUM(`Count`) AS
TotalCount
FROM
greylisting_tracking
WHERE
TrackKey =
".DBQuote($key)."
AND FirstSeen >=
".DBQuote($addedTime)."
");
...
my $totalCount =
defined($row->{'totalcount'}) ? $row->{'totalcount'} : 0;
### getting failcount:
$sth = DBSelect("
SELECT
SUM(Tries) AS
PassCount
FROM
greylisting_tracking
WHERE
TrackKey =
".DBQuote($key)."
AND FirstSeen
>= ".DBQuote($addedTime)."
AND Count != 0
");
2011/5/17 Alexey Murz Korepov <[email protected]>:
> As I look in code, in autowhitelist we are checking not count of total
> successfully delivered messages, but number of different "from-to"
> pairs that successfully delivered.
> here is code snippets:
> ### getting totalcount:
> my $sth = DBSelect("
> SELECT
> Count(*) AS TotalCount
> FROM
> greylisting_tracking
> WHERE
> TrackKey =
> ".DBQuote($key)."
> AND FirstSeen >=
> ".DBQuote($addedTime)."
> ");
> ...
> if ($totalCount > 0 && $totalCount >=
> $policy{'AutoBlacklistCount'}) {
>
> ### getting failcount:
> $sth = DBSelect("
> SELECT
> Count(*) AS
> FailCount
> FROM
>
> greylisting_tracking
> WHERE
> TrackKey =
> ".DBQuote($key)."
> AND FirstSeen
> >= ".DBQuote($addedTime)."
> AND Count = 0
> ");
>
> my $percentage = (
> $failCount / $totalCount ) * 100;
>
> So if one server sends one email to other server many times, it will
> never added to whitelist. I have some servers that sends many mail to
> only one recipient, that successfully pass greylisting, but didn't
> added to whitelist.
> For example from first message, my server sends 1260 messages with 1
> try (first attempt) and it is a good candidate to autowhitelist, but
> it isn't pass AutoBlacklistCount value because from first query it
> gets count=1.
> Maybe better to count not number of different TrackKey in table, but
> sum of Count values of selected item like this?
>
> ### getting totalcount:
> my $sth = DBSelect("
> SELECT
> Sum( Count ) AS
> TotalCount
> FROM
> greylisting_tracking
> WHERE
> TrackKey =
> ".DBQuote($key)."
> AND FirstSeen >=
> ".DBQuote($addedTime)."
> ");
> ...
> if ($totalCount > 0 && $totalCount >=
> $policy{'AutoBlacklistCount'}) {
>
> ### getting failcount:
> $sth = DBSelect("
> SELECT
> Sum( Tries )
> AS FailCount
> FROM
>
> greylisting_tracking
> WHERE
> TrackKey =
> ".DBQuote($key)."
> AND FirstSeen
> >= ".DBQuote($addedTime)."
> AND Count = 0
> ");
>
> my $percentage = (
> $failCount / $totalCount ) * 100;
>
> What do you think about this modification?
>
>
> 2011/5/13 Alexey Murz Korepov <[email protected]>
>>
>> I add this problem in issues list
>> http://devlabs.linuxassist.net/issues/88 but maybe here is better
>> place for discuss problem.
>>
>> I use 2.0.10 version and have created the rule:
>>
>> INSERT INTO `greylisting` (`ID`, `PolicyID`, `Name`, `UseGreylisting`,
>> `GreylistPeriod`, `Track`, `GreylistAuthValidity`,
>> `GreylistUnAuthValidity`, `UseAutoWhitelist`, `AutoWhitelistPeriod`,
>> `AutoWhitelistCount`, `AutoWhitelistPercentage`, `UseAutoBlacklist`,
>> `AutoBlacklistPeriod`, `AutoBlacklistCount`,
>> `AutoBlacklistPercentage`, `Comment`, `Disabled`) VALUES
>> (1, 3, 'greylist_inbound', 1, 55, 'SenderIP:/32', 7776000, 259200, 1,
>> 604800, 100, 90, 1, 604800, 50, 100, '', 0);
>>
>> After some time I see some records in greylisting_autoblacklist table,
>> but whitelist is empty.
>> But in greylisting_tracking table I see candidates, for example:
>>
>> SELECT SUM(Tries), SUM(Count) FROM `greylisting_tracking` WHERE
>> `TrackKey` LIKE 'SenderIP:77.88.34.8/32'
>> GROUP BY TrackKey
>>
>> Result is:
>>
>> SUM(Tries): 1
>> SUM(Count): 1260
>>
>> So, this is mean that this sender IP sends successfully 1260 messages
>> with 1 greylist check, so this is better that 90%, setted in
>> AutoWhitelistPercentage.
>> Why policyd didn't add it into whitelist? Maybe something wrong in
>> rule settings?
>>
>> --
>> С уважением,
>> Алексей Murz Корепов.
>> Email, jabber: [email protected]
>
>
>
> --
> Best regards,
> Alexey Murz Korepov.
> Email, Jabber: [email protected]
>
--
Best regards,
Alexey Murz Korepov.
Email, Jabber: [email protected]
_______________________________________________
Users mailing list
[email protected]
http://lists.policyd.org/mailman/listinfo/users
