Welcome, Guest. Please login or register.

Author Topic: IP address management  (Read 11687 times)

pc_sg

  • Full Member
  • ***
  • Posts: 245
    • View Profile
Re: IP address management
« Reply #15 on: March 15, 2012, 08:19:16 AM »
Hello,
In my compagny we've decide to add a new report to list all network.
Here is the code:

Hi Richard, where can I  find the needed "inc/libdb-rl.php" ? Is not present in a 1.0.7 installation.

TIA!

Paolo

richard.lajaunie

  • Newbie
  • *
  • Posts: 26
    • View Profile
Re: IP address management
« Reply #16 on: March 15, 2012, 02:23:13 PM »
Hello,
Ooups, i forgot to delete this line.
You can detele this include line.

In case of ...
The libdb-rl.php is use only to generate the SQL query ($query) in the script.

if you want to use my original code, replace the line $query= .... by
Code: [Select]
$query = GenQuery2('networks','x',"group_concat(distinct vrfname) as vrfname,inet_ntoa(ifip&mask) as network, inet_ntoa(mask) as mask,group_concat(distinct concat(device,' (',ifname,')')) as devices",$ord,'',array($ina,''),array($opa,''),array($sta,''),array('')  );
here is the code of libdb-rl.php
This lib provide a modification of the function GenQuery of the standard libdb-msq.php



Code: [Select]
<?php
/*
#============================================================================
# Program: libdb-rl.php
# Programmer: Richard Lajaunie
#
*/
function GenQuery2($tab,$do='s',$col='*',$ord='',$lim='',$in=array(),$op=array(),$st=array(),$co=array() ,$leftkey=array()){

if($do == 'i'){
return "INSERT INTO $tab ("implode(',',$in) .") VALUES (\""implode('","',$st) ."\")";
}elseif($do == 'u'){
if( $in[0] ){
$x 0;
foreach ($in as $c){
if($c){$s[]="$c=\"$st[$x]\"";}
$x++;
}
return "UPDATE $tab SET "implode(',',$s) ." WHERE $col=\"$ord\"";
}
}elseif($do ==  't'){
$lt = ($tab)?"LIKE \"$tab\"":"";
return "SHOW TABLES $lt";
}elseif($do == 'c'){
return "SHOW COLUMNS FROM $tab";
}else{
$l = ($lim) ? "LIMIT $lim" "";
if($ord == 'ifname'){
$od "ORDER BY device,SUBSTRING_INDEX(ifname, '/', 1), SUBSTRING_INDEX(ifname, '/', -1)*1+0";
}elseif($ord == 'ifname desc'){
$od "ORDER BY device desc,SUBSTRING_INDEX(ifname, '/', 1), SUBSTRING_INDEX(ifname, '/', -1)*1+0";
}elseif($ord){
$od "ORDER BY $ord";
}else{
$od "";
}
if( isset($st[0]) and $st[0] != ""  ){
$w "WHERE";
$x 0;
do{
$cop = isset($co[$x]) ? $co[$x] : "";
if($op[$x]){
$c $in[$x];
$v $st[$x];
$o $op[$x];
if( preg_match("/^(firstseen|lastseen|time|i[fp]update)$/",$c) and !preg_match("/^[0-9]+$/",$v) ){
$v strtotime($v);
}elseif($c == 'mac'){
$v preg_replace("/[.:-]/",""$v);
}elseif($c == 'ips'||$c=='network'){
$c "inet_ntoa(ip)";
}elseif(preg_match("/^(origip|ip)$/",$c) and !preg_match('/^[0-9]+$/',$v) ){
if( strstr($v,'/') ){
list($ip$prefix) = explode('/'$v);
$dip sprintf("%u"ip2long($ip));
$dmsk 0xffffffff << (32 $prefix);
$dnet sprintf("%u"ip2long($ip) & $dmsk );
$c "$c & $dmsk";
$v $dnet;
}else{
$v sprintf("%u"ip2long($v));
}
}
if( strpos($op[$x],'CI') ){
$c "LCASE($in[$x])";
$o substr($op[$x],0,-2);
}
if(strpos($op[$x],'exp') and $v == '' ){$v '.';}
$w .= " $c $o \"$v\" $cop";
}
$x++;
}while($cop);
}elseif( isset($co[0]) and $co[0] != "" ){
$w "WHERE $in[0] $co[0] $in[1]";
}else{
$w "";
}
if($do == 'd'){
return "DELETE FROM $tab $w $od $l";
}elseif($do == 'g'){
return "SELECT $col,count(*) FROM  $tab $w GROUP BY $col $od $l";
}elseif($do == 'a'){
return "SELECT ".(($col != '*')?"$col,":"")."count(*),avg($lim) as avg FROM  $tab $w".(($col != '*')?"GROUP BY $col":"")." $od";
}elseif($do == 'm'){
return "SELECT ".(($col != '*')?"$col,":"")."count(*),sum($lim) as sum FROM  $tab $w".(($col != '*')?"GROUP BY $col":"")." $od;";
}elseif($do == 'x'){
return "SELECT ".(($col != '*')?"$col":"")." FROM  $tab $w  GROUP BY inet_ntoa(ifip&mask)  ".(($od!='') ? "$od""ORDER BY vrfname");
}elseif($do == 'z'){
return "SELECT ".(($col != '*')?"$col":"")." FROM  $tab $w  GROUP BY vlanid  ".(($od!='') ? "$od""ORDER BY vlanid");
}else{
if (is_array($tab)){
$inner_str $tab[0]." t0";
for($i=1$i<count($tab); $i++){
$ii $i-1;
$inner_str .= " INNER JOIN {$tab[$i]} t$i ON t0.{$leftkey[$ii]}=t$i.{$leftkey[$ii]}";
}
return "SELECT $col FROM $inner_str $w $od $l";
}else{
return "SELECT $col FROM $tab $w $od $l";
}
}
}
}
?>



