OCS Inventory: OCSReports

Merge lp:~simonsp/ocsinventory-ocsreports/fix_ipdiscover_stale_noninventoried_missing into lp:ocsinventory-ocsreports

  1. fix_ipdiscover_stale_noninventoried_missing
  2. Merge into trunk
Proposed by Paul W. Simons
Status: Merged
Merge reported by: Gonéri Le Bouder
Merged at revision: not available
Proposed branch: lp:~simonsp/ocsinventory-ocsreports/fix_ipdiscover_stale_noninventoried_missing
Merge into: lp:ocsinventory-ocsreports
Diff against target: 108 lines (+33/-24)
2 files modified
plugins/main_sections/ms_ipdiscover/ms_custom_info.php (+5/-2)
plugins/main_sections/ms_ipdiscover/ms_ipdiscover.php (+28/-22)
To merge this branch: bzr merge lp:~simonsp/ocsinventory-ocsreports/fix_ipdiscover_stale_noninventoried_missing
To post a comment you must log in.
Revision history for this message
Paul W. Simons (simonsp) wrote :

This fixes a problem where an update network map would not be used over a stale inventory. If a pc was inventoried on subnet A, and then moved to subnet B, when displaying the results of devices on subnet B, it would not show up. I have modified sql statements to force the stale inventory to show up as a non-inventoried machine, since the inventory needs to be updated.

Revision history for this message
Gonéri Le Bouder (goneri) wrote :

