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