source: LATMOS-Accounts/scripts/la-sw-net @ 861

#!/usr/bin/perl

use strict;
use warnings;
use LATMOS::Accounts;
use Getopt::Long;
use Pod::Usage;
use DBI;

=head1 NAME

    la-query - Tools to query base in LATMOS::Accounts system

=head1 SYNOPSIS

    la-query [options] [obj_id]

=item [obj_id] : If present, all set attributes (rw) will be displayed for that obj_id.
        If none is given, all obj_ids will be printed.

For the default object_type (user), obj_id = login.

Example : la-query lambda

=cut

GetOptions(
    'c|config=s'        => \my $config,
    'help'              => sub { pod2usage(0) },
) or pod2usage();


=head1 OPTIONS

=over 4

=item -c|--config configdir

Use this configuration directory instead of the default one.

=back

=cut

my $LA = LATMOS::Accounts->new($config, noacl => 1);
my $labase = $LA->default_base;
$labase && $labase->load or die "Cannot load base";

$labase->unexported(1);

my $swdb = DBI->connect('dbi:Pg:host=pgsql.latmos.ipsl.fr;dbname=switchs', 'thauvin')
    or die "Can't connect to db";

my $listarp = $swdb->prepare(q{
    select * from arp where lastview > now() - '6 months'::interval
    });

$listarp->execute();
my $res = $listarp->fetchall_hashref([ 'ip', 'mac' ]);

my %host_seen = map { $_ => 0 } $labase->list_objects('nethost');

foreach my $ip (keys %{ $res }) {
    my ($host) = $labase->search_objects('nethost', 'ip=' . $ip);
    $host or next;
    my $hosto = $labase->get_object('nethost', $host);
    $host_seen{$host} = 1;
    my %macs = map { $_ => 1 } grep { $_ } $hosto->get_attributes('macaddr');
    foreach my $mac (keys %{ $res->{$ip} }) {
        if ($macs{$mac}) {
            next
        };
        my ($hostm) = $labase->search_objects('nethost', 'macaddr=' . $mac);
        if ($hostm) {
            $host_seen{$hostm} ||= 2;
            print "$mac belong to $hostm (not $host -- $ip)\n";
        }
    }
}

foreach (sort keys %host_seen) {
    $host_seen{$_} and next;
    my $h = $labase->get_object('nethost', $_);
    my $owner = $h->get_attributes('owner');
    my $user = $labase->get_object('user', $owner);
    my $desc = $h->get_attributes('description');
    printf("%s%s%s\n",
        $_,
        ($desc ? " $desc" : ''),
        ($user ? ' (' . $user->get_attributes('displayName') . ')' : '')
    );
}