aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rwxr-xr-xclients/dhcptail.pl39
-rw-r--r--nms/nms-dump.sql232
-rwxr-xr-xweb/nms.gathering.org/api/private/switch-add8
-rwxr-xr-xweb/nms.gathering.org/api/private/switches-management2
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()) {
generated by cgit v1.2.3 (git 2.25.1) at 2025-04-29 14:54:02 +0000