diff options
Diffstat (limited to 'web/api')
| -rwxr-xr-x | web/api/public/dhcp | 4 | ||||
| -rwxr-xr-x | web/api/public/distro-tree | 2 | ||||
| -rwxr-xr-x | web/api/public/location | 2 | ||||
| -rwxr-xr-x | web/api/public/switch-state | 9 | ||||
| -rwxr-xr-x | web/api/public/switches | 2 | ||||
| -rwxr-xr-x | web/api/read/distro-management | 2 | ||||
| -rwxr-xr-x | web/api/read/switches-management | 4 | ||||
| -rwxr-xr-x | web/api/write/switch-update | 2 |
8 files changed, 14 insertions, 13 deletions
diff --git a/web/api/public/dhcp b/web/api/public/dhcp index 88f1eb5..0b0bb49 100755 --- a/web/api/public/dhcp +++ b/web/api/public/dhcp @@ -8,13 +8,13 @@ use warnings; use Data::Dumper; nms::web::setwhen('60m'); -my $q = $nms::web::dbh->prepare('select distinct on (sysname) extract(epoch from date_trunc(\'second\',time)) as time,sysname from dhcp join switches on dhcp.switch = switches.switch where ' . $nms::web::when . ' order by sysname,time desc;'); +my $q = $nms::web::dbh->prepare('select distinct on (sysname) extract(epoch from date_trunc(\'second\',time)) as time,sysname from dhcp join switches on dhcp.switch = switches.switch where ' . $nms::web::when . ' and switches.deleted = false order by sysname,time desc;'); $q->execute(); while ( my $ref = $q->fetchrow_hashref() ) { my $sysname = $ref->{'sysname'}; $json{'dhcp'}{$ref->{'sysname'}} = $ref->{'time'}; } -my $q2 = $nms::web::dbh->prepare("select sysname,count(distinct mac) as clients,count(distinct ip) as addresses,count(mac) as acks from dhcp natural join switches where $nms::web::when group by switches.sysname;"); +my $q2 = $nms::web::dbh->prepare("select sysname,count(distinct mac) as clients,count(distinct ip) as addresses,count(mac) as acks from dhcp natural join switches where $nms::web::when and switches.deleted = false group by switches.sysname;"); $q2->execute(); while (my $ref = $q2->fetchrow_hashref()) { $nms::web::json{'switches'}{$ref->{sysname}} = $ref; diff --git a/web/api/public/distro-tree b/web/api/public/distro-tree index 5d93e1b..cff35ec 100755 --- a/web/api/public/distro-tree +++ b/web/api/public/distro-tree @@ -12,7 +12,7 @@ use Data::Dumper; $nms::web::cc{'max-age'} = "20"; -my $q2 = $nms::web::dbh->prepare('select sysname,distro_phy_port,distro_name from switches where placement is not null and distro_name is not null and distro_phy_port is not null'); +my $q2 = $nms::web::dbh->prepare('select sysname,distro_phy_port,distro_name from switches where placement is not null and distro_name is not null and distro_phy_port is not null and deleted = false'); $q2->execute(); while (my $ref = $q2->fetchrow_hashref()) { diff --git a/web/api/public/location b/web/api/public/location index 2247f73..b960bb2 100755 --- a/web/api/public/location +++ b/web/api/public/location @@ -8,7 +8,7 @@ use warnings; use Data::Dumper; #my $query = $nms::web::dbh->prepare("select * from switches where '185.110.150.7' << subnet4"); -my $query = $nms::web::dbh->prepare("select * from switches where ? << subnet4 or ? << subnet6"); +my $query = $nms::web::dbh->prepare("select * from switches where deleted = false and (? << subnet4 or ? << subnet6)"); print "Cache-Control: max-age=0"; print "Content-Type: text/html"; diff --git a/web/api/public/switch-state b/web/api/public/switch-state index 77350c7..965e881 100755 --- a/web/api/public/switch-state +++ b/web/api/public/switch-state @@ -14,9 +14,9 @@ my ($switch, $port) = split(/\//,$target,2); my $q; if (!defined($switch)) { - $q = $nms::web::dbh->prepare('select sysname,extract(epoch from date_trunc(\'second\',time)) as time,data from snmp natural join switches where id in (select max(id) from snmp where ' . $nms::web::when . 'group by switch);'); + $q = $nms::web::dbh->prepare('select sysname,extract(epoch from date_trunc(\'second\',time)) as time,data from snmp natural join switches where id in (select max(id) from snmp where ' . $nms::web::when . ' and switches.deleted = false group by switch);'); } else { - $q = $nms::web::dbh->prepare('select sysname,extract(epoch from date_trunc(\'second\',time)) as time,data from snmp natural join switches where id in (select max(id) from snmp where ' . $nms::web::when . 'group by switch) and sysname = ' . $nms::web::dbh->quote($switch) . ';'); + $q = $nms::web::dbh->prepare('select sysname,extract(epoch from date_trunc(\'second\',time)) as time,data from snmp natural join switches where id in (select max(id) from snmp where ' . $nms::web::when . 'group by switch) and sysname = ' . $nms::web::dbh->quote($switch) . ' and switches.deleted = false;'); } $q->execute(); @@ -24,7 +24,7 @@ while ( my $ref = $q->fetchrow_hashref() ) { my $sysname = $ref->{'sysname'}; my %data = %{JSON::XS::decode_json($ref->{'data'})}; - + $json{'switches'}{$sysname}{'clients'}{'live'} = 0; $json{'then'}{$sysname}{'clients'}{'live'} = 0; for my $porti (keys %{$data{'ports'}}) { @@ -93,7 +93,7 @@ while ( my $ref = $q2->fetchrow_hashref() ) { my $sysname = $ref->{'sysname'}; my %data = %{JSON::XS::decode_json($ref->{'data'})}; - + for my $porti (keys %{$data{'ports'}}) { if (defined($port) and $port ne "" and $port ne $porti) { next; @@ -149,3 +149,4 @@ while ( my $ref = $q2->fetchrow_hashref() ) { $nms::web::cc{'max-age'} = "5"; $nms::web::cc{'stale-while-revalidate'} = "30"; finalize_output(); + diff --git a/web/api/public/switches b/web/api/public/switches index e7f4cb0..44fde3c 100755 --- a/web/api/public/switches +++ b/web/api/public/switches @@ -13,7 +13,7 @@ use Data::Dumper; $nms::web::cc{'max-age'} = "5"; $nms::web::cc{'stale-while-revalidate'} = "30"; -my $q2 = $nms::web::dbh->prepare('select switch,sysname,tags,distro_name,placement,mgmt_v4_addr,mgmt_v6_addr,mgmt_v4_gw,mgmt_v6_gw,mgmt_vlan,traffic_vlan,poll_frequency,community,last_updated from switches where placement is not null'); +my $q2 = $nms::web::dbh->prepare('select switch,sysname,tags,distro_name,placement,mgmt_v4_addr,mgmt_v6_addr,mgmt_v4_gw,mgmt_v6_gw,mgmt_vlan,traffic_vlan,poll_frequency,community,last_updated from switches where placement is not null and deleted = false'); $q2->execute(); while (my $ref = $q2->fetchrow_hashref()) { diff --git a/web/api/read/distro-management b/web/api/read/distro-management index 2aa3273..7fbebef 100755 --- a/web/api/read/distro-management +++ b/web/api/read/distro-management @@ -19,7 +19,7 @@ my $q2; $nms::web::cc{'max-age'} = "5"; $nms::web::cc{'stale-while-revalidate'} = "30"; -$q2 = $nms::web::dbh->prepare('SELECT distro_name,sysname,subnet4,subnet6,traffic_vlan,distro_phy_port FROM switches WHERE placement is not null AND distro_name is not null AND distro_phy_port is not null'); +$q2 = $nms::web::dbh->prepare('SELECT distro_name,sysname,subnet4,subnet6,traffic_vlan,distro_phy_port FROM switches WHERE placement is not null AND distro_name is not null AND distro_phy_port is not null and deleted = false'); $q2->execute(); while (my $ref = $q2->fetchrow_hashref()) { diff --git a/web/api/read/switches-management b/web/api/read/switches-management index 9d8bc53..9182b60 100755 --- a/web/api/read/switches-management +++ b/web/api/read/switches-management @@ -20,9 +20,9 @@ $nms::web::cc{'max-age'} = "5"; $nms::web::cc{'stale-while-revalidate'} = "30"; if (!defined($switch)) { - $q2 = $nms::web::dbh->prepare('select sysname,host(mgmt_v4_addr) as mgmt_v4_addr,subnet4,subnet6,host(mgmt_v6_addr) as mgmt_v6_addr,host(mgmt_v4_gw) as mgmt_v4_gw,host(mgmt_v6_gw) as mgmt_v6_gw,mgmt_vlan,traffic_vlan,poll_frequency,last_updated,distro_name,distro_phy_port,community from switches where placement is not null'); + $q2 = $nms::web::dbh->prepare('select sysname,host(mgmt_v4_addr) as mgmt_v4_addr,subnet4,subnet6,host(mgmt_v6_addr) as mgmt_v6_addr,host(mgmt_v4_gw) as mgmt_v4_gw,host(mgmt_v6_gw) as mgmt_v6_gw,mgmt_vlan,traffic_vlan,poll_frequency,last_updated,distro_name,distro_phy_port,community from switches where placement is not null and deleted = false'); } else { - $q2 = $nms::web::dbh->prepare('select sysname,host(mgmt_v4_addr) as mgmt_v4_addr,subnet4,subnet6,host(mgmt_v6_addr) as mgmt_v6_addr,host(mgmt_v4_gw) as mgmt_v4_gw,host(mgmt_v6_gw) as mgmt_v6_gw,mgmt_vlan,traffic_vlan,poll_frequency,last_updated,distro_name,distro_phy_port,community from switches where placement is not null and sysname = ' . $nms::web::dbh->quote($switch) . ';'); + $q2 = $nms::web::dbh->prepare('select sysname,host(mgmt_v4_addr) as mgmt_v4_addr,subnet4,subnet6,host(mgmt_v6_addr) as mgmt_v6_addr,host(mgmt_v4_gw) as mgmt_v4_gw,host(mgmt_v6_gw) as mgmt_v6_gw,mgmt_vlan,traffic_vlan,poll_frequency,last_updated,distro_name,distro_phy_port,community from switches where placement is not null and deleted = false and sysname = ' . $nms::web::dbh->quote($switch) . ';'); } $q2->execute(); diff --git a/web/api/write/switch-update b/web/api/write/switch-update index 0ee4681..b71548b 100755 --- a/web/api/write/switch-update +++ b/web/api/write/switch-update @@ -23,7 +23,7 @@ my @dups; my $sth = $nms::web::dbh->prepare("SELECT sysname FROM switches WHERE sysname=?"); -my @fields = ( 'community', 'tags', 'distro_name', 'distro_phy_port', 'mgmt_v4_addr', 'mgmt_v4_gw', 'mgmt_v4_netsize', 'mgmt_v6_addr', 'mgmt_v6_gw', 'mgmt_v6_netsize', 'mgmt_vlan', 'placement', 'poll_frequency', 'subnet4', 'subnet6', 'sysname', 'traffic_vlan'); +my @fields = ( 'community', 'tags', 'distro_name', 'distro_phy_port', 'mgmt_v4_addr', 'mgmt_v4_gw', 'mgmt_v4_netsize', 'mgmt_v6_addr', 'mgmt_v6_gw', 'mgmt_v6_netsize', 'mgmt_vlan', 'placement', 'poll_frequency', 'subnet4', 'subnet6', 'sysname', 'traffic_vlan', 'deleted'); sub convertplace { |