1package App::Netdisco::DB::Result::Virtual::OrphanedDevices;
2
3use strict;
4use warnings;
5
6use utf8;
7use base 'App::Netdisco::DB::Result::Device';
8
9__PACKAGE__->load_components('Helper::Row::SubClass');
10__PACKAGE__->subclass;
11
12__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
13__PACKAGE__->table('orphaned_devices');
14__PACKAGE__->result_source_instance->is_virtual(1);
15__PACKAGE__->result_source_instance->view_definition(<<'ENDSQL');
16SELECT *
17FROM device
18WHERE ip NOT IN
19    ( SELECT DISTINCT dp.ip AS ip
20     FROM
21       (SELECT device_port.ip,
22               device_port.remote_ip
23        FROM device_port
24        WHERE device_port.remote_port IS NOT NULL
25        GROUP BY device_port.ip,
26                 device_port.remote_ip
27        ORDER BY device_port.ip) dp
28     LEFT JOIN device_ip di ON dp.remote_ip = di.alias
29     WHERE di.ip IS NOT NULL)
30ENDSQL
31
321;
33