Registrations to Open-AudIT forums are now closed. To ask any new questions please visit Opmantek Community Questions.

Open-AudIT

What's on your network?
It is currently Fri Mar 29, 2024 8:30 pm

All times are UTC + 10 hours




Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 36 posts ]  Go to page Previous  1, 2, 3  Next
Author Message
PostPosted: Thu Nov 13, 2008 8:36 pm 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1964
Location: Brisbane, Australia
Below is a current dump of the MySQL structure.
If anyone wishes to hack the script above, please try to use field names from this.


[code]-- MySQL dump 10.11

--

-- Host: localhost Database: oanew

-- ------------------------------------------------------

-- Server version 5.0.51a-3ubuntu5.2



/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;



--

-- Table structure for table `oa_alert_log`

--



DROP TABLE IF EXISTS `oa_alert_log`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `oa_alert_log` (

`alert_id` int(10) NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`alert_table` varchar(50) NOT NULL,

`alert_foreign_row` int(10) NOT NULL,

`alert_details` varchar(200) NOT NULL,

`user_id` int(10) unsigned default NULL,

`alert_ack_time` datetime NOT NULL default '0000-00-00 00:00:00',

`alert_note` varchar(200) NOT NULL,

`system_change_id` int(10) unsigned default NULL,

`alert_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`alert_id`),

KEY `system_id` (`system_id`),

KEY `oa_alert_log_user_id` (`user_id`),

KEY `oa_alert_change_id` (`system_change_id`),

CONSTRAINT `id_1` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`),

CONSTRAINT `id_2` FOREIGN KEY (`user_id`) REFERENCES `oa_user` (`user_id`),

CONSTRAINT `id_3` FOREIGN KEY (`system_change_id`) REFERENCES `sys_man_change` (`system_change_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `oa_audit_log`

--



DROP TABLE IF EXISTS `oa_audit_log`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `oa_audit_log` (

`audit_log_id` int(10) NOT NULL auto_increment,

`user_id` int(10) unsigned default '0',

`system_id` int(10) unsigned default NULL,

`audit_log_event_type` varchar(50) NOT NULL,

`audit_log_event_details` varchar(200) NOT NULL,

`audit_log_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`audit_log_id`),

KEY `user_id` (`user_id`),

CONSTRAINT `oa_audit_log_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`),

