Open-AudIT

What's on your network?
It is currently Fri Apr 20, 2018 1:15 pm

All times are UTC + 10 hours




Post new topic Reply to topic  [ 3 posts ] 
Author Message
PostPosted: Thu Nov 17, 2011 8:48 am 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1944
Location: Brisbane, Australia
I use OAv2 not just for the obvious reporting. For example, we have a domian (well, multiple domains) that have PCs that have not been logged in for various amounts of time. Usually these are items such as laptops or PCs stored for later use (think disaster recovery / business continuity purposes). I'm working on a way to collect data from Active Directory on all systems, uncluding last logged on date (can do this already) and somehow matching this against what OAv2 has seen. Maybe put all items into OAv2 directly from AD (with the limited data AD returns) and some reporting like "we have seen 123 PCs in OAv2, but AD reports we have 456 PCs. What are the PCs we have not seen and how long since they've logged into AD". That type of use of the data sources.

Do you use OAv2 and/or it's data for something that OAv2 doesn't do "out of the box" ?

_________________
Support and Development hours available from Opmantek.
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
PostPosted: Thu Nov 17, 2011 10:26 pm 
Offline
Newbie

Joined: Fri Sep 30, 2011 10:24 pm
Posts: 7
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 )

and
Code:
   SELECT ip_address_v4
   FROM sys_hw_network_card_ip
   GROUP BY ip_address_v4
   HAVING ( COUNT(ip_address_v4) > 1 )


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'


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


Top
 Profile  
Reply with quote  
PostPosted: Fri Nov 18, 2011 9:29 am 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1944
Location: Brisbane, Australia
A report to show duplicate hosts along with first and last audit date should be easy enough. Then you could simply select the earlier system's, bulk edit them and set their status to "deleted". a Group can be created containing all "deleted" status systems, too (if required).

I'll have a poke about and post the report and group definitions here.

_________________
Support and Development hours available from Opmantek.
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC + 10 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group