Subversion Repositories computer_asset_manager_v1

/* find all routers and the software installed on them */
select
   device.device_id device_id,
   device.name device,
   software.package_name package,
   software.description description,
   software_version.version version
from
   device
   join device_type using (device_type_id)
   join installed_packages using (device_id)
   join software using (software_id)
   join software_version using (software_version_id)
where
   device_type.name = 'Router' 
   and device.removed_date is null
   and installed_packages.removed_date is null
;


select
   device.device_id device_id,
   device.name device,
   device_type.device_type_id,
   device_type.name device_type,
   software.package_name package,
   software.description description,
   software_version.version version
from
   device
   join device_type using (device_type_id)
   join installed_packages using (device_id)
   join software using (software_id)
   join software_version using (software_version_id)
where
   software.package_name in ('ipfire','core-upgrade')
   and device.removed_date is null
   and installed_packages.removed_date is null
;


select distinct(device.device_id) 
from 
   device 
   join device_operating_system using (device_id) 
   join operating_system using (operating_system_id) 
where 
   operating_system.kernel like '%ipfire%';

select
   device.name as 'Device',
   client.name as 'Client',
   maintenance_task.description as 'Task',
   maintenance_schedule.schedule as schedule,
   date_format(max(maintenance_performed.maintenance_date),'%Y-%m-%d') as 'Last Performed',
   date_format(adddate(max(maintenance_performed.maintenance_date),maintenance_schedule.schedule),'%Y-%m-%d') as 'Next Due'
from
   maintenance_schedule join maintenance_task using (maintenance_task_id)
      join device using (device_id)
      join maintenance_performed using (maintenance_task_id,device_id)
      join site using (site_id)
      join client using (client_id)
where
   maintenance_schedule.removed_date is null
   and device.removed_date is null
group by
   device.name,
   maintenance_task.description
order by
   client.name,
   device.name,
   maintenance_task.description
;

select 
   device_type.name as 'Type',
   device.name as Device,
   concat( client.name, ' - ',site.name) as Site,
   count(maintenance_schedule.maintenance_schedule_id) as 'Maintenance' 
from
   device 
   join device_type using (device_type_id) 
   join site using (site_id) 
   join client using (client_id) 
   left outer join maintenance_schedule using (device_id) 
where 
   device_type.device_type_id in (1,2,9) 
   and device.removed_date is null 
group by device_id;