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 [EMAIL PROTECTED] To unsubscribe, email '[EMAIL PROTECTED]' with 'unsubscribe radiator' in the body of the message.