view release on metacpan or  search on metacpan

lib/App/Netdisco/DB/ResultSet/Device.pm  view on Meta::CPAN

package App::Netdisco::DB::ResultSet::Device;
use base 'App::Netdisco::DB::ResultSet';

use strict;
use warnings;

use Try::Tiny;
use Regexp::Common 'net';
use NetAddr::IP::Lite ':lower';
use NetAddr::MAC ();

require Dancer::Logger;

=head1 ADDITIONAL METHODS

=head2 device_ips_with_address_or_name( $address_or_name )

Returns a correlated subquery for the set of C<device_ip> entries for each
device. The IP alias or dns matches the supplied C<address_or_name>, using
C<ILIKE>.

=cut

sub device_ips_with_address_or_name {
  my ($rs, $q, $ipbind) = @_;
  $q ||= '255.255.255.255/32';

  return $rs->search(undef,{
    # NOTE: bind param list order is significant
    join => ['device_ips_by_address_or_name'],
    bind => [$q, $ipbind, $q],
  });
}

=head2 with_module_serials

Adds the C<module_serials.serial> field to the results using
C<module_serials> relation.

=cut

sub with_module_serials {
  my $rs = shift;
  return $rs->search(undef, {
    join => 'module_serials',
    '+columns' => [ qw/ module_serials.ip module_serials.index module_serials.serial / ],
    collapse => 1,
    distinct => 0,
  });
}

=head2 ports_with_mac( $mac )

Returns a correlated subquery for the set of C<device_port> entries for each
device. The port MAC address matches the supplied C<mac>, using C<ILIKE>.

=cut

sub ports_with_mac {
  my ($rs, $mac) = @_;
  $mac ||= '00:00:00:00:00:00';

  return $rs->search(undef,{
    # NOTE: bind param list order is significant
    join => ['ports_by_mac'],
    bind => [$mac],
  });
}

=head2 with_times

This is a modifier for any C<search()> (including the helpers below) which
will add the following additional synthesized columns to the result set:

=over 4

=item uptime_age

=item first_seen_stamp

=item last_discover_stamp

=item last_macsuck_stamp

=item last_arpnip_stamp

=item since_first_seen

=item since_last_discover

=item since_last_macsuck

=item since_last_arpnip

=back

=cut

sub with_times {
  my ($rs, $cond, $attrs) = @_;

  return $rs
    ->search_rs($cond, $attrs)
    ->search({},
      {
        '+columns' => {
          uptime_age => \("replace(age(timestamp 'epoch' + me.uptime / 100 * interval '1 second', "
            ."timestamp '1970-01-01 00:00:00-00')::text, 'mon', 'month')"),
          first_seen_stamp    => \"to_char(me.creation, 'YYYY-MM-DD HH24:MI')",
          last_discover_stamp => \"to_char(me.last_discover, 'YYYY-MM-DD HH24:MI')",
          last_macsuck_stamp  => \"to_char(me.last_macsuck,  'YYYY-MM-DD HH24:MI')",
          last_arpnip_stamp   => \"to_char(me.last_arpnip,   'YYYY-MM-DD HH24:MI')",
          since_first_seen    => \"extract(epoch from (age(LOCALTIMESTAMP, me.creation)))",
          since_last_discover => \"extract(epoch from (age(LOCALTIMESTAMP, me.last_discover)))",
          since_last_macsuck  => \"extract(epoch from (age(LOCALTIMESTAMP, me.last_macsuck)))",
          since_last_arpnip   => \"extract(epoch from (age(LOCALTIMESTAMP, me.last_arpnip)))",
        },
      });
}

=head2 search_aliases( {$name or $ip or $prefix}, \%options? )

Tries to find devices in Netdisco which have an identity corresponding to
C<$name>, C<$ip> or C<$prefix>.

The search is across all aliases of the device, as well as its "root IP"

lib/App/Netdisco/DB/ResultSet/Device.pm  view on Meta::CPAN

              'device_ips.alias' => { '<<=' => $p->{ip}->cidr },
            ]) : ()),
        ],
      },
      {
        order_by => [qw/ me.dns me.ip /],
        ((scalar @joins) ? (
          join => \@joins,
          distinct => 1,
        ) : ()),
      }
    );
}

=head2 search_fuzzy( $value )

This method accepts a single parameter only and returns a ResultSet of rows
from the Device table where one field matches the passed parameter.

The following fields are inspected for a match:

=over 4

=item contact

=item serial

=item chassis_id

=item module serials (exact)

=item location

=item name

=item mac (including port addresses)

=item description

=item dns

=item ip (including aliases)

=back

=cut

sub search_fuzzy {
    my ($rs, $q) = @_;

    die "missing param to search_fuzzy\n"
      unless $q;
    $q = "\%$q\%" if $q !~ m/\%/;
    (my $qc = $q) =~ s/\%//g;

    # basic IP check is a string match
    my $ip_clause = [
        'me.ip::text'  => { '-ilike' => $q },
        'device_ips_by_address_or_name.alias::text' => { '-ilike' => $q },
    ];
    my $ipbind = '255.255.255.255/32';

    # but also allow prefix search
    if ($qc =~ m{^(?:$RE{net}{IPv4}|$RE{net}{IPv6})(?:/\d+)?$}i
        and my $ip = NetAddr::IP::Lite->new($qc)) {

        $ip_clause = [
            'me.ip'  => { '<<=' => $ip->cidr },
            'device_ips_by_address_or_name.alias' => { '<<=' => $ip->cidr },
        ];
        $ipbind = $ip->cidr;
    }

    # get IEEE MAC format
    my $mac = NetAddr::MAC->new(mac => ($q || ''));
    undef $mac if
      ($mac and $mac->as_ieee
      and (($mac->as_ieee eq '00:00:00:00:00:00')
        or ($mac->as_ieee !~ m/^$RE{net}{MAC}$/i)));
    $mac = ($mac ? $mac->as_ieee : $q);

    return $rs->ports_with_mac($mac)
              ->device_ips_with_address_or_name($q, $ipbind)
              ->search(
      {
        -or => [
          'me.contact'     => { '-ilike' => $q },
          'me.serial'      => { '-ilike' => $q },
          'me.chassis_id'  => { '-ilike' => $q },
          'me.location'    => { '-ilike' => $q },
          'me.name'        => { '-ilike' => $q },
          'me.description' => { '-ilike' => $q },
          'me.ip' => { '-in' =>
            $rs->search({ 'modules.serial' => $qc },
                        { join => 'modules', columns => 'ip' })->as_query()
          },
          -or => [
            'me.mac::text' => { '-ilike' => $mac},
            'ports_by_mac.mac::text' => { '-ilike' => $mac},
          ],
          -or => [
            'me.dns'      => { '-ilike' => $q },
            'device_ips_by_address_or_name.dns' => { '-ilike' => $q },
          ],
          -or => $ip_clause,
        ],
      },
      {
        order_by => [qw/ me.dns me.ip /],
        distinct => 1,
      }
    );
}

=head2 carrying_vlan( \%cond, \%attrs? )

 my $set = $rs->carrying_vlan({ vlan => 123 });

Like C<search()>, this returns a ResultSet of matching rows from the Device
table.

The returned devices each are aware of the given Vlan.

=over 4

=item *

The C<cond> parameter must be a hashref containing a key C<vlan> with
the value to search for.

=item *

Results are ordered by the Device DNS and IP fields.

=item *

Column C<pcount> gives a count of the number of ports on the device
that are actually configured to carry the VLAN.

=back

=cut