CONSTRAINT `oa_audit_log_user_id` FOREIGN KEY (`user_id`) REFERENCES `oa_user` (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `oa_config`

--



DROP TABLE IF EXISTS `oa_config`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `oa_config` (

`config_name` varchar(45) NOT NULL default '',

`config_value` varchar(45) NOT NULL default '',

PRIMARY KEY (`config_name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `oa_group`

--



DROP TABLE IF EXISTS `oa_group`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `oa_group` (

`group_id` int(10) unsigned NOT NULL auto_increment,

`group_name` varchar(100) NOT NULL,

`group_type` varchar(1) NOT NULL,

`group_dynamic_select` text NOT NULL,

`group_parent` int(10) NOT NULL default '1',

`group_contact` varchar(50) NOT NULL,

`group_description` varchar(255) NOT NULL,

`group_category` varchar(50) NOT NULL,

`group_phone` varchar(20) NOT NULL,

`group_fax` varchar(20) NOT NULL,

`group_address` varchar(200) NOT NULL,

`group_city` varchar(50) NOT NULL,

`group_state` varchar(3) NOT NULL,

`group_postcode` int(6) NOT NULL,

PRIMARY KEY (`group_id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `oa_group_gateway`

--



DROP TABLE IF EXISTS `oa_group_gateway`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `oa_group_gateway` (

`group_gateway_id` int(10) NOT NULL auto_increment,

`group_id` int(10) NOT NULL,

`system_id` int(10) unsigned default NULL,

`group_gateway_link_type` varchar(100) NOT NULL,

`group_gateway_ip_address_internal` varchar(30) NOT NULL,

`group_gateway_ip_address_external` varchar(30) NOT NULL,

`group_gateway_connecting_group` int(10) NOT NULL,

PRIMARY KEY (`group_gateway_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `oa_group_gateway_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `oa_group_sys`

--



DROP TABLE IF EXISTS `oa_group_sys`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `oa_group_sys` (

`group_sys_id` int(10) NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`group_id` int(10) unsigned default NULL,

`group_sys_type` varchar(20) NOT NULL,

PRIMARY KEY (`group_sys_id`),

KEY `system_id` (`system_id`),

KEY `group_id` (`group_id`),

CONSTRAINT `oa_group_sys_group_id` FOREIGN KEY (`group_id`) REFERENCES `oa_group` (`group_id`),

CONSTRAINT `oa_group_sys_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `oa_group_user`

--



DROP TABLE IF EXISTS `oa_group_user`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `oa_group_user` (

`group_user_id` int(10) NOT NULL auto_increment,

`user_id` int(10) unsigned NOT NULL,

`group_id` int(10) unsigned NOT NULL,

`group_user_access_level` int(10) NOT NULL,

PRIMARY KEY (`group_user_id`),

KEY `user_id` (`user_id`),

KEY `group_id` (`group_id`),

CONSTRAINT `oa_group_user_group_id` FOREIGN KEY (`group_id`) REFERENCES `oa_group` (`group_id`),

CONSTRAINT `oa_group_user_user_id` FOREIGN KEY (`user_id`) REFERENCES `oa_user` (`user_id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `oa_net_nmap_ports`

--



DROP TABLE IF EXISTS `oa_net_nmap_ports`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `oa_net_nmap_ports` (

`nmap_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`nmap_port_number` int(10) unsigned NOT NULL default '0',

`nmap_port_proto` varchar(10) NOT NULL default '',

`nmap_port_name` varchar(45) NOT NULL default '',

`nmap_port_version` varchar(100) NOT NULL default '',

`nmap_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`nmap_id`),

KEY `system_id` (`system_id`),

KEY `id2` (`nmap_port_number`),

KEY `id3` (`nmap_port_proto`),

CONSTRAINT `oa_net_nmap_ports_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `oa_net_scan_latest`

--



DROP TABLE IF EXISTS `oa_net_scan_latest`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `oa_net_scan_latest` (

`scan_latest_id` int(10) NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`scan_latest_ip_address` varchar(30) NOT NULL,

`scan_latest_type` varchar(10) NOT NULL,

`scan_latest_detail` varchar(100) NOT NULL,

`scan_latest_frequency` tinyint(4) NOT NULL,

`scan_latest_date_time` datetime NOT NULL default '0000-00-00 00:00:00',

`scan_latest_result` varchar(20) NOT NULL,

`scan_latest_success` varchar(2) NOT NULL,

PRIMARY KEY (`scan_latest_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `oa_net_scan_latest_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `oa_net_scan_log`

--



DROP TABLE IF EXISTS `oa_net_scan_log`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `oa_net_scan_log` (

`scan_log_id` int(10) NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`scan_log_ip_address` varchar(30) NOT NULL,

`scan_log_type` varchar(10) NOT NULL,

`scan_log_detail` varchar(100) NOT NULL,

`scan_log_frequency` tinyint(4) NOT NULL,

`scan_log_date_time` datetime NOT NULL default '0000-00-00 00:00:00',

`scan_log_result` varchar(20) NOT NULL,

`scan_log_success` varchar(2) NOT NULL,

PRIMARY KEY (`scan_log_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `oa_net_scan_log_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `oa_net_scan_type`

--



DROP TABLE IF EXISTS `oa_net_scan_type`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `oa_net_scan_type` (

`scan_type_id` int(10) NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`scan_type_ip_address` varchar(30) NOT NULL,

`scan_type` varchar(10) NOT NULL,

`scan_type_detail` varchar(100) NOT NULL,

`scan_type_frequency` tinyint(4) NOT NULL,

PRIMARY KEY (`scan_type_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `oa_net_scan_type_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `oa_spare`

--



DROP TABLE IF EXISTS `oa_spare`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `oa_spare` (

`spare_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`spare_field_1` varchar(45) NOT NULL default '',

`spare_field_2` varchar(100) NOT NULL default '',

`spare_field_3` varchar(200) NOT NULL default '',

`spare_field_4` varchar(200) NOT NULL default '',

`spare_field_5` varchar(200) NOT NULL default '',

`spare_field_6` varchar(200) NOT NULL default '',

`spare_field_7` varchar(200) NOT NULL default '',

`spare_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`spare_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `oa_spare_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `oa_switch_ports`

--



DROP TABLE IF EXISTS `oa_switch_ports`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `oa_switch_ports` (

`switch_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`switch_system_id` int(10) unsigned default NULL,

`switch_port` varchar(100) NOT NULL default '',

`switch_timestamp` varchar(200) NOT NULL default '',

`switch_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`switch_id`),

KEY `system_id` (`system_id`),

KEY `switch_system_id` (`switch_system_id`),

CONSTRAINT `oa_switch_ports_switch_switch_id` FOREIGN KEY (`switch_system_id`) REFERENCES `system` (`system_id`),

CONSTRAINT `oa_switch_ports_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `oa_user`

--



DROP TABLE IF EXISTS `oa_user`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `oa_user` (

`user_id` int(10) unsigned NOT NULL auto_increment,

`user_name` varchar(100) NOT NULL,

`user_password` varchar(100) NOT NULL,

`user_full_name` varchar(100) NOT NULL,

`user_email` varchar(100) default NULL,

`user_lang` varchar(100) default NULL,

`user_display_number` smallint(6) NOT NULL default '10',

`user_theme` varchar(100) default NULL,

PRIMARY KEY (`user_id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Definition of table `oa_user_sessions`

--



DROP TABLE IF EXISTS `oa_user_sessions`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `oa_user_sessions` (

`session_id` varchar(40) NOT NULL default '0',

`ip_address` varchar(16) NOT NULL default '0',

`user_agent` varchar(50) NOT NULL,

`last_activity` int(10) unsigned NOT NULL default '0',

PRIMARY KEY (`session_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;





--

-- Table structure for table `sys_hw_battery`

--



DROP TABLE IF EXISTS `sys_hw_battery`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_battery` (

`bt_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`bt_description` varchar(100) NOT NULL default '',

`bt_device_id` varchar(100) NOT NULL default '',

`bt_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`bt_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`bt_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_battery_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_bios`

--



DROP TABLE IF EXISTS `sys_hw_bios`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_bios` (

`bios_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`bios_description` varchar(200) NOT NULL default '',

`bios_manufacturer` varchar(200) NOT NULL default '',

`bios_serial_number` varchar(100) NOT NULL default '',

`bios_sm_bios_version` varchar(100) NOT NULL default '',

`bios_version` varchar(100) NOT NULL default '',

`bios_asset_tag` varchar(100) NOT NULL default '',

`bios_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`bios_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`bios_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_bios_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_firewire`

--



DROP TABLE IF EXISTS `sys_hw_firewire`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_firewire` (

`fw_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`fw_description` varchar(200) NOT NULL default '',

`fw_manufacturer` varchar(100) NOT NULL default '',

`fw_caption` varchar(200) NOT NULL default '',

`fw_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`fw_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`fw_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_firewire_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_floppy`

--



DROP TABLE IF EXISTS `sys_hw_floppy`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_floppy` (

`floppy_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`floppy_description` varchar(100) NOT NULL default '',

`floppy_device_id` varchar(100) NOT NULL default '',

`floppy_manufacturer` varchar(100) NOT NULL default '',

`floppy_caption` varchar(100) NOT NULL default '',

`floppy_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`floppy_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`floppy_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_floppy_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_graphs_disk`

--



DROP TABLE IF EXISTS `sys_hw_graphs_disk`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_graphs_disk` (

`disk_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`disk_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

`disk_letter` varchar(4) NOT NULL default '',

`disk_percent` varchar(3) NOT NULL default '',

PRIMARY KEY (`disk_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_graphs_disk_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_hard_drive`

--



DROP TABLE IF EXISTS `sys_hw_hard_drive`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_hard_drive` (

`hard_drive_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`hard_drive_caption` varchar(100) NOT NULL default '',

`hard_drive_index` varchar(100) NOT NULL default '',

`hard_drive_interface_type` varchar(10) NOT NULL default '',

`hard_drive_manufacturer` varchar(100) NOT NULL default '',

`hard_drive_model` varchar(100) NOT NULL default '',

`hard_drive_serial` varchar(100) NOT NULL default '',

`hard_drive_partitions` int(10) unsigned NOT NULL default '0',

`hard_drive_scsi_bus` varchar(10) NOT NULL default '',

`hard_drive_scsi_logical_unit` varchar(100) NOT NULL default '',

`hard_drive_scsi_port` varchar(10) NOT NULL default '',

`hard_drive_size` int(10) unsigned NOT NULL default '0',

`hard_drive_device_id` varchar(200) NOT NULL default '',

`hard_drive_status` varchar(10) NOT NULL default '',

`hard_drive_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`hard_drive_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`hard_drive_id`),

KEY `hard_drive_index` (`hard_drive_index`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_hard_drive_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_keyboard`

--



DROP TABLE IF EXISTS `sys_hw_keyboard`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_keyboard` (

`keyboard_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`keyboard_description` varchar(100) NOT NULL default '',

`keyboard_caption` varchar(100) NOT NULL default '',

`keyboard_connection` varchar(45) NOT NULL default '',

`keyboard_device_id` varchar(100) NOT NULL default '',

`keyboard_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`keyboard_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`keyboard_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_keyboard_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_memory`

--



DROP TABLE IF EXISTS `sys_hw_memory`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_memory` (

`memory_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`memory_bank` varchar(45) NOT NULL default '',

`memory_type` varchar(45) NOT NULL default '',

`memory_form_factor` varchar(45) NOT NULL default '',

`memory_detail` varchar(45) NOT NULL default '',

`memory_capacity` int(10) NOT NULL,

`memory_speed` varchar(45) NOT NULL default '',

`memory_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`memory_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`memory_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_memory_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_modem`

--



DROP TABLE IF EXISTS `sys_hw_modem`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_modem` (

`modem_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`modem_attached_to` varchar(100) NOT NULL default '',

`modem_country_selected` varchar(100) NOT NULL default '',

`modem_description` varchar(100) NOT NULL default '',

`modem_device_id` varchar(100) NOT NULL default '',

`modem_device_type` varchar(100) NOT NULL default '',

`modem_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`modem_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`modem_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_modem_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_monitor`

--



DROP TABLE IF EXISTS `sys_hw_monitor`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_monitor` (

`monitor_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`monitor_manufacturer` varchar(45) NOT NULL default '',

`monitor_device_id` varchar(45) NOT NULL default '',

`monitor_manufacture_date` varchar(45) NOT NULL default '',

`monitor_model` varchar(45) NOT NULL default '',

`monitor_serial` varchar(45) NOT NULL default '',

`monitor_serial_man` varchar(45) NOT NULL default '',

`monitor_edid` varchar(45) NOT NULL default '',

`monitor_description` varchar(100) NOT NULL default '',

`monitor_value` varchar(45) NOT NULL default '',

`monitor_purchase_order_number` varchar(45) NOT NULL default '',

`monitor_date_purchased` date NOT NULL default '0000-00-00',

`monitor_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`monitor_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`monitor_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_monitor_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_motherboard`

--



DROP TABLE IF EXISTS `sys_hw_motherboard`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_motherboard` (

`motherboard_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`motherboard_manufacturer` varchar(50) NOT NULL default '',

`motherboard_product` varchar(50) NOT NULL default '',

`motherboard_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`motherboard_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`motherboard_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_motherboard_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_mouse`

--



DROP TABLE IF EXISTS `sys_hw_mouse`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_mouse` (

`mouse_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`mouse_description` varchar(100) NOT NULL default '',

`mouse_number_of_buttons` varchar(45) NOT NULL default '',

`mouse_device_id` varchar(100) NOT NULL default '',

`mouse_type` varchar(45) NOT NULL default '',

`mouse_port` varchar(45) NOT NULL default '',

`mouse_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`mouse_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`mouse_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_mouse_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_network_card`

--



DROP TABLE IF EXISTS `sys_hw_network_card`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_network_card` (

`net_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`net_mac_address` varchar(17) NOT NULL default '',

`net_manufacturer` varchar(100) NOT NULL default '',

`net_model` varchar(100) NOT NULL default '',

`net_description` varchar(255) NOT NULL default '',

`net_ip_enabled` varchar(10) NOT NULL default '',

`net_index` varchar(10) NOT NULL default '',

`net_dhcp_enabled` varchar(100) NOT NULL default '',

`net_dhcp_server` varchar(30) NOT NULL default '',

`net_dhcp_lease_obtained` varchar(14) NOT NULL default '',

`net_dhcp_lease_expires` varchar(14) NOT NULL default '',

`net_dns_host_name` varchar(100) NOT NULL default '',

`net_dns_server` varchar(100) NOT NULL default '',

`net_dns_domain` varchar(100) NOT NULL default '',

`net_dns_domain_suffix` varchar(100) NOT NULL default '',

`net_dns_domain_reg_enabled` varchar(10) NOT NULL default '',

`net_wins_primary` varchar(30) NOT NULL default '',

`net_wins_secondary` varchar(30) NOT NULL default '',

`net_wins_lmhosts_enabled` varchar(10) NOT NULL default '',

`net_adapter_type` varchar(100) NOT NULL default '',

`net_connection_id` varchar(255) NOT NULL default '',

`net_connection_status` varchar(30) NOT NULL default '',

`net_speed` varchar(10) NOT NULL default '',

`net_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`net_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`net_id`),

KEY `net_mac_address` (`net_mac_address`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_network_card_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_network_card_ip`

--



DROP TABLE IF EXISTS `sys_hw_network_card_ip`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_network_card_ip` (

`ip_id` int(10) unsigned NOT NULL auto_increment,

`net_mac_address` varchar(17) NOT NULL default '',

`ip_address` varchar(30) NOT NULL default '',

`ip_address_v6` varchar(30) NOT NULL default '',

`ip_subnet` varchar(30) NOT NULL default '',

`ip_address_version` varchar(10) NOT NULL default '',

`ip_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`ip_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`ip_id`),

KEY `id` (`net_mac_address`),

CONSTRAINT `sys_hw_network_card_ip_net_mac_address` FOREIGN KEY (`net_mac_address`) REFERENCES `sys_hw_network_card` (`net_mac_address`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_onboard_device`

--



DROP TABLE IF EXISTS `sys_hw_onboard_device`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_onboard_device` (

`onboard_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`onboard_description` varchar(50) NOT NULL default '',

`onboard_type` varchar(20) NOT NULL default '',

`onboard_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`onboard_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`onboard_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_onboard_device_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_optical_drive`

--



DROP TABLE IF EXISTS `sys_hw_optical_drive`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_optical_drive` (

`optical_drive_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`optical_drive_caption` varchar(100) NOT NULL default '',

`optical_drive_model` varchar(100) NOT NULL default '',

`optical_drive_device_id` varchar(100) NOT NULL default '',

`optical_drive_mount_point` varchar(10) NOT NULL default '',

`optical_drive_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`optical_drive_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`optical_drive_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_optical_drive_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_partition`

--



DROP TABLE IF EXISTS `sys_hw_partition`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_partition` (

`partition_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`hard_drive_index` varchar(100) NOT NULL default '',

`partition_mount_point` varchar(100) NOT NULL default '',

`partition_name` varchar(100) NOT NULL default '',

`partition_size` int(10) unsigned NOT NULL default '1',

`partition_free_space` int(10) unsigned NOT NULL default '1',

`partition_used_space` int(10) unsigned NOT NULL default '1',

`partition_format` varchar(20) NOT NULL default '',

`partition_caption` varchar(100) NOT NULL default '',

`partition_device_id` varchar(100) NOT NULL default '',

`partition_disk_index` varchar(50) NOT NULL default '',

`partition_boot_partition` varchar(10) NOT NULL default '',

`partition_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`partition_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`partition_id`),

KEY `system_id` (`system_id`),

KEY `hard_drive_index` (`hard_drive_index`),

CONSTRAINT `sys_hw_partition_hard_drive_index` FOREIGN KEY (`hard_drive_index`) REFERENCES `sys_hw_hard_drive` (`hard_drive_index`),

CONSTRAINT `sys_hw_partition_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_processor`

--



DROP TABLE IF EXISTS `sys_hw_processor`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_processor` (

`processor_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`processor_cores` int(2) unsigned NOT NULL default '0',

`processor_description` varchar(100) NOT NULL default '',

`processor_speed` int(10) unsigned NOT NULL default '0',

`processor_manufacturer` varchar(100) NOT NULL default '',

`processor_power_management_supported` varchar(20) NOT NULL default '',

`processor_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`processor_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`processor_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_processor_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_scsi_controller`

--



DROP TABLE IF EXISTS `sys_hw_scsi_controller`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_scsi_controller` (

`scsi_controller_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`scsi_controller_caption` varchar(200) NOT NULL default '',

`scsi_controller_device_id` varchar(200) NOT NULL default '',

`scsi_controller_manufacturer` varchar(100) NOT NULL default '',

`scsi_controller_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`scsi_controller_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`scsi_controller_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_scsi_controller_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_scsi_device`

--



DROP TABLE IF EXISTS `sys_hw_scsi_device`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_scsi_device` (

`scsi_device_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`scsi_device_controller` int(10) unsigned default NULL,

`scsi_device_device` varchar(200) NOT NULL default '',

`scsi_device_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`scsi_device_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`scsi_device_id`),

KEY `system_id` (`system_id`),

KEY `scsi_device_controller` (`scsi_device_controller`),

CONSTRAINT `sys_hw_scsi_device_scsi_device_controller` FOREIGN KEY (`scsi_device_controller`) REFERENCES `sys_hw_scsi_controller` (`scsi_controller_id`),

CONSTRAINT `sys_hw_scsi_device_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_sound`

--



DROP TABLE IF EXISTS `sys_hw_sound`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_sound` (

`sound_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`sound_manufacturer` varchar(100) NOT NULL default '',

`sound_device_id` varchar(100) NOT NULL default '',

`sound_name` varchar(100) NOT NULL default '',

`sound_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`sound_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`sound_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_sound_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_tape_drive`

--



DROP TABLE IF EXISTS `sys_hw_tape_drive`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_tape_drive` (

`tape_drive_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`tape_drive_caption` varchar(100) NOT NULL default '',

`tape_drive_description` varchar(100) NOT NULL default '',

`tape_drive_device_id` varchar(100) NOT NULL default '',

`tape_drive_manufacturer` varchar(100) NOT NULL default '',

`tape_drive_name` varchar(100) NOT NULL default '',

`tape_drive_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`tape_drive_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`tape_drive_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_tape_drive_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_usb`

--



DROP TABLE IF EXISTS `sys_hw_usb`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_usb` (

`usb_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`usb_caption` varchar(100) NOT NULL default '',

`usb_description` varchar(100) NOT NULL default '',

`usb_manufacturer` varchar(100) NOT NULL default '',

`usb_device_id` varchar(120) NOT NULL default '',

`usb_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`usb_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`usb_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_usb_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_hw_video`

--



DROP TABLE IF EXISTS `sys_hw_video`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_hw_video` (

`video_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`video_description` varchar(100) NOT NULL default '',

`video_manufacturer` varchar(100) NOT NULL default '',

`video_current_horizontal_res` varchar(20) NOT NULL default '',

`video_current_vertical_res` varchar(20) NOT NULL default '',

`video_memory` varchar(100) NOT NULL default '',

`video_caption` varchar(100) NOT NULL default '',

`video_current_number_colours` varchar(20) NOT NULL default '',

`video_current_refresh_rate` varchar(20) NOT NULL default '',

`video_device_id` varchar(100) NOT NULL default '',

`video_driver_date` varchar(20) NOT NULL default '',

`video_driver_version` varchar(20) NOT NULL default '',

`video_max_refresh_rate` varchar(20) NOT NULL default '',

`video_min_refresh_rate` varchar(20) NOT NULL default '',

`video_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`video_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`video_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_hw_video_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_man_audits`

--



DROP TABLE IF EXISTS `sys_man_audits`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_man_audits` (

`system_audits_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`system_audits_username` varchar(45) NOT NULL default '',

`system_audits_type` varchar(45) NOT NULL default '',

`system_audits_time` varchar(45) NOT NULL default '',

`system_audits_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`system_audits_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_man_audits_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_man_change`

--



DROP TABLE IF EXISTS `sys_man_change`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_man_change` (

`system_change_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`system_change_date` datetime NOT NULL default '0000-00-00 00:00:00',

`system_change_short_desc` varchar(200) NOT NULL default '',

`system_change_detailed_desc` varchar(200) NOT NULL default '',

`system_change_authorising_person` varchar(45) NOT NULL default '',

`system_change_reason` varchar(200) NOT NULL default '',

`system_change_potential_issues` varchar(200) NOT NULL default '',

`system_change_backout_plan` varchar(200) NOT NULL default '',

`system_change_callid` int(10) unsigned NOT NULL default '0',

`system_change_call_techid` int(10) unsigned NOT NULL default '0',

`system_change_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`system_change_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_man_change_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_man_change_log`

--



DROP TABLE IF EXISTS `sys_man_change_log`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_man_change_log` (

`system_change_log_id` int(10) unsigned NOT NULL auto_increment,

`system_change_id` int(10) unsigned NOT NULL default '0',

`system_change_log_attachmentid` int(10) unsigned NOT NULL default '0',

`system_change_log_call_techid` int(10) unsigned NOT NULL default '0',

`system_change_log_date` datetime NOT NULL default '0000-00-00 00:00:00',

`system_change_log_comments` varchar(200) NOT NULL default '',

PRIMARY KEY (`system_change_log_id`),

KEY `system_change_id` (`system_change_id`),

CONSTRAINT `sys_man_change_log_system_change_id` FOREIGN KEY (`system_change_id`) REFERENCES `sys_man_change` (`system_change_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_man_invoice`

--



DROP TABLE IF EXISTS `sys_man_invoice`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_man_invoice` (

`invoice_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`invoice_filename` varchar(100) NOT NULL default '',

`invoice_image` blob,

PRIMARY KEY (`invoice_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_man_invoice_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_man_notes`

--



DROP TABLE IF EXISTS `sys_man_notes`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_man_notes` (

`notes_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`user_id` int(10) unsigned default NULL,

`notes_title` varchar(200) NOT NULL default '',

`notes_text` text NOT NULL default '',

`notes_timestamp` varchar(200) NOT NULL default '',

PRIMARY KEY (`notes_id`),

KEY `system_id` (`system_id`),

KEY `user_id` (`user_id`),

CONSTRAINT `sys_man_notes_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`),

CONSTRAINT `sys_man_notes_user_id` FOREIGN KEY (`user_id`) REFERENCES `oa_user` (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_man_passwords`

--



DROP TABLE IF EXISTS `sys_man_passwords`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_man_passwords` (

`passwords_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`passwords_application` varchar(100) NOT NULL default '',

`passwords_password` varchar(100) NOT NULL default '',

`passwords_user` varchar(100) NOT NULL default '',

PRIMARY KEY (`passwords_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_man_passwords_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_antivirus`

--



DROP TABLE IF EXISTS `sys_sw_antivirus`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_antivirus` (

`virus_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`virus_manufacturer` varchar(150) NOT NULL default '',

`virus_version` varchar(45) NOT NULL default '',

`virus_name` varchar(100) NOT NULL default '',

`virus_uptodate` varchar(45) NOT NULL default '',

`virus_def_date` date NOT NULL default '0000-00-00',

`virus_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`virus_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`virus_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_antivirus_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_database`

--



DROP TABLE IF EXISTS `sys_sw_database`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_database` (

`db_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`db_type` varchar(50) NOT NULL default '',

`db_version` varchar(50) NOT NULL default '',

`db_port` int(10) unsigned default NULL,

`db_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`db_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`db_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_db_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_database_details`

--



DROP TABLE IF EXISTS `sys_sw_database_details`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_database_details` (

`details_id` int(10) unsigned NOT NULL auto_increment,

`db_id` int(10) unsigned default NULL,

`details_name` varchar(50) NOT NULL default '',

`details_internal_id` varchar(50) NOT NULL default '',

`details_current_size` varchar(50) NOT NULL default '',

`details_filename` varchar(200) NOT NULL default '',

`details_creation_date` datetime NOT NULL default '0000-00-00 00:00:00',

`details_last_accessed_date` varchar(50) NOT NULL default '',

`details_last_accessed_login_name` varchar(50) NOT NULL default '',

`details_last_accessed_hostname` varchar(50) NOT NULL default '',

`details_last_accessed_program` varchar(50) NOT NULL default '',

`details_last_accessed_domain` varchar(50) NOT NULL default '',

`details_last_accessed_username` varchar(50) NOT NULL default '',

`details_last_accessed_protocol` varchar(50) NOT NULL default '',

`details_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`details_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`details_id`),

KEY `system_id` (`db_id`),

CONSTRAINT `sys_sw_db_id` FOREIGN KEY (`db_id`) REFERENCES `sys_sw_database` (`db_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_database_log`

--



DROP TABLE IF EXISTS `sys_sw_database_log`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_database_log` (

`log_id` int(10) unsigned NOT NULL auto_increment,

`details_id` int(10) unsigned default NULL,

`log_size` varchar(50) NOT NULL default '',

`log_last_accessed_date` varchar(50) NOT NULL default '',

`log_last_accessed_login_name` varchar(50) NOT NULL default '',

`log_last_accessed_hostname` varchar(50) NOT NULL default '',

`log_last_accessed_program` varchar(50) NOT NULL default '',

`log_last_accessed_domain` varchar(50) NOT NULL default '',

`log_last_accessed_username` varchar(50) NOT NULL default '',

`log_last_accessed_protocol` varchar(50) NOT NULL default '',

`log_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`log_id`),

KEY `system_id` (`details_id`),

CONSTRAINT `sys_sw_db_details_id` FOREIGN KEY (`details_id`) REFERENCES `sys_sw_database_details` (`details_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_environment_variable`

--



DROP TABLE IF EXISTS `sys_sw_environment_variable`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_environment_variable` (

`env_var_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`env_var_name` varchar(50) NOT NULL default '',

`env_var_value` varchar(250) NOT NULL default '',

`env_var_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`env_var_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`env_var_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_environment_variable_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_event_log`

--



DROP TABLE IF EXISTS `sys_sw_event_log`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_event_log` (

`evt_log_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`evt_log_name` varchar(50) NOT NULL default '',

`evt_log_file_name` varchar(100) NOT NULL default '',

`evt_log_file_size` int(10) NOT NULL default '0',

`evt_log_max_file_size` int(10) NOT NULL default '0',

`evt_log_overwrite` varchar(30) NOT NULL default '',

`evt_log_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`evt_log_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`evt_log_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_event_log_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_firewall`

--



DROP TABLE IF EXISTS `sys_sw_firewall`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_firewall` (

`firewall_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`firewall_enabled_domain` varchar(45) NOT NULL default '',

`firewall_disablenotifications_domain` varchar(45) NOT NULL default '',

`firewall_donotallowexceptions_domain` varchar(45) NOT NULL default '',

`firewall_enabled_standard` varchar(45) NOT NULL default '',

`firewall_disablenotifications_standard` varchar(45) NOT NULL default '',

`firewall_donotallowexceptions_standard` varchar(45) NOT NULL default '',

`firewall_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`firewall_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`firewall_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_firewall_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_firewall_auth_app`

--



DROP TABLE IF EXISTS `sys_sw_firewall_auth_app`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_firewall_auth_app` (

`firewall_app_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`firewall_app_name` varchar(100) NOT NULL default '',

`firewall_app_executable` varchar(200) NOT NULL default '',

`firewall_app_remote_address` varchar(45) NOT NULL default '',

`firewall_app_enabled` varchar(45) NOT NULL default '',

`firewall_app_profile` varchar(45) NOT NULL default '',

`firewall_app_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`firewall_app_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`firewall_app_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_firewall_auth_app_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_firewall_ports`

--



DROP TABLE IF EXISTS `sys_sw_firewall_ports`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_firewall_ports` (

`port_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`port_number` int(10) unsigned NOT NULL default '0',

`port_protocol` varchar(45) NOT NULL default '',

`port_scope` varchar(45) NOT NULL default '',

`port_enabled` varchar(45) NOT NULL default '',

`port_profile` varchar(45) NOT NULL default '',

`port_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`port_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`port_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_firewall_ports_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_groups`

--



DROP TABLE IF EXISTS `sys_sw_groups`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_groups` (

`groups_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`groups_description` varchar(200) NOT NULL default '',

`groups_name` varchar(100) NOT NULL default '',

`groups_members` varchar(100) NOT NULL default '',

`groups_sid` varchar(100) NOT NULL default '',

`groups_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`groups_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`groups_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_groups_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_groups_details`

--



DROP TABLE IF EXISTS `sys_sw_groups_details`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_groups_details` (

`gd_name` varchar(100) NOT NULL default '',

`gd_description` varchar(200) NOT NULL default '',

PRIMARY KEY (`gd_name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_ip_route`

--



DROP TABLE IF EXISTS `sys_sw_ip_route`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_ip_route` (

`ip_route_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`ip_route_destination` varchar(20) NOT NULL default '',

`ip_route_mask` varchar(20) NOT NULL default '',

`ip_route_metric` varchar(10) NOT NULL default '',

`ip_route_next_hop` varchar(20) NOT NULL default '',

`ip_route_protocol` varchar(10) NOT NULL default '',

`ip_route_type` varchar(10) NOT NULL default '',

`ip_route_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`ip_route_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`ip_route_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_ip_route_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_manual_software`

--



DROP TABLE IF EXISTS `sys_sw_manual_software`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_manual_software` (

`man_soft_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`man_soft_det_id` int(10) unsigned NOT NULL default '0',

`man_soft_version` varchar(45) NOT NULL default '',

`man_soft_filesize` int(10) unsigned NOT NULL default '0',

`man_soft_date_detected` date NOT NULL default '0000-00-00',

`man_soft_date_first_detected` date NOT NULL default '0000-00-00',

PRIMARY KEY (`man_soft_id`),

KEY `sys_sw_manual_software_system_id` (`system_id`),

CONSTRAINT `sys_sw_manual_software_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_manual_software_detection`

--



DROP TABLE IF EXISTS `sys_sw_manual_software_detection`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_manual_software_detection` (

`man_soft_det_id` int(10) unsigned NOT NULL auto_increment,

`man_soft_det_dir` varchar(45) NOT NULL default '',

`man_soft_det_file` varchar(45) NOT NULL default '',

`man_soft_det_name` varchar(45) NOT NULL default '',

`man_soft_det_comments` varchar(45) NOT NULL default '',

PRIMARY KEY (`man_soft_det_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_mapped`

--



DROP TABLE IF EXISTS `sys_sw_mapped`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_mapped` (

`mapped_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`mapped_device_id` varchar(100) NOT NULL default '',

`mapped_file_system` varchar(100) NOT NULL default '',

`mapped_provider_name` varchar(100) NOT NULL default '',

`mapped_free_space` int(10) unsigned NOT NULL default '0',

`mapped_size` int(10) unsigned NOT NULL default '0',

`mapped_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`mapped_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`mapped_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_mapped_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_media`

--



DROP TABLE IF EXISTS `sys_sw_media`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_media` (

`media_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`media_type` varchar(45) NOT NULL default '',

`media_file` varchar(250) NOT NULL default '',

`media_size` int(10) unsigned NOT NULL default '0',

`media_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`media_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_media_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_pagefile`

--



DROP TABLE IF EXISTS `sys_sw_pagefile`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_pagefile` (

`pagefile_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`pagefile_name` varchar(100) NOT NULL default '',

`pagefile_initial_size` int(10) NOT NULL default '0',

`pagefile_max_size` int(10) NOT NULL default '0',

`pagefile_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`pagefile_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`pagefile_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_pagefile_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_scheduled_task`

--



DROP TABLE IF EXISTS `sys_sw_scheduled_task`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_scheduled_task` (

`sched_task_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`sched_task_name` varchar(100) NOT NULL default '',

`sched_task_next_run` varchar(50) NOT NULL default '',

`sched_task_status` varchar(50) NOT NULL default '',

`sched_task_last_run` varchar(50) NOT NULL default '',

`sched_task_last_result` varchar(50) NOT NULL default '',

`sched_task_creator` varchar(50) NOT NULL default '',

`sched_task_schedule` varchar(100) NOT NULL default '',

`sched_task_task` varchar(100) NOT NULL default '',

`sched_task_state` varchar(10) NOT NULL default '',

`sched_task_runas` varchar(50) NOT NULL default '',

`sched_task_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`sched_task_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`sched_task_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_scheduled_task_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_service`

--



DROP TABLE IF EXISTS `sys_sw_service`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_service` (

`service_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`service_display_name` varchar(100) NOT NULL default '',

`service_name` varchar(100) NOT NULL default '',

`service_path_name` varchar(200) NOT NULL default '',

`service_started` varchar(10) NOT NULL default '',

`service_start_mode` varchar(10) NOT NULL default '',

`service_state` varchar(10) NOT NULL default '',

`service_count` varchar(5) NOT NULL default '',

`service_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`service_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`service_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_service_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_service_details`

--



DROP TABLE IF EXISTS `sys_sw_service_details`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_service_details` (

`sd_display_name` varchar(100) NOT NULL default '',

`sd_description` varchar(200) NOT NULL default '',

PRIMARY KEY (`sd_display_name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_shares`

--



DROP TABLE IF EXISTS `sys_sw_shares`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_shares` (

`shares_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`shares_caption` varchar(100) NOT NULL default '',

`shares_name` varchar(100) NOT NULL default '',

`shares_path` varchar(100) NOT NULL default '',

`shares_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`shares_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`shares_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_shares_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_software`

--



DROP TABLE IF EXISTS `sys_sw_software`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_software` (

`software_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`software_name` varchar(256) NOT NULL default '',

`software_version` varchar(256) NOT NULL default '',

`software_location` varchar(256) NOT NULL default '',

`software_uninstall` mediumtext NOT NULL,

`software_install_date` varchar(100) NOT NULL default '',

`software_publisher` varchar(256) NOT NULL default '',

`software_install_source` varchar(200) NOT NULL default '',

`software_system_component` varchar(2) NOT NULL default '',

`software_url` varchar(256) NOT NULL default '',

`software_email` varchar(256) NOT NULL default '',

`software_comment` varchar(200) NOT NULL default '',

`software_man_comment` varchar(200) NOT NULL default '',

`software_count` varchar(5) NOT NULL default '',

`software_code_base` varchar(256) NOT NULL default '',

`software_status` varchar(45) NOT NULL default '',

`software_key` varchar(45) NOT NULL default '',

`software_key_release` varchar(45) NOT NULL default '',

`software_key_edition` varchar(45) NOT NULL default '',

`software_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`software_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`software_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_software_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_software_key`

--



DROP TABLE IF EXISTS `sys_sw_software_key`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_software_key` (

`key_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`key_name` varchar(250) NOT NULL default '',

`key_text` varchar(100) NOT NULL default '',

`key_release` varchar(100) NOT NULL default '',

`key_edition` varchar(100) NOT NULL default '',

`key_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`key_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`key_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_software_key_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_startup`

--



DROP TABLE IF EXISTS `sys_sw_startup`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_startup` (

`startup_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`startup_caption` varchar(200) NOT NULL default '',

`startup_name` varchar(100) NOT NULL default '',

`startup_command` varchar(200) NOT NULL default '',

`startup_description` varchar(200) NOT NULL default '',

`startup_location` varchar(200) NOT NULL default '',

`startup_user` varchar(100) NOT NULL default '',

`startup_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`startup_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`startup_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_startup_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_system_security`

--



DROP TABLE IF EXISTS `sys_sw_system_security`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_system_security` (

`ss_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`ss_qno` varchar(45) NOT NULL default '',

`ss_status` varchar(45) NOT NULL default '',

`ss_reason` varchar(200) NOT NULL default '',

`ss_product` varchar(45) NOT NULL default '',

`ss_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`ss_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`ss_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_system_security_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_system_security_bulletins`

--



DROP TABLE IF EXISTS `sys_sw_system_security_bulletins`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_system_security_bulletins` (

`ssb_title` varchar(200) NOT NULL default '',

`ssb_description` text NOT NULL,

`ssb_bulletin` varchar(45) NOT NULL default '',

`ssb_qno` varchar(45) NOT NULL default '',

`ssb_url` varchar(100) NOT NULL default '',

PRIMARY KEY (`ssb_qno`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_users`

--



DROP TABLE IF EXISTS `sys_sw_users`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_users` (

`users_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`users_disabled` varchar(20) NOT NULL default '',

`users_full_name` varchar(100) NOT NULL default '',

`users_name` varchar(100) NOT NULL default '',

`users_password_changeable` varchar(20) NOT NULL default '',

`users_password_expires` varchar(20) NOT NULL default '',

`users_password_required` varchar(20) NOT NULL default '',

`users_sid` varchar(100) NOT NULL default '',

`users_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`users_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`users_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_users_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_users_detail`

--



DROP TABLE IF EXISTS `sys_sw_users_detail`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_users_detail` (

`ud_name` varchar(100) NOT NULL default '',

`ud_description` varchar(200) NOT NULL default '',

PRIMARY KEY (`ud_name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_web_server`

--



DROP TABLE IF EXISTS `sys_sw_web_server`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_web_server` (

`ws_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`ws_site` int(10) unsigned NOT NULL default '0',

`ws_server_type` varchar(100) NOT NULL default '',

`ws_description` varchar(100) NOT NULL default '',

`ws_logging_enabled` varchar(100) NOT NULL default '',

`ws_logging_dir` varchar(100) NOT NULL default '',

`ws_logging_format` varchar(100) NOT NULL default '',

`ws_logging_time_period` varchar(100) NOT NULL default '',

`ws_home_directory` varchar(100) NOT NULL default '',

`ws_directory_browsing` varchar(100) NOT NULL default '',

`ws_default_documents` varchar(100) NOT NULL default '',

`ws_secure_ip` varchar(100) NOT NULL default '',

`ws_secure_port` varchar(100) NOT NULL default '',

`ws_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`ws_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`ws_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_web_server_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_web_server_ip`

--



DROP TABLE IF EXISTS `sys_sw_web_server_ip`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_web_server_ip` (

`ws_ip_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`ws_ip_site` varchar(100) NOT NULL default '',

`ws_ip_address` varchar(30) NOT NULL default '',

`ws_ip_port` varchar(100) NOT NULL default '',

`ws_ip_host_header` varchar(100) NOT NULL default '',

`ws_ip_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`ws_ip_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`ws_ip_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_web_server_ip_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_web_server_vd`

--



DROP TABLE IF EXISTS `sys_sw_web_server_vd`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_web_server_vd` (

`ws_vd_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`ws_vd_site` varchar(100) NOT NULL default '',

`ws_vd_name` varchar(100) NOT NULL default '',

`ws_vd_path` varchar(100) NOT NULL default '',

`ws_vd_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`ws_vd_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`ws_vd_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_web_server_vd_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `sys_sw_windows`

--



DROP TABLE IF EXISTS `sys_sw_windows`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `sys_sw_windows` (

`windows_id` int(10) unsigned NOT NULL auto_increment,

`system_id` int(10) unsigned default NULL,

`windows_build_number` varchar(20) NOT NULL default '',

`windows_user_name` varchar(100) NOT NULL default '',

`windows_client_site_name` varchar(100) NOT NULL default '',

`windows_domain_controller_address` varchar(100) NOT NULL default '',

`windows_domain_controller_name` varchar(100) NOT NULL default '',

`windows_domain_role` varchar(40) NOT NULL default '',

`windows_part_of_domain` varchar(10) NOT NULL default '',

`windows_id_number` varchar(100) NOT NULL default '',

`windows_time_caption` varchar(100) NOT NULL default '',

`windows_time_daylight` varchar(100) NOT NULL default '',

`windows_boot_device` varchar(100) NOT NULL default '',

`windows_country_code` varchar(50) NOT NULL default '',

`windows_organisation` varchar(80) NOT NULL default '',

`windows_language` varchar(50) NOT NULL default '',

`windows_registered_user` varchar(50) NOT NULL default '',

`windows_service_pack` varchar(20) NOT NULL default '',

`windows_version` varchar(20) NOT NULL default '',

`windows_install_directory` varchar(20) NOT NULL default '',

`windows_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`windows_first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`windows_id`),

KEY `system_id` (`system_id`),

CONSTRAINT `sys_sw_windows_system_id` FOREIGN KEY (`system_id`) REFERENCES `system` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;



--

-- Table structure for table `system`

--



DROP TABLE IF EXISTS `system`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `system` (

`system_id` int(10) unsigned NOT NULL auto_increment,

`system_key` varchar(200) NOT NULL default '',

`uuid` varchar(100) NOT NULL default '',

`mac_address` varchar(17) NOT NULL default '',

`ip_address` varchar(30) NOT NULL default '',

`hostname` varchar(100) NOT NULL default '',

`domain` varchar(100) NOT NULL default '',

`description` varchar(100) NOT NULL default '',

`type` varchar(50) NOT NULL default '',

`icon` varchar(50) NOT NULL default '',

`os` varchar(50) NOT NULL default '',

`os_short_name` varchar(50) NOT NULL default '',

`os_full_name` varchar(100) NOT NULL default '',

`os_version` varchar(50) NOT NULL default '',

`linked_sys` int(10) NOT NULL default '0',

`serial` varchar(50) NOT NULL default '',

`model` varchar(50) NOT NULL default '',

`manufacturer` varchar(50) NOT NULL default '',

`form_factor` varchar(50) NOT NULL default '',

`pc_memory` int(10) NOT NULL default '0',

`pc_num_processor` int(10) NOT NULL default '0',

`pc_date_os_installation` date NOT NULL default '1970-01-01',

`printer_port_name` varchar(50) NOT NULL default '',

`printer_shared` varchar(50) NOT NULL default '',

`printer_shared_name` varchar(50) NOT NULL default '',

`man_acting_server` varchar(3) NOT NULL default '',

`man_status` ENUM('production', 'retired', 'maintenance', 'deleted') NOT NULL default 'production',

`man_environment` ENUM('production', 'test', 'uat', 'dev', 'eval', 'DR') NOT NULL default 'production',

`man_criticality` ENUM('critical', 'normal', 'low') NOT NULL default 'critical',

`man_description` varchar(100) NOT NULL default '',

`man_type` varchar(100) NOT NULL default '',

`man_ip_address` varchar(30) NOT NULL default '',

`man_location_name` varchar(100) NOT NULL default '',

`man_location_address` varchar(100) NOT NULL default '',

`man_location_city` varchar(100) NOT NULL default '',

`man_location_state` varchar(100) NOT NULL default '',

`man_location_country` varchar(100) NOT NULL default '',

`man_location_room` varchar(100) NOT NULL default '',

`man_location_rack` varchar(100) NOT NULL default '',

`man_serial` varchar(50) NOT NULL default '',

`man_asset_number` varchar(50) NOT NULL default '',

`man_model` varchar(50) NOT NULL default '',

`man_manufacturer` varchar(50) NOT NULL default '',

`man_form_factor` varchar(50) NOT NULL default '',

`man_icon` varchar(50) NOT NULL default '',

`man_vendor` varchar(150) NOT NULL default '',

`man_vm_server_name` varchar(150) NOT NULL default '',

`man_vm_system_id` varchar(150) NOT NULL default '',

`man_vm_group` varchar(150) NOT NULL default '',

`man_cluster_name` varchar(150) NOT NULL default '',

`man_purchase_order_number` varchar(50) NOT NULL default '',

`man_purchase_cost_center` varchar(50) NOT NULL default '',

`man_purchase_vendor` varchar(100) NOT NULL default '',

`man_purchase_invoice` varchar(100) NOT NULL default '',

`man_purchase_date` date NOT NULL default '0000-00-00',

`man_purchase_amount` varchar(50) NOT NULL default '',

`man_warranty_duration` int(5) unsigned NOT NULL default '0',

`man_warranty_expires` date NOT NULL default '0000-00-00',

`man_warranty_type` ENUM('', '24x7x365', '9x5x5', 'Next Business Day') NOT NULL default '',

`man_terminal_number` int(10) NOT NULL default '0',

`man_switch_id` varchar(100) NOT NULL default '',

`man_switch_port` varchar(100) NOT NULL default '',

`man_ethernet_socket` varchar(45) NOT NULL default '',

`man_wall_port` varchar(100) NOT NULL default '',

`man_picture` varchar(100) NOT NULL default '',

`man_contact_name` varchar(100) NOT NULL default '',

`man_contact_organisation` varchar(100) NOT NULL default '',

`man_contact_phone_number` varchar(20) NOT NULL default '',

`man_contact_address` varchar(100) NOT NULL default '',

`man_contact_city` varchar(100) NOT NULL default '',

`man_contact_state` varchar(100) NOT NULL default '',

`man_contact_country` varchar(100) NOT NULL default '',

`timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

`first_timestamp` datetime NOT NULL default '0000-00-00 00:00:00',

PRIMARY KEY (`system_id`),

KEY `id` (`timestamp`),

KEY `id2` (`system_key`),

KEY `id3` (`ip_address`),

KEY `system_id` (`system_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET character_set_client = @saved_cs_client;





LOCK TABLES `oa_config` WRITE;

INSERT INTO `oa_config` VALUES ('oa_debug','off');

UNLOCK TABLES;



LOCK TABLES `oa_group` WRITE;



INSERT INTO `oa_group` VALUES (1,'All','s','',1,'Administrator','All','','','','','','',0);



INSERT INTO `oa_group` VALUES (10,'DNS Servers','d','SELECT distinct(system.system_id) FROM system, sys_sw_software WHERE sys_sw_software.software_name = \'bind9\' AND system.system_id = sys_sw_software.system_id AND system.timestamp = sys_sw_software.software_timestamp AND system.man_status = \'production\'',1,'Administrator','DNS','','','','','','',0);



INSERT INTO `oa_group` VALUES (11,'DHCP Servers','d','SELECT distinct(system.system_id) FROM system, sys_sw_software WHERE sys_sw_software.software_name = \'DHCP\' AND system.system_id = sys_sw_software.system_id AND system.timestamp = sys_sw_software.software_timestamp AND system.man_status = \'production\'',1,'Administrator','DHCP','','','','','','',0);



INSERT INTO `oa_group` VALUES (12,'Web Servers','d','SELECT distinct(system.system_id) FROM system, sys_sw_software WHERE (sys_sw_software.software_name LIKE \'apache%\' OR sys_sw_software.software_name = \'IIS\') AND system.system_id = sys_sw_software.system_id AND system.timestamp = sys_sw_software.software_timestamp AND system.man_status = \'production\'',1,'Administrator','Web','','','','','','',0);



INSERT INTO `oa_group` VALUES (13,'Database Servers','d','SELECT distinct(system.system_id) FROM system, sys_sw_software WHERE (sys_sw_software.software_name LIKE \'%SQL Server%\' OR sys_sw_software.software_name LIKE \'mysql-server%\') AND system.system_id = sys_sw_software.system_id AND system.timestamp = sys_sw_software.software_timestamp AND system.man_status = \'production\'',1,'Administrator','Database','','','','','','',0);



INSERT INTO `oa_group` VALUES (14,'SSH Servers','d','SELECT distinct(system.system_id) FROM system, sys_sw_software WHERE sys_sw_software.software_name = \'openssh-server\' AND system.system_id = sys_sw_software.system_id AND system.timestamp = sys_sw_software.software_timestamp AND system.man_status = \'production\'',1,'Administrator','SSH','','','','','','',0);



INSERT INTO `oa_group` VALUES (15,'Windows Systems','d','SELECT distinct(system.system_id) FROM system WHERE system.os = \'windows\' AND system.man_status = \'production\'',1,'Administrator','Windows','','','','','','',0);



INSERT INTO `oa_group` VALUES (16,'Linux Systems','d','SELECT distinct(system.system_id) FROM system WHERE system.os = \'linux\' AND system.man_status = \'production\'',1,'Administrator','Linux','','','','','','',0);



INSERT INTO `oa_group` VALUES (17,'Virtual Systems','d','SELECT distinct(system.system_id) FROM system, sys_hw_video WHERE sys_hw_video.video_description LIKE \'%vmware%\' AND system.man_status = \'production\' AND sys_hw_video.system_id = system.system_id',1,'Administrator','Virtual','','','','','','',0);



INSERT INTO `oa_group` VALUES (18,'Windows Servers','d','SELECT distinct(system.system_id) FROM system WHERE system.os_full_name LIKE \'%windows%server%\' AND system.man_status = \'production\'',1,'Administrator','Win Svr','','','','','','',0);



INSERT INTO `oa_group` VALUES (19,'Windows XP','d','SELECT distinct(system.system_id) FROM system WHERE system.os_full_name LIKE \'%windows xp%\' AND system.man_status = \'production\'',1,'Administrator','XP','','','','','','',0);



INSERT INTO `oa_group` VALUES (20,'Windows 2000','d','SELECT distinct(system.system_id) FROM system WHERE system.os_full_name LIKE \'%windows%2000%\' AND system.man_status = \'production\'',1,'Administrator','Win 2000','','','','','','',0);



INSERT INTO `oa_group` VALUES (21,'Windows 2003','d','SELECT distinct(system.system_id) FROM system WHERE system.os_full_name LIKE \'%windows%2003%\' AND system.man_status = \'production\'',1,'Administrator','Win 2003','','','','','','',0);



INSERT INTO `oa_group` VALUES (22,'Windows Vista','d','SELECT distinct(system.system_id) FROM system WHERE system.os_full_name LIKE \'%vista%\' AND system.man_status = \'production\'',1,'Administrator','Vista','','','','','','',0);



INSERT INTO `oa_group` VALUES (23,'Ubuntu','d','SELECT distinct(system.system_id) FROM system WHERE system.os_full_name LIKE \'%ubuntu%\' AND system.man_status = \'production\'',1,'Administrator','Ubuntu','','','','','','',0);



INSERT INTO `oa_group` VALUES (24,'Debian','d','SELECT distinct(system.system_id) FROM system WHERE system.os_full_name LIKE \'%debian%\' AND system.man_status = \'production\'',1,'Administrator','Debian','','','','','','',0);



INSERT INTO `oa_group` VALUES (25,'RedHat','d','SELECT distinct(system.system_id) FROM system WHERE system.os_full_name LIKE \'%red%hat%\' AND system.man_status = \'production\'',1,'Administrator','RedHat','','','','','','',0);



ALTER TABLE `oa_group` AUTO_INCREMENT = 1000;



UNLOCK TABLES;



LOCK TABLES `oa_group_user` WRITE;

INSERT INTO `oa_group_user` VALUES (1,1,1,10);

INSERT INTO `oa_group_user` VALUES (2,1,10,10);

INSERT INTO `oa_group_user` VALUES (3,1,11,10);

INSERT INTO `oa_group_user` VALUES (4,1,12,10);

INSERT INTO `oa_group_user` VALUES (5,1,13,10);

INSERT INTO `oa_group_user` VALUES (6,1,14,10);

INSERT INTO `oa_group_user` VALUES (7,1,15,10);

INSERT INTO `oa_group_user` VALUES (8,1,16,10);

INSERT INTO `oa_group_user` VALUES (9,1,17,10);

INSERT INTO `oa_group_user` VALUES (10,1,18,10);

INSERT INTO `oa_group_user` VALUES (11,1,19,10);

INSERT INTO `oa_group_user` VALUES (12,1,20,10);

INSERT INTO `oa_group_user` VALUES (13,1,21,10);

INSERT INTO `oa_group_user` VALUES (14,1,22,10);

INSERT INTO `oa_group_user` VALUES (15,1,23,10);

INSERT INTO `oa_group_user` VALUES (16,1,24,10);

INSERT INTO `oa_group_user` VALUES (17,1,25,10);

UNLOCK TABLES;



LOCK TABLES `oa_user` WRITE;

INSERT INTO `oa_user` VALUES (1,'admin','5f4dcc3b5aa765d61d8327deb882cf99','Administrator',NULL,'en',10,'default');

UNLOCK TABLES;









/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;



/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;



-- Dump completed on 2008-09-17 21:43:02


[/code]

_________________
Support and Development hours available from [url=https://opmantek.com]Opmantek[/url].
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
PostPosted: Thu Nov 13, 2008 8:38 pm 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1964
Location: Brisbane, Australia
And here is the Linux audit script.

NOTE #1 - only tested on Ubuntu 8.04 (I am currently working in an all Windows shop - shudder).
NOTE #2 - I haven't touched this in a while, so it probably doesn't have as much as the Windows script.

[code]#!/bin/bash

OA_SUBMIT_URL=http://192.168.0.7/oa/admin_system_add.php
OA_SUBMIT=y
OA_VERBOSE=y
OA_SAFEMODE=n

INT_PACKAGES="abiword alsa amarok apache apache2 apparmor beagle bind9 build-essentials
compiz cups-client cups-pdf cups-sys dbus dhcp dhcp-client dhcp3-server dhcp3-client
emacs evolution firefox firefox-3.0 freetype gcc gimp glibc gnome gnucash gnumeric hal
httpd icedtea-java7-bin inkscape jre java-runtime java-sdk java-virtual-machine java5-sdk
java6-sdk java7-sdk kde-core kde4-core k3b kdebase koffice koffice2 libgtk2.0-0 libqt3-mt libqt4-core libxine1
module-init-tools mono mplayer mysql-admin mysql-client mysql-client-5.0 mysql-server mysql-server-5.0 mysql-query-browser nautilus
nmap nvidia-glx nvidia-glx-legacy nvidia-glx-new openoffice.org openssh-client openssh-server
openssl perl php5 php5-cli php5-gd php5-imagick php5-imap php5-json php5-ldap php5-memcache php5-mysql
php5-odbc php5-pgsql php5-snmp php5-sqlite php5-sqlite3 php5-suhosin phpgroupware phpldapadmin phpmyadmin
phppgadmin phpsysinfo pidgin postfix postgresql python samba samba-common sendmail sun-java5-bin sun-java6-bin
thunderbird udev vim vlc vmware-server xfce4 xinetd xorg xorg-driver-fglrx xserver-xorg-video-ati
xserver-xorg-video-i810 xserver-xorg-video-intel xserver-xorg-video-nv xserver-xorg-video-s3 xserver-xorg-video-vmware xubuntu-desktop"

# Change this to nothing if you want to track ALL installed packages on dpkg systems.
OA_PACKAGES="apt azureus bash build-essential cdparanoia cdrdao cdrecord cpp cron cupsys cvs dbus dhcp3-client diff dpkg epiphany-browser esound evolution firefox flashplugin-nonfree foomatic-db g++ gaim gcc gdm gedit gimp gnome-about gnucash gnumeric gtk+ httpd inkscape iptables k3b kdebase koffice libgnome2-0 linux-image-386 metacity mozilla-browser mysql-admin mysql-query-browser mysql-server-4.1 nautilus openoffice.org openssh-client openssh-server perl php4 php5 postfix postgresql python python2.4 rdesktop rhythmbox samba-common sendmail smbclient subversion sun-j2re1.5 swf-player synaptic thunderbird tsclient udev vim vlc vnc-common webmin xfce xmms xserver-xorg"
OA_DPKG_TRACK=$OA_PACKAGES
OA_YUM_TRACK=$OA_PACKAGES

# If you're not worried about attacks, you can just use the first one in the path.
if [ $OA_SAFEMODE="n" ] || [ $OA_SAFEMODE="N" ]
then
OA_AWK=`which awk`
OA_CAT=`which cat`
OA_CUT=`which cut`
OA_DATE=`which date`
OA_DF=`which df`
OA_DPKG=`which dpkg 2>/dev/null`
OA_EXPR=`which expr`
OA_FDISK=`which fdisk`
OA_GREP=`which grep`
OA_HAL_FIND=`which hal-find-by-property`
OA_HAL_GET=`which hal-get-property`
OA_HAL_LIST=`which lshal`
OA_HOSTNAME=`which hostname`
OA_IFCONFIG=`which ifconfig`
OA_LSPCI=`which lspci`
OA_RM=`which rm`
OA_SED=`which sed`
OA_UNAME=`which uname`
OA_TAIL=`which tail`
OA_TR=`which tr`
OA_WC=`which wc`
OA_WGET=`which wget`
OA_WHOAMI=`which whoami`
OA_YUM=`which yum 2>/dev/null`
else
OA_AWK=/usr/bin/awk
OA_CAT=/bin/cat
OA_CUT=/usr/bin/cut
OA_DATE=/bin/date
OA_DF=/bin/df
OA_DPKG=/usr/bin/dpkg
OA_EXPR=/usr/bin/expr
OA_FDISK=/sbin/fdisk
OA_GREP=/bin/grep
OA_HAL_FIND=/usr/bin/hal-find-by-property
OA_HAL_GET=/usr/bin/hal-get-property
OA_HAL_LIST=/usr/bin/lshal
OA_HOSTNAME=/bin/hostname
OA_IFCONFIG=/sbin/ifconfig
OA_LSPCI=/usr/bin/lspci
OA_RM=/bin/rm
OA_SED=/bin/sed
OA_TAIL=/usr/bin/tail
OA_TR=/usr/bin/tr
OA_UNAME=/bin/uname
OA_WC=/usr/bin/wc
OA_WGET=/usr/bin/wget
OA_WHOAMI=/usr/bin/whoami
OA_YUM=/usr/bin/yum
fi

INT_SYSTEM=`$OA_HAL_FIND --key info.product --string Computer`

# Get the UUID
SYSTEM_UUID=""
SYSTEM_UUID=`$OA_HAL_GET --udi $INT_SYSTEM --key system.hardware.uuid 2>/dev/null`
if [ "$SYSTEM_UUID" = "" ]
then
SYSTEM_UUID=`$OA_HAL_GET --udi $INT_SYSTEM --key smbios.system.uuid 2>/dev/null`
fi



for i in `$OA_HAL_FIND --key linux.subsystem --string net`; do
SYSTEM_MAC_ADDRESS=`$OA_HAL_GET --udi $i --key net.address`
INT_NET_NAME=`$OA_HAL_GET --udi $i --key net.interface`
if [ "`$OA_IFCONFIG $NET_NAME | $OA_GREP 'inet addr'`" ]
then
SYSTEM_IP_ADDRESS=`$OA_IFCONFIG $INT_NET_NAME | $OA_GREP -w inet | $OA_CUT -d":" -f2 | $OA_CUT -d" " -f1`
else
SYSTEM_IP_ADDRESS="0.0.0.0"
fi
done
SYSTEM_HOSTNAME=`$OA_HOSTNAME`
INT_XML_FILE=$HOSTNAME.txt
SYSTEM_TYPE="system"
SYSTEM_OS="linux"
INT_NAME=`$OA_UNAME -s`
if [ "$INT_NAME" = "Linux" ]
then if test -f /etc/redhat-release; then
SYSTEM_OS_SHORT_NAME="RedHat"
SYSTEM_OS_FULL_NAME=`$OA_CAT /etc/redhat-release`
SYSTEM_PC_DATE_OS_INSTALLATION=`ls -lha /etc/redhat-release | cut -d" " -f6`
INT_PCK_MGR=$OA_YUM
elif test -f /etc/redhat-version; then
SYSTEM_OS_SHORT_NAME="RedHat"
SYSTEM_OS_FULL_NAME=`$OA_CAT /etc/redhat-version`
SYSTEM_PC_DATE_OS_INSTALLATION=`ls -lha /etc/redhat-version | cut -d" " -f6`
INT_PCK_MGR=$OA_YUM
elif test -f /etc/fedora-release; then
SYSTEM_OS_SHORT_NAME="Fedora"
SYSTEM_OS_FULL_NAME=`$OA_CAT /etc/fedora-release`
SYSTEM_PC_DATE_OS_INSTALLATION=`ls -lha /etc/fedora-release | cut -d" " -f6`
INT_PCK_MGR=$OA_YUM
elif test -f /etc/mandrake-release; then
SYSTEM_OS_SHORT_NAME="Mandrake"
SYSTEM_OS_FULL_NAME=`$OA_CAT /etc/mandrake-release`
SYSTEM_PC_DATE_OS_INSTALLATION=`ls -lha /etc/mandrake-release | cut -d" " -f6`
INT_PCK_MGR='urpmi'
elif test -f /etc/SuSE-release; then
SYSTEM_OS_SHORT_NAME="Novell SuSE"
SYSTEM_OS_FULL_NAME=`$OA_CAT /etc/SuSE-release`
SYSTEM_PC_DATE_OS_INSTALLATION=`ls -lha /etc/SuSE-release | cut -d" " -f6`
INT_PCK_MGR=$OA_YUM
elif test -f /etc/arch-release; then
SYSTEM_OS_SHORT_NAME="Arch"
SYSTEM_OS_FULL_NAME=`$OA_CAT /etc/arch-release`
SYSTEM_PC_DATE_OS_INSTALLATION=`ls -lha /etc/arch-release | cut -d" " -f6`
INT_PCK_MGR=''
elif test -f /etc/gentoo-release; then
SYSTEM_OS_SHORT_NAME="Gentoo"
SYSTEM_OS_FULL_NAME=`$OA_CAT /etc/gentoo-release`
SYSTEM_PC_DATE_OS_INSTALLATION=`ls -lha /etc/gentoo-release | cut -d" " -f6`
INT_PCK_MGR=''
elif test -f /etc/slackware-release; then
SYSTEM_OS_SHORT_NAME="Slackware"
SYSTEM_OS_FULL_NAME=`$OA_CAT /etc/slackware-release`
SYSTEM_PC_DATE_OS_INSTALLATION=`ls -lha /etc/slackware-release | cut -d" " -f6`
INT_PCK_MGR=''
elif test -f /etc/slackware-version; then
SYSTEM_OS_SHORT_NAME="Slackware"
SYSTEM_OS_FULL_NAME=`$OA_CAT /etc/slackware-version`
SYSTEM_PC_DATE_OS_INSTALLATION=`ls -lha /etc/slackware-version | cut -d" " -f6`
INT_PCK_MGR=''
elif test -f /etc/yellowdog-release; then
SYSTEM_OS_SHORT_NAME="Yellow dog"
SYSTEM_OS_FULL_NAME=`$OA_CAT /etc/yellowdog-release`
SYSTEM_PC_DATE_OS_INSTALLATION=`ls -lha /etc/yellowdog-release | cut -d" " -f6`
INT_PCK_MGR=''
elif test -f /etc/issue; then
SYSTEM_OS_SHORT_NAME="Ubuntu"
SYSTEM_OS_FULL_NAME=`$OA_CAT /etc/issue`
SYSTEM_PC_DATE_OS_INSTALLATION=`ls -lha /etc/issue | cut -d" " -f6`
INT_PCK_MGR=$OA_DPKG
elif test -f /etc/debian-version; then
SYSTEM_OS_SHORT_NAME="Debian"
SYSTEM_OS_FULL_NAME=`$OA_CAT /etc/debian-version`
SYSTEM_PC_DATE_OS_INSTALLATION=`ls -lha /etc/debian-version | cut -d" " -f6`
INT_PCK_MGR=$OA_DPKG
else SYSTEM_OS_SHORT_NAME="unknown"
SYSTEM_OS_FULL_NAME="unknown"
SYSTEM_PC_DATE_OS_INSTALLATION=`unknown`
INT_PCK_MGR=''
fi
fi
SYSTEM_OS_VERSION=`uname -r`

SYSTEM_SERIAL=""
SYSTEM_SERIAL=`$OA_HAL_GET --udi $INT_SYSTEM --key system.hardware.serial 2>/dev/null`
if [ "$SYSTEM_SERIAL" = "" ]
then
SYSTEM_SERIAL=`$OA_HAL_GET --udi $INT_SYSTEM --key smbios.system.serial 2>/dev/null`
fi

SYSTEM_MODEL=""
SYSTEM_MODEL=`$OA_HAL_GET --udi $INT_SYSTEM --key system.hardware.product 2>/dev/null`
if [ "$SYSTEM_MODEL" = "" ]
then
SYSTEM_MODEL=`$OA_HAL_GET --udi $INT_SYSTEM --key smbios.system.product 2>/dev/null`
fi

SYSTEM_MANUFACTURER=""
SYSTEM_MANUFACTURER=`$OA_HAL_GET --udi $INT_SYSTEM --key system.hardware.vendor 2>/dev/null`
if [ "$SYSTEM_MANUFACTURER" = "" ]
then
SYSTEM_MANUFACTURER=`$OA_HAL_GET --udi $INT_SYSTEM --key smbios.system.manufacturer 2>/dev/null`
fi

SYSTEM_FORM_FACTOR=""
SYSTEM_FORM_FACTOR=`$OA_HAL_GET --udi $INT_SYSTEM --key system.formfactor 2>/dev/null`
if [ "$SYSTEM_FORM_FACTOR" = "" ] || [ "$SYSTEM_FORM_FACTOR" = "unknown" ]
then
SYSTEM_FORM_FACTOR=`$OA_HAL_GET --udi $INT_SYSTEM --key smbios.chassis.type 2>/dev/null`
fi

SYSTEM_PC_MEMORY=` $OA_CAT /proc/meminfo | $OA_GREP MemTotal | $OA_CUT -d: -f2 | $OA_CUT -dk -f1 | $OA_SED "s/^ *//"`
SYSTEM_PC_NUM_PROCESSOR=`$OA_CAT /proc/cpuinfo | $OA_GREP -G "^processor" | wc -l`
if [ "$SYSTEM_PC_NUM_PROCESSOR" == 0 ]
then
SYSTEM_PC_NUM_PROCESSOR=`$OA_CAT /proc/cpuinfo | $OA_GREP -G "^cpu" | wc -l`
fi

SYSTEM_TIMESTAMP=`date '+%F %T'`

# Create initial file
echo "<system>" > $INT_XML_FILE

# Create 'system' section of XML
echo " <sys>" >> $INT_XML_FILE
echo " <system_date>$SYSTEM_TIMESTAMP</system_date>" >> $INT_XML_FILE
echo " <system_uuid>$SYSTEM_UUID</system_uuid>" >> $INT_XML_FILE
echo " <system_mac_address>$SYSTEM_MAC_ADDRESS</system_mac_address>" >> $INT_XML_FILE
echo " <system_ip_address>$SYSTEM_IP_ADDRESS</system_ip_address>" >> $INT_XML_FILE
echo " <system_hostname>$SYSTEM_HOSTNAME</system_hostname>" >> $INT_XML_FILE
echo " <system_domain></system_domain>" >> $INT_XML_FILE
echo " <system_description></system_description>" >> $INT_XML_FILE
echo " <system_type>$SYSTEM_TYPE</system_type>" >> $INT_XML_FILE
echo " <system_os>$SYSTEM_OS</system_os>" >> $INT_XML_FILE
echo " <system_os_short_name>$SYSTEM_OS_SHORT_NAME</system_os_short_name>" >> $INT_XML_FILE
echo " <system_os_full_name>$SYSTEM_OS_FULL_NAME</system_os_full_name>" >> $INT_XML_FILE
echo " <system_os_version>$SYSTEM_OS_VERSION</system_os_version>" >> $INT_XML_FILE
echo " <system_serial>$SYSTEM_SERIAL</system_serial>" >> $INT_XML_FILE
echo " <system_model>$SYSTEM_MODEL</system_model>" >> $INT_XML_FILE
echo " <system_manufacturer>$SYSTEM_MANUFACTURER</system_manufacturer>" >> $INT_XML_FILE
echo " <system_form_factor>$SYSTEM_FORM_FACTOR</system_form_factor>" >> $INT_XML_FILE
echo " <system_pc_memory>$SYSTEM_PC_MEMORY</system_pc_memory>" >> $INT_XML_FILE
echo " <system_pc_num_processor>$SYSTEM_PC_NUM_PROCESSOR</system_pc_num_processor>" >> $INT_XML_FILE
echo " <system_pc_date_os_installation>$SYSTEM_PC_DATE_OS_INSTALLATION</system_pc_date_os_installation>" >> $INT_XML_FILE
echo " </sys>" >> $INT_XML_FILE






#Bios
BIOS_DESCRIPTION=`$OA_HAL_GET --udi $INT_SYSTEM --key smbios.system.product 2>/dev/null`
BIOS_MANUFACTURER=`$OA_HAL_GET --udi $INT_SYSTEM --key smbios.bios.vendor 2>/dev/null`
BIOS_SERIAL=`$OA_HAL_GET --udi $INT_SYSTEM --key smbios.system.serial 2>/dev/null`
BIOS_SMVERSION=`$OA_HAL_GET --udi $INT_SYSTEM --key smbios.system.version 2>/dev/null`
BIOS_VERSION=`$OA_HAL_GET --udi $INT_SYSTEM --key smbios.bios.version 2>/dev/null`
if [ "$BIOS_DESCRIPTION" = "" ] && [ "$BIOS_SERIAL" = "" ]
then
BIOS="null"
else
echo " <bios>" >> $INT_XML_FILE
echo " <bios_description>$BIOS_DESCRIPTION</bios_description>" >> $INT_XML_FILE
echo " <bios_manufacturer>$BIOS_MANUFACTURER</bios_manufacturer>" >> $INT_XML_FILE
echo " <bios_serial>$BIOS_SERIAL</bios_serial>" >> $INT_XML_FILE
echo " <bios_smversion>$BIOS_SMVERSION</bios_smversion>" >> $INT_XML_FILE
echo " <bios_version>$BIOS_VERSION</bios_version>" >> $INT_XML_FILE
echo " </bios>" >> $INT_XML_FILE
fi






#Hard Disks
echo " <hard_disks>" >> $INT_XML_FILE
for i in `$OA_HAL_FIND --key storage.drive_type --string disk 2>/dev/null`; do
HARD_DRIVE_CAPTION=`$OA_HAL_GET --udi $i --key block.device 2>/dev/null`
HARD_DRIVE_INDEX=`$OA_HAL_GET --udi $i --key block.device 2>/dev/null`
HARD_DRIVE_INTERFACE=`$OA_HAL_GET --udi $i --key storage.bus 2>/dev/null`
HARD_DRIVE_MANUFACTURER=`$OA_HAL_GET --udi $i --key storage.vendor 2>/dev/null`
HARD_DRIVE_MODEL=`$OA_HAL_GET --udi $i --key storage.model 2>/dev/null`
HARD_DRIVE_SERIAL=`$OA_HAL_GET --udi $i --key storage.serial 2>/dev/null`
HARD_DRIVE_SIZE=`$OA_HAL_GET --udi $i --key storage.size 2>/dev/null`
HARD_DRIVE_DEVICE_ID=`$OA_HAL_GET --udi $i --key info.udi 2>/dev/null`
HARD_DRIVE_PATH=`$OA_HAL_GET --udi $i --key block.device 2>/dev/null`
INT_HARD_DRIVE_UDI=`$OA_HAL_GET --udi $i --key info.udi 2>/dev/null`

if [ "$HARD_DRIVE_INTERFACE" = "usb" ]
then
HARD_DRIVE_SIZE=`$OA_HAL_GET --udi $i --key storage.removable.media_size 2>/dev/null`
fi

# Return disk size in megabytes (not GB for legacy drives, and VMs)
if test -n "$HARD_DRIVE_SIZE"
then
HARD_DRIVE_SIZE=`$OA_EXPR $($OA_EXPR $HARD_DRIVE_SIZE / 1024) / 1024`
else
# Some devices, such as VMWare drives, don't have the storage.size key
# Fallback to using fdisk to report the required value
# ***NOTE*** must user superuser privs to run fdisk
HARD_DRIVE_SIZE=`$OA_FDISK -s $HARD_DRIVE_PATH`
HARD_DRIVE_SIZE=`$OA_EXPR $HARD_DRIVE_SIZE / 1024` # Already returned in 1024-byte blocks
fi

echo " <hard_disk>" >> $INT_XML_FILE
echo " <hard_drive_caption>$HARD_DRIVE_CAPTION</hard_drive_caption>" >> $INT_XML_FILE
echo " <hard_drive_index>$HARD_DRIVE_INDEX</hard_drive_index>" >> $INT_XML_FILE
echo " <hard_drive_interface_type>$HARD_DRIVE_INTERFACE</hard_drive_interface_type>" >> $INT_XML_FILE
echo " <hard_drive_manufacturer>$HARD_DRIVE_MANUFACTURER</hard_drive_manufacturer>" >> $INT_XML_FILE
echo " <hard_drive_model>$HARD_DRIVE_MODEL</hard_drive_model>" >> $INT_XML_FILE
echo " <hard_drive_serial>$HARD_DRIVE_SERIAL</hard_drive_serial>" >> $INT_XML_FILE
echo " <hard_drive_size>$HARD_DRIVE_SIZE</hard_drive_size>" >> $INT_XML_FILE
echo " <hard_drive_device_id>$HARD_DRIVE_DEVICE_ID</hard_drive_device_id>" >> $INT_XML_FILE
echo " </hard_disk>" >> $INT_XML_FILE
INT_HD_LIST+=`echo "$INT_HARD_DRIVE_UDI "`
done
echo " </hard_disks>" >> $INT_XML_FILE

#echo "$INT_HD_LIST"
echo " <partitions>" >> $INT_XML_FILE
for i in $INT_HD_LIST
do
for j in `$OA_HAL_FIND --key info.parent --string "$i"`; do
HARD_DRIVE_INDEX=`$OA_HAL_GET --udi $i --key block.device 2>/dev/null`
PARTITION_MOUNT_POINT=`$OA_HAL_GET --udi $j --key volume.mount_point 2>/dev/null`
PARTITION_NAME=`$OA_HAL_GET --udi $j --key volume.label 2>/dev/null`
PARTITION_CAPTION=`$OA_HAL_GET --udi $j --key block.device 2>/dev/null`
PARTITION_SIZE=`df $PARTITION_CAPTION | grep $PARTITION_CAPTION | awk {'print $2'}`
if [ "$PARTITION_SIZE" == "" ]
then
PARTITION_SIZE="0"
fi
PARTITION_SIZE=`$OA_EXPR $PARTITION_SIZE / 1024`
PARTITION_USED_SPACE=`df $PARTITION_CAPTION | grep $PARTITION_CAPTION | awk {'print $3'}`
if [ "$PARTITION_USED_SPACE" == "" ]
then
PARTITION_USED_SPACE="0"
fi
PARTITION_USED_SPACE=`$OA_EXPR $PARTITION_USED_SPACE / 1024`
PARTITION_FREE_SPACE=`df $PARTITION_CAPTION | grep $PARTITION_CAPTION | awk {'print $4'}`
if [ "$PARTITION_FREE_SPACE" == "" ]
then
PARTITION_FREE_SPACE="0"
fi
PARTITION_FREE_SPACE=`$OA_EXPR $PARTITION_FREE_SPACE / 1024`
PARTITION_FORMAT=`$OA_HAL_GET --udi $j --key volume.fstype 2>/dev/null`
PARTITION_DEVICE_ID=`$OA_HAL_GET --udi $j --key info.udi 2>/dev/null`
PARTITION_DISK_INDEX=`$OA_HAL_GET --udi $j --key volume.partition.number 2>/dev/null`
echo " <partition>" >> $INT_XML_FILE
echo " <hard_drive_index>$HARD_DRIVE_INDEX</hard_drive_index>" >> $INT_XML_FILE
echo " <partition_mount_point>$PARTITION_MOUNT_POINT</partition_mount_point>" >> $INT_XML_FILE
echo " <partition_name>$PARTITION_NAME</partition_name>" >> $INT_XML_FILE
echo " <partition_size>$PARTITION_SIZE</partition_size>" >> $INT_XML_FILE
echo " <partition_free_space>$PARTITION_FREE_SPACE</partition_free_space>" >> $INT_XML_FILE
echo " <partition_used_space>$PARTITION_USED_SPACE</partition_used_space>" >> $INT_XML_FILE
echo " <partition_format>$PARTITION_FORMAT</partition_format>" >> $INT_XML_FILE
echo " <partition_caption>$PARTITION_CAPTION</partition_caption>" >> $INT_XML_FILE
echo " <partition_device_id>$PARTITION_DEVICE_ID</partition_device_id>" >> $INT_XML_FILE
echo " <partition_disk_index>$PARTITION_DISK_INDEX</partition_disk_index>" >> $INT_XML_FILE
echo " </partition>" >> $INT_XML_FILE
done
done
echo " </partitions>" >> $INT_XML_FILE

echo " <network_cards>" >> $INT_XML_FILE
for i in `$OA_HAL_FIND --key info.product --string "Networking Interface"`; do
NET_MAC_ADDRESS=`$OA_HAL_GET --udi $i --key net.address 2>/dev/null`
NET_DESCRIPTION=`$OA_HAL_GET --udi $i --key net.interface 2>/dev/null`
INT_NET_PARENT=`$OA_HAL_GET --udi $i --key info.parent 2>/dev/null`
NET_MANUFACTURER=`$OA_HAL_GET --udi $INT_NET_PARENT --key info.vendor 2>/dev/null`
NET_MODEL=`$OA_HAL_GET --udi $INT_NET_PARENT --key info.product 2>/dev/null`
echo " <network_card>" >> $INT_XML_FILE
echo " <net_mac_address>$NET_MAC_ADDRESS</net_mac_address>" >> $INT_XML_FILE
echo " <net_description>$NET_DESCRIPTION</net_description>" >> $INT_XML_FILE
echo " <net_manufacturer>$NET_MANUFACTURER</net_manufacturer>" >> $INT_XML_FILE
echo " <net_model>$NET_MODEL</net_model>" >> $INT_XML_FILE
echo " </network_card>" >> $INT_XML_FILE
done
for i in `$OA_HAL_FIND --key info.product --string "WLAN Interface"`; do
NET_MAC_ADDRESS=`$OA_HAL_GET --udi $i --key net.address 2>/dev/null`
NET_DESCRIPTION=`$OA_HAL_GET --udi $i --key net.interface 2>/dev/null`
INT_NET_PARENT=`$OA_HAL_GET --udi $i --key info.parent 2>/dev/null`
NET_MANUFACTURER=`$OA_HAL_GET --udi $INT_NET_PARENT --key info.vendor 2>/dev/null`
NET_MODEL=`$OA_HAL_GET --udi $INT_NET_PARENT --key info.product 2>/dev/null`
echo " <network_card>" >> $INT_XML_FILE
echo " <net_mac_address>$NET_MAC_ADDRESS</net_mac_address>" >> $INT_XML_FILE
echo " <net_description>$NET_DESCRIPTION</net_description>" >> $INT_XML_FILE
echo " <net_manufacturer>$NET_MANUFACTURER</net_manufacturer>" >> $INT_XML_FILE
echo " <net_model>$NET_MODEL</net_model>" >> $INT_XML_FILE
echo " </network_card>" >> $INT_XML_FILE
done
echo " </network_cards>" >> $INT_XML_FILE

echo " <addresses>" >> $INT_XML_FILE
for i in `$OA_HAL_FIND --key info.product --string "Networking Interface"`; do
NET_DESCRIPTION=`$OA_HAL_GET --udi $i --key net.interface 2>/dev/null`
if [ "`$OA_IFCONFIG $NET_DESCRIPTION | $OA_GREP 'inet addr'`" ]
then
NET_MAC_ADDRESS=`$OA_HAL_GET --udi $i --key net.address`
IP_ADDRESS=`$OA_IFCONFIG $NET_DESCRIPTION | $OA_GREP -w inet | $OA_CUT -d":" -f2 | $OA_CUT -d" " -f1`
IP_ADDRESS_V6=`$OA_IFCONFIG $NET_DESCRIPTION | $OA_GREP -w inet6 | $OA_CUT -d" " -f13`
IP_SUBNET=`$OA_IFCONFIG $NET_DESCRIPTION | $OA_GREP -w inet | $OA_CUT -d":" -f4 | $OA_CUT -d" " -f1`
else
# Interface is not online
IP_ADDRESS="0.0.0.0"
IP_ADDRESS_V6=""
IP_SUBNET="0.0.0.0"
fi
echo " <ip_address>" >> $INT_XML_FILE
echo " <net_mac_address>$NET_MAC_ADDRESS</net_mac_address>" >> $INT_XML_FILE
echo " <ip_address>$IP_ADDRESS</ip_address>" >> $INT_XML_FILE
echo " <ip_address_v6>$IP_ADDRESS_V6</ip_address_v6>" >> $INT_XML_FILE
echo " <ip_subnet>$IP_SUBNET</ip_subnet>" >> $INT_XML_FILE
echo " </ip_address>" >> $INT_XML_FILE
done
for i in `$OA_HAL_FIND --key info.product --string "WLAN Interface"`; do
NET_DESCRIPTION=`$OA_HAL_GET --udi $i --key net.interface 2>/dev/null`
if [ "`$OA_IFCONFIG $NET_DESCRIPTION | $OA_GREP 'inet addr'`" ]
then
NET_MAC_ADDRESS=`$OA_HAL_GET --udi $i --key net.address`
IP_ADDRESS=`$OA_IFCONFIG $NET_DESCRIPTION | $OA_GREP -w inet | $OA_CUT -d":" -f2 | $OA_CUT -d" " -f1`
IP_ADDRESS_V6=`$OA_IFCONFIG $NET_DESCRIPTION | $OA_GREP -w inet6 | $OA_CUT -d" " -f13`
IP_SUBNET=`$OA_IFCONFIG $NET_DESCRIPTION | $OA_GREP -w inet | $OA_CUT -d":" -f4 | $OA_CUT -d" " -f1`
else
# Interface is not online
IP_ADDRESS="0.0.0.0"
IP_ADDRESS_V6=""
IP_SUBNET="0.0.0.0"
fi
echo " <ip_address>" >> $INT_XML_FILE
echo " <net_mac_address>$NET_MAC_ADDRESS</net_mac_address>" >> $INT_XML_FILE
echo " <ip_address>$IP_ADDRESS</ip_address>" >> $INT_XML_FILE
echo " <ip_address_v6>$IP_ADDRESS_V6</ip_address_v6>" >> $INT_XML_FILE
echo " <ip_subnet>$IP_SUBNET</ip_subnet>" >> $INT_XML_FILE
echo " </ip_address>" >> $INT_XML_FILE
done
echo " </addresses>" >> $INT_XML_FILE

# Sound Cards
echo " <sound_cards>" >> $INT_XML_FILE
for i in `$OA_HAL_FIND --key info.category --string sound`; do
SOUND_NAME=`$OA_HAL_GET --udi $i --key info.product 2>/dev/null`
INT_SOUND_PARENT=`$OA_HAL_GET --udi $i --key info.parent 2>/dev/null`
SOUND_MANUFACTURER=`$OA_HAL_GET --udi $INT_SOUND_PARENT --key info.vendor 2>/dev/null`
SOUND_DEVICE_ID=`echo "$i" 2>/dev/null`
echo " <sound_card>" >> $INT_XML_FILE
echo " <sound_name>$SOUND_NAME</sound_name>" >> $INT_XML_FILE
echo " <sound_manufacturer>$SOUND_MANUFACTURER</sound_manufacturer>" >> $INT_XML_FILE
echo " <sound_device_id>$SOUND_DEVICE_ID</sound_device_id>" >> $INT_XML_FILE
echo " </sound_card>" >> $INT_XML_FILE
done
echo " </sound_cards>" >> $INT_XML_FILE



# Optical Drives
echo " <optical_drives>" >> $INT_XML_FILE
for i in `$OA_HAL_FIND --key storage.drive_type --string cdrom`; do
OPTICAL_DRIVE_CAPTION=`$OA_HAL_GET --udi $i --key linux.fstab.mountpoint 2>/dev/null`
OPTICAL_DRIVE_MODEL=`$OA_HAL_GET --udi $i --key storage.model 2>/dev/null`
OPTICAL_DRIVE_DEVICE_ID=`$OA_HAL_GET --udi $i --key info.udi 2>/dev/null`
OPTICAL_DRIVE_MOUNT_POINT=`$OA_HAL_GET --udi $i --key block.device 2>/dev/null`
if test -z "$OPTICAL_DRIVE_MOUNT_POINT"
then
OPTICAL_DRIVE_MOUNT_POINT=`$OA_HAL_GET --udi $i --key linux.fstab.mountpoint 2>/dev/null`
fi

echo " <optical_drive>" >> $INT_XML_FILE
echo " <optical_drive_caption>$OPTICAL_DRIVE_CAPTION</optical_drive_caption>" >> $INT_XML_FILE
echo " <optical_drive_model>$OPTICAL_DRIVE_MODEL</optical_drive_model>" >> $INT_XML_FILE
echo " <optical_drive_device_id>$OPTICAL_DRIVE_DEVICE_ID</optical_drive_device_id>" >> $INT_XML_FILE
echo " <optical_drive_mount_point>$OPTICAL_DRIVE_MOUNT_POINT</optical_drive_mount_point>" >> $INT_XML_FILE
echo " </optical_drive>" >> $INT_XML_FILE
done
echo " </optical_drives>" >> $INT_XML_FILE



#Video
echo " <video_cards>" >> $INT_XML_FILE
#for i in `$OA_LSPCI -mm | $OA_GREP "VGA compatible controller"`
#do
VIDEO_MODEL=`$OA_LSPCI -mm | $OA_GREP "VGA compatible controller" | $OA_CUT -d'"' -f6`
VIDEO_MANUFACTURER=`$OA_LSPCI -mm | $OA_GREP "VGA compatible controller" | $OA_CUT -d'"' -f4`
#VIDEO_HOR=`xrandr | grep "*" | sed "s/ //" | cut -d"x" -f1 2>/dev/null`
#VIDEO_VERT=`xrandr | grep "*" | sed "s/ //" | cut -d"x" -f2 | cut -d" " -f1 2>/dev/null`
echo " <video_card>" >> $INT_XML_FILE
echo " <video_description>$VIDEO_MODEL</video_description>" >> $INT_XML_FILE
echo " <video_manufacturer>$VIDEO_MANUFACTURER</video_manufacturer>" >> $INT_XML_FILE
#echo " <video_current_horizontal_res>$VIDEO_HOR</video_current_horizontal_res>" >> $INT_XML_FILE
#echo " <video_current_vertical_res>$VIDEO_VERT</video_current_vertical_res>" >> $INT_XML_FILE
echo " </video_card>" >> $INT_XML_FILE
#done
echo " </video_cards>" >> $INT_XML_FILE



# Processor
INT_PROC=""
echo " <processors>" >> $INT_XML_FILE
INT_PROC=`$OA_CAT /proc/cpuinfo | $OA_GREP -G "^processor" | $OA_CUT -d: -f2 | $OA_CUT -c2-`
if [ "$INT_PROC" == "" ]
then
PROCESSOR_DESCRIPTION=`$OA_CAT /proc/cpuinfo | $OA_GREP -G "^cpu" | $OA_CUT -d: -f2 | $OA_CUT -c2-`
PROCESSOR_SPEED=`$OA_CAT /proc/cpuinfo | $OA_GREP -G "^Cpu0Bogo" | $OA_CUT -d: -f2 | $OA_CUT -c2-`
echo " <processor>" >> $INT_XML_FILE
echo " <processor_cores>$SYSTEM_PC_NUM_PROCESSOR</processor_cores>" >> $INT_XML_FILE
echo " <processor_description>$PROCESSOR_DESCRIPTION</processor_description>" >> $INT_XML_FILE
echo " <processor_speed>$PROCESSOR_SPEED</processor_speed>" >> $INT_XML_FILE
echo " <processor_manufacturer>Sun</processor_manufacturer>" >> $INT_XML_FILE
echo " </processor>" >> $INT_XML_FILE
else
PROCESSOR_DESCRIPTION=`$OA_CAT /proc/cpuinfo | $OA_GREP "model name" | $OA_CUT -d: -f2 | $OA_CUT -c2- | $OA_TR "\n" "^" | $OA_CUT -d^ -f1`
PROCESSOR_SPEED=`$OA_CAT /proc/cpuinfo | $OA_GREP "cpu MHz" | $OA_CUT -d: -f2 | $OA_CUT -c2- | $OA_CUT -d. -f1 | $OA_TR "\n" "^" | $OA_CUT -d^ -f1`
PROCESSOR_MANUFACTURER=`$OA_CAT /proc/cpuinfo | $OA_GREP "vendor_id" | $OA_CUT -d: -f2 | $OA_CUT -c2- | tr "\n" "^" | $OA_CUT -d^ -f1`
echo " <processor>" >> $INT_XML_FILE
echo " <processor_cores>$SYSTEM_PC_NUM_PROCESSOR</processor_cores>" >> $INT_XML_FILE
echo " <processor_description>$PROCESSOR_DESCRIPTION</processor_description>" >> $INT_XML_FILE
echo " <processor_speed>$PROCESSOR_SPEED</processor_speed>" >> $INT_XML_FILE
echo " <processor_manufacturer>$PROCESSOR_MANUFACTURER</processor_manufacturer>" >> $INT_XML_FILE
echo " </processor>" >> $INT_XML_FILE
fi
echo " </processors>" >> $INT_XML_FILE




#INT_PACKAGES=`dpkg --list | grep ii | awk '{print $2}'`
echo " <software>" >> $INT_XML_FILE
for i in $INT_PACKAGES; do
PACKAGE_NAME="$i"
PACKAGE_URL=""
INT_PACKAGE_TEST=`echo "$PACKAGE_NAME" | grep "^samba"` ; if [ "$PACKAGE_NAME" = "$INT_PACKAGE_TEST" ]; then PACKAGE_URL="http://www.samba.org"; fi;
INT_PACKAGE_TEST=`echo "$PACKAGE_NAME" | grep "^php"` ; if [ "$PACKAGE_NAME" = "$INT_PACKAGE_TEST" ]; then PACKAGE_URL="http://www.php.net"; fi;
INT_PACKAGE_TEST=`echo "$PACKAGE_NAME" | grep "^openoffice"` ; if [ "$PACKAGE_NAME" = "$INT_PACKAGE_TEST" ]; then PACKAGE_URL="http://www.openoffice.org"; fi;
INT_PACKAGE_TEST=`echo "$PACKAGE_NAME" | grep "^firefox"` ; if [ "$PACKAGE_NAME" = "$INT_PACKAGE_TEST" ]; then PACKAGE_URL="http://www.mozilla.com"; fi;
INT_PACKAGE_TEST=`echo "$PACKAGE_NAME" | grep "^mysql"` ; if [ "$PACKAGE_NAME" = "$INT_PACKAGE_TEST" ]; then PACKAGE_URL="http://www.mysql.com"; fi;
INT_PACKAGE_TEST=`echo "$PACKAGE_NAME" | grep "^vlc"` ; if [ "$PACKAGE_NAME" = "$INT_PACKAGE_TEST" ]; then PACKAGE_URL="http://www.videolan.com"; fi;
INT_PACKAGE_TEST=`echo "$PACKAGE_NAME" | grep "^python"` ; if [ "$PACKAGE_NAME" = "$INT_PACKAGE_TEST" ]; then PACKAGE_URL="http://www.python.com"; fi;
PACKAGE_VERSION=`$OA_DPKG -s $i 2>/dev/null | $OA_GREP "^Version" | $OA_CUT -d":" -f2 | $OA_SED "s/ //"`
PACKAGE_PUBLISHER=`$OA_DPKG -s $i 2>/dev/null | $OA_GREP "^Maintainer" | $OA_SED "s/^Maintainer: *//" | cut -d"<" -f1 | $OA_SED "s/ $//"`
PACKAGE_EMAIL=`$OA_DPKG -s $i 2>/dev/null | $OA_GREP "^Maintainer" | $OA_SED "s/^Maintainer: *//" | cut -d"<" -f2 | $OA_SED "s/<//" | $OA_SED "s/>//"`
PACKAGE_STATUS=`$OA_DPKG -s $i 2>/dev/null | $OA_GREP "^Status" | $OA_CUT -d":" -f2 | $OA_SED "s/ //"`
if [ "$PACKAGE_VERSION" = "" ] && [ "$PACKAGE_PUBLISHER"= ""]
then
PACKAGE_NAME=""
else
echo " <package>" >> $INT_XML_FILE
echo " <software_name>$PACKAGE_NAME</software_name>" >> $INT_XML_FILE
echo " <software_version>$PACKAGE_VERSION</software_version>" >> $INT_XML_FILE
echo " <software_location></software_location>" >> $INT_XML_FILE
echo " <software_uninstall></software_uninstall>" >> $INT_XML_FILE
echo " <software_install_date></software_install_date>" >> $INT_XML_FILE
echo " <software_publisher>$PACKAGE_PUBLISHER</software_publisher>" >> $INT_XML_FILE
echo " <software_install_source></software_install_source>" >> $INT_XML_FILE
echo " <software_system_component></software_system_component>" >> $INT_XML_FILE
echo " <software_url>$PACKAGE_URL</software_url>" >> $INT_XML_FILE
echo " <software_email>$PACKAGE_EMAIL</software_email>" >> $INT_XML_FILE
echo " <software_comment></software_comment>" >> $INT_XML_FILE
echo " <software_code_base></software_code_base>" >> $INT_XML_FILE
echo " <software_status>$PACKAGE_STATUS</software_status>" >> $INT_XML_FILE
echo " </package>" >> $INT_XML_FILE
fi
done
echo " </software>" >> $INT_XML_FILE


# close the XML
echo "</system>" >> $INT_XML_FILE[/code]

_________________
Support and Development hours available from [url=https://opmantek.com]Opmantek[/url].
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
PostPosted: Fri Nov 14, 2008 11:14 pm 
Offline
Moderator
User avatar

Joined: Tue Jan 25, 2005 3:09 am
Posts: 2140
Location: Scotland
The script looks good, the XML output, excellent idea, only trouble is, we have nothing to post the results to. (HINT :twisted: )
Is there any chance of a sneak preview of the OAv2 code, warts and all, so we can start hacking?

_________________
Andrew

[size=85]OA Server: Windows XP/ XAMPP, Mandriva/Apache, Ubuntu
Auditing: 300+ Wstns, 20+ Srvrs, Thin clients, Linux boxes, Routers, etc
OS's: Windows XP , W2K Srvr, W2K3 Srvr, W2K8, Vista, Windows 7, Linuxes (and a Mac at home)
LDAP: Active Directory[/size]


Top
 Profile  
Reply with quote  
PostPosted: Fri Nov 14, 2008 11:19 pm 
Offline
Moderator
User avatar

Joined: Tue Jan 25, 2005 3:09 am
Posts: 2140
Location: Scotland
I suppose the simplest way to modularise would be to take each of the existing functional code blocks, and make a function of it, then have a main function which calls the others, sounds simple I know, but these things rarely are.

_________________
Andrew

[size=85]OA Server: Windows XP/ XAMPP, Mandriva/Apache, Ubuntu
Auditing: 300+ Wstns, 20+ Srvrs, Thin clients, Linux boxes, Routers, etc
OS's: Windows XP , W2K Srvr, W2K3 Srvr, W2K8, Vista, Windows 7, Linuxes (and a Mac at home)
LDAP: Active Directory[/size]


Top
 Profile  
Reply with quote  
PostPosted: Tue Nov 18, 2008 2:19 am 
Offline
Helper

Joined: Fri Nov 16, 2007 1:32 am
Posts: 73
Location: Dallas,Texas
Are you keeping the database schema the same for OAv2?

If so you could just give us the code for xml to sql injection and we could start working on this beast ;)

_________________
1400 Servers Audited (1 hour interval) Applied via a local scheduler, deployed via GPO.
Running OA on IIS6 Web Server
90% Windows 2k3 Server (std,ent)
5% Windows XP
5% Windows 2000


Top
 Profile  
Reply with quote  
PostPosted: Tue Nov 18, 2008 9:38 am 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1964
Location: Brisbane, Australia
[quote]Are you keeping the database schema the same for OAv2?

No - major changes.
[quote]If so you could just give us the code for xml to sql injection and we could start working on this beast
I have some done - everything you see in the demo video is actual working code.
It's not quite that straight forward, though.
You have to know the relationships between the tables, and tie it in with Groups and Users permissions.
When I open a new topic (forum section maybe), I'll post diagrams showing the relationships, notes I have made, formats for things, etc, etc.
I plan on making more of an effort to document stuff.... :-)
I haven't had a lot of time lately, but I should have something up before Xmas (I'll regret saying that).

_________________
Support and Development hours available from [url=https://opmantek.com]Opmantek[/url].
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
PostPosted: Wed Nov 19, 2008 2:28 am 
Offline
Contributor

Joined: Fri Jul 28, 2006 6:30 am
Posts: 157
Location: London
I've made a start at re-coding a more modular audit.vbs based on the code that Mark posted and thought I'd post my first draft. All it does at the moment is extract the audit data from the system and currently makes no attempt to format it for submission to the db. Also I haven't tested the SQL audit function because I couldn't get the same functionality from Marks code working either (don't think I have the SQL COM library installed on my system).

It's currently using a Dictionary object to store the audit data, but this is just for convenience at this stage in development.

I see this process flow for getting audited data into the db:
(1) Raw Audit -> (2) Data presentation/conversion -> (3) SQL statement creation -> (4) DB insertion

Where
(1) runs the WMI and registry queries to actually obtain the raw data
(2) converts and formats the data to make it suitable for transmission/db insertion (e.g. Software keys are returned as binary from the registry but need to be in a string format in the db. Or the "domain role" as returned by WMI is an integer number which needs to be translated to a string description - "Standalone Server")
(3) Takes the formatted data and creates the required SQL statement strings
(4) SQL db insertion

It's given that (1) has to happen at the client and (4) has to be on the server, but (2) and (3) could possibly be client or server. Which is better?
Gotta run now. May post my thoughts on this later this evening ....

[code]' ***********************************
' Open Audit
' Software and Hardware Inventory
' (c) Open-Audit.org 2003-2008 '
' Licensed under the GPL '
' ***********************************
Option Explicit

' **** Options *******************************
Dim gsComputer : gsComputer = "."
Dim gbDebug : gbDebug = True
Dim gbDetectSql : gbDetectSql = False

' ***********************************
' Don't change the settings below here '
' ***********************************
Const HKEY_CLASSES_ROOT = &H80000000
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_LOCAL_MACHINE = &H80000002
Const HKEY_USERS = &H80000003

const REG_SZ = 1
const REG_EXPAND_SZ = 2
const REG_BINARY = 3
const REG_DWORD = 4
const REG_MULTI_SZ = 7

' **** Global Objects *******************************
Dim goWmiWmi ' WMI reference to \root\wmi
Dim goWmiCimv2 ' WMI reference to \root\cimv2
Dim goWmiReg ' WMI reg provider
Dim goWmiIE ' WMI IE provider

Dim goAuditData ' Audit info dictionary

' **** Script Entry Point *******************************
Main
Wscript.Quit
' **** Script Exit Point *******************************

' **** Main Logic *******************************
Sub Main()
On Error Resume Next

Dim start_time
Dim system_timestamp

CreateGlobalObjects
start_time = Timer
system_timestamp = FormatDateTime(Now())
Echo "Auditing ..."
GetRawAuditData
Echo "... audit complete."
PrintRawAuditData

End Sub

' **** Print Results *******************************
Sub PrintRawAuditData()
Dim sKey
Echo vbNewLine & "**************** Raw Audit Data *****************************"
For Each sKey In goAuditData.Keys
Echo sKey & vbTab & "=>" & vbTab & goAuditData.Item(sKey)
Next
End Sub


' **** Extract audit data from WMI *****************************
Sub GetRawAuditData()
On Error Resume Next

RunWmiQuery goWmiCimv2, "Win32_ComputerSystem","", Array("Name","Domain","TotalPhysicalMemory","NumberOfProcessors","Model","DomainRole","PartOfDomain"), False
RunWmiQuery goWmiCimv2, "Win32_ComputerSystemProduct","", Array("IdentifyingNumber","Vendor","UUID"), False

Echo "Windows Info"
RunWmiQuery goWmiCimv2, "Win32_OperatingSystem","", _
Array("Caption","Version","Description","InstallDate","BuildNumber","BootDevice","CountryCode","Organization","OSLanguage","RegisteredUser","ServicePackMajorVersion","Name"), False
RunWmiQuery goWmiCimv2, "Win32_SystemEnclosure","", Array("ChassisTypes"), False
If goAuditData.Item("Win32_ComputerSystem_PartOfDomain") = True Then RunWmiQuery goWmiCimv2, "Win32_NTDomain","DomainName='" & GetNetBIOSNameFromDNSName(goAuditData.Item("Win32_ComputerSystem_Domain"))& "'", _
Array("ClientSiteName","DomainControllerAddress","DomainControllerName"), False
RunWmiQuery goWmiCimv2, "Win32_TimeZone","", Array("Caption","DaylightName"), False

Echo "BIOS Info"
RunWmiQuery goWmiCimv2, "Win32_BIOS","", Array("Description","Manufacturer","SerialNumber","SMBIOSBIOSVersion","Version"), False

Echo "Disk Info"
RunWmiQuery goWmiCimv2, "Win32_DiskDrive","", Array("Caption","Index","InterfaceType","manufacturer","Model","size","deviceid","Partitions"), True

Echo "Partition Info"
RunWmiQuery goWmiCimv2, "Win32_LogicalDiskToPartition","", Array("Antecedent","Dependent"), True
RunWmiQuery goWmiCimv2, "Win32_LogicalDisk","", Array("DeviceID","Caption","FileSystem","FreeSpace","Size","VolumeName","__Path"), True
RunWmiQuery goWmiCimv2, "Win32_DiskPartition","", _
Array("Bootable","BootPartition","DeviceID","DiskIndex","Index","PrimaryPartition","__Path"), True

Echo "Network Info"
RunWmiQuery goWmiCimv2, "Win32_NetworkAdapterConfiguration","IPEnabled = 'True'", _
Array("Index","Description","DHCPEnabled","DHCPServer","DHCPLeaseObtained","DHCPLeaseExpires","DNSServerSearchOrder","DNSHostName","DNSDomain", _
"IPEnabled","DomainDNSRegistrationEnabled","WINSPrimaryServer","WINSEnableLMHostsLookup","WINSSecondaryServer","MACAddress","IPAddress","IPSubnet"), True
RunWmiQuery goWmiCimv2, "Win32_NetworkAdapter","", _
Array("Index","AdapterType","MACAddress","NetConnectionID","Manufacturer","ProductName","NetConnectionStatus"), True
RunWmiQuery goWmiWmi, "MSNdis_LinkSpeed","", Array("InstanceName","NdisLinkSpeed"), True

Echo "Processor Info"
RunWmiQuery goWmiCimv2, "Win32_Processor","", Array("Name","MaxClockSpeed","Manufacturer","PowerManagementSupported"), True

Echo "Optical Info"
RunWmiQuery goWmiCimv2, "Win32_CDROMDrive","", Array("Caption","DeviceID","Drive"), True

Echo "Video Cards"
RunWmiQuery goWmiCimv2, "Win32_VideoController","NOT (Caption='vnc' OR Caption='Innobec SideWindow' OR Caption='Microsoft SMS Mirror Driver' OR Caption='LogMeIn Mirror Driver')", _
Array("Name","AdapterRAM"), True

Echo "Sound Info"
RunWmiQuery goWmiCimv2, "Win32_SoundDevice","", Array("Name","Manufacturer","DeviceID"), True

Echo "BHO Info"
' MicrosoftIE_Object is not available on Win95, Win 98 or Vista - need to look for alternative solution
RunWmiQuery goWmiIE, "MicrosoftIE_Object","", Array("ProgramFile","CodeBase","Status"), True

Echo "Codec Info"
RunWmiQuery goWmiCimv2, "Win32_CodecFile","NOT Manufacturer='Microsoft Corporation'", _
Array("Group","Filename","Version","Caption","InstallDate","Manufacturer","Description"), True

Echo "MDAC Version"
GetRegValues goWmiReg, "SOFTWARE\Microsoft\DataAccess", Array("Version"), False

Echo "DirectX Version"
GetRegValues goWmiReg, "SOFTWARE\Microsoft\DirectX", Array("Version"), False

Echo "Windows Media Player Version"
GetRegValues goWmiReg, "SOFTWARE\Microsoft\MediaPlayer\PlayerUpgrade", Array("PlayerVersion"), False

Echo "Internet Explorer Version"
GetRegValues goWmiReg, "SOFTWARE\Microsoft\Internet Explorer", Array("Version"), False

Echo "Outlook Express Version" ' Not applicable to Vista
RunWmiQuery goWmiCimv2, "CIM_Datafile","Name = 'c:\\program files\\Outlook Express\\msimn.exe'", Array("Version"), True

Echo "Software info"
GetRegValues goWmiReg, "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall", _
Array("DisplayName","DisplayVersion","UninstallString","InstallDate","Publisher","InstallSource","InstallLocation","SystemComponent","URLInfoAbout","Comments"), True

Echo "Services Info"
RunWmiQuery goWmiCimv2, "Win32_Service","", Array("Description","DisplayName","Name","PathName","Started","StartMode","State"), True

Echo "Software Keys"
GetRegValues goWmiReg, "SOFTWARE\Microsoft\Windows NT\CurrentVersion", Array("DigitalProductId"), False
GetRegValues goWmiReg, "SOFTWARE\Microsoft\Office\11.0\Registration", Array("DigitalProductID"), True

If gbDetectSql Then GetRawSQLAuditData

End Sub


' **** Get domain NetBIOS name from domain DNS name *****************************
Function GetNetBIOSNameFromDNSName(sDnsName)

Dim domain_dn
Dim oTranslate
Dim hr
Dim domain_nb

domain_dn = "DC=" & Replace(sDnsName,".",",DC=")
Set oTranslate = CreateObject("NameTranslate")
hr = oTranslate.Init (3, "")
hr = oTranslate.Set (1, domain_dn)
domain_nb = oTranslate.Get(3)
GetNetBIOSNameFromDNSName = Left(domain_nb,Len(domain_nb)-1)

End Function

' **** Create & Initialise Global Objects *******************************
Sub CreateGlobalObjects()
On Error Resume Next

Set goWmiCimv2 = GetObject("winmgmts:\\" & gsComputer & "\root\cimv2")
Set goWmiWmi = GetObject("winmgmts:\\" & gsComputer & "\root\WMI")
Set goWmiIE = GetObject("winmgmts:\\" & gsComputer & "\root\cimv2\Applications\MicrosoftIE")
Set goWmiReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & gsComputer & "\root\default:StdRegProv")
Set goAuditData = CreateObject("Scripting.Dictionary")

End Sub

' **** Echo text *******************************************************************************
Sub Echo(sText)
If gbDebug Then wscript.echo sText
End Sub

' **** Format Date & Time *******************************************************************************
Function FormatDateTime(dt)
FormatDateTime = Year(dt) & "-" & Right("0" & Month(dt),2) & "-" & Right("0" & Day(dt),2) & " " & Right("0" & Hour(dt),2) & ":" & Right("0" & Minute(dt),2) & ":" & Right("0" & Second(dt),2)
End Function

' **** Generic WMI query sub *******************************************************************************
Sub RunWmiQuery(oWmiSvc, sWmiClass, sWhereClause, aProperties, bArrayExpected)

Dim sWmiQuery, colItems, iCount, oItem, sProperty, Value, sValue, sKey

' Construct WQL Query
If IsNull(bArrayExpected) Then bArrayExpected = False
If len(sWhereClause) > 0 Then sWhereClause = " WHERE " & sWhereClause Else sWhereClause = ""
sWmiQuery = "Select * from " & sWmiClass & sWhereClause

' Execute query and loop thru results
Set colItems = oWmiSvc.ExecQuery(sWmiQuery,,48)
iCount = 1
For Each oItem in colItems
' Get each property value from property array
For Each sProperty in aProperties
If sProperty = "__Path" Then
sValue = oItem.Path_.Path
Else
Value = oItem.Properties_(sProperty)
If IsArray(Value) Then sValue = Join(Value,",") Else sValue = Value
End If
' Write results to dictionary
If bArrayExpected Then sKey = sWmiClass & "_" & iCount & "_" & sProperty Else sKey = sWmiClass & "_" & sProperty
goAuditData.Add sKey, sValue
Next
iCount = iCount + 1
Next

End Sub


' **** Generic Registry query sub *******************************************************************************
Sub GetRegValues(oReg, sKeyPath, aValueNames, bEnumKeysFirst)

Dim aNames
Dim aTypes
Dim iCount
Dim oNamesAndTypes
Dim ValueName
Dim sValue
Dim aSubKeys
Dim sSubKey
Dim aValue

' Enumerate keys first - used to get values from all subkeys below sKeyPath
If bEnumKeysFirst Then
oReg.EnumKey HKEY_LOCAL_MACHINE, sKeyPath, aSubKeys
For Each sSubKey In aSubKeys : GetRegValues oReg, sKeyPath & "\" & sSubKey, aValueNames, False : Next
End If

' Enumerate registry value names & types under this key and store in dictionary
Set oNamesAndTypes = CreateObject("Scripting.Dictionary")
oReg.EnumValues HKEY_LOCAL_MACHINE, sKeyPath, aNames, aTypes
If Not IsArray(aNames) Then Exit Sub
For iCount = 0 To UBound(aNames)
oNamesAndTypes.Add aNames(iCount),aTypes(iCount)
Next

' Loop thru requested values, use appropriate method to retrieve data and convert to readable format
For Each ValueName In aValueNames
Select Case oNamesAndTypes.Item(ValueName)
Case REG_SZ
oReg.GetStringValue HKEY_LOCAL_MACHINE, sKeyPath, ValueName, sValue
goAuditData.Add "HKEY_LOCAL_MACHINE\" & sKeyPath & "_" & ValueName, sValue
Case REG_EXPAND_SZ
'oReg.GetExpandedStringValue
Case REG_BINARY
oReg.GetBinaryValue HKEY_LOCAL_MACHINE, sKeyPath, ValueName, aValue
goAuditData.Add "HKEY_LOCAL_MACHINE\" & sKeyPath & "_" & ValueName, ConvertByteArrayToString(aValue)
Case REG_DWORD
'oReg.GetDWORDValue
Case REG_MULTI_SZ
'oReg.GetMultiStringValue
End Select
Next

End Sub

' **** Get Raw SQL Audit Data *****************************
Sub GetRawSQLAuditData()
On Error Resume Next

Const SQLDMOSecurity_Integrated = 1
Const SQLDMOSecurity_Mixed = 2
Const SQLDMOSecurity_Normal = 0
Const SQLDMOSecurity_Unknown = 9

Dim oSQLServer
Dim oConnection
Dim sConnection
Dim sSQLQuery
Dim oRS
Dim iCount
Dim sDbFileName
Dim oFiles
Dim oFile
Dim iFileSize

Echo "SQL Info"

Set objSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.LoginSecure = True
oSQLServer.Connect gsComputer

' Get SQL server info
iLoginMode = oSQLServer.ServerLoginMode(gsComputer)
goAuditData.Add "SQL_LoginMode", CStr(iLoginMode)
goAuditData.Add "SQL_VersionMajor", oSQLServer.VersionMajor
goAuditData.Add "SQL_VersionMinor", oSQLServer.VersionMinor

' Get database info
Set oConnection = CreateObject("ADODB.Connection")
sConnection = "Driver={SQL Server};Server=" & gsComputer & ";Database=MASTER;Trusted_Connection=TRUE"
oConnection.Open sConnection
sSQLQuery = "SELECT * FROM sys.sysdatabases ORDER BY dbid"
Set oRS = oConnection.Execute(sSQLQuery)
iCount = 1
Do Until oRS.EOF

' Get size of DB files
sDbFileName = replace(oRS.Fields("filename"), "\", "\\")
Set oFiles = goWmiCimv2.ExecQuery ("Select * from CIM_Datafile Where name = '" & sDbFileName & "'")
For Each oFile in oFiles
iFileSize = oFile.FileSize / 1024
Next

For Each sField In Array("name","dbid","crdate","","filename")
goAuditData.Add "SQL_Database_" & CStr(iCount) & "_" & sField, oRS.Fields(sField)
Next
goAuditData.Add "SQL_Database_" & CStr(iCount) & "_filesize", iFileSize
iCount = iCount +1
oRS.MoveNext
Loop

' Get login info
sSQLQuery = "SELECT d.dbid, d.name, p.login_time, p.hostname, p.program_name, p.nt_domain, p.nt_username, p.net_library, p.loginame "
sSQLQuery = sSQLQuery & "FROM sys.sysprocesses p, sys.sysdatabases d "
sSQLQuery = sSQLQuery & "WHERE p.dbid = d.dbid AND p.login_time = (SELECT max(sys.sysprocesses.login_time) FROM sys.sysprocesses WHERE [dbid] = d.[dbid])"
Set oRS = oConnection.Execute(strSQLQuery)

iCount = 1
Do Until oRS.EOF
For Each sField In Array("name","dbid","login_time","loginame","hostname","program_name","nt_domain","nt_username","net_library")
goAuditData.Add "SQL_Database_" & CStr(iCount) & "_" & sField, oRS.Fields(sField)
Next
iCount = iCount +1
oRS.MoveNext
Loop

oRS.Close
oConnection.Close

End Sub

' **** Convert Byte Array To String *******************************************************************************
Function ConvertByteArrayToString(aBytes)

Dim Bytes
Dim sString : sString = ""

ConvertByteArrayToString = ""
For Each Bytes In aBytes
sString = sString & Right("00" & Hex(Bytes), 2) & ","
Next
ConvertByteArrayToString = left(sString, Len(sString) -1)

End Function
[/code]

_________________
Cheers, Nick.

[size=85]OA Server: Windows Server 2003 / Apache 2
Auditing: 1600 Workstations, 200 Servers
OS's: Windows XP / Windows 2000 / Windows 2003 Server / Windows Vista
LDAP: Active Directory[/size]


Top
 Profile  
Reply with quote  
PostPosted: Wed Nov 19, 2008 9:46 am 
Offline
Contributor

Joined: Fri Jul 28, 2006 6:30 am
Posts: 157
Location: London
... To follow up my previous post:

In the current deisgn steps (1) & (2) are done by audit.vbs on the client, and (3) and (4) by admin_pc_add_1.php on the server (as far has I can tell - I haven't studied the code in depth yet). But there are two other possibilities:

Step (1) on the client, (2),(3) & (4) on the server
In this model audit.vbs would not be functionally much different from the code I have posted earlier. The main difference being that the results would probably be stored in XML rather than the Dictionary object that I'm currently using (I'm working on the XML output next). The XML file would represent the actual audit results with barely any data manipulation. Something like:
[code]
<Audit_Info>
<Win32_OperatingSystem>
<Caption>Microsoft© Windows VistaT Home Premium</Caption>
<Version>6.0.6001</Version>
<Description>NPB</Description>
<InstallDate>20071024023030.000000+060</InstallDate>
<BuildNumber>6001</BuildNumber>
<BootDevice>\Device\HarddiskVolume1</BootDevice>
<CountryCode>44</CountryCode>
<Organization>Hewlett-Packard
<OSLanguage>1033</OSLanguage>
<RegisteredUser>Nick</RegisteredUser>
<ServicePackMajorVersion>1</ServicePackMajorVersion>
<Name>Microsoft© Windows VistaT Home Premium |C:\Windows|\Device\Harddisk0\Partition1</Name>
</Win32_OperatingSystem>
<Win32_SystemEnclosure>
<ChassisTypes>10</ChassisTypes>
</Win32_SystemEnclosure>
<Win32_TimeZone>
<Caption>(GMT) Greenwich Mean Time : Dublin, Edinburgh, Lisbon, London</Caption>
<DaylightName>GMT Daylight Time</DaylightName>
</Win32_TimeZone>
<Win32_BIOS>
<Description>Default System BIOS</Description>
<Manufacturer>Hewlett-Packard</Manufacturer>
<SerialNumber>CND74312W5</SerialNumber>
<SMBIOSBIOSVersion>F.33</SMBIOSBIOSVersion>
<Version>HPQOEM - 1</Version>
</Win32_BIOS>
</Audit_Info>
[/code]

Steps (1),(2),(3) on the client and (4) on the server
In this model audit.vbs would need to be coded to perform all data manipulation to the extent that the generated XML would define the actual SQL data that needs to be inserted (Table/Fields/Values). Something like:
[code]
<Audit_Info>
<Table name=sys_hw_bios>
<system_id>1234567890</system_id>
<bios_description>Default System BIOS</bios_description>
<bios_manufacturer>Hewlett-Packard</bios_manufacturer>
<bios_serial_number>CND74312W5</bios_serial_number>
<bios_sm_bios_version>F.33</bios_sm_bios_version>
<bios_version>HPQOEM - 1</bios_version>
<bios_asset_tag>ABC123</bios_asset_tag>
<bios_timestamp>2008-00-00 00:00:00</bios_timestamp>
<bios_first_timestamp>2008-00-00 00:00:00</bios_first_timestamp>
</Table>
</Audit_Info>
[/code]
The server code for processing this XML could be really simple.

My preference is for the first option as I'd like to see as little code executed on the audited system as possible and would prefer to do the bulk of the data manipulation in PHP.

Opinions?

_________________
Cheers, Nick.

[size=85]OA Server: Windows Server 2003 / Apache 2
Auditing: 1600 Workstations, 200 Servers
OS's: Windows XP / Windows 2000 / Windows 2003 Server / Windows Vista
LDAP: Active Directory[/size]


Top
 Profile  
Reply with quote  
PostPosted: Wed Nov 19, 2008 5:59 pm 
Offline
Newbie

Joined: Wed Aug 29, 2007 5:24 pm
Posts: 6
Wow, I didn't thought my initial request let imagination run wild...

That's great :D, keep on working.

_________________
Claudio Nicora
http://coolsoft.altervista.org


Top
 Profile  
Reply with quote  
PostPosted: Fri Nov 21, 2008 9:42 pm 
Offline
Contributor

Joined: Fri Jul 28, 2006 6:30 am
Posts: 157
Location: London
This version outputs as XML (to C:\audit.xml). The SQL audit now works and I've added some Group Policy auditing code of my own.

[code]' ***********************************
' Open Audit
' Software and Hardware Inventory
' (c) Open-Audit.org 2003-2008 '
' Licensed under the GPL '
' ***********************************
Option Explicit

' **** Options *******************************
Dim gsComputer : gsComputer = "."
Dim gbDebug : gbDebug = True
Dim gbDetectSql : gbDetectSql = True

' ***********************************
' Don't change the settings below here '
' ***********************************
Const HKEY_CLASSES_ROOT = &H80000000
Const HKEY_CURRENT_USER = &H80000001
Const HKEY_LOCAL_MACHINE = &H80000002
Const HKEY_USERS = &H80000003

const REG_SZ = 1
const REG_EXPAND_SZ = 2
const REG_BINARY = 3
const REG_DWORD = 4
const REG_MULTI_SZ = 7

' **** Global Objects *******************************
Dim goWmiWmi ' WMI reference to \root\wmi
Dim goWmiCimv2 ' WMI reference to \root\cimv2
Dim goWmiReg ' WMI reg provider
Dim goWmiIE ' WMI IE provider
Dim goXmlDoc ' XML Doc for results

' **** Script Entry Point *******************************
Main
' **** Script Exit Point *******************************

' **** Main Logic *******************************
Sub Main()
On Error Resume Next

Dim start_time
Dim system_timestamp
Dim wshNetwork
Dim windows_user_name

CreateGlobalObjects
start_time = Timer
system_timestamp = FormatDateTime(Now())
goXmlDoc.loadXML("<?xml version=""1.0"" encoding=""UTF-8""?><Audit_Data/>")
goXmlDoc.setProperty "SelectionLanguage", "XPath"

Echo "Auditing ..."
GetRawAuditData
Echo "... audit complete."
goXmlDoc.Save "C:\audit.xml"

End Sub

' **** Extract audit data from WMI *****************************
Sub GetRawAuditData()
On Error Resume Next

Dim oRootNode

Set oRootNode = goXmlDoc.documentElement

Echo "System Info"
RunWmiQuery goWmiCimv2, "Win32_ComputerSystem","", Array("Name","Domain","TotalPhysicalMemory","NumberOfProcessors","Model","DomainRole","PartOfDomain")
RunWmiQuery goWmiCimv2, "Win32_ComputerSystemProduct","", Array("IdentifyingNumber","Vendor","UUID")

Echo "Windows Info"
RunWmiQuery goWmiCimv2, "Win32_OperatingSystem","", _
Array("Caption","Version","Description","InstallDate","BuildNumber","BootDevice","CountryCode","Organization","OSLanguage","RegisteredUser","ServicePackMajorVersion","Name")
RunWmiQuery goWmiCimv2, "Win32_SystemEnclosure","", Array("ChassisTypes")
If cbool(GetXmlPropertyValue("*/Win32_ComputerSystem/PartOfDomain")) Then RunWmiQuery goWmiCimv2, "Win32_NTDomain","DomainName='" & GetNetBIOSNameFromDNSName(GetXmlPropertyValue("*/Win32_ComputerSystem/Domain")) & "'", _
Array("ClientSiteName","DomainControllerAddress","DomainControllerName")
RunWmiQuery goWmiCimv2, "Win32_TimeZone","", Array("Caption","DaylightName")

Echo "BIOS Info"
RunWmiQuery goWmiCimv2, "Win32_BIOS","", Array("Description","Manufacturer","SerialNumber","SMBIOSBIOSVersion","Version")

Echo "Disk Info"
RunWmiQuery goWmiCimv2, "Win32_DiskDrive","", Array("Caption","Index","InterfaceType","manufacturer","Model","size","deviceid","Partitions")

Echo "Partition Info"
RunWmiQuery goWmiCimv2, "Win32_LogicalDiskToPartition","", Array("Antecedent","Dependent")
RunWmiQuery goWmiCimv2, "Win32_LogicalDisk","", Array("DeviceID","Caption","FileSystem","FreeSpace","Size","VolumeName","__Path")
RunWmiQuery goWmiCimv2, "Win32_DiskPartition","", _
Array("Bootable","BootPartition","DeviceID","DiskIndex","Index","PrimaryPartition","__Path")

Echo "Network Info"
RunWmiQuery goWmiCimv2, "Win32_NetworkAdapterConfiguration","IPEnabled = 'True'", _
Array("Index","Description","DHCPEnabled","DHCPServer","DHCPLeaseObtained","DHCPLeaseExpires","DNSServerSearchOrder","DNSHostName","DNSDomain", _
"IPEnabled","DomainDNSRegistrationEnabled","WINSPrimaryServer","WINSEnableLMHostsLookup","WINSSecondaryServer","MACAddress","IPAddress","IPSubnet")
RunWmiQuery goWmiCimv2, "Win32_NetworkAdapter","", _
Array("Index","AdapterType","MACAddress","NetConnectionID","Manufacturer","ProductName","NetConnectionStatus")
RunWmiQuery goWmiWmi, "MSNdis_LinkSpeed","", Array("InstanceName","NdisLinkSpeed")

Echo "Processor Info"
RunWmiQuery goWmiCimv2, "Win32_Processor","", Array("Name","MaxClockSpeed","Manufacturer","PowerManagementSupported")

Echo "Optical Info"
RunWmiQuery goWmiCimv2, "Win32_CDROMDrive","", Array("Caption","DeviceID","Drive")

Echo "Video Cards"
RunWmiQuery goWmiCimv2, "Win32_VideoController","NOT (Caption='vnc' OR Caption='Innobec SideWindow' OR Caption='Microsoft SMS Mirror Driver' OR Caption='LogMeIn Mirror Driver')", _
Array("Name","AdapterRAM")

Echo "Sound Info"
RunWmiQuery goWmiCimv2, "Win32_SoundDevice","", Array("Name","Manufacturer","DeviceID")

Echo "BHO Info"
' MicrosoftIE_Object is not available on Win95, Win 98 or Vista - need to look for alternative solution
RunWmiQuery goWmiIE, "MicrosoftIE_Object","", Array("ProgramFile","CodeBase","Status")

Echo "Codec Info"
RunWmiQuery goWmiCimv2, "Win32_CodecFile","NOT Manufacturer='Microsoft Corporation'", _
Array("Group","Filename","Version","Caption","InstallDate","Manufacturer","Description")

Echo "MDAC Version"
GetRegValues goWmiReg,"SOFTWARE\Microsoft\DataAccess", Array("Version"), False, "MDAC", oRootNode

Echo "DirectX Version"
GetRegValues goWmiReg, "SOFTWARE\Microsoft\DirectX", Array("Version"), False, "DirectX", oRootNode

Echo "Windows Media Player Version"
GetRegValues goWmiReg,"SOFTWARE\Microsoft\MediaPlayer\PlayerUpgrade", Array("PlayerVersion"), False, "Media_Player", oRootNode

Echo "Internet Explorer Version"
GetRegValues goWmiReg, "SOFTWARE\Microsoft\Internet Explorer", Array("Version"), False, "Internet_Explorer", oRootNode

Echo "Outlook Express Version" ' Not applicable to Vista
RunWmiQuery goWmiCimv2, "CIM_Datafile","Name = 'c:\\program files\\Outlook Express\\msimn.exe'", Array("Name","Version")

Echo "Software info"
GetRegValues goWmiReg, "SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall", _
Array("DisplayName","DisplayVersion","UninstallString","InstallDate","Publisher","InstallSource","InstallLocation","SystemComponent","URLInfoAbout","Comments"), True, "Software", oRootNode

Echo "Services Info"
RunWmiQuery goWmiCimv2, "Win32_Service","", Array("Description","DisplayName","Name","PathName","Started","StartMode","State")

Echo "Software Keys"
GetRegValues goWmiReg, "SOFTWARE\Microsoft\Windows NT\CurrentVersion", Array("DigitalProductId"), False, "OS_Keys", oRootNode
GetRegValues goWmiReg, "SOFTWARE\Microsoft\Office\11.0\Registration", Array("DigitalProductID"), True, "Software_Keys", oRootNode

GetRawGpoAuditData
If gbDetectSql Then GetRawSQLAuditData

End Sub

' **** Get value from the XML node defined by sXmlPath *****************************
Function GetXmlPropertyValue(sXmlPath)

Dim sValue

sValue = goXmlDoc.selectSingleNode(sXmlPath).Text
GetXmlPropertyValue = sValue

End Function

' **** Create new property node and set value *****************************
Sub SetXmlPropertyValue(oParentNode, sProperty, sValue)

Dim oPropertyNode

Set oPropertyNode = oParentNode.appendChild(goXmlDoc.createElement(sProperty))
oPropertyNode.appendChild(goXmlDoc.createTextNode(sValue))

End Sub


' **** Get domain NetBIOS name from domain DNS name *****************************
Function GetNetBIOSNameFromDNSName(sDnsName)

Dim domain_dn
Dim oTranslate
Dim hr
Dim domain_nb

domain_dn = "DC=" & Replace(sDnsName,".",",DC=")
Set oTranslate = CreateObject("NameTranslate")
hr = oTranslate.Init (3, "")
hr = oTranslate.Set (1, domain_dn)
domain_nb = oTranslate.Get(3)
GetNetBIOSNameFromDNSName = Left(domain_nb,Len(domain_nb)-1)

End Function

' **** Create & Initialise Global Objects *******************************
Sub CreateGlobalObjects()
On Error Resume Next

Set goWmiCimv2 = GetObject("winmgmts:\\" & gsComputer & "\root\cimv2")
Set goWmiWmi = GetObject("winmgmts:\\" & gsComputer & "\root\WMI")
Set goWmiIE = GetObject("winmgmts:\\" & gsComputer & "\root\cimv2\Applications\MicrosoftIE")
Set goWmiReg = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & gsComputer & "\root\default:StdRegProv")
Set goXmlDoc = CreateObject("Microsoft.XMLDOM")

End Sub

' **** Echo text *******************************************************************************
Sub Echo(sText)
If gbDebug Then wscript.echo sText
End Sub

' **** Format Date & Time *******************************************************************************
Function FormatDateTime(dt)
FormatDateTime = Year(dt) & "-" & Right("0" & Month(dt),2) & "-" & Right("0" & Day(dt),2) & " " & Right("0" & Hour(dt),2) & ":" & Right("0" & Minute(dt),2) & ":" & Right("0" & Second(dt),2)
End Function

' **** Generic WMI query sub *******************************************************************************
Sub RunWmiQuery(oWmiSvc, sWmiClass, sWhereClause, aProperties)
On Error Resume Next

Dim sWmiQuery, colItems, oItem, sProperty, Value, sValue, sKey, oClassNode

' Add class node to XML doc
Set oClassNode = goXmlDoc.documentElement.appendChild(goXmlDoc.createElement(sWmiClass))

' Construct WQL Query
If len(sWhereClause) > 0 Then sWhereClause = " WHERE " & sWhereClause Else sWhereClause = ""
sWmiQuery = "Select * from " & sWmiClass & sWhereClause

' Execute query and loop thru results
Set colItems = oWmiSvc.ExecQuery(sWmiQuery,,48)
For Each oItem in colItems
' Get each property value from property array
For Each sProperty in aProperties
sValue=""
If sProperty = "__Path" Then
sValue = oItem.Path_.Path
Else
Value = oItem.Properties_(sProperty)
If IsArray(Value) Then sValue = Join(Value,",") Else sValue = Value
End If
SetXmlPropertyValue oClassNode, sProperty, sValue
Next
Next

End Sub


' **** Generic Registry query sub *******************************************************************************
Sub GetRegValues(oReg, sKeyPath, aValueNames, bEnumKeysFirst, sXmlClass, oParentXmlNode)
On Error Resume Next

Dim aNames
Dim aTypes
Dim oNamesAndTypes
Dim ValueName
Dim sValue
Dim aSubKeys
Dim sSubKey
Dim aValue
Dim oClassNode

' Add class node to XML doc
Set oClassNode = oParentXmlNode.appendChild(goXmlDoc.createElement(sXmlClass))

' bEnumKeysFirst - Use this option to get values from all *subkeys* of sKeyPath, not from sKeyPath itself
If bEnumKeysFirst Then
oReg.EnumKey HKEY_LOCAL_MACHINE, sKeyPath, aSubKeys
For Each sSubKey In aSubKeys
GetRegValues oReg, sKeyPath & "\" & sSubKey, aValueNames, False, "SubKey", oClassNode
Next
End If

' Enumerate registry value names & types under this key and store in dictionary
Set oNamesAndTypes = CreateObject("Scripting.Dictionary")
oReg.EnumValues HKEY_LOCAL_MACHINE, sKeyPath, aNames, aTypes
If Not IsArray(aNames) Then Exit Sub
For iCount = 0 To UBound(aNames)
oNamesAndTypes.Add aNames(iCount),aTypes(iCount)
Next

' Loop thru requested values, use appropriate method to retrieve data and convert to readable format
For Each ValueName In aValueNames
sValue=""
Select Case oNamesAndTypes.Item(ValueName)
Case REG_SZ
oReg.GetStringValue HKEY_LOCAL_MACHINE, sKeyPath, ValueName, sValue
Case REG_EXPAND_SZ
'oReg.GetExpandedStringValue
Case REG_BINARY
oReg.GetBinaryValue HKEY_LOCAL_MACHINE, sKeyPath, ValueName, aValue
sValue = ConvertByteArrayToString(aValue)
Case REG_DWORD
'oReg.GetDWORDValue
Case REG_MULTI_SZ
'oReg.GetMultiStringValue
End Select
SetXmlPropertyValue oClassNode, ValueName, sValue
Next

End Sub

' **** Get Raw SQL Audit Data *****************************
Sub GetRawSQLAuditData()

Const SQLDMOSecurity_Integrated = 1
Const SQLDMOSecurity_Mixed = 2
Const SQLDMOSecurity_Normal = 0
Const SQLDMOSecurity_Unknown = 9

Dim oSQLServer
Dim oConnection
Dim sConnection
Dim sSQLQuery
Dim oRS
Dim sDbFileName
Dim oFiles
Dim oFile
Dim iFileSize
Dim oClassNode
Dim oNode
Dim sFieldName

Echo "SQL Info"
Set oClassNode = goXmlDoc.documentElement.appendChild(goXmlDoc.createElement("SQL_Server"))

' How to distribute and how to install SQL-DMO for SQL Server 2000: http://support.microsoft.com/kb/326613
' SQL-DMO Reference: http://msdn.microsoft.com/en-us/library/ms133993.aspx
Set oSQLServer = CreateObject("SQLDMO.SQLServer")
oSQLServer.LoginSecure = True
oSQLServer.Connect gsComputer

' Get SQL server info
SetXmlPropertyValue oClassNode, "LoginMode", oSQLServer.ServerLoginMode(gsComputer)
SetXmlPropertyValue oClassNode, "VersionMajor", oSQLServer.VersionMajor
SetXmlPropertyValue oClassNode, "VersionMinor", oSQLServer.VersionMinor

' *** Get database info ****
Set oConnection = CreateObject("ADODB.Connection")
sConnection = "Driver={SQL Server};Server=" & gsComputer & ";Database=MASTER;Trusted_Connection=TRUE"
oConnection.Open sConnection
sSQLQuery = "SELECT * FROM sysdatabases ORDER BY dbid"
Set oRS = oConnection.Execute(sSQLQuery)

' Loop thru returned dbs
Do Until oRS.EOF

' Create new XML node for each db
Set oNode = oClassNode.appendChild(goXmlDoc.createElement("Database"))

' Get size of DB files
sDbFileName = replace(oRS.Fields("filename"), "\", "\\")
Set oFiles = goWmiCimv2.ExecQuery ("Select * from CIM_Datafile Where name = '" & sDbFileName & "'")
For Each oFile in oFiles
iFileSize = oFile.FileSize / 1024
Next

' Write db properties to XML
For Each sFieldName In Array("name","dbid","crdate","filename")
SetXmlPropertyValue oNode, sFieldName, oRS.Fields(sFieldName)
Next
SetXmlPropertyValue oNode, "filesize", iFileSize
oRS.MoveNext
Loop

' *** Get login info ****
sSQLQuery = "SELECT d.dbid, d.name, p.login_time, p.hostname, p.program_name, p.nt_domain, p.nt_username, p.net_library, p.loginame "
sSQLQuery = sSQLQuery & "FROM sysprocesses p, sysdatabases d "
sSQLQuery = sSQLQuery & "WHERE p.dbid = d.dbid AND p.login_time = (SELECT max(sysprocesses.login_time) FROM sysprocesses WHERE [dbid] = d.[dbid])"
Set oRS = oConnection.Execute(sSQLQuery)

' Loop thru returned logins
Do Until oRS.EOF
' Create new XML node for each login
Set oNode = oClassNode.appendChild(goXmlDoc.createElement("Login"))
For Each sFieldName In Array("name","dbid","login_time","loginame","hostname","program_name","nt_domain","nt_username","net_library")
SetXmlPropertyValue oNode, sFieldName, oRS.Fields(sFieldName)
Next
oRS.MoveNext
Loop

oRS.Close
oConnection.Close

End Sub

' **** Convert Byte Array To String *******************************************************************************
Function ConvertByteArrayToString(aBytes)

Dim Bytes
Dim sString : sString = ""

ConvertByteArrayToString = ""
For Each Bytes In aBytes
sString = sString & Right("00" & Hex(Bytes), 2) & ","
Next
ConvertByteArrayToString = left(sString, Len(sString) -1)

End Function


' **** Get Raw GPO Audit Data *****************************
Sub GetRawGpoAuditData()
On Error Resume Next

' Used by WMI RsopLoggingModeProvider Class
Const FLAG_NO_USER = 1
Const FLAG_NO_COMPUTER = 2
Const FLAG_FORCE_CREATENAMESPACE = 4

Const RSOP_USER_ACCESS_DENIED = 1
Const RSOP_COMPUTER_ACCESS_DENIED = 2
Const RSOP_TEMPNAMESPACE_EXISTS = 4

Dim oRsopProvider ' WMI RSOP provider object
Dim oRsopLogProvider ' WMI RSOP Logging provider object
Dim sSnapshotNamespace ' Temp RSOP snapshot namespace returned from RsopCreateSession
Dim lResult ' COM HRESULT returned from RsopCreateSession & RsopDeleteSession
Dim lInfo ' Extended info returned from RsopCreateSession
Dim oItems
Dim oItem
Dim oClassNode, oComputersNode
Dim oNode
Dim sProperty
Dim oRsopComputer

Echo "GPO Info"
Set oClassNode = goXmlDoc.documentElement.appendChild(goXmlDoc.createElement("RSOP_GPO"))

' Connect to WMI and create snapshot of RSOP data using RsopLoggingModeProvider Class
Set oRsopProvider = GetObject("winmgmts:\\" & gsComputer & "\root\rsop")
Set oRsopLogProvider = oRsopProvider.Get("RsopLoggingModeProvider")
oRsopLogProvider.RsopCreateSession FLAG_FORCE_CREATENAMESPACE, null, sSnapshotNamespace, lResult, lInfo
sSnapshotNamespace = Replace (sSnapshotNamespace,"\\.\","\\" & gsComputer & "\")

' Get Computer policies
Set oComputersNode = oClassNode.appendChild(goXmlDoc.createElement("Computer"))
Set oRsopComputer = GetObject("winmgmts:" & sSnapshotNamespace & "\Computer")
' Now query the WMI RSOP snapshot
Set oItems = oRsopComputer.ExecQuery("Select * from RSOP_GPO")
For Each oItem in oItems
' Create new XML node for each policy
Set oNode = oComputersNode.appendChild(goXmlDoc.createElement("Policy"))
For Each sProperty In Array("name","enabled","version","guidName")
SetXmlPropertyValue oNode, sProperty, oItem.Properties_(sProperty)
Next
Next

' Delete snapshot WMI namespace
oRsopLogProvider.RsopDeleteSession sSnapshotNamespace, lResult

End Sub
[/code]

_________________
Cheers, Nick.

[size=85]OA Server: Windows Server 2003 / Apache 2
Auditing: 1600 Workstations, 200 Servers
OS's: Windows XP / Windows 2000 / Windows 2003 Server / Windows Vista
LDAP: Active Directory[/size]


Top
 Profile  
Reply with quote  
PostPosted: Sat Nov 22, 2008 3:27 am 
Offline
Moderator
User avatar

Joined: Tue Jan 25, 2005 3:09 am
Posts: 2140
Location: Scotland
Looks good Nick, (had a busy week myself, so not been able to contribute).

As was discussed some time ago, and as you also stated, processing XML should indeed be "fairly simple", especially if we keep the same field names in the XML as we use in the DB.

We could choose more human readable XML fields, but this would require a look up table stage on the PHP end to convert to the db field names.

It might also make sense to make the input stage modular.

In other words, we use a form, and an input variable on the web host, rather than one huge great input form, something like...

[code]
http://openaudit/admin_pc_add_xml.php?uuid=xxx-xxx-xxx-xxx&section=hardware

and

http://openaudit/admin_pc_add_xml.php?uuid=xxx-xxx-xxx-xxx&section=software
[/code]

The script then posts each section of audit as it goes along, to the web form, using the section name and the UUID as the key.

This would be done to allow posting smaller chunks of XML, and this in turn would allow partial results to be posted, and also allow the input form to be more easily modularised.

You don't actually need this stage, but it does allow for better debugging, as should posting to a particular result type fail for some reason, you can continue to post the rest of the results without bombing the entire audit. You could also log from the input form to the logfile, instead of or as well as logging from the script to a local logfile.

What do you think?

_________________
Andrew

[size=85]OA Server: Windows XP/ XAMPP, Mandriva/Apache, Ubuntu
Auditing: 300+ Wstns, 20+ Srvrs, Thin clients, Linux boxes, Routers, etc
OS's: Windows XP , W2K Srvr, W2K3 Srvr, W2K8, Vista, Windows 7, Linuxes (and a Mac at home)
LDAP: Active Directory[/size]


Top
 Profile  
Reply with quote  
PostPosted: Sat Nov 22, 2008 3:47 am 
Offline
Contributor

Joined: Fri Jul 28, 2006 6:30 am
Posts: 157
Location: London
[quote="NickBrown"]This version outputs as XML (to C:\audit.xml). The SQL audit now works and I've added some Group Policy auditing code of my own.


There's a bug in the posted code. You need to add:[code]Dim iCount
[/code] at line 258.

_________________
Cheers, Nick.

[size=85]OA Server: Windows Server 2003 / Apache 2
Auditing: 1600 Workstations, 200 Servers
OS's: Windows XP / Windows 2000 / Windows 2003 Server / Windows Vista
LDAP: Active Directory[/size]


Top
 Profile  
Reply with quote  
PostPosted: Sat Nov 22, 2008 4:06 am 
Offline
Moderator
User avatar

Joined: Tue Jan 25, 2005 3:09 am
Posts: 2140
Location: Scotland
Looks good. :D

_________________
Andrew

[size=85]OA Server: Windows XP/ XAMPP, Mandriva/Apache, Ubuntu
Auditing: 300+ Wstns, 20+ Srvrs, Thin clients, Linux boxes, Routers, etc
OS's: Windows XP , W2K Srvr, W2K3 Srvr, W2K8, Vista, Windows 7, Linuxes (and a Mac at home)
LDAP: Active Directory[/size]


Top
 Profile  
Reply with quote  
PostPosted: Sat Nov 22, 2008 11:44 am 
Offline
Site Admin
User avatar

Joined: Mon Jun 07, 2004 11:48 am
Posts: 1964
Location: Brisbane, Australia
Some thoughts...
[quote]As was discussed some time ago, and as you also stated, processing XML should indeed be "fairly simple", especially if we keep the same field names in the XML as we use in the DB.

Only problem with that would be that you're "trusting" the user input (script output) to have the correct field names. I (personally) think that's way to much trust to place on the user input. I am thinking more in terms of web submitted audits, etc. I am trying to trust as little as possible of the input data. Yes, I want the field names to match, but I don't think we should actually rely and automate (hence trust) this.

[quote]In other words, we use a form, and an input variable on the web host, rather than one huge great input form, something like...
http://openaudit/admin_pc_add_xml.php?u ... n=hardware
A couple of points... Each system in the DB has a unique id. This is the auto-incrementing key on the table. The way I am uniquely IDing the system(s) is to use a concatenated UUID+MAC+SystemName and it must be flagged "production" (another column). So, you'd need to include UUID, MAC and System Name in the URL. You would also need a global timestamp to be the same for submission with each section. Bearing all that in mind, I think a real form with fields for each of these, and another test box for the submitted XML would be the way to go.

I have been thinking of making each section of the audit script a function. Inside this function, use an "on error" to catch and format any errors. These would then be returned inside the function results. Each section processing function (in PHP on the server) would just need to also check for there being <error> inside the input. Not too hard (but I haven't done it yet).

I have also started a diagram to model the process of creating an audit script from the web interface. It will ask various questions, and build the script for you. Even set it to run as a cron job (or scheduled task). Assuming your PCs allow remote audit (as per the current Open-AudIT) you sould be able to run the whole shebang from the web interface. More info on this will be forth-coming.

If anyone wants to use the DB schema and convert parts of the original Open-AudIT script into sections for the new script, please go right ahead, and post the code back here. It would be very helpful for me.

Thanks, Mark.

_________________
Support and Development hours available from [url=https://opmantek.com]Opmantek[/url].
Please consider a purchase to help make Open-AudIT better for everyone.


Top
 Profile  
Reply with quote  
PostPosted: Sun Nov 23, 2008 10:51 pm 
Offline
Helper

Joined: Fri Nov 16, 2007 1:32 am
Posts: 73
Location: Dallas,Texas
Hey Mark, any chance you can post your code for the pc input for the xml. So we dont have to start from scratch (if youve got it, i know the tables are different)


I would like to start trying to turn the audit.vbs and the pc add for the old version of open-audit into xml input/recieve

_________________
1400 Servers Audited (1 hour interval) Applied via a local scheduler, deployed via GPO.
Running OA on IIS6 Web Server
90% Windows 2k3 Server (std,ent)
5% Windows XP
5% Windows 2000


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 36 posts ]  Go to page Previous  1, 2, 3  Next

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