#!/usr/bin/perl -w
use strict;
use lib '/usr/local/myNMS/bin' ;
use My_Utils ;
use My_DButils ;
use My_Config '0.2' ;

my  $VERSION = '1.7' ;

=pod
Copyright (c) 2000-2001 John Stumbles <john@uk.stumbles.org> and the University of Reading. All rights reserved. This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

                                T O - D O
                                =========

Add a way of importing into Vendor_MAC DB table from a text file

                        C H A N G E    H I S T O R Y
                        ============================

1.7     27Feb01 undup wasn't removing consecutive records for IP_MAC_user - fixed

1.6.1   20Feb01 removed $CONFIG{DIR}{BASE} from myNMS subdir specs
1.6     20Feb01 added --mkhtaccess to make .htaccess and .passwd.{group} files
                for NMS web pages

1.5     06Feb01 added explain_tables function

1.4     30Jan01 use My_DButils

1.3     26Jan01 added delete_device
                decalred my %myNMS_DIRS outside BEGIN{} block

1.2     23Jan01 changed table creation to avoid duplication of table name i.e.
                Table_Foo  =>  qq(....),        instead of
                Table_Foo  =>  qq(CREATE TABLE Table_Foo (....) ),

1.1     23Jan01 change to use new directory structure
1.0.1   09Jan01 move_subnet code stripped out (was in 1.0)
                documented (in usage) showtime function

