"JC" wrote in message
...
On Tue, 22 Feb 2005 10:14:00 -0000, "Bob Phillips"
wrote:

The process is simple and difficult at the same time. The simple part is
to
look for multiple entries with the same source IP address. What makes it
more
difficult is that the end result I want is really based on ISP address
ranges.

ISPs have large address ranges. For example ISP X may have an IP address
range
from 1.2.3.4 - 1.2.32.255. A complication is that ISP X may have
multiple IP
address ranges such as 1.2.3.4 - 1.2.32.255 and 12.16.0.0 - 12.18.255.255.

That partly explains the 2 groups of ISP components, I guess it makes that
checking easier.

It would be easy to program a macro to colour multiple entries from a
single IP
address but not so easy to cater for ISP address ranges. It requires
knowledge
of the entire range of IP address allocations probably in some form of
lookup
table and an updating process to keep it current. I check with APNIC etc
when
the number of entries exceeds 3 to get the ISP details including address
range.
This I put into column G and may result in me uncovering more entries
coming
from that ISP's address range. I have listed the most common offending
ISPs in
a workbook but this is only a tiny proportion of the entire list.

But it would be easier would it not to maintain a list on a separate
spreadsheet and write code that checks that database rather than do it
manually. Maintaining that list is a simple typing exercise.

I send out emails to the ISP if more than 3 entries occur in the month.
If
more entries appear on following days I send out additional emails listing
all
entries to date this month to reinforce my original request that the spam
etc
being sent be stopped. Detecting these additional entries would be more
difficult if the macro automated the colouring process.

This process results in about 20% of the entries being coloured with the
rest
remaining grey. I realise that the system I am using is inefficient and
does
not detect all cases of multiple entries coming from an ISPs address
range.

It seems incredibly tedious to me. I understand it might be necessary, but I
am glad it is not me doing it.

I hope this helps you understand the process. If you would like to see a
copy
of the current spreadsheet I could email it to you.

If you think there is further help I could offer, I would be happy to.