What i usually check for is duplicate IP addresses and/or hostnames within our discovered devices (for obvious reason). For that purpose i have composes a couple of assistant sql queries: [code] SELECT `hostname` FROM system GROUP BY hostname HAVING ( COUNT(hostname) > 1 )[/code] and [code] SELECT ip_address_v4 FROM sys_hw_network_card_ip GROUP BY ip_address_v4 HAVING ( COUNT(ip_address_v4) > 1 )[/code]
Due to the fact that i prefer deleting duplicate host entries (e.g. when a PC's hardware is replaced, but the computer name is kept intact), what i would also appreciate would be a database schema with 'ON DELETE CASCADE', because right now, in order to clean up the entries that are marked as deleted, i have to run something like: [code] delete from oa_alert_log where system_id IN (select system_id from system where man_status='deleted'); delete from oa_audit_log where system_id IN (select system_id from system where man_status='deleted'); delete from oa_group_sys where system_id IN (select system_id from system where man_status='deleted'); delete from sys_hw_bios where system_id IN (select system_id from system where man_status='deleted'); delete from sys_hw_graphs_disk where system_id IN (select system_id from system where man_status='deleted'); delete from sys_hw_hard_drive where system_id IN (select system_id from system where man_status='deleted'); delete from sys_hw_memory where system_id IN (select system_id from system where man_status='deleted'); delete from sys_hw_monitor where system_id IN (select system_id from system where man_status='deleted'); delete from sys_hw_motherboard where system_id IN (select system_id from system where man_status='deleted'); delete from sys_hw_network_card where system_id IN (select system_id from system where man_status='deleted'); delete from sys_hw_optical_drive where system_id IN (select system_id from system where man_status='deleted'); delete from sys_hw_partition where system_id IN (select system_id from system where man_status='deleted'); delete from sys_hw_processor where system_id IN (select system_id from system where man_status='deleted'); delete from sys_man_audits where system_id IN (select system_id from system where man_status='deleted'); delete from sys_sw_group where system_id IN (select system_id from system where man_status='deleted'); delete from sys_sw_log where system_id IN (select system_id from system where man_status='deleted'); delete from sys_sw_route where system_id IN (select system_id from system where man_status='deleted'); delete from sys_sw_service where system_id IN (select system_id from system where man_status='deleted'); delete from sys_sw_software_key where system_id IN (select system_id from system where man_status='deleted'); delete from sys_sw_software where system_id IN (select system_id from system where man_status='deleted'); delete from sys_sw_share where system_id IN (select system_id from system where man_status='deleted'); delete from sys_sw_user where system_id IN (select system_id from system where man_status='deleted'); delete from sys_sw_windows where system_id IN (select system_id from system where man_status='deleted'); delete from sys_hw_network_card_ip where system_id IN (select system_id from system where man_status='deleted'); delete from sys_hw_sound where system_id IN (select system_id from system where man_status='deleted'); delete from sys_hw_video where system_id IN (select system_id from system where man_status='deleted'); delete from sys_sw_variable where system_id IN (select system_id from system where man_status='deleted'); delete from system where man_status='deleted'[/code]
To sum up, a report for duplicate hosts, along with a way of selecting which hosts should be marked as deleted, would be much appreciated. The rest (cascading deletes,etc) are not very substantial.
_________________ Server Info: OS : CentOS Linux release 6.0 (Final) Auditing: 700 machines LDAP: Active Directory
|