1.0     09Jan01 undup seems to be working OK on IP_MAC_user, IP_MAC and Vendor_MAC
                (hard to tell on the latter 2 as they don't currently have any dups)
                create_DB working
                --safe switch working (not retested on create_DB)

=cut


my  $dbh ;

my  ($PROGPATH,$PROGNAME)=$0=~/(.*\/)?([^\/]+)/;

sub INFORM { print STDERR @_; }

my  $USAGE = qq($PROGNAME args
args can be:
    --safe
        this flag causes SQL statements to make database-altering changes
        to be printed to standard output (where they can be redirected
        into a file or pipe) instead of being performed immediately
    --delete_device DB deviceID
        deletes records for device from all tables in the specified database
    --create_DB DB [table [table ...]]
        creates specified table(s), or all if none specified
    --recreate_DB DB [table [table ...]]
        drops and creates table(s), as create_DB
    --explain_tables DB
        explains tables in given DB, outputting HTML page
    --undup DB table
        removes duplicate records from various tables
    --mkhtaccess
        makes .htaccess and .passwd.{group} files
        controlling access to myNMS web pages
    --showtime expr
        evaluates, in Unix seconds*, an expression involving the time periods:
        NOW YEAR QUARTER MONTH WEEK DAY HOUR MINUTE SECOND
        e.g. --showtime 'NOW - (2 * QUARTER)' gives time 6 months ago from now
            (* seconds since 00:00:00 of 01-Jan-1970 )
);

my  %TABLES = init_tables();
my  $SAFE = 0 ;

while ($_ = shift)
{
    if  (/^--safe/i)        {   $SAFE = 1; INFORM "SAFE mode\n" ;  }
    if  (/^--delete[_-]device/i) { delete_device(@ARGV);   exit;   }
    if  (/^--time/i)        {   showtime(@ARGV);    exit;   }
    if  (/^--create_DB/i)   {   create_DB(0, @ARGV);    exit;   }
    if  (/^--recreate_DB/i) {   create_DB(1, @ARGV);    exit;   }
    if  (/^--explain_table/i) { explain_tables(@ARGV);  exit;   }
    if  (/^--showtime/i)    {   showtime(@ARGV);    exit;   }
    if  (/^--undup/i)           {   undup(@ARGV);     exit;   }
    if  (/^--mkhtaccess/i)  {   mkhtaccess(@ARGV);     exit;   }
}
die "$USAGE\n";

##############################################################################
sub mkhtaccess
#   make htaccess and password files
{
    #   read structure of ACCESS_GROUPS table from config
    #   (using home-brew My_Config module: a better module would give us this structure already cooked)
    my  %ACCESS_GROUPS ;
    foreach my $tag (keys %{$CONFIG{ACCESS_GROUPS}})
    {
        ($ACCESS_GROUPS{$tag}{dir}, $ACCESS_GROUPS{$tag}{name}, $ACCESS_GROUPS{$tag}{descr} ) = split /\t/, $CONFIG{ACCESS_GROUPS}{$tag} ;
    }

    my  $ACL_file       =   $CONFIG{ACCESS}{SOURCE} ;    #   access lists file to read for data to generate acls
    my  $passwdfile     =   $CONFIG{ACCESS}{PASSWD} ;    #   base of name of .passwd.{group} files to generate for .htaccess files
    my  $htaccessfile   =   $CONFIG{ACCESS}{ACL} ;        #   base of name for .htaccess.{group} files to generate
    my  $htaccessdir    =   $CONFIG{DIR}{WWW} ;    #   base directory for .htaccess files

    my  $DB = 'NMS' ;
    $dbh = DB_init($DB) ;

    open (ACL,$ACL_file) || die "Could not open acl file $ACL_file";

    my  $ACL_VERSION ;
    my  $SECTION    ;
    my  %passwd ;   #   hash to hold username:passwds
    my  %Users ;    #   hash of lists of users by group
    my  %Hosts ;    #   hash of lists of hosts by group
    my  %Groups ;   #   list we build up as we go through .htpasswd file of unique group names of users
    my  $Group ;    #   group we are currently reading
    while (<ACL>)
    {
        next if /^\s*(#.*)?$/;  #   skip blank lines and standard unix-ey comment lines
        chomp;
        unless($ACL_VERSION)
        {
            next unless (/^VERSION\s+(\S+)/) ;   #   skip anything except VERSION number
            $ACL_VERSION = $1;
            die "ACL file $ACL_file requires version $ACL_VERSION : $PROGNAME is only version $VERSION\n" if ($ACL_VERSION gt $VERSION) ;
        }

        if (/^\s*SECTION\W+(\S+)/)
        {
            $SECTION    =   $1;
            next ;
        }
        next unless $SECTION ;  #   do not proceed unless we know what section we're dealing with
        
        if ($SECTION eq 'USERS')
        {
            if (/\s*GROUP\W+(\S+)/i)
            {
                $Group = lc($1);
                $Groups{$Group} = 1;
                next ;
            }
            next unless $Group ;        #   must have a Group

            s/^\s*//;   #   remove leading whitespace
            if (/^(\w+):(\S+)/)     #   may be username:passwd
            {
                $passwd{$1} = $2 ;
                push (@{$Users{$Group}}, $1) ;
                next  ;
            }
            # user records may refer to a group list
            if (/^\@(\w\S+)/)
            {
                my  $g = lc($1);
                die "$0 *** ERROR *** User record ($_) refers to group list ($g) which does not exist in acl file ($ACL_file)\n"
                    unless scalar(@{$Users{$g}});
                push (@{$Users{$Group}}, @{$Users{$g}});
                next ;
            }
            if (/^(\w+)/)
            {
                push (@{$Users{$Group}}, $1);
                next ;
            }
            next ;  #   should not get here!
        }
        elsif ($SECTION eq 'HOSTS')
        {
            if (/\s*GROUP\W+(\S+)/i)
            {
                $Group = lc($1);
                next ;
            }
            next unless $Group ;    #   must have a Group
            
            if (/\@(\w\S+)/)          #   @xxxx means include group list xxxx
            {
                my  $g = lc($1);
                next unless $Hosts{$g} ;    #   skip empty group lists
                die "$0 *** ERROR *** Host record ($_) refers to group list ($g) which does not exist in acl file ($ACL_file)\n"
                    unless scalar(@{$Hosts{$g}});
                push (@{$Hosts{$Group}}, @{$Hosts{$g}});
                next ;
            }
            if (/^\s*(\S+)/)    #   other host record
            {
                push (@{$Hosts{$Group}}, $1);
                next ;
            }
            next ;  #   should not get here!
        }
    }
    close ACL;

#    foreach $Group (@Groups)
    foreach $Group (keys %{$CONFIG{ACCESS_GROUPS}})
    {
        next unless ($ACCESS_GROUPS{$Group}{dir});
        my  $groupdir = $ACCESS_GROUPS{$Group}{dir} ;
        my $outfile="$passwdfile.$groupdir";
        open (OUT, ">$outfile") || die "Could not open >$outfile\n\n";

        my  $User;
        foreach $User (@{$Users{$Group}})
        {
            if ($passwd{$User}) #   already in passwd specials/cache?
            {
                print OUT "$User:$passwd{$User}\n";
                next ;
            }
            #   (else)  look in db

            my  $pwd ;
            ($pwd) = passwd_by_user($User) ;
            if ($pwd)   #   did we find it?
            {
                print OUT "$User:$pwd\n";
                $passwd{$User}=$pwd;    #   cache for next time needed
            }
            else
            {
                print "$0 *** ERROR *** no password found for user $User\n";
            }
        }
        close OUT;
#        $outfile="$htaccessdir/$groupdir/.htaccess";
        $outfile="$htaccessfile.$groupdir";
        unless (open (OUT, ">$outfile"))
        {
            print STDERR "$0 *** ERROR could not open $outfile\n";
        }
        else
        {
            print OUT
"AuthUserFile $passwdfile.$groupdir
AuthGroupFile /dev/null
AuthName NetInfo
AuthType Basic
<Limit GET PUT POST>
order deny,allow
deny from all
" ;
            foreach (@{$Hosts{$Group}})
            {
                print OUT
"allow from $_
";
            }
            print OUT
"require valid-user
satisfy any
</Limit>
" ;
            close OUT;
            #   fix up symlink from group directory to actual file
            unlink "$htaccessdir/$groupdir/.htaccess";
            system "cd $htaccessdir/$groupdir ; ln -s $htaccessfile.$groupdir .htaccess";
        }
    }
}
##############################################################################
sub DO
{
    my  $do = shift or return ;
    if ($SAFE)
    {
        print "$do ;\n" ;
    }
    else
    {
        $dbh->do($do);
    }
}
##############################################################################
sub delete_device
{
    my  $DB = shift or die $USAGE ;
    my  $deviceID = shift or die $USAGE ;
    die "*** ERROR deviceID must be numeric - got [$deviceID]\n" unless $deviceID =~ /^\d+$/ ;
    $dbh = DB_init($DB) ;
    my  @tables = qw(SNMP_system SNMP_ifTable SNMP_ipAddrTable dot1d MAC_connections) ;
    INFORM "Deleting device $deviceID from database $DB tables:\n" ;
    foreach my $table (@tables)
    {
        INFORM "\t$table\n";
        DO ("delete from $table where deviceID=$deviceID") ;
    }
    INFORM " - DONE\n\n";
}
##############################################################################
sub showtime
{
    my  @expr = @_ ;
    my  $expr = '' ;
    unless (scalar @expr)
    {
        print time(), "\n";
        return ;
    }
    foreach my $x (@expr)
    {
        foreach (split /\s/, $x)
        {
            $_ = uc ($_) ;
            s/\b(NOW|YEAR|QUARTER|MONTH|WEEK|DAY|HOUR|MINUTE|SECOND)\b/eval $1/eg ;
            $expr .= $_ ;
        }
        $expr .= ' ' ;
    }
    printf "%u\n",  eval $expr ;
}
##############################################################################
sub undup
#   remove duplicate records (and fix UPPER/lowercase chars in MAC adds) in various tables
{
    my  ($DB, $table) = @_ ;
    die "$USAGE\n" unless $table;

    $dbh = DB_init($DB) ;

    die "unsupported table $table to undup\n\n$USAGE\n" unless ($table eq 'IP_MAC' or $table eq 'IP_MAC_user' or $table eq 'Vendor_MAC') ;
    
    my  ($sth, $DO, %REC, $SELECT) ;
    my  $DELETED = 0 ;
    if ($table eq 'IP_MAC')
    {
        $SELECT = "SELECT IPadd, MAC, time1, time2 FROM $table ORDER BY IPadd, time2" ;   #   order by last time to get most recent record
        $sth = $dbh->prepare($SELECT) ;
        $sth->execute ;
        if ($sth->rows)
        {
            %REC = (
                IPadd   =>  '',
                MAC     =>  '',
                t1      =>  0,
                t2      =>  0
                ) ;
        my  ($IPadd, $MAC, $t1, $t2) ;
            $sth->bind_columns(undef, \($IPadd, $MAC, $t1, $t2) );
            while ($sth->fetch)
            {
                $MAC = lc($MAC) ;   #   convert to lowercase
                unless ($IPadd =~ /\d+\.\d+\.\d+\.\d+/)    #   check against corrupt records in DB
                {
                    INFORM sprintf "Corrupt IP address in $table table: %-15s %-12s %s %s\n", $IPadd, $MAC, $t1, $t2 ;
                    DO (sprintf "DELETE FROM $table WHERE IPadd=%s", $dbh->quote($IPadd)) ;
                    next ;
                }
                unless ($MAC =~ /[0-9a-fA-F]{12}/)    #   check against corrupt records in DB
                {
                    INFORM sprintf "Corrupt MAC address in IP_MAC table: %-15s %-12s %s %s\n", $IPadd, $MAC, $t1, $t2 ;
                    DO (sprintf "DELETE FROM $table WHERE MAC=%s", $dbh->quote($MAC)) ;
                    next ;
                }
                if (($IPadd eq $REC{IPadd}) and ($MAC eq $REC{MAC}))   #   same IP and MAC address?
                {
                    if ($t2 == $REC{t2})
                    {
                        INFORM sprintf "Duplicate records for IP/MAC/time2: %-15s %-12s %s\n", $IPadd, $MAC, YYYYMMDDhhmmss($t2) ;
                        #   delete the duplicate records:
                        DO ("DELETE FROM $table WHERE IPadd='$IPadd' AND lower(MAC)='$MAC' AND time2=$t2") ;
                        $DELETED = 1 ;
                    }
                    else
                    {
                        INFORM sprintf "Consecutive records for IP & MAC : %-15s %-12s\n\t(1) t1: %s t2: %s\n\t(2) t1: %s t2: %s\n",
                            $IPadd, $MAC, YYYYMMDDhhmmss($REC{t1}), YYYYMMDDhhmmss($REC{t2}), YYYYMMDDhhmmss($t1), YYYYMMDDhhmmss($t2) ;
#                            #   delete both records:
#                            DO ("DELETE FROM $table where IPadd='$IPadd' AND lower(MAC)='$MAC' AND (t2=$REC{t2} OR t2=$t2)") ;
#                            $DELETED = 1 ;
                    }
                    #   update t2 field
                    $REC{t2} = $t2 ;
                }
                else    #   change of IP and/or MAC address
                {
                    if ($REC{t2})   #   if we have a previous record
                    {
                        if ($DELETED)
                        {
                            DO ("INSERT INTO $table VALUES ('$REC{IPadd}', '$REC{MAC}', $REC{t1}, $REC{t2})") ;
                            $DELETED = 0 ;
                        }
                    }
                    #   save new record:
                    %REC = (
                        IPadd   =>  $IPadd,
                        MAC     =>  $MAC,
                        t1      =>  $t1,
                        t2      =>  $t2
                        ) ;
                }
            }
        }
    }
    elsif ($table eq 'IP_MAC_user')
    {
        $SELECT = "SELECT * FROM $table ORDER BY IPadd, mtime" ;
        $sth = $dbh->prepare($SELECT) ;
        $sth->execute ;
        if ($sth->rows)
        {
            %REC = (
                IPadd   =>  '',
                MAC     =>  '',
                username => '',
                mtime   =>  0
                ) ;
            my  ($IPadd, $MAC, $username, $mtime) ;
            $sth->bind_columns(undef, \($IPadd, $MAC, $username, $mtime) );
            while ($sth->fetch)
            {
                unless ($IPadd =~ /\d+\.\d+\.\d+\.\d+/)    #   check against corrupt records in DB
                {
                    INFORM sprintf "Corrupt IP address in IP_MAC_user table: %-15s %-12s %s %s\n", $IPadd, $MAC, $username, $mtime ;
                    DO ("DELETE FROM $table WHERE IPadd='$IPadd'") ;
                    next ;
                }
                if ($MAC and ! $MAC =~ /[0-9a-fA-F]{12}/)    #   check against corrupt records in DB
                {
                    INFORM sprintf "Corrupt MAC address in IP_MAC_user table: %-15s %-12s %s %s\n", $IPadd, $MAC, $username, $mtime ;
                    DO ("DELETE FROM $table WHERE MAC='$MAC'") ;
                    next ;
                }
                if (($IPadd eq $REC{IPadd}) and (lc($MAC) eq $REC{MAC}) and ($username eq $REC{username}))   #   same IP, MAC address and username
                {
                        #   update mtime field
                        if ($REC{mtime}==$mtime)
                        {
                            INFORM sprintf "Duplicate records for IP/MAC/user/time: %-15s %-12s %s %s\n", $IPadd, $MAC, $username, YYYYMMDDhhmmss($mtime) ;
                            #   delete the duplicate records:
                            DO (sprintf "DELETE FROM $table WHERE IPadd=%s AND lower(MAC)=%s AND username=%s AND mtime=%u" ,
                               $dbh->quote($IPadd), $dbh->quote($MAC), $dbh->quote($username), $mtime) ;
                            $DELETED = 1 ;
                        }
                        else
                        {
                            INFORM sprintf "Consecutive records for IP, MAC, user : %-15s %-12s %s\n\tmtime(1): %s\n\tmtime(2): %s\n", $IPadd, $MAC, $username, YYYYMMDDhhmmss($REC{mtime}), YYYYMMDDhhmmss($mtime) ;
                            #   delete earlier record:
                            DO (sprintf "DELETE FROM $table WHERE IPadd=%s AND lower(MAC)=%s AND username=%s AND mtime=%u" ,
                               $dbh->quote($IPadd), $dbh->quote($MAC), $dbh->quote($username), $REC{mtime}) ;
                        }
                        $REC{mtime} = $mtime ;

                }
                else    #   change of IP and/or MAC address
                {
                    if ($REC{mtime})   #   if we have a previous record
                    {
                        if ($DELETED)   #   insert new record to replace duplicates deleted:
                        {
                            DO (sprintf "INSERT INTO $table VALUES (%s, %s, %s, %u)" ,
                                $dbh->quote($REC{IPadd}), $dbh->quote($REC{MAC}), $dbh->quote($REC{username}), $REC{mtime}) ;
                            $DELETED = 0 ;
                        }
                    }
                    #   save new record:
                    %REC = (
                        IPadd   =>  $IPadd,
                        MAC     =>  lc($MAC),
                        username =>  $username,
                        mtime   =>  $mtime
                        ) ;
                }
            }
        }
    }
    elsif ($table eq 'Vendor_MAC')
    {
        $SELECT = "SELECT Code, Vendor, Vendor_name, Comment FROM $table ORDER BY Code" ;
        $sth = $dbh->prepare($SELECT) ;
        $sth->execute ;
        if ($sth->rows)
        {
            %REC = (
                Code        =>  '',
                Vendor      =>  '',
                Vendor_name =>  '',
                Comment     =>  ''
                ) ;
            my  ($Code, $Vendor, $Vendor_name, $Comment) ;
            $sth->bind_columns(undef, \($Code, $Vendor, $Vendor_name, $Comment) );
            while ($sth->fetch)
            {
                $Code = lc($Code) ;
                unless ($Code =~ /[0-9a-f]{6}/)    #   check against corrupt records in DB
                {
                    INFORM "Corrupt code in Vendor_MAC table: $Code\t$Vendor\t$Vendor_name\t$Comment\n" ;
                    DO (sprintf "DELETE FROM $table WHERE Code=%s", $dbh->quote($Code)) ;
                    next ;
                }
                if ($Code eq $REC{Code})   #   same code ?
                {
                    INFORM sprintf "Duplicate code: %-6s %-8s\t%-30s\t%s\n", $Code, $Vendor, $Vendor_name, $Comment ;
                    if (($Vendor eq $REC{Vendor}) and ($Vendor_name eq $REC{Vendor_name}) and ($Comment eq $REC{Comment}))
                    {
                        INFORM sprintf "Duplicate records: %-6s %-8s\t%-30s\t%s\n", $Code, $Vendor, $Vendor_name, $Comment ;
                        DO (sprintf "DELETE FROM $table WHERE Code=%s", $dbh->quote($Code)) ;
                        $DELETED = 1 ;
                    }
                    else
                    {
                        INFORM sprintf " fields differ: %-6s %-8s\t%-30s\t%s\n\tplease check and edit by hand\n", $REC{Code}, $REC{Vendor}, $REC{Vendor_name}, $REC{Comment} ;
                    }
                }
                else    #   change of IP and/or MAC address
                {
                    if ($REC{Code})   #   if we have a previous record
                    {
                        if ($DELETED)
                        {
                            #   write out previous record:
                            DO (sprintf "INSERT INTO $table VALUES ('%s', %s, %s, %s)",
                                $REC{Code},
                                $dbh->quote($REC{Vendor}),
                                $dbh->quote($REC{Vendor_name}),
                                ($REC{Comment} ? $dbh->quote($REC{Comment}) : 'NULL')
                                );
                            $DELETED = 0 ;
                        }
                    }
                    #   save new record:
                    %REC = (
                        Code        =>  $Code,
                        Vendor      =>  $Vendor,
                        Vendor_name =>  $Vendor_name,
                        Comment     =>  $Comment
                        ) ;
                }
            }
        }
    }
    $sth->finish ;
}
##############################################################################
sub explain_tables
#   prints 'explain {table_name}' commands for each table in list
{
    my  $DB = shift or return  ;
    $dbh = DB_init($DB) ;
    my @tables = keys %TABLES ;
    print "<html><head><title>myNMS: explain tables of DB: $DB</title></head>
<body><h1 align=center>myNMS</h1>
<h2 align=center>explain tables of DB: $DB</h2>
<table border>\n" ;
    my $theader = "<tr><th>" . join ('</th><th>', qw(Field Type Null Key Default Extra)) . "</th></tr>\n" ;

    foreach my $table (sort @tables)
    {
        my $sth=$dbh->prepare("explain $table");
        $sth->execute ;
        next unless $sth->rows;
        my ($Field, $Type, $Null, $Key, $Default, $Extra);
        print "<tr><td colspan=6><p><br><h2>$table</td></tr>\n$theader";
        $sth->bind_columns(undef, \($Field, $Type, $Null, $Key, $Default, $Extra));
        while ($sth->fetch)
        {
            $^W=0;  #   warnings off
            print '<tr><td><tt>' . join ('</tt></td><td><tt>', ($Field, $Type, $Null, $Key, $Default, $Extra)) . "</tt></td></tr>\n";
            $^W=1;  #   warnings on
        }
        $sth->finish ;
    }
        print "</table></body></html>\n";
}
##############################################################################
sub create_DB
#   [drop and re-]create the NMS database tables
{
    my  ($drop, $DB, @tables) = @_ ;
    $dbh = DB_init($DB) ;
    @tables = keys %TABLES unless scalar (@tables) ;
    my %existing_tables ;
    my @existing_tables = $dbh->func( '_ListTables' ) ;
    for my $x (@existing_tables) {
        $existing_tables{$x} = 1 ;
    }
    foreach my $table (@tables)
    {
        die "*** ERROR: no definition found for table $table\n" unless $TABLES{$table} ;
        if ($existing_tables{$table})
                    #   or use 'drop table if exists ...'
        {
            if ($drop)
            {
                INFORM "DROPPING EXISTING TABLE: $table\n";
                DO("DROP TABLE $table") ;
            }
            else
            {
                die "TABLE $table EXISTS!!! (use recreate_DB to drop and re-create tables)\n" ;
            }
        }
        INFORM "CREATING TABLE: $table\n$TABLES{$table}\n==========\n";

        DO("CREATE TABLE $table ($TABLES{$table})") ;
    }
    
}
##############################################################################
sub init_tables
#   return a hash of table definitions
{
#   define tables:

#   GENERAL NOTE:
#       many tables have an 'mtime' field. These contain time as Unix-type seconds since the epoch (1.1.1970)
#       With mySQL we would probably do better to use a TIMESTAMP(14) and the function UNIX_TIMESTAMP(), 
#       but this database was first implemented with mSQL which did not have these.
#       Maybe sometime I'll get round to converting them.


    return (
###############################################################################
#   IP_hosts: hosts' IP addresses and names
#   from NIS (could be from DNS)
#   Maintained by MAP_info
#   contains current data only: may be deleted and recreated
###############################################################################
IP_hosts    =>  qq(
    IPadd   char(15)    NOT NULL,
    name    char(30)    NOT NULL    PRIMARY KEY,
    INDEX   X_IPadd ( IPadd )
),

###############################################################################
#   IP_hostnames: hosts' IP names as space-separated list by IP address
#   Maintained by MAP_info
#   contains current data only: may be deleted and recreated
###############################################################################
IP_hostnames    =>  qq(
    IPadd   char(15)    NOT NULL    PRIMARY KEY,
    names   text        NOT NULL
),

###############################################################################
#   IP_MAC: ARP table from routers
#   maintained by SNMP_info
#   archive data: REALLY should not be deleted as this shows us how long 
#   any given IP address has been in use, so can be used to show IP address
#   allocations which are really redundant and thus reusable
##############################################################################
IP_MAC  =>  qq(
    IPadd       char(15)    NOT NULL,
    MAC         char(12)    NOT NULL,
    time1       INTEGER     UNSIGNED,
    time2       INTEGER     UNSIGNED    NOT NULL,
    INDEX     ( IPadd ),
    INDEX     ( MAC ),
    INDEX     ( time2 )
),

###############################################################################
#   IP_MAC_user: table of correlation of IPadd, MAC add and usernames
#   derived from webcache logs (and potentially other sources e.g. mail and authentication logs, 'last' on unix hosts)
#   maintained by LOG_info
#   archive data - should not be deleted (but ancient records should be pruned
#   - see Maintenance.html)
###############################################################################
IP_MAC_user =>  qq(
    IPadd       char(15)    NOT NULL,
    MAC         char(12),
    username    char(8)     NOT NULL,
    mtime       INTEGER     UNSIGNED    NOT NULL,
    INDEX       (   IPadd       ),
    INDEX       (   username    ),
    INDEX       (   mtime       )
),

###############################################################################
#   Shared_Users: users seen simultaneously on multiple hosts suggesting
#   that the same account is being used by multiple users
#   (either genuine shared accounts or compromised accounts)
#   archive data: cannot be recreated, though old data will be of limited use
###############################################################################
Shared_Users    =>  qq(
    username    char(8)     NOT NULL,
    IPadd       char(15)    NOT NULL,
    MAC         char(12),
    mtime       INTEGER     UNSIGNED    NOT NULL,
    INDEX       (   username    ),
    INDEX       (   IPadd       ),
    INDEX       (   mtime       )
),

###############################################################################
#   Vendor_MAC: mapping of vendor part of MAC address to Vendor name & info
#   derived from 'cavebear' list, updated by Query with local additions
#   archive data: really should NOT be deleted (has our own additions to cavebear list)
###############################################################################
Vendor_MAC  =>  qq(
    Code        char(6)     NOT NULL    PRIMARY KEY,
    Vendor      char(8)     NOT NULL,
    Vendor_name char(90),
    Comment     char(70),
    INDEX       X_Vendor    (Vendor)
),

###############################################################################
#   SNMP_system: basically SNMP 'system' group + no of interfaces,
#   and sysUpTime replaced by UpSinceTime
#   maintained by NMSP_info
#   archive data: really should NOT be deleted as it is the master for deviceIDs
#   but if it is then all other rables containing deviceID fields should also be
#   deleted and re-created
###############################################################################
SNMP_system =>  qq(
    deviceID    INTEGER UNSIGNED        NOT NULL,
    sysName     tinytext,
    sysDescr    tinytext,
    sysContact  tinytext,
    sysLocation tinytext,
    sysObjectID char(30)    NOT NULL,
    sysObjIDtxt char(80),
    sysServices INTEGER UNSIGNED        NOT NULL,
    ifNumber    INTEGER UNSIGNED,
    UpSinceTime INTEGER UNSIGNED        NOT NULL,
    mtime       INTEGER UNSIGNED        NOT NULL,
    UNIQUE      X_deviceID      (   deviceID    )
),

###############################################################################
#   SNMP_ipAddrTable: some variables from SNMP ipAddrTable
#   maintained by SNMP_info
#   probably should not be deleted as may break link between deviceIDs (must check this)
###############################################################################
SNMP_ipAddrTable    =>  qq(
    deviceID    INTEGER UNSIGNED        NOT NULL,
    IPadd       char(15)    NOT NULL,
    ifIndex     INTEGER UNSIGNED        NOT NULL,
    NetMask     char(15)    NOT NULL,
    Status      INTEGER UNSIGNED,
    INDEX       X_deviceID          (   deviceID    ),
    INDEX       X_deviceID_ifIndex  (   deviceID,   ifIndex ),
    INDEX       X_IPadd             (   IPadd   )
),

###############################################################################
#   SNMP_iftable: some variables from SNMP interfaces table
#   plus Name which contains ifName from 'if' table or Descr or whtever
#   is useful (quite device-dependent: intended always to provide a useful
#   short printable string)
#   maintained by SNMP_info
#   may be deleted and will be recreated
###############################################################################
SNMP_ifTable    =>  qq(
    deviceID    INTEGER UNSIGNED        NOT NULL,
    ifIndex     INTEGER UNSIGNED        NOT NULL,
    Descr       TINYTEXT,
    Name        char(20),
    Type        char(60),
    Speed       INTEGER UNSIGNED,
    PhysAddress   char(12),
    AdminStatus   char(4),
    OperStatus    char(4),
    LastChangeAt  INTEGER UNSIGNED,
    INDEX       X_deviceID          (   deviceID    ),
    UNIQUE      X_deviceID_ifIndex  (   deviceID,   ifIndex )
),

###############################################################################
#   dot1d: table of switches' (and routers') forwarding tables
#   maintained by SNMP_info
#   (semi)archive data: deletion will lose historical information of MACs seen
##############################################################################
dot1d   =>  qq(
    deviceID    INTEGER UNSIGNED    NOT NULL,
    ifIndex     INTEGER UNSIGNED    NOT NULL,
    MAC         char(12)            NOT NULL,
    nMACs       INTEGER UNSIGNED    NOT NULL,
    mtime       INTEGER UNSIGNED,
    UNIQUE      X_dev_if_mac    (   deviceID, ifIndex, MAC  )
),

#   nMACs is total number of MAC addresses seen on deviceID:ifIndex at time (mtime) that MAC was seen
#   mtime shoudl probably be NOT NULL

###############################################################################
#   MAC_connections: what device and interface each MAC (seen in dot1d tables)
#       appears to be connected to
#   maintained by SNMP_info
#   this table is derived from dot1d and is summary rather than primary or source data
#   it contains current data only: may be deleted and recreated
###############################################################################
MAC_connections =>  qq(
    MAC         CHAR(12)    NOT NULL    PRIMARY KEY,
    deviceID    INTEGER UNSIGNED,
    ifIndex     INTEGER UNSIGNED,
    nMACs       INTEGER UNSIGNED,
    mtime       TIMESTAMP(14)
),

#################################################################
#   netgroup_hosts: expanded lists of hosts from NIS 'netgroup' table
#   maintained by MAP_info
#   not archive - recreated from NIS
################################################################
netgroup_hosts  =>  qq(
    groupname   char(15)    NOT NULL,
    hostname    char(30)    NOT NULL,
    INDEX   X_groupname     (   groupname   ),
    INDEX   X_hostname      (   hostname    )
),

#################################################################
#   netgroup_users: expanded lists of users from NIS 'netgroup' table
#   maintained by MAP_info
#   not archive - recreated from NIS
#################################################################
netgroup_users  =>  qq(
    groupname char(15) not null,
    username char(8) not null,
    INDEX   X_groupname     (   groupname   ),
    INDEX   X_username      (   username    )
),

#################################################################
#   export_hosts: table of NFS exports from main fileserver
#   maintained by MAP_info
#   not archive - recreated from sufs1 export list
#################################################################
export_hosts    =>  qq(
    hostname    char(30) NOT NULL,
    filesystem  char(40) NOT NULL,
    INDEX       X_hostname      (   hostname    ),
    INDEX       X_filesystem    (   filesystem  )
),

#################################################################
#   passwd: NIS passwd table
#   maintained by MAP_info
#   not archive - recreated from NIS
#################################################################
passwd  =>  qq(
    username    char(8)     NOT NULL    PRIMARY KEY,
    pwd         char(13)    NOT NULL,
    pwd_ctime   INTEGER UNSIGNED    NOT NULL,
    uid         INTEGER UNSIGNED    NOT NULL,
    gid         INTEGER UNSIGNED    NOT NULL,
    full_name   TINYTEXT,
    home        TINYTEXT    NOT NULL,
    shell       TINYTEXT    NOT NULL,
    email_address   TINYTEXT,
    email_aliases   TINYTEXT
),

# would be useful to index on ( full_name ) but requires MyISAM in mySQL 3.23

#################################################################
#   quotas: quotas for general users on user partitions of fileserver
#   maintained by MAP_info
#   not archive - recreated from exports files
#################################################################
quotas  =>  qq(
    uid         INTEGER UNSIGNED    NOT NULL    PRIMARY KEY,
    bhard       INTEGER UNSIGNED    NOT NULL,
    bsoft       INTEGER UNSIGNED    NOT NULL,
    bcurr       INTEGER UNSIGNED    NOT NULL,
    btime       INTEGER UNSIGNED    NOT NULL,
    fhard       INTEGER UNSIGNED    NOT NULL,
    fsoft       INTEGER UNSIGNED    NOT NULL,
    fcurr       INTEGER UNSIGNED    NOT NULL,
    ftime       INTEGER UNSIGNED    NOT NULL,
    ctime       INTEGER UNSIGNED    NOT NULL
),

);   ###################################   end of %TABLES definition

=pod
###############################################################################
#   DHCP_hosts: IP addresses allocated by DHCP server against MAC addresses
#   experimental
###############################################################################
DHCP_hosts  =>  qq(
    IPadd       char(15)    NOT NULL    PRIMARY KEY,
    MAC         char(12)    NOT NULL,
    mtime       timestamp(14),
    INDEX       X_MAC ( IPadd)
)   ,

###############################################################################
    These tables are not currently used
    - for possible enhancements/extensions, or discontinued

###############################################################################
#   IP_summary: various info on hosts by IP address
#   this table is derived from others and is summary rather than primary or source data
#   it contains current data only: may be deleted and recreated
###############################################################################

IP_summary  =>  qq(CREATE TABLE IP_summary (
    IPadd           CHAR(15)    NOT NULL    PRIMARY KEY,
    subnet          CHAR(15)    NOT NULL,
    MAC             CHAR(12),
    MAC_changes     INTEGER UNSIGNED,
    MAC_lastchange  TIMESTAMP(14),
    MAC_Vendor      CHAR(8),
    Conn_deviceID   INTEGER UNSIGNED,
    Conn_ifIndex    INTEGER UNSIGNED,
    Conn_sysName    TINYTEXT,
    Conn_ifName     CHAR(20),
    username        CHAR(8),
    usernames       INTEGER UNSIGNED,
    first_seen      TIMESTAMP(14),
    last_seen       TIMESTAMP(14),
    SNMP_deviceID   INTEGER UNSIGNED,
    SNMP_sysName    TINYTEXT,
    SNMP_services   INTEGER UNSIGNED,
    names           TINYTEXT
)   )   ,

Table of Monitor status where 'Monitor' is device or service or whatever (rainfall, brownness of toast) being monitored

DROP TABLE Monitor_Status ;

create table Monitor_Status (
    monitorID   INTEGER UNSIGNED NOT NULL,
    dependID   INTEGER UNSIGNED,
    deviceID INTEGER UNSIGNED NOT NULL,
    ifIndex INTEGER UNSIGNED NOT NULL,
    status  INTEGER ,
    etime   INTEGER UNSIGNED ,
    mtime   INTEGER UNSIGNED
) ;
#   monitorID is unique ID for this record
#   dependID is ID of record for something on which this depends
#   ifIndex may be 'real' ifIndex of fake number assigned to e.g. service
#   status is some value
#   etime  is time of last event e.g. change from up to down
#   mtime  is time last checked (called mtime as that's what we call it everywhere else)

CREATE INDEX X_devID on Monitor_Status (
    deviceID
) ;
CREATE INDEX X_devID_ifX on Monitor_Status (
    deviceID,
    ifIndex
) ;
EXPLAIN Monitor_Status ;

DROP TABLE Monitor_Table ;
CREATE TABLE Monitor_Table (
    deviceID	INTEGER UNSIGNED    NOT NULL,
    ifIndex     INTEGER UNSIGNED,
    name    CHAR(30),
    description TINYTEXT,
    problem     TEXT
) ;
#   ifIndex     may be null for main device entry
#   name        text label
#   description longer descriptive field
#   problem     long description of problems associated with this thing being down

CREATE INDEX X_devID on Monitor_Table (
    deviceID
) ;
EXPLAIN Monitor_Table;

DROP TABLE SMB_hosts ;
CREATE TABLE SMB_hosts (
    IPadd       CHAR(15)    NOT NULL,
    sharehost   CHAR(30),
    sharename   CHAR(20),
    atime       TIMESTAMP(14),
    INDEX   X_IPadd (   IPadd    )
) ;
EXPLAIN SMB_hosts;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| IPadd     | char(15)      |      | MUL |         |       |
| sharehost | char(30)      | YES  |     | NULL    |       |
| sharename | char(20)      | YES  |     | NULL    |       |
| atime     | timestamp(14) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+

DROP TABLE SMB_name ;
CREATE TABLE SMB_name (
    IPadd   char(15)    NOT NULL,
    name    char(30)    NOT NULL    PRIMARY KEY,
    INDEX   X_IPadd ( IPadd )
) ;
EXPLAIN SMB_name;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| IPadd | char(15) |      | MUL |         |       |
| name  | char(30) |      | PRI |         |       |
+-------+----------+------+-----+---------+-------+
=cut
}
##############################################################################
__END__