Merge branch 'master' of ssh://github.com/tech-server/tgmanage

4 files changed, 15 insertions, 266 deletions

diff --git a/clients/dhcptail.pl b/clients/dhcptail.pl
index e7898aa..2b7e6bf 100755
--- a/clients/dhcptail.pl
+++ b/clients/dhcptail.pl
@@ -31,44 +31,15 @@ my %months = (
 	Dec => 12
 );
 
-my ($dbh, $q, $cq);
+my ($dbh, $q);
+$dbh = nms::db_connect();
+$q = $dbh->prepare("INSERT INTO dhcp (switch,time,mac) VALUES((SELECT switch FROM switches WHERE ?::inet << network),?,?)");
 open(SYSLOG, "tail -n 9999999 -F /var/log/syslog |") or die "Unable to tail syslog: $!";
 while (<SYSLOG>) {
 	/(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\s+(\d+)\s+(\d+:\d+:\d+).*DHCPACK on (\d+\.\d+\.\d+\.\d+) to (\S+)/ or next;
 	my $date = $year . "-" . $months{$1} . "-" . $2 . " " . $3;
 	my $machine = $5;
-	my $owner_color;
-
-	if ($machine eq '00:15:c5:42:ce:e9') {
-		$owner_color = '#00ff00';  # Steinar
-	} elsif ($machine eq '00:1e:37:1c:d2:65') {
-		$owner_color = '#c0ffee';  # Trygve
-	} elsif ($machine eq '00:16:d3:ce:8f:a7') {
-		$owner_color = '#f00f00';  # Jon
-	} elsif ($machine eq '00:16:d4:0c:8a:1c') {
-		$owner_color = '#ff99ff';  # Jørgen
-	} elsif ($machine eq '00:18:8b:aa:2f:f8') {
-		$owner_color = '#663300';  # Kjetil
-	} elsif ($machine eq '00:15:58:29:14:e3') {
-		$owner_color = '#f1720f';  # Bård
-	} else {
-		$owner_color = "#000000";  # Unknown
-	}
-
-	if (!defined($dbh) || !$dbh->ping) {
-		$dbh = nms::db_connect();
-		$q = $dbh->prepare("UPDATE dhcp SET last_ack=? WHERE ?::inet << network AND ( last_ack < ? OR last_ack IS NULL )")
-			or die "Couldn't prepare query";
-		$cq = $dbh->prepare("UPDATE dhcp SET owner_color=? WHERE ?::inet << network AND owner_color IS NULL")
-			or die "Couldn't prepare query";
-	}
-
-	print STDERR "$date $4\n";
-	$q->execute($date, $4, $date)
-		or die "Couldn't push $1 into database";
-	if (defined($owner_color)) {
-		$cq->execute($owner_color, $4)
-			or die "Couldn't push $1 into database";
-	}
+	$q->execute($4,$date,$machine);
+	$q->commit;
 }
 close SYSLOG;
diff --git a/nms/nms-dump.sql b/nms/nms-dump.sql
index 1480827..bbe141d 100644
--- a/nms/nms-dump.sql
+++ b/nms/nms-dump.sql
@@ -39,234 +39,6 @@ CREATE TYPE comment_state AS ENUM (
 
 ALTER TYPE comment_state OWNER TO nms;
 
---
--- Name: datarate; Type: TYPE; Schema: public; Owner: nms
---
-
-CREATE TYPE datarate AS (
-	switch integer,
-	ifname character varying(30),
-	ifhcinoctets double precision,
-	ifhcoutoctets double precision,
-	last_poll_time timestamp with time zone
-);
-
-
-ALTER TYPE datarate OWNER TO nms;
-
---
--- Name: operstatuses; Type: TYPE; Schema: public; Owner: postgres
---
-
-CREATE TYPE operstatuses AS (
-	switch integer,
-	ifdescr character(30),
-	ifoperstatus integer,
-	last_poll_time timestamp with time zone
-);
-
-
-ALTER TYPE operstatuses OWNER TO postgres;
-
---
--- Name: sample; Type: TYPE; Schema: public; Owner: postgres
---
-
-CREATE TYPE sample AS (
-	value bigint,
-	polled timestamp with time zone
-);
-
-
-ALTER TYPE sample OWNER TO postgres;
-
---
--- Name: sample_state; Type: TYPE; Schema: public; Owner: postgres
---
-
-CREATE TYPE sample_state AS (
-	last sample,
-	next_last sample
-);
-
-
-ALTER TYPE sample_state OWNER TO postgres;
-
---
--- Name: add_new_element(sample[], sample); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION add_new_element(sample[], sample) RETURNS sample[]
-    LANGUAGE sql
-    AS $_$ select ('{' || $1[1] || ', ' || $2 || '}')::sample[] $_$;
-
-
-ALTER FUNCTION public.add_new_element(sample[], sample) OWNER TO postgres;
-
---
--- Name: add_new_element(sample_state, sample); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION add_new_element(sample_state, sample) RETURNS sample_state
-    LANGUAGE sql
-    AS $_$
-        SELECT ($1.next_last, $2)::sample_state
-$_$;
-
-
-ALTER FUNCTION public.add_new_element(sample_state, sample) OWNER TO postgres;
-
---
--- Name: get_current_datarate(); Type: FUNCTION; Schema: public; Owner: nms
---
-
-CREATE FUNCTION get_current_datarate() RETURNS SETOF datarate
-    LANGUAGE sql
-    AS $$
-      SELECT switch,ifname,
-      (ifhcoutoctets[1] - ifhcoutoctets[2]) / EXTRACT(EPOCH FROM (time[1] - time[2])) AS ifhcoutoctets,
-      (ifhcinoctets[1] - ifhcinoctets[2]) / EXTRACT(EPOCH FROM (time[1] - time[2])) AS ifhcinoctets,
-      time[1] AS last_poll_time
-      FROM (
-        SELECT switch,ifname,
-        ARRAY_AGG(time) AS time,
-        ARRAY_AGG(ifhcinoctets) AS ifhcinoctets,
-        ARRAY_AGG(ifhcoutoctets) AS ifhcoutoctets
-        FROM (
-           SELECT *,rank() OVER (PARTITION BY switch,ifname ORDER BY time DESC) AS poll_num
-           FROM polls WHERE time BETWEEN (now() - interval '11 minutes') AND now()
-        ) t1
-        WHERE poll_num <= 2
-        GROUP BY switch,ifname
-      ) t2
-      WHERE
-        time[2] IS NOT NULL
-        AND ifhcinoctets[1] >= 0 AND ifhcoutoctets[1] >= 0
-        AND ifhcinoctets[2] >= 0 AND ifhcoutoctets[2] >= 0
-        AND ifhcoutoctets[1] >= ifhcoutoctets[2]
-        AND ifhcinoctets[1] >= ifhcinoctets[2];
-$$;
-
-
-ALTER FUNCTION public.get_current_datarate() OWNER TO nms;
-
---
--- Name: get_datarate(); Type: FUNCTION; Schema: public; Owner: nms
---
-
-CREATE FUNCTION get_datarate() RETURNS SETOF datarate
-    LANGUAGE plpgsql
-    AS $$
-DECLARE
-        num_entries INTEGER;
-        poll polls;
-        second_last_poll polls;
-        last_poll polls;
-        timediff float;
-        ret datarate;
-BEGIN
-        num_entries := 0;
-        last_poll.switch = -1;
-
-        FOR poll IN select * from polls where time >= now() - '15 minutes'::interval and time < now() order by switch,ifname,time LOOP
-                IF poll.switch <> last_poll.switch OR poll.ifname <> last_poll.ifname THEN
-                        IF num_entries >= 2 THEN
-                                timediff := EXTRACT(epoch from last_poll.time - second_last_poll.time);
-                                ret.switch := last_poll.switch;
-                                ret.ifname := last_poll.ifname;
-                                
-                                IF last_poll.ifhcinoctets < second_last_poll.ifhcinoctets THEN
-                                        second_last_poll.ifhcinoctets = 0;
-                                END IF;
-                                IF last_poll.ifhcoutoctets < second_last_poll.ifhcoutoctets THEN
-                                        second_last_poll.ifhcoutoctets = 0;
-                                END IF;
-
-                                ret.ifhcinoctets := (last_poll.ifhcinoctets - second_last_poll.ifhcinoctets) / timediff;
-                                ret.ifhcoutoctets := (last_poll.ifhcoutoctets - second_last_poll.ifhcoutoctets) / timediff;
-                                ret.last_poll_time := last_poll.time;
-                                return next ret;
-                        ELSIF num_entries = 1 THEN
-                                ret.switch := last_poll.switch;
-                                ret.ifname := last_poll.ifname;
-                                ret.ifhcinoctets := -1;
-                                ret.ifhcoutoctets := -1;
-                                ret.last_poll_time := last_poll.time;
-                                return next ret;
-                        END IF;
-                        num_entries := 1;
-                ELSE
-                        num_entries := num_entries + 1;
-                END IF;
-                second_last_poll.switch := last_poll.switch;
-                second_last_poll.ifname := last_poll.ifname;
-                second_last_poll.time := last_poll.time;
-                second_last_poll.ifhcinoctets := last_poll.ifhcinoctets;
-                second_last_poll.ifhcoutoctets := last_poll.ifhcoutoctets;
-                last_poll.switch := poll.switch;
-                last_poll.ifname := poll.ifname;
-                last_poll.time := poll.time;
-                last_poll.ifhcinoctets := poll.ifhcinoctets;
-                last_poll.ifhcoutoctets := poll.ifhcoutoctets;
-        END LOOP;
-       -- pah, and once more, for the last switch/ifname...
-        IF num_entries >= 2 THEN
-                timediff := EXTRACT(epoch from last_poll.time - second_last_poll.time);
-                ret.switch := last_poll.switch;
-                ret.ifname := last_poll.ifname;
-                
-                IF last_poll.ifhcinoctets < second_last_poll.ifhcinoctets THEN
-                        second_last_poll.ifhcinoctets = 0;
-                END IF;
-                IF last_poll.ifhcoutoctets < second_last_poll.ifhcoutoctets THEN
-                        second_last_poll.ifhcoutoctets = 0;
-                END IF;
-
-                ret.ifhcinoctets := (last_poll.ifhcinoctets - second_last_poll.ifhcinoctets) / timediff;
-                ret.ifhcoutoctets := (last_poll.ifhcoutoctets - second_last_poll.ifhcoutoctets) / timediff;
-		ret.last_poll_time := last_poll.time;
-                return next ret;
-        ELSIF num_entries = 1 THEN
-                ret.switch := last_poll.switch;
-                ret.ifname := last_poll.ifname;
-                ret.ifhcinoctets := -1;
-                ret.ifhcoutoctets := -1;
-		ret.last_poll_time := last_poll.time;
-                return next ret;
-        END IF;
-        
-        RETURN;
-END;
-$$;
-
-
-ALTER FUNCTION public.get_datarate() OWNER TO nms;
-
---
--- Name: sha1_hmac(bytea, bytea); Type: FUNCTION; Schema: public; Owner: postgres
---
-
-CREATE FUNCTION sha1_hmac(bytea, bytea) RETURNS text
-    LANGUAGE sql IMMUTABLE STRICT
-    AS $_$
-      SELECT encode(hmac($1, $2, 'sha1'), 'hex')
-    $_$;
-
-
-ALTER FUNCTION public.sha1_hmac(bytea, bytea) OWNER TO postgres;
-
---
--- Name: current_change(sample); Type: AGGREGATE; Schema: public; Owner: postgres
---
-
-CREATE AGGREGATE current_change(sample) (
-    SFUNC = public.add_new_element,
-    STYPE = sample_state
-);
-
-
-ALTER AGGREGATE public.current_change(sample) OWNER TO postgres;
-
 SET default_tablespace = '';
 
 SET default_with_oids = false;
@@ -401,7 +173,7 @@ CREATE TABLE snmp (
 );
 
 
-ALTER TABLE snmp OWNER TO postgres;
+ALTER TABLE snmp OWNER TO nms;
 
 --
 -- Name: snmp_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
@@ -415,7 +187,7 @@ CREATE SEQUENCE snmp_id_seq
     CACHE 1;
 
 
-ALTER TABLE snmp_id_seq OWNER TO postgres;
+ALTER TABLE snmp_id_seq OWNER TO nms;
 
 --
 -- Name: snmp_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
diff --git a/web/nms.gathering.org/api/private/switch-add b/web/nms.gathering.org/api/private/switch-add
index 979a1d8..3d7b119 100755
--- a/web/nms.gathering.org/api/private/switch-add
+++ b/web/nms.gathering.org/api/private/switch-add
@@ -22,7 +22,7 @@ my @dups;
 
 my $sth = $nms::web::dbh->prepare("SELECT sysname FROM switches WHERE sysname=?");
 
-my @fields = ('ip', 'sysname', 'switchtype', 'last_updated', 'locked', 'poll_frequency', 'community', 'lldp_chassis_id', 'secondary_ip', 'subnet4', 'subnet6', 'placement');
+my @fields = ('ip', 'sysname', 'switchtype', 'last_updated', 'locked', 'poll_frequency', 'community', 'lldp_chassis_id', 'secondary_ip', 'subnet4', 'subnet6', 'placement', 'distro', 'secondary_ip');
 
 sub convertplace
 {
@@ -64,6 +64,9 @@ foreach my $tmp2 (@tmp) {
 		if (not defined($switch{'ip'}) and defined($switch{'mgtmt4'})) {
 			$switch{'ip'} = $switch{'mgtmt4'};
 		}
+		if (not defined($switch{'secondary_ip'}) and defined($switch{'mgtmt6'})) {
+			$switch{'secondary_ip'} = $switch{'mgtmt6'};
+		}
 		my ($x1,$x2,$y1,$y2);
 		$x1 = $placement{'x1'};
 		$y1 = $placement{'y1'};
@@ -99,6 +102,9 @@ foreach my $tmp2 (@tmp) {
 		if (not defined($switch{'ip'}) and defined($switch{'mgtmt4'})) {
 			$switch{'ip'} = $switch{'mgtmt4'};
 		}
+		if (not defined($switch{'secondary_ip'}) and defined($switch{'mgtmt6'})) {
+			$switch{'secondary_ip'} = $switch{'mgtmt6'};
+		}
 		my @set;
 		map {
 			if (defined($template{$_})) {
diff --git a/web/nms.gathering.org/api/private/switches-management b/web/nms.gathering.org/api/private/switches-management
index 543d08d..6b5fdcd 100755
--- a/web/nms.gathering.org/api/private/switches-management
+++ b/web/nms.gathering.org/api/private/switches-management
@@ -12,7 +12,7 @@ use Data::Dumper;
 
 $nms::web::cc{'max-age'} = "60";
 
-my $q2 = $nms::web::dbh->prepare('select sysname,ip,poll_frequency,community,subnet4,subnet6,last_updated from switches ');
+my $q2 = $nms::web::dbh->prepare('select sysname,ip,poll_frequency,community,subnet4,subnet6,distro,last_updated from switches ');
 
 $q2->execute();
 while (my $ref = $q2->fetchrow_hashref()) {