Thread: 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:
This e-mail and any files transmitted with it may be proprietary
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 ---
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.
Attachment
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
isnot 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
--- Let us all bask in television's warm glowing warming glow ---
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.