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