package App::Netdisco::DB::ResultSet::NodeIp;
use base 'App::Netdisco::DB::ResultSet';
use strict;
use warnings;
__PACKAGE__->load_components(qw/
+App::Netdisco::DB::ExplicitLocking
/);
my $order_by_time_last_and_join_manufacturer = {
order_by => {'-desc' => 'time_last'},
'+columns' => [
'manufacturer.company',
'manufacturer.abbrev',
{ time_first_stamp => \"to_char(time_first, 'YYYY-MM-DD HH24:MI')" },
{ time_last_stamp => \"to_char(time_last, 'YYYY-MM-DD HH24:MI')" },
],
join => 'manufacturer'
};
=head1 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 time_first_stamp
=item time_last_stamp
=back
=cut
sub with_times {
my ($rs, $cond, $attrs) = @_;
return $rs
->search_rs({}, $order_by_time_last_and_join_manufacturer)
->search($cond, $attrs);
}
=head1 with_router
This is a modifier for any C<search()> (including the helpers below) which
will add the following additional synthesized column to the result set:
=over 4
=item router_ip
=item router_name
=back
=cut
sub with_router {
my ($rs, $cond, $attrs) = @_;
return $rs
->search_rs({}, {
'+columns' => [
{ router_ip => \q{(SELECT key FROM json_each_text(seen_on_router_last::json) ORDER BY value::timestamp DESC LIMIT 1)} },
{ router_name => \q{COALESCE(NULLIF(router.dns,''), NULLIF(router.name,''), '')} },
],
join => 'router'
})
->search($cond, $attrs);
}
=head1 search_by_ip( \%cond, \%attrs? )
my $set = $rs->search_by_ip({ip => '192.0.2.1', active => 1});
Like C<search()>, this returns a ResultSet of matching rows from the
NodeIp table.
=over 4
=item *
The C<cond> parameter must be a hashref containing a key C<ip> with the value
to search for. Value can either be a simple string of IPv4 or IPv6, or a
L<NetAddr::IP::Lite> object in which case all results within the CIDR/Prefix
will be retrieved.
=item *
Results are ordered by time last seen.
=item *
Additional columns C<time_first_stamp> and C<time_last_stamp> provide
preformatted timestamps of the C<time_first> and C<time_last> fields.
=item *
A JOIN is performed on the Manufacturer table and the Manufacturer C<company> column prefetched.
=back
To limit results only to active IPs, set C<< {active => 1} >> in C<cond>.
=cut
sub search_by_ip {
my ($rs, $cond, $attrs) = @_;
die "ip address required for search_by_ip\n"
if ref {} ne ref $cond or !exists $cond->{ip};
# handle either plain text IP or NetAddr::IP (/32 or CIDR)
my ($op, $ip) = ('=', delete $cond->{ip});
if ('NetAddr::IP::Lite' eq ref $ip and $ip->num > 1) {
$op = '<<=';
$ip = $ip->cidr;
}
$cond->{'me.ip'} = { $op => $ip };
return $rs
->search_rs({}, $order_by_time_last_and_join_manufacturer)
->search($cond, $attrs);
}
=head1 search_by_dns( \%cond, \%attrs? )
my $set = $rs->search_by_dns({
dns => 'foo.example.com',
suffix => qr/(?:\.example\..com|\.local)$/,
active => 1
});
Like C<search()>, this returns a ResultSet of matching rows from the
NodeIp table.
=over 4
=item *
The NodeIp table must have a C<dns> column for this search to work. Typically
this column is the IP's DNS PTR record, cached at the time of Netdisco Arpnip.
=item *
The C<cond> parameter must be a hashref containing a key C<dns> with the value
to search for. The value may optionally include SQL wildcard characters.
=item *
If C<dns> is a plain string, then the C<cond> parameter may optionally have a
C<suffix> parameter which is a regular expression of domain names - one of
which must match the results.
=item *
Results are ordered by time last seen.
=item *
Additional columns C<time_first_stamp> and C<time_last_stamp> provide
preformatted timestamps of the C<time_first> and C<time_last> fields.
=item *
A JOIN is performed on the Manufacturer table and the Manufacturer C<company> column prefetched.
=back
To limit results only to active IPs, set C<< {active => 1} >> in C<cond>.
=cut
sub search_by_dns {
my ($rs, $cond, $attrs) = @_;
die "dns field required for search_by_dns\n"
if ref {} ne ref $cond or !exists $cond->{dns};
my $dns_field = delete $cond->{dns};
(my $suffix = ($cond->{suffix} || ''))
=~ s|\Q(?^\E[-xismu]*|(?|g;
if (q{} eq ref $dns_field and exists $cond->{suffix}) {
(my $stripped_dns_field = $dns_field) =~ s/\.\%$//;
(my $fqdn_field = $stripped_dns_field) .= '%';
$stripped_dns_field =~ s/$cond->{suffix}$// if $cond->{suffix};
$stripped_dns_field .= '.%';
$cond->{'me.dns'} = [ -or =>
[ -and =>
{ '-ilike' => $stripped_dns_field },
{ '~*' => "***:$suffix" },
],
[ -and =>
{ '-ilike' => $dns_field },
{ '~*' => "***:$suffix" },
],
{ '-ilike' => $fqdn_field },
];
}
elsif (q{} ne ref $dns_field) {
$cond->{'me.dns'} = $dns_field;
}
else {
$cond->{'me.dns'} = { '-ilike' => $dns_field };
}
delete $cond->{suffix};
return $rs
->search_rs({}, $order_by_time_last_and_join_manufacturer)
->search($cond, $attrs);
}
=head1 search_by_mac( \%cond, \%attrs? )
my $set = $rs->search_by_mac({mac => '00:11:22:33:44:55', active => 1});
Like C<search()>, this returns a ResultSet of matching rows from the
NodeIp table.
=over 4
=item *
The C<cond> parameter must be a hashref containing a key C<mac> with the value
to search for.
=item *
Results are ordered by time last seen.
=item *
Additional columns C<time_first_stamp> and C<time_last_stamp> provide
preformatted timestamps of the C<time_first> and C<time_last> fields.
=item *
A JOIN is performed on the Manufacturer table and the Manufacturer C<company> column prefetched.
=back
To limit results only to active IPs, set C<< {active => 1} >> in C<cond>.
=cut
sub search_by_mac {
my ($rs, $cond, $attrs) = @_;
die "mac address required for search_by_mac\n"
if ref {} ne ref $cond or !exists $cond->{mac};
return $rs
->search_rs({}, $order_by_time_last_and_join_manufacturer)
->search($cond, $attrs);
}
=head2 ip_version( $version )
my $rset = $rs->ip_version(4);
This predefined C<search()> returns a ResultSet of matching rows from the
NodeIp table of nodes with addresses of the supplied IP version.
=over 4
=item *
The C<version> parameter must be an integer either 4 or 6.
=back
=cut
sub ip_version {
my ( $rs, $version ) = @_;
die "ip_version input must be either 4 or 6\n"
unless $version && ( $version == 4 || $version == 6 );
return $rs->search_rs( \[ 'family(me.ip) = ?', $version ] );
}
1;