1use utf8; 2package App::Netdisco::DB::Result::NodeIp; 3 4 5use strict; 6use warnings; 7 8use NetAddr::MAC; 9 10use base 'App::Netdisco::DB::Result'; 11__PACKAGE__->table("node_ip"); 12__PACKAGE__->add_columns( 13 "mac", 14 { data_type => "macaddr", is_nullable => 0 }, 15 "ip", 16 { data_type => "inet", is_nullable => 0 }, 17 "dns", 18 { data_type => "text", is_nullable => 1 }, 19 "active", 20 { data_type => "boolean", is_nullable => 1 }, 21 "time_first", 22 { 23 data_type => "timestamp", 24 default_value => \"current_timestamp", 25 is_nullable => 1, 26 original => { default_value => \"now()" }, 27 }, 28 "time_last", 29 { 30 data_type => "timestamp", 31 default_value => \"current_timestamp", 32 is_nullable => 1, 33 original => { default_value => \"now()" }, 34 }, 35); 36__PACKAGE__->set_primary_key("mac", "ip"); 37 38 39 40=head1 RELATIONSHIPS 41 42=head2 oui 43 44Returns the C<oui> table entry matching this Node. You can then join on this 45relation and retrieve the Company name from the related table. 46 47The JOIN is of type LEFT, in case the OUI table has not been populated. 48 49=cut 50 51__PACKAGE__->belongs_to( oui => 'App::Netdisco::DB::Result::Oui', 52 sub { 53 my $args = shift; 54 return { 55 "$args->{foreign_alias}.oui" => 56 { '=' => \"substring(cast($args->{self_alias}.mac as varchar) for 8)" } 57 }; 58 }, 59 { join_type => 'LEFT' } 60); 61 62=head2 node_ips 63 64Returns the set of all C<node_ip> entries which are associated together with 65this IP. That is, all the IP addresses hosted on the same interface (MAC 66address) as the current Node IP entry. 67 68Note that the set will include the original Node IP object itself. If you wish 69to find the I<other> IPs excluding this one, see the C<ip_aliases> helper 70routine, below. 71 72Remember you can pass a filter to this method to find only active or inactive 73nodes, but do take into account that both the C<node> and C<node_ip> tables 74include independent C<active> fields. 75 76=cut 77 78__PACKAGE__->has_many( node_ips => 'App::Netdisco::DB::Result::NodeIp', 79 { 'foreign.mac' => 'self.mac' } ); 80 81=head2 nodes 82 83Returns the set of C<node> entries associated with this IP. That is, all the 84MAC addresses recorded which have ever hosted this IP Address. 85 86Remember you can pass a filter to this method to find only active or inactive 87nodes, but do take into account that both the C<node> and C<node_ip> tables 88include independent C<active> fields. 89 90See also the C<node_sightings> helper routine, below. 91 92=cut 93 94__PACKAGE__->has_many( nodes => 'App::Netdisco::DB::Result::Node', 95 { 'foreign.mac' => 'self.mac' } ); 96 97=head2 netbios 98 99Returns the set of C<node_nbt> entries associated with the MAC of this IP. 100That is, all the NetBIOS entries recorded which shared the same MAC with this 101IP Address. 102 103=cut 104 105__PACKAGE__->has_many( netbios => 'App::Netdisco::DB::Result::NodeNbt', 106 { 'foreign.mac' => 'self.mac' } ); 107 108my $search_attr = { 109 order_by => {'-desc' => 'time_last'}, 110 '+columns' => { 111 time_first_stamp => \"to_char(time_first, 'YYYY-MM-DD HH24:MI')", 112 time_last_stamp => \"to_char(time_last, 'YYYY-MM-DD HH24:MI')", 113 }, 114}; 115 116=head2 ip_aliases( \%cond, \%attrs? ) 117 118Returns the set of other C<node_ip> entries hosted on the same interface (MAC 119address) as the current Node IP, excluding the current IP itself. 120 121Remember you can pass a filter to this method to find only active or inactive 122nodes, but do take into account that both the C<node> and C<node_ip> tables 123include independent C<active> fields. 124 125=over 4 126 127=item * 128 129Results are ordered by time last seen. 130 131=item * 132 133Additional columns C<time_first_stamp> and C<time_last_stamp> provide 134preformatted timestamps of the C<time_first> and C<time_last> fields. 135 136=back 137 138=cut 139 140sub ip_aliases { 141 my ($row, $cond, $attrs) = @_; 142 143 my $rs = $row->node_ips({ip => { '!=' => $row->ip }}); 144 145 return $rs 146 ->search_rs({}, $search_attr) 147 ->search($cond, $attrs); 148} 149 150=head2 node_sightings( \%cond, \%attrs? ) 151 152Returns the set of C<node> entries associated with this IP. That is, all the 153MAC addresses recorded which have ever hosted this IP Address. 154 155Remember you can pass a filter to this method to find only active or inactive 156nodes, but do take into account that both the C<node> and C<node_ip> tables 157include independent C<active> fields. 158 159=over 4 160 161=item * 162 163Results are ordered by time last seen. 164 165=item * 166 167Additional columns C<time_first_stamp> and C<time_last_stamp> provide 168preformatted timestamps of the C<time_first> and C<time_last> fields. 169 170=item * 171 172A JOIN is performed on the Device table and the Device DNS column prefetched. 173 174=back 175 176=cut 177 178sub node_sightings { 179 my ($row, $cond, $attrs) = @_; 180 181 return $row 182 ->nodes({}, { 183 '+columns' => [qw/ device.dns /], 184 join => 'device', 185 }) 186 ->search_rs({}, $search_attr) 187 ->search($cond, $attrs); 188} 189 190=head1 ADDITIONAL COLUMNS 191 192=head2 time_first_stamp 193 194Formatted version of the C<time_first> field, accurate to the minute. 195 196The format is somewhat like ISO 8601 or RFC3339 but without the middle C<T> 197between the date stamp and time stamp. That is: 198 199 2012-02-06 12:49 200 201=cut 202 203sub time_first_stamp { return (shift)->get_column('time_first_stamp') } 204 205=head2 time_last_stamp 206 207Formatted version of the C<time_last> field, accurate to the minute. 208 209The format is somewhat like ISO 8601 or RFC3339 but without the middle C<T> 210between the date stamp and time stamp. That is: 211 212 2012-02-06 12:49 213 214=cut 215 216sub time_last_stamp { return (shift)->get_column('time_last_stamp') } 217 218=head2 net_mac 219 220Returns the C<mac> column instantiated into a L<NetAddr::MAC> object. 221 222=cut 223 224sub net_mac { return NetAddr::MAC->new(mac => ((shift)->mac || '')) } 225 2261; 227