Help needed on syntax for IP address field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help needed on syntax for IP address field

Author

Topic

Ripples
Starting Member

5 Posts

Posted - 2003-06-26 : 15:25:05

I have a table with IP address's (a list of people spamming me). Up to now I add this to my mail server blacklist - but naturally the list is growing pretty large.

I'd like to use the * wildcard for the last block and cover all IP's in that block if say 10 appear with matching first 3 blocks.

Can anyone guide me how to generate a list of IPs where there are 10 occurrances with the first 3 number groups matching?

i.e.
123.123.123.*
125.66.4.*
etc

Thanks
Peter

Edited by - Ripples on 06/26/2003 15:37:45

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-26 : 15:41:46

use parsename and it's simple

select parsename(ip,4)+ '.' + parsename(ip,3)+ '.' + parsename(ip,2)+ '.*'
from tbl
group by parsename(ip,4),parsename(ip,3),parsename(ip,2)
having count(*) > 10

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Edited by - nr on 06/26/2003 15:42:22

Ripples
Starting Member

5 Posts

Posted - 2003-06-26 : 16:25:37

Thank nr,

Tried the code but get an error...

Code:
select PARSENAME(ServerIP,4)+ '.' + PARSENAME(ServerIP,3)+ '.' + PARSENAME(ServerIP,2)+ '.*' from JunkServers group by PARSENAME(ServerIP,4),PARSENAME(ServerIP,3),PARSENAME(ServerIP,2) having count(*) > 10

Error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'JunkServers.ServerIP' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I'm running SQL 2000 through Cold Fusion 5.

Thanks
Peter

Edited by - Ripples on 06/26/2003 16:26:11

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-26 : 17:57:34

oops try

select PARSENAME(ServerIP,4)+ '.' + PARSENAME(ServerIP,3)+ '.' + PARSENAME(ServerIP,2)+ '.*'
from JunkServers
group by PARSENAME(ServerIP,4)+ '.' + PARSENAME(ServerIP,3)+ '.' + PARSENAME(ServerIP,2)+ '.*'
having count(*) > 10

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ripples
Starting Member

5 Posts

Posted - 2003-06-26 : 23:39:42

Thanks 'nr' - Brilliant, works great!

Best wishes
Peter

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources