1package App::Netdisco::DB::ResultSet::NodeIp; 2use base 'App::Netdisco::DB::ResultSet'; 3 4use strict; 5use warnings; 6 7__PACKAGE__->load_components(qw/ 8 +App::Netdisco::DB::ExplicitLocking 9/); 10 11my $search_attr = { 12 order_by => {'-desc' => 'time_last'}, 13 '+columns' => [ 14 'oui.company', 15 'oui.abbrev', 16 { time_first_stamp => \"to_char(time_first, 'YYYY-MM-DD HH24:MI')" }, 17 { time_last_stamp => \"to_char(time_last, 'YYYY-MM-DD HH24:MI')" }, 18 ], 19 join => 'oui' 20}; 21 22=head1 with_times 23 24This is a modifier for any C<search()> (including the helpers below) which 25will add the following additional synthesized columns to the result set: 26 27=over 4 28 29=item time_first_stamp 30 31=item time_last_stamp 32 33=back 34 35=cut 36 37sub with_times { 38 my ($rs, $cond, $attrs) = @_; 39 40 return $rs 41 ->search_rs({}, $search_attr) 42 ->search($cond, $attrs); 43} 44 45=head1 search_by_ip( \%cond, \%attrs? ) 46 47 my $set = $rs->search_by_ip({ip => '192.0.2.1', active => 1}); 48 49Like C<search()>, this returns a ResultSet of matching rows from the 50NodeIp table. 51 52=over 4 53 54=item * 55 56The C<cond> parameter must be a hashref containing a key C<ip> with the value 57to search for. Value can either be a simple string of IPv4 or IPv6, or a 58L<NetAddr::IP::Lite> object in which case all results within the CIDR/Prefix 59will be retrieved. 60 61=item * 62 63Results are ordered by time last seen. 64 65=item * 66 67Additional columns C<time_first_stamp> and C<time_last_stamp> provide 68preformatted timestamps of the C<time_first> and C<time_last> fields. 69 70=item * 71 72A JOIN is performed on the OUI table and the OUI C<company> column prefetched. 73 74=back 75 76To limit results only to active IPs, set C<< {active => 1} >> in C<cond>. 77 78=cut 79 80sub search_by_ip { 81 my ($rs, $cond, $attrs) = @_; 82 83 die "ip address required for search_by_ip\n" 84 if ref {} ne ref $cond or !exists $cond->{ip}; 85 86 # handle either plain text IP or NetAddr::IP (/32 or CIDR) 87 my ($op, $ip) = ('=', delete $cond->{ip}); 88 89 if ('NetAddr::IP::Lite' eq ref $ip and $ip->num > 1) { 90 $op = '<<='; 91 $ip = $ip->cidr; 92 } 93 $cond->{ip} = { $op => $ip }; 94 95 return $rs 96 ->search_rs({}, $search_attr) 97 ->search($cond, $attrs); 98} 99 100=head1 search_by_dns( \%cond, \%attrs? ) 101 102 my $set = $rs->search_by_dns({ 103 dns => 'foo.example.com', 104 suffix => qr/(?:\.example\..com|\.local)$/, 105 active => 1 106 }); 107 108Like C<search()>, this returns a ResultSet of matching rows from the 109NodeIp table. 110 111=over 4 112 113=item * 114 115The NodeIp table must have a C<dns> column for this search to work. Typically 116this column is the IP's DNS PTR record, cached at the time of Netdisco Arpnip. 117 118=item * 119 120The C<cond> parameter must be a hashref containing a key C<dns> with the value 121to search for. The value may optionally include SQL wildcard characters. 122 123=item * 124 125The C<cond> parameter may optionally have a C<suffix> parameter which is a 126regular expression of domain names - one of which must match the results. 127 128=item * 129 130Results are ordered by time last seen. 131 132=item * 133 134Additional columns C<time_first_stamp> and C<time_last_stamp> provide 135preformatted timestamps of the C<time_first> and C<time_last> fields. 136 137=item * 138 139A JOIN is performed on the OUI table and the OUI C<company> column prefetched. 140 141=back 142 143To limit results only to active IPs, set C<< {active => 1} >> in C<cond>. 144 145=cut 146 147sub search_by_dns { 148 my ($rs, $cond, $attrs) = @_; 149 150 die "dns field required for search_by_dns\n" 151 if ref {} ne ref $cond or !exists $cond->{dns}; 152 153 (my $suffix = (delete $cond->{suffix} || '')) 154 =~ s|\Q(?^\Eu?|(?|g; 155 156 $cond->{dns} = [ -and => 157 { '-ilike' => delete $cond->{dns} }, 158 { '~*' => "***:$suffix" }, 159 ]; 160 161 return $rs 162 ->search_rs({}, $search_attr) 163 ->search($cond, $attrs); 164} 165 166=head1 search_by_mac( \%cond, \%attrs? ) 167 168 my $set = $rs->search_by_mac({mac => '00:11:22:33:44:55', active => 1}); 169 170Like C<search()>, this returns a ResultSet of matching rows from the 171NodeIp table. 172 173=over 4 174 175=item * 176 177The C<cond> parameter must be a hashref containing a key C<mac> with the value 178to search for. 179 180=item * 181 182Results are ordered by time last seen. 183 184=item * 185 186Additional columns C<time_first_stamp> and C<time_last_stamp> provide 187preformatted timestamps of the C<time_first> and C<time_last> fields. 188 189=item * 190 191A JOIN is performed on the OUI table and the OUI C<company> column prefetched. 192 193=back 194 195To limit results only to active IPs, set C<< {active => 1} >> in C<cond>. 196 197=cut 198 199sub search_by_mac { 200 my ($rs, $cond, $attrs) = @_; 201 202 die "mac address required for search_by_mac\n" 203 if ref {} ne ref $cond or !exists $cond->{mac}; 204 205 return $rs 206 ->search_rs({}, $search_attr) 207 ->search($cond, $attrs); 208} 209 210=head2 ip_version( $version ) 211 212 my $rset = $rs->ip_version(4); 213 214This predefined C<search()> returns a ResultSet of matching rows from the 215NodeIp table of nodes with addresses of the supplied IP version. 216 217=over 4 218 219=item * 220 221The C<version> parameter must be an integer either 4 or 6. 222 223=back 224 225=cut 226 227sub ip_version { 228 my ( $rs, $version ) = @_; 229 230 die "ip_version input must be either 4 or 6\n" 231 unless $version && ( $version == 4 || $version == 6 ); 232 233 return $rs->search_rs( \[ 'family(me.ip) = ?', $version ] ); 234} 235 2361; 237