Search database from shell output...multiple files

[Guest_imported]

Hello,

Im working on a script that will
generate output based on a matching line
from a database. Im stuck!

Given the output from a shell script

10 200.171.14.12

I need to see if $2 matches an
ip from a known spammer in a text database.
IF the database contains a line like
200.171.14.12 REJECT

then print $0" ""BLACKLISTED"
should output

10 200.171.14.12 BLACKLISTED

If it doesnt match an ip in the database
Just Print $0
should output

10 200.171.14.12

Im not sure what to do, heres what
Ive tried.

#!/bin/sh

cat spam |awk 'BEGIN {
while( (getline listed < &quot;/etc/postfix/access&quot > 0)
}

listed ~ $2 {$3=&quot;BLACKLISTED&quot;}
print $1&quot; &quot;$2&quot; &quot;$3

close (&quot;/etc/postfix/access&quot}'

Thanks in advance

Robert

 

[Guest_imported]

Hello again!

This didnt work either but it seems a little
closer ??

#!/bin/sh

cat spam |awk 'BEGIN {
if (listed == $2) {
while( (getline listed < &quot;/etc/postfix/access&quot > 0)
print $1&quot; &quot;$2&quot; &quot;$3
close (&quot;/etc/postfix/access&quot
}
else
print
}'

Thanks,

Robert

 

[CaKiwi]

This should do it.

BEGIN {
n=1
while( (getline listed[n++] < &quot;/etc/postfix/access&quot > 0);
n--
close (&quot;/etc/postfix/access&quot
}
{
for (ix=1;ix<=n;ix++) {
if ($2 == listed[ix]) break
}
if (ix <= n)
print $0 &quot; BLACKLISTED&quot;
else
print
}

Hope this helps

CaKiwi

 

[Guest_imported]

Hello !

Looks like were almost there

output from script.

5 207.55.218.10
9 203.36.248.1
15 200.38.77.252
18 199.78.23.44
BLACKLISTED #blank line
7 200.42.64.81
7 211.72.252.7
9 200.35.80.90
9 211.21.86.94
19 212.119.170.130

should be

5 207.55.218.10 BLACKLISTED
9 203.36.248.1 BLACKLISTED
15 200.38.77.252 BLACKLISTED
18 199.78.23.44

7 200.42.64.81 BLACKLISTED
7 211.72.252.7 BLACKLISTED
9 200.35.80.90 BLACKLISTED
9 211.21.86.94 BLACKLISTED
19 212.119.170.130 BLACKLISTED

Thanks,

Robert

 

[CaKiwi]

It looks like we are not reading the access file correctly. Put a print statement in the while statement to debug it.

while( (getline listed[n++] < &quot;/etc/postfix/access&quot > 0) print listed[n-1];

If you have blank lines in your input add the following before the for statement.

if (NF<2) next

Post part of your access file if you are still having problems.

CaKiwi

 

[Guest_imported]

Here is what the access file looks like.
It also contains domain and email address
entrys, but I'm only concerned with ip's
at this point.

216.87.84.26 REJECT
194.140.65.252 REJECT
203.1.24.189 REJECT
194.154.200.2 REJECT
195.94.213.195 REJECT
200.38.77.252 REJECT
203.36.248.1 REJECT
somespamersdomain.com REJECT
anotherpammersdomain.com REJECT
removeme@spam.com REJECT

Thanks,

Robert

 

[CaKiwi]

How's this?

BEGIN {
n=0
while( (getline listed[n++] < &quot;/etc/postfix/access&quot > 0) {
if (/^[0-9]/) {
n++
listed[n] = $1
}
}
close (&quot;/etc/postfix/access&quot
}
{
if (NF < 2) {
print
next
}
for (ix=1;ix<=n;ix++) {
if ($2 == listed[ix]) break
}
if (ix <= n)
print $0 &quot; BLACKLISTED&quot;
else
print
}

CaKiwi

 

[CaKiwi]

Sorry I messed up. The while should be

while( (getline < &quot;/etc/postfix/access&quot > 0) {

CaKiwi

 

[Guest_imported]

That was AWESOME !!!!!!! it worked

Thank You So much !

I had worked for about 8 hours
on my approach and, couldnt have done
it without your help in a couple of
months.

In case your interested here is the rest
of the script that generates the input.



#!/bin/sh
# this script is great for detecting rumplestiltskin attacks
# on your mail server. It also catches a lot of spammers
# because most spammers never delete an address
# even if
# the user no longer exists; Unfortunatly it also catches
# some poorly maintained mailing lists. Some manual
# log checking is still required but this saves a lot of # time.

DATE=$(date &quot;+%b %e&quot

echo &quot;Mail to Unknown Users&quot;

grep &quot;User unknown&quot; /var/log/spam.log \ /var/log/messages | grep &quot;$DATE&quot; | cut -d[ -f3 | cut -d] -f1 | sort | uniq -c | sort | sed 's/^ *//' | egrep &quot;(^[5-9]|^[1-9][0-9][0-9]?[0-9]?)&quot;
echo
echo &quot;Sender address rejected&quot;
echo
grep &quot;Sender address rejected: Access denied&quot; \ /var/log/spam.log /var/log/messages | grep &quot;$DATE&quot; | cut -d[ -f3 | cut -d] -f1 | sort | uniq -c | sort | sed 's/^ *//' | egrep &quot;(^[5-9]|^[1-9][0-9][0-9]?[0-9]?)&quot;