My changes to SQL queries works not like I want, so I describe the idea: At now we operates with number of different pairs from-to of emails. For whitelist we count percents as passed/total
So, if we have server that very often sends mail from one email [email protected] to another [email protected], count of different pairs will not increase more than one, so this server will never adds to autowhitelist and I always will get the greylist delay from it. My offer is to look not only to number of pairs, but also to number of successfully delivered mail. So if we see one server that sends 532 messages from [email protected] to another [email protected], now it got totalcount=1 and didn't pass the AutoWhitelistCount more than1, but in my version we must compare 532 to AutoWhitelistCount. What do you think about this? On 17 May 2011 17:46, Alexey Murz Korepov <[email protected]> wrote: > Updated variant: > > ### 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 FailCount > FROM > > greylisting_tracking > WHERE > > TrackKey = ".DBQuote($key)." > AND > FirstSeen >= ".DBQuote($addedTime)." > AND Count = 0 > "); > my $percentage > = ( 1 - $failCount / $totalCount ) * 100; > > 2011/5/17 Alexey Murz Korepov <[email protected]>: >> ### 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 >> "); >> > > > > -- > 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
