(RADIATOR) IP Pool Verification - Useful SQL for Oracle & mySQL

[Paul]

Hi,

Some useful SQL for Oracle and mySQL if you want to check what's in your IP
pools.

Ouputs like:

POOL                                     Min IP          Max IP
---------------------------------------- --------------- ---------------
pool-193.100.100.1-username              010.001.000.001 010.001.003.254
pool-193.100.100.2-username              010.001.004.001 010.001.007.254
pool-193.100.100.3-username              010.001.008.001 010.001.011.254

Queries below:


##########
# mySQL
##########

select POOL ,
min(concat(lpad(substring(YIADDR,1,instr(YIADDR,'.')-1),3,'0'),'.',lpad(
substring(substring(YIADDR,instr(YIADDR,'.')+1),1,instr(substring(YIADDR
,instr(YIADDR,'.')+1) ,'.')-1),3,'0'),'.',
lpad(substring(substring(substring(YIADDR,instr(YIADDR,'.')+2),instr(sub
string(YIADDR,instr(YIADDR,'.')+1)
,'.')),1,instr(substring(substring(YIADDR,instr(YIADDR,'.')+2),instr(sub
string(YIADDR,instr(YIADDR,'.')+1) ,'.')),'.')-1),3,'0'),'.',
lpad(substring(substring(substring(YIADDR,instr(YIADDR,'.')+4),instr(sub
string(YIADDR,instr(YIADDR,'.')+1)
,'.')),instr(substring(substring(YIADDR,instr(YIADDR,'.')+2),instr(subst
ring(YIADDR,instr(YIADDR,'.')+1) ,'.')),'.')-1),3,'0'))) "Min IP" ,
max(concat(lpad(substring(YIADDR,1,instr(YIADDR,'.')-1),3,'0'),'.',lpad(
substring(substring(YIADDR,instr(YIADDR,'.')+1),1,instr(substring(YIADDR
,instr(YIADDR,'.')+1) ,'.')-1),3,'0'),'.',
lpad(substring(substring(substring(YIADDR,instr(YIADDR,'.')+2),instr(sub
string(YIADDR,instr(YIADDR,'.')+1)
,'.')),1,instr(substring(substring(YIADDR,instr(YIADDR,'.')+2),instr(sub
string(YIADDR,instr(YIADDR,'.')+1) ,'.')),'.')-1),3,'0'),'.',
lpad(substring(substring(substring(YIADDR,instr(YIADDR,'.')+4),instr(sub
string(YIADDR,instr(YIADDR,'.')+1)
,'.')),instr(substring(substring(YIADDR,instr(YIADDR,'.')+2),instr(subst
ring(YIADDR,instr(YIADDR,'.')+1) ,'.')),'.')-1),3,'0'))) "Max IP"
from RADPOOL group by POOL
order by 2;

############
# Oracle
############

select POOL ,
 min(
 lpad(substr(YIADDR,1,instr(YIADDR,'.')-1),3,'0')||'.'||
 lpad(substr(substr(YIADDR,instr(YIADDR,'.')+1),1,instr(substr(YIADDR
  ,instr(YIADDR,'.')+1) ,'.')-1),3,'0')||'.'||

lpad(substr(substr(substr(YIADDR,instr(YIADDR,'.')+2),instr(substr(YIADDR,in
str(YIADDR,'.')+1)

,'.')),1,instr(substr(substr(YIADDR,instr(YIADDR,'.')+2),instr(substr(YIADDR
,instr(YIADDR,'.')+1) ,'.')),'.')-1),3,'0')||'.'||

lpad(substr(substr(substr(YIADDR,instr(YIADDR,'.')+4),instr(substr(YIADDR,in
str(YIADDR,'.')+1)

,'.')),instr(substr(substr(YIADDR,instr(YIADDR,'.')+2),instr(substr(YIADDR,i
nstr(YIADDR,'.')+1) ,'.')),'.')-1),3,'0')) "Min IP"
,
 max(
 lpad(substr(YIADDR,1,instr(YIADDR,'.')-1),3,'0')||'.'||
 lpad(substr(substr(YIADDR,instr(YIADDR,'.')+1),1,instr(substr(YIADDR
  ,instr(YIADDR,'.')+1) ,'.')-1),3,'0')||'.'||

lpad(substr(substr(substr(YIADDR,instr(YIADDR,'.')+2),instr(substr(YIADDR,in
str(YIADDR,'.')+1)

,'.')),1,instr(substr(substr(YIADDR,instr(YIADDR,'.')+2),instr(substr(YIADDR
,instr(YIADDR,'.')+1) ,'.')),'.')-1),3,'0')||'.'||

lpad(substr(substr(substr(YIADDR,instr(YIADDR,'.')+4),instr(substr(YIADDR,in
str(YIADDR,'.')+1)

,'.')),instr(substr(substr(YIADDR,instr(YIADDR,'.')+2),instr(substr(YIADDR,i
nstr(YIADDR,'.')+1) ,'.')),'.')-1),3,'0')) "Max IP"
  from radius.radpool group by POOL
 order by 2;


Thanks,

Paul

___________________

Paul O'Shea
Level9 Networks
___________________





===
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.