bye
Richard

pc_sg

  • Full Member
  • ***
  • Posts: 245
    • View Profile
Re: IP address management
« Reply #17 on: March 16, 2012, 08:47:40 AM »
Hi again, Richard,
if I use your original code (commenting unnecessary include), page runs, but filters are not applied. I'm not a php coder, but seems that "$ina" is not used in the query line (comparing your propsed modified one with original).

Instead, using "missing" include file and the modified query, the output shows strange networks (mine are 10.6x.x.x, output shows 0.0.0.10), and nothing at all with a filter (with a "error" that shows "Unknown column 'ip' in 'where clause'")

Bye

Paolo

richard.lajaunie

  • Newbie
  • *
  • Posts: 26
    • View Profile
Re: IP address management
« Reply #18 on: March 16, 2012, 09:57:29 AM »
hello paolo,
By testing some new thing i've made few mistakes.

Here is the patch:
Code: [Select]
diff --git a/html/Reports-Networks.php b/html/Reports-Networks.php
index b4f7950..4fa1ea9 100644
--- a/html/Reports-Networks.php
+++ b/html/Reports-Networks.php
@@ -77,8 +77,8 @@ if ($ina){
        echo "</tr>\n";
 
        $link   = @DbConnect($dbhost,$dbuser,$dbpass,$dbname);
-       $query  = GenQuery2('networks','x',"group_concat(distinct vrfname) as vrfname,inet_ntoa(ifip&mask) as network, inet_ntoa(mask) as mask,group_concat(distinct c
-       $query  = "SELECT group_concat(distinct vrfname) as vrfname,ifip&mask as network, mask as mask,group_concat(distinct concat(device,' (',ifname,')')) as device
+       $query  = GenQuery2('networks','x',"group_concat(distinct vrfname) as vrfname,ifip&mask as network, mask as mask,group_concat(distinct concat(device,' (',ifna
+       //$query        = "SELECT group_concat(distinct vrfname) as vrfname,ifip&mask as network, mask as mask,group_concat(distinct concat(device,' (',ifname,')')) a
        $res    = @DbQuery($query,$link);
        if($res){
                $row = 0;
diff --git a/html/inc/libdb-rl.php b/html/inc/libdb-rl.php
index 5b11466..60daa88 100644
--- a/html/inc/libdb-rl.php
+++ b/html/inc/libdb-rl.php
@@ -46,7 +46,7 @@ function GenQuery2($tab,$do='s',$col='*',$ord='',$lim='',$in=array(),$op=array()
                                        }elseif($c == 'mac'){
                                                $v = preg_replace("/[.:-]/","", $v);
                                        }elseif($c == 'ips'||$c=='network'){
-                                               $c = "inet_ntoa(ip)";
+                                               $c = "inet_ntoa(ifip&mask)";
                                        }elseif(preg_match("/^(origip|ip)$/",$c) and !preg_match('/^[0-9]+$/',$v) ){
                                                if( strstr($v,'/') ){
                                                        list($ip, $prefix) = explode('/', $v);


Richard

pc_sg

  • Full Member
  • ***
  • Posts: 245
    • View Profile
Re: IP address management
« Reply #19 on: March 16, 2012, 10:40:29 AM »
Sorry Richard, to avoid problem with patching, could you repost both codes already patched?

Easier and quicker.

TIA!

Paolo

richard.lajaunie

  • Newbie
  • *
  • Posts: 26
    • View Profile
Re: IP address management
« Reply #20 on: March 16, 2012, 12:27:52 PM »
Code: [Select]
<?php
/*
#============================================================================
# Program: Topology-Networks.php
# Programmer: Remo Rickli
#
# DATE COMMENT
# -----------------------------------------------------------
# 04/07/07 initial version.
# 30/08/07 implemented CSS scheme
# 20/09/07 implemented language support
*/

$printable 1;

include_once (
"inc/header.php");
include_once (
"inc/libdev.php");
include_once (
"inc/libdb-rl.php");

$_GET sanitize($_GET);
$sta = isset($_GET['sta']) ? $_GET['sta'] : "";
$stb = isset($_GET['stb']) ? $_GET['stb'] : "";
$ina = isset($_GET['ina']) ? $_GET['ina'] : "";
$inb = isset($_GET['inb']) ? $_GET['inb'] : "";
$opa = isset($_GET['opa']) ? $_GET['opa'] : "";
$opb = isset($_GET['opb']) ? $_GET['opb'] : "";
$cop = isset($_GET['cop']) ? $_GET['cop'] : "";
$ord = isset($_GET['ord']) ? $_GET['ord'] : "";
$col = isset($_GET['col']) ? $_GET['col'] : array('vrfname','network','mask','devices','population');

$cols = array( "vrfname"=>"Vrf",
"network"=>"Network",
"mask"=>$msklbl,
"devices"=>"Devices",
"population"=>$poplbl
);
?>

<h1><?=$netlbl?> <?=$lstlbl?></h1>
<form method="get" name="list" action="<?=$self?>.php">
<table class="content"><tr class="<?=$modgroup[$self]?>1">
<th width="80"><a href="<?=$self?>.php"><img src="img/32/<?=$selfi?>.png"></a></th>
<th valign="top"><?=$cndlbl?> A<p>
<SELECT size="1" name="ina">
<?
//foreach ($cols as $k => $v){
       //echo "<option value=\"$k\"".( ($ina == $k)?"selected":"").">$v\n";
       echo "<option value=\"vrfname\"".( ($ina == 'vrfname')?"selected":"").">Vrf\n";
       echo "<option value=\"network\"".( ($ina == 'network')?"selected":"").">Network\n";
//}
?>
</SELECT>
<SELECT size="1" name="opa">
<? selectbox("oper",$opa);?>
</SELECT>
<p>
<input type="text" name="sta" value="<?=$sta?>" size="20">
</th>
<th valign="top"><?=$dislbl?><p>
<SELECT MULTIPLE name="col[]" size=4>
<?
foreach ($cols as $k => $v){
       echo "<option value=\"$k\"".((in_array($k,$col))?"selected":"").">$v\n";
}
?>
</SELECT>
</th>
<th width="80"><input type="submit" value="<?=$sholbl?>"></th>
</tr></table></form><p>
<?
if ($ina){
?>
<table class="content"><tr class="<?=$modgroup[$self]?>2">
<?
foreach($col as $h){
ColHead($h);
}
echo "</tr>\n";

$link = @DbConnect($dbhost,$dbuser,$dbpass,$dbname);
$query = GenQuery2('networks','x',"group_concat(distinct vrfname) as vrfname,ifip&mask as network, mask as mask,group_concat(distinct concat(device,' (',ifname,')')) as devices",$ord,'',array($ina,''),array($opa,''),array($sta,''),array('')  );
//$query = "SELECT group_concat(distinct vrfname) as vrfname,ifip&mask as network, mask as mask,group_concat(distinct concat(device,' (',ifname,')')) as devices FROM networks GROUP BY inet_ntoa(ifip&mask) ".($ord ? "ORDER BY $ord" : '');
$res = @DbQuery($query,$link);
if($res){
$row = 0;
while( ($m = @DbFetchArray($res)) ){
if ($row % 2){$bg = "txta"; $bi = "imga";}else{$bg = "txtb"; $bi = "imgb";}
$row++;
echo "<tr class=\"$bg\">";
if(in_array("vrfname",$col)){echo "<td>{$m['vrfname']}</td>\n";}
if(in_array("network",$col)){echo "<td>".long2ip($m['network'])."</td>";}
if(in_array("mask",$col)){echo "<td>".long2ip($m['mask'])."</td>";}
if(in_array("devices",$col)){echo "<td>{$m['devices']}</td>";}
if(in_array("population",$col)){echo "<td><a href=\"Reports-Interfaces.php?ina=devip&opa=regexp&sta=".long2ip($m['network'])."%2F".substr_count(decbin($m['mask']),'1')."&rep[]=pop&lim=10&do=Show\"><img src=\"img/16/nods.png\"></a></td>";}
echo "</tr>\n";
}
@DbFreeResult($res);
}else{
print @DbError($link);
}
?>
</table>
<table class="content">
<!--<tr class="<?=$modgroup[$self]?>2"><td><?=$row?> <?=$vallbl?><br> (<?=$query?>)<br>(<?=$query2?>)</td></tr>-->
<tr class="<?=$modgroup[$self]?>2"><td><?=$row?> <?=$vallbl?><br> (<?=$query?>)</td></tr>
</table>
<?
}
include_once ("inc/footer.php");
?>

---

Code: [Select]
<?php
//===============================
// mySQL functions.
//===============================

function GenQuery2($tab,$do='s',$col='*',$ord='',$lim='',$in=array(),$op=array(),$st=array(),$co=array() ,$leftkey=array()){

if($do == 'i'){
return "INSERT INTO $tab ("implode(',',$in) .") VALUES (\""implode('","',$st) ."\")";
}elseif($do == 'u'){
if( $in[0] ){
$x 0;
foreach ($in as $c){
if($c){$s[]="$c=\"$st[$x]\"";}
$x++;
}
return "UPDATE $tab SET "implode(',',$s) ." WHERE $col=\"$ord\"";
}
}elseif($do ==  't'){
$lt = ($tab)?"LIKE \"$tab\"":"";
return "SHOW TABLES $lt";
}elseif($do == 'c'){
return "SHOW COLUMNS FROM $tab";
}else{
$l = ($lim) ? "LIMIT $lim" "";
if($ord == 'ifname'){
$od "ORDER BY device,SUBSTRING_INDEX(ifname, '/', 1), SUBSTRING_INDEX(ifname, '/', -1)*1+0";
}elseif($ord == 'ifname desc'){
$od "ORDER BY device desc,SUBSTRING_INDEX(ifname, '/', 1), SUBSTRING_INDEX(ifname, '/', -1)*1+0";
}elseif($ord){
$od "ORDER BY $ord";
}else{
$od "";
}
if( isset($st[0]) and $st[0] != ""  ){
$w "WHERE";
$x 0;
do{
$cop = isset($co[$x]) ? $co[$x] : "";
if($op[$x]){
$c $in[$x];
$v $st[$x];
$o $op[$x];
if( preg_match("/^(firstseen|lastseen|time|i[fp]update)$/",$c) and !preg_match("/^[0-9]+$/",$v) ){
$v strtotime($v);
}elseif($c == 'mac'){
$v preg_replace("/[.:-]/",""$v);
}elseif($c == 'ips'||$c=='network'){
$c "inet_ntoa(ifip&mask)";
}elseif(preg_match("/^(origip|ip)$/",$c) and !preg_match('/^[0-9]+$/',$v) ){
if( strstr($v,'/') ){
list($ip$prefix) = explode('/'$v);
$dip sprintf("%u"ip2long($ip));
$dmsk 0xffffffff << (32 $prefix);
$dnet sprintf("%u"ip2long($ip) & $dmsk );
$c "$c & $dmsk";
$v $dnet;
}else{
$v sprintf("%u"ip2long($v));
}
}
if( strpos($op[$x],'CI') ){
$c "LCASE($in[$x])";
$o substr($op[$x],0,-2);
}
if(strpos($op[$x],'exp') and $v == '' ){$v '.';}
$w .= " $c $o \"$v\" $cop";
}
$x++;
}while($cop);
}elseif( isset($co[0]) and $co[0] != "" ){
$w "WHERE $in[0] $co[0] $in[1]";
}else{
$w "";
}
if($do == 'd'){
return "DELETE FROM $tab $w $od $l";
}elseif($do == 'g'){
return "SELECT $col,count(*) FROM  $tab $w GROUP BY $col $od $l";
}elseif($do == 'a'){
return "SELECT ".(($col != '*')?"$col,":"")."count(*),avg($lim) as avg FROM  $tab $w".(($col != '*')?"GROUP BY $col":"")." $od";
}elseif($do == 'm'){
return "SELECT ".(($col != '*')?"$col,":"")."count(*),sum($lim) as sum FROM  $tab $w".(($col != '*')?"GROUP BY $col":"")." $od;";
}elseif($do == 'x'){
return "SELECT ".(($col != '*')?"$col":"")." FROM  $tab $w  GROUP BY inet_ntoa(ifip&mask)  ".(($od!='') ? "$od""ORDER BY vrfname");
}elseif($do == 'z'){
return "SELECT ".(($col != '*')?"$col":"")." FROM  $tab $w  GROUP BY vlanid  ".(($od!='') ? "$od""ORDER BY vlanid");
}else{
if (is_array($tab)){
$inner_str $tab[0]." t0";
for($i=1$i<count($tab); $i++){
$ii $i-1;
$inner_str .= " INNER JOIN {$tab[$i]} t$i ON t0.{$leftkey[$ii]}=t$i.{$leftkey[$ii]}";
}
return "SELECT $col FROM $inner_str $w $od $l";
}else{
return "SELECT $col FROM $tab $w $od $l";
}
}
}
}
?>


pc_sg

  • Full Member
  • ***
  • Posts: 245
    • View Profile
Re: IP address management
« Reply #21 on: March 16, 2012, 03:29:12 PM »
Thanks!

Paolo

rickli

  • Administrator
  • Hero Member
  • *****
  • Posts: 2509
    • View Profile
    • NeDi
Re: IP address management
« Reply #22 on: May 13, 2012, 01:18:24 PM »
I looked at it, but it's not really compliant to my 1.0.8 code anymore :-/ What are my network reports missing, compared to this one?
Please consider Other-Invoices on your NeDi installation for an annual contribution, tx!
-Remo

michieltimmers

  • Newbie
  • *
  • Posts: 35
    • View Profile
Re: IP address management
« Reply #23 on: January 07, 2013, 10:53:00 AM »
Not quite what you wanted, but maybe this could be an alternative? The beauty is, it could be combined with DNS to find stale entries or other IP mgmt tools...


How can I get this output that you have attached?

rickli

  • Administrator
  • Hero Member
  • *****
  • Posts: 2509
    • View Profile
    • NeDi
Re: IP address management
« Reply #24 on: January 07, 2013, 07:12:17 PM »
Report-Interfaces then select Network Population
Please consider Other-Invoices on your NeDi installation for an annual contribution, tx!
-Remo