Thank you! :)

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
=== modified file 'plugins/main_sections/ms_ipdiscover/ms_custom_info.php'
--- plugins/main_sections/ms_ipdiscover/ms_custom_info.php 2009-11-13 10:57:00 +0000
+++ plugins/main_sections/ms_ipdiscover/ms_custom_info.php 2010-01-26 15:35:21 +0000
@@ -125,8 +125,11 @@
125 //$netid=mysql_escape_string($protectedGet['value']);125 //$netid=mysql_escape_string($protectedGet['value']);
126 if ($protectedGet['prov'] == "no_inv"){126 if ($protectedGet['prov'] == "no_inv"){
127 $title=$l->g(947);127 $title=$l->g(947);
128 $sql="SELECT ip, mac, mask, date, name FROM netmap WHERE netid='".$protectedGet['value']."' AND mac NOT IN (SELECT DISTINCT(macaddr) FROM networks) 128 $sql="SELECT ip, mac, mask, date, name FROM netmap n
129 AND mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices)";129 LEFT JOIN networks ns ON ns.macaddr=n.mac
130 WHERE n.netid='".$protectedGet['value']."'
131 AND (ns.macaddr IS NULL OR ns.IPSUBNET <> n.netid)
132 AND mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices)";
130 $list_fields= array($l->g(34) => 'ip','MAC'=>'mac',133 $list_fields= array($l->g(34) => 'ip','MAC'=>'mac',
131 $l->g(208)=>'mask',134 $l->g(208)=>'mask',
132 $l->g(232)=>'date',135 $l->g(232)=>'date',
133136
=== modified file 'plugins/main_sections/ms_ipdiscover/ms_ipdiscover.php'
--- plugins/main_sections/ms_ipdiscover/ms_ipdiscover.php 2009-11-16 17:51:40 +0000
+++ plugins/main_sections/ms_ipdiscover/ms_ipdiscover.php 2010-01-26 15:35:21 +0000
@@ -63,11 +63,12 @@
63 GROUP BY netid) 63 GROUP BY netid)
64 ident on ipdiscover.RSX=ident.RSX left join64 ident on ipdiscover.RSX=ident.RSX left join
65 (SELECT COUNT(DISTINCT mac) as c,'NON IDENTIFIE' as TYPE,netid as RSX65 (SELECT COUNT(DISTINCT mac) as c,'NON IDENTIFIE' as TYPE,netid as RSX
66 FROM netmap 66 FROM netmap n
67 WHERE mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices) 67 LEFT JOIN networks ns ON ns.macaddr=n.mac
68 and mac NOT IN (SELECT DISTINCT(macaddr) FROM networks) 68 WHERE n.mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices)
69 and netid in ('".$list_rsx."')69 and (ns.macaddr IS NULL OR ns.IPSUBNET <> n.netid)
70 GROUP BY netid) 70 and n.netid in ('".$list_rsx."')
71 GROUP BY n.netid)
71 non_ident on non_ident.RSX=ipdiscover.RSX where non_ident.c is not null and ident.c is not null72 non_ident on non_ident.RSX=ipdiscover.RSX where non_ident.c is not null and ident.c is not null
72 union73 union
73 select inv.RSX,74 select inv.RSX,
@@ -93,11 +94,12 @@
93 GROUP BY netid) 94 GROUP BY netid)
94 ident on ipdiscover.RSX=ident.RSX left join95 ident on ipdiscover.RSX=ident.RSX left join
95 (SELECT COUNT(DISTINCT mac) as c,'NON IDENTIFIE' as TYPE,netid as RSX96 (SELECT COUNT(DISTINCT mac) as c,'NON IDENTIFIE' as TYPE,netid as RSX
96 FROM netmap 97 FROM netmap n
97 WHERE mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices) 98 LEFT JOIN networks ns ON ns.macaddr=n.mac
98 and mac NOT IN (SELECT DISTINCT(macaddr) FROM networks) 99 WHERE n.mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices)
99 and netid in ('".$list_rsx."')100 and (ns.macaddr IS NULL OR ns.IPSUBNET <> n.netid)
100 GROUP BY netid) 101 and n.netid in ('".$list_rsx."')
102 GROUP BY n.netid)
101 non_ident on non_ident.RSX=ipdiscover.RSX where non_ident.c is null and ident.c is not null103 non_ident on non_ident.RSX=ipdiscover.RSX where non_ident.c is null and ident.c is not null
102 union104 union
103 select inv.RSX,105 select inv.RSX,
@@ -123,11 +125,12 @@
123 GROUP BY netid) 125 GROUP BY netid)
124 ident on ipdiscover.RSX=ident.RSX left join126 ident on ipdiscover.RSX=ident.RSX left join
125 (SELECT COUNT(DISTINCT mac) as c,'NON IDENTIFIE' as TYPE,netid as RSX127 (SELECT COUNT(DISTINCT mac) as c,'NON IDENTIFIE' as TYPE,netid as RSX
126 FROM netmap 128 FROM netmap n
127 WHERE mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices) 129 LEFT JOIN networks ns ON ns.macaddr=n.mac
128 and mac NOT IN (SELECT DISTINCT(macaddr) FROM networks) 130 WHERE n.mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices)
129 and netid in ('".$list_rsx."')131 and (ns.macaddr IS NULL OR ns.IPSUBNET <> n.netid)
130 GROUP BY netid) 132 and n.netid in ('".$list_rsx."')
133 GROUP BY n.netid)
131 non_ident on non_ident.RSX=ipdiscover.RSX where ident.c is null and non_ident.c is not null134 non_ident on non_ident.RSX=ipdiscover.RSX where ident.c is null and non_ident.c is not null
132 union135 union
133 select inv.RSX,136 select inv.RSX,
@@ -153,11 +156,12 @@
153 GROUP BY netid) 156 GROUP BY netid)
154 ident on ipdiscover.RSX=ident.RSX left join157 ident on ipdiscover.RSX=ident.RSX left join
155 (SELECT COUNT(DISTINCT mac) as c,'NON IDENTIFIE' as TYPE,netid as RSX158 (SELECT COUNT(DISTINCT mac) as c,'NON IDENTIFIE' as TYPE,netid as RSX
156 FROM netmap 159 FROM netmap n
157 WHERE mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices) 160 LEFT JOIN networks ns ON ns.macaddr=n.mac
158 and mac NOT IN (SELECT DISTINCT(macaddr) FROM networks) 161 WHERE n.mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices)
159 and netid in ('".$list_rsx."')162 and (ns.macaddr IS NULL OR ns.IPSUBNET <> n.netid)
160 GROUP BY netid) 163 and n.netid in ('".$list_rsx."')
164 GROUP BY n.netid)
161 non_ident on non_ident.RSX=ipdiscover.RSX where ident.c is null and non_ident.c is null165 non_ident on non_ident.RSX=ipdiscover.RSX where ident.c is null and non_ident.c is null
162 ) toto";166 ) toto";
163167
@@ -199,10 +203,12 @@
199 $tab_options['NO_TRI']['LBL_RSX']='LBL_RSX';203 $tab_options['NO_TRI']['LBL_RSX']='LBL_RSX';
200 204
201 $sql_count="SELECT COUNT(DISTINCT mac) as total205 $sql_count="SELECT COUNT(DISTINCT mac) as total
202 FROM netmap 206 FROM netmap n
207 LEFT OUTER JOIN networks ns ON ns.macaddr = mac
203 WHERE mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices) 208 WHERE mac NOT IN (SELECT DISTINCT(macaddr) FROM network_devices)
204 and mac NOT IN (SELECT DISTINCT(macaddr) FROM networks) 209 and ( ns.macaddr IS NULL OR ns.IPSUBNET <> n.netid)
205 and netid in ('".$list_rsx."')";210 and netid in ('".$list_rsx."')";
211
206 $res_count = mysql_query($sql_count, $_SESSION['OCS']["readServer"] );212 $res_count = mysql_query($sql_count, $_SESSION['OCS']["readServer"] );
207 $val_count = mysql_fetch_array( $res_count );213 $val_count = mysql_fetch_array( $res_count );
208 $strEnTete = $_SESSION['OCS']["ipdiscover_id"]." ".$dpt[$protectedPost['DPT_CHOISE']]." <br>";214 $strEnTete = $_SESSION['OCS']["ipdiscover_id"]." ".$dpt[$protectedPost['DPT_CHOISE']]." <br>";

Subscribers

People subscribed via source and target branches