ip contained within subnet
I've been trying, without any luck, to determine whether or an IP address is contained within (a list of) subnet(s). The computer assets table contains subnet information, cidr type with /32 for individual hosts, as one would expect. I'm selecting the IP of hosts that fit certain criteria, but also want to exclude those hosts if they are contained in one of my subnets:
select a.ip, b.foo from tablea a, tableb, b where a.id=b.id and a.ip ! <<= (select network from assets where network is not null)
1. "! <<=" doesn't seem to be valid
2. the subquery returns more than one row
3. I'm not getting anywhere trying to write a function to take the ip and loop through all the subnets using the "<<=" operator and, I guess, & all the boolean results to determine t or f.
Has anyone run into such a problem? Any suggestions?
Scott
--- Let us all bask in television's warm glowing warming glow ---
Scott Brunza 860.326.3637 scottso@sonalysts.comThis e-mail and any files transmitted with it may be proprietary
and are intended solely for the use of the individual or entity
to whom they are addressed. If you have received this e-mail in
error please notify the sender.
Attachments:
Scott Brunza <scottso@sonalysts.com> writes:
select a.ip, b.foo from tablea a, tableb, b where a.id=b.id and a.ip ! <<= (select network from assets where network is not null)
This is definitely not legal SQL. I think you are looking for something
like
select a.ip, b.foo from tablea a, tableb b where a.id=b.id and
not (a.ip <<= any (select network from assets where network is not null))
regards, tom lane
select a.ip, b.foo from tablea a, tableb b where a.id=b.id and
not (a.ip <<= any (select network from assets where network is not null))regards, tom lane
Yes, that did it. I guess I'll have to hit the books again to fully grok what you did. THANKS!
--- Let us all bask in television's warm glowing warming glow ---
Scott Brunza 860.326.3637 scottso@sonalysts.comThis e-mail and any files transmitted with it may be proprietary
and are intended solely for the use of the individual or entity
to whom they are addressed. If you have received this e-mail in
error please notify the sender.