use Net::LDAP;
use Net::Ping;
use Win32::OLE;
use Win32::OLE::Enum;
use Win32::TieRegistry(Delimiter=>"/");
use Data::Dumper;
use Win32::ODBC;
use Getopt::Std;
use Win32::AdminMisc;
use POSIX;
use Win32::NetAdmin;
use Net::SNMP;
use Net::SMTP;
use constant wbemFlagReturnImmediately => 0x10;
use constant wbemFlagForwardOnly => 0x20;



sub usage{
  print <<EOF;
  
  "$0" will find new Windows machines and insert them into
  the DCI database host table as well as fill out extended
  host data in the host_info table.  All hosts are injected
  into the "lost and found" rack # 99.
  
  ex : $0 -a  -b ldapbase -r filter -d domain -c dc -m singlemachine -f file -h -g -l -x -o sort -s searchfilter

  Where :
    *domain:	Netbios name of domain to run against (as apposed to
    			all known to WINS).
    *a:			Process all servers in a given domain (-a will take
    			precendence over a -d or -m).  Requires domain listed
    *ldapbase:	Search base to gather list of machines from.
    			(ex. "ou=servers,ou=ah,dc=ad,dc=mathworks,dc=com")
    *searchfilter:	Optional ldap search filter
    			(ex. "(&(objectCategory=computer)(operatingSystem=\*Server\*))"
    *dc			Name of nearest Domain controller (FQDN is preferred)
    			(ex. "dc-00.ad.mathworks.com")
    *singlemachine:	Name of machine to dump configuration for.
    *file:		Name of file with machines to process.
    *sort:		Parameter to determine the sort order of the list of
    			machines (2 values "f" "r" for forward and reverse)
    *l:			Create a logfile
    *g:			Print debug info
    *t:			use MathWorks default of finding all servers.  This
    			is done by taking all server machines known to WINS
    			and AD and creating a combined list.
    *x:			execute changes to DCI - otherwise just audit changes
    *h:			Display this helpfile   
    			
    All *'s are optional but if all are left out your are not asking the'
    script to find anything on any of the machines so you will get no results
    
    typical usage: $0 -t -o f 
    
EOF
    exit( 1 );

}

getopts( 'ahlgtxd:m:b:c:f:s:o:' );

my $all    				= $Getopt::Std::opt_a;
my $domain      	= $Getopt::Std::opt_d;
my $dc		      	= (length($Getopt::Std::opt_c)) ? $Getopt::Std::opt_c : "dc-00.ad.mathworks.com";
my $search	     	= $Getopt::Std::opt_s;
my $base					= $Getopt::Std::opt_b;
my $singlemachine = $Getopt::Std::opt_m;
my $help					= $Getopt::Std::opt_h;
my $file 					= $Getopt::Std::opt_f;
my $logfile				= $Getopt::Std::opt_l;
my $debug					= $Getopt::Std::opt_g;
my $sort					= $Getopt::Std::opt_o;
my $tmwsearch			= $Getopt::Std::opt_t;
my $execute				= $Getopt::Std::opt_x;

chomp $domain;
chomp $file;
chomp $singlemachine;
chomp $base;
chomp $search;
chomp $sort;

$sort = lc($sort);
$domain = lc($domain);
$singlemachine = lc($singlemachine);

if ($help){usage();exit( 1 );};

$time = strftime "%a.%b.%Y_%H.%M.%S", localtime;
$date_time = strftime "%Y-%m-%d %H:%M:%S", localtime;
$filename = "$0.$time.txt";
if ($logfile) {open (FILE, "> $filename") || die("Can't open file $!")};

if ($tmwsearch) { 
	$search = "(&(objectCategory=computer)(operatingSystem=\*Server\*))";
	$base = "dc=ad,dc=mathworks,dc=com";
	};

&setPing;				#Sets the protocol and timeout for Net::Ping
&openDBs;
&getClients;
&loadExceptions; 	#Loads all exception files

my $count_multiple_dci = 0;
my $count_vm_hw_conflict = 0;
my $count_serial_conflict = 0;
my $admin = "DCIserverImport";

# email variables
my $smtphost = "exhub-00-ah.mathworks.com";
my $subject = "DCI - Magic Sync conflicts";
my $from = "winadmin\@mathworks.com";
my $to = "jsteele\@mathworks.com";



my @clients;
my @assetemail;
if ($singlemachine) {$clients[0] = $singlemachine};
foreach $machine(@clients)
	{
	chomp ($machine);
	$machine = uc($machine);
	print "\n$machine:\n";	
	if ($logfile) {print FILE "\n$machine:\n"};
	if($p->ping($machine) == 1)		#Client is on the network
		{
		if (grep(/$machine/i,@globalexceptions))
			{
				print "$machine is in the exempted for discovery table in the DCI database\n";
			}
		else
			{
			&GetHardwareInfo ($machine,\%hardware);
			}
		}
	else 
		{ 
		print "\t***Error: $machine not pingable\n";
		if ($logfile) {print FILE "\t***Error: $machine not pingable\n"}
		};
	&undefine_vars;
	};				
$body = join ('',@assetemail);
#&mail ($subject,$body,$to);


############################################################################

sub undefine_vars
{
	undef(%hardware);
	undef($machine_serial_num);
	undef(@machine_serial_num);
	undef($magic_serial_num);
	undef(@magic_serial_num);
	undef($temp);
	undef($temp2);
	undef($temp3);
	undef($dciserial);
	undef(@dciserial);
	undef($magic_asset_tag);
	undef(@magic_asset_tag);
	undef($dci_asset_tag);
	undef(@dci_asset_tag);
	undef($dci_id);
	undef(@dci_id);
	undef($magic_install_date_mod);
	undef(@magic_install_date_mod);
	undef($dci_purchase_date);
	undef(@dci_purchase_date);	
	undef($owner);
}

############################################################################

	
############################################################################

sub GetHardwareInfo
{

print "$machine : Hardware Info\n";
my $objWMIService = "";
unless ($objWMIService = Win32::OLE->GetObject("winmgmts:\\\\$machine\\root\\CIMV2")) {print "WMI connection to $machine failed.\n"; return;}
my $NetworkAdapterConfigurationItems = $objWMIService->ExecQuery("SELECT IPAddress,MACAddress,Description FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = \'TRUE\'", "WQL", wbemFlagReturnImmediately | wbemFlagForwardOnly);
my $PhysicalMemoryItems = $objWMIService->ExecQuery("SELECT Capacity,DeviceLocator,Speed FROM Win32_PhysicalMemory", "WQL", wbemFlagReturnImmediately | wbemFlagForwardOnly);
my $BIOSItems = $objWMIService->ExecQuery("SELECT Manufacturer,SerialNumber,SMBIOSBIOSVersion FROM Win32_BIOS", "WQL", wbemFlagReturnImmediately | wbemFlagForwardOnly);
my $ComputerSystemItems = $objWMIService->ExecQuery("SELECT Manufacturer,Model,NumberOfProcessors,ThermalState,TotalPhysicalMemory FROM Win32_ComputerSystem", "WQL", wbemFlagReturnImmediately | wbemFlagForwardOnly);
my $ComputerSystemProductItems = $objWMIService->ExecQuery("SELECT IdentifyingNumber,Name,SKUNumber,UUID,Vendor,Version FROM Win32_ComputerSystemProduct", "WQL", wbemFlagReturnImmediately | wbemFlagForwardOnly);
my $DiskDriveItems = $objWMIService->ExecQuery("SELECT Description,Manufacturer,MediaType,Partitions,Signature,Size,Status,StatusInfo FROM Win32_DiskDrive", "WQL", wbemFlagReturnImmediately | wbemFlagForwardOnly);
my $LogicalDiskItems = $objWMIService->ExecQuery("SELECT Name,DriveType,FileSystem,Size,VolumeName,VolumeSerialNumber,DriveType FROM Win32_LogicalDisk Where DriveType=3", "WQL", wbemFlagReturnImmediately | wbemFlagForwardOnly);
my $ProcessorItems = $objWMIService->ExecQuery("SELECT * FROM Win32_Processor", "WQL", wbemFlagReturnImmediately | wbemFlagForwardOnly);
#my $ProcessorItems = $objWMIService->ExecQuery("SELECT CurrentClockSpeed,DeviceID,SocketDesignation,Name,MaxClockSpeed,L2CacheSize FROM Win32_Processor", "WQL", wbemFlagReturnImmediately | wbemFlagForwardOnly);
my $OperatingSystemItems = $objWMIService->ExecQuery("SELECT * FROM Win32_OperatingSystem", "WQL", wbemFlagReturnImmediately | wbemFlagForwardOnly);

my $ComputerSystemNumberOfProcessors = 0;
my $BIOSItemsManufacturer = "";
local $BIOSItemsSerialNumber = "";
my $BIOSItemsSMBIOSBIOSVersion = "";
my $ComputerSystemManufacturer = "";
my $ComputerSystemModel = "";
my $ComputerSystemThermalState = "";
my $ComputerSystemTotalPhysicalMemory = 0;
my $PhysicalMemorycapacity = 0;
my $PhysicalMemoryspeed = 0;
my $PhysicalMemoryDeviceLocator = "";
my $NetworkAdapterConfigurationipaddress = "";
my $NetworkAdapterConfigurationMACAddress = "";
my $NetworkAdapterConfigurationDescription = "";
my $ProcessorItemsCurrentClockSpeed = 0;
my $ProcessorItemsDeviceID = "";
my $ProcessorItemsSocketDesignation = "";
my $ProcessorItemsName = "";
my $ProcessorItemsMaxClockSpeed = 0;
my $ProcessorItemsL2CacheSize = 0;
my $ProcessorItemsNumberOfCores = undef;
my $DiskDriveItemsDescription = "";
my $DiskDriveItemsManufacturer = "";
my $DiskDriveItemsMediaType = "";
my $DiskDriveItemsPartitions = "";
my $DiskDriveItemsSignature = "";
my $DiskDriveItemsSize = "";
my $DiskDriveItemsStatus = "";
my $DiskDriveItemsStatusInfo = "";
my $LogicalDiskItemsName = "";
my $LogicalDiskItemsDriveType = "";
my $LogicalDiskItemsFileSystem = "";
my $LogicalDiskItemsSize = "";
my $LogicalDiskItemsVolumeName = "";
my $LogicalDiskItemsVolumeSerialNumber = "";
my $OperatingSystemItemsVersion = "";
my $OperatingSystemItemsCaption = "";
my $OperatingSystemItemsName = "";
my $OperatingSystemItemsCSDVersion = "";
my $OperatingSystemItemsInstallDate = "";
my $OperatingSystemItemsLastBootUpTime = "";
my $hw_type = "";
my $bios = "";
my $proc_info = "";
my $mem_info = "";
my $nic_info = "";
my $drive_info = "";
my $ldrive_info = "";
my $os = "";
my $os_build = "";
my $os_sp = "";
my $os_install_date = "";
my $last_boot = "";
my $last_seen = "";
local $ip = "";
local $mac = "";


if ($BIOSItems) {
foreach my $objBIOSItems (in $BIOSItems) 
{
	$BIOSItemsManufacturer = $objBIOSItems->{Manufacturer};
	$BIOSItemsSerialNumber = $objBIOSItems->{SerialNumber};
	$BIOSItemsSMBIOSBIOSVersion = $objBIOSItems->{SMBIOSBIOSVersion};
}
}
if ($ComputerSystemItems) {
foreach my $objComputerSystem (in $ComputerSystemItems)
{
	$ComputerSystemManufacturer = $objComputerSystem->{Manufacturer};
	$ComputerSystemModel = $objComputerSystem->{Model};
	$ComputerSystemNumberOfProcessors = $objComputerSystem->{NumberOfProcessors};
	$ComputerSystemThermalState = $objComputerSystem->{ThermalState};
	$ComputerSystemTotalPhysicalMemory = $objComputerSystem->{TotalPhysicalMemory}/1024/1024/1024;
	print "\tMachine Type: $ComputerSystemManufacturer $ComputerSystemModel\n";
	$hw_type = "$ComputerSystemManufacturer $ComputerSystemModel";
}
}
print "\tMachine Serial number: $BIOSItemsSerialNumber\n";
print "\tMachine BIOS version: $BIOSItemsSMBIOSBIOSVersion\n";
$bios = "$BIOSItemsSMBIOSBIOSVersion";
print "\tNumber of processors (Aggregate cores unless OS correctly identified as multicore below): $ComputerSystemNumberOfProcessors\n";
if ($ProcessorItems) {
foreach my $objProcessorItems (in $ProcessorItems)
{
	$ProcessorItemsCurrentClockSpeed = $objProcessorItems->{CurrentClockSpeed};
	$ProcessorItemsDeviceID = $objProcessorItems->{DeviceID};
	$ProcessorItemsSocketDesignation = $objProcessorItems->{SocketDesignation};
	$ProcessorItemsName = $objProcessorItems->{Name};
	$ProcessorItemsMaxClockSpeed = $objProcessorItems->{MaxClockSpeed};
	$ProcessorItemsL2CacheSize = $objProcessorItems->{L2CacheSize}/1024;
	$ProcessorItemsNumberOfCores = $objProcessorItems->{NumberOfCores};
	print "\t\tCPU: $ProcessorItemsDeviceID-$ProcessorItemsSocketDesignation-$ProcessorItemsName at $ProcessorItemsCurrentClockSpeed\/$ProcessorItemsMaxClockSpeed MHz $ProcessorItemsL2CacheSize MB L2 cache\n";
	$proc_info = $proc_info."$ProcessorItemsDeviceID-$ProcessorItemsSocketDesignation-$ProcessorItemsName at $ProcessorItemsCurrentClockSpeed\/$ProcessorItemsMaxClockSpeed MHz $ProcessorItemsL2CacheSize MB L2 cache\n";
	if ($ProcessorItemsNumberOfCores) {print "\t\tMachines OS correctly identified multicore: $ProcessorItemsNumberOfCores cores\n";$proc_info = $proc_info."Machines OS correctly identified multicore: $ProcessorItemsNumberOfCores cores\n"}
}
}
print "\tTotal memory: ";printf("%.2f",$ComputerSystemTotalPhysicalMemory);print" GB\n";
$mem_info = "Total memory: ".sprintf("%.2f",$ComputerSystemTotalPhysicalMemory)." GB\n";
if ($PhysicalMemoryItems) {
foreach my $objPhysicalMemory (in $PhysicalMemoryItems) 
{
	$PhysicalMemorycapacity = $objPhysicalMemory ->{Capacity}/1024/1024/1024;;
	$PhysicalMemoryspeed = $objPhysicalMemory ->{Speed};
	$PhysicalMemoryDeviceLocator = $objPhysicalMemory ->{DeviceLocator};
	print "\t\tMemory DIMM: $PhysicalMemorycapacity GB at $PhysicalMemoryspeed MHz in slot $PhysicalMemoryDeviceLocator\n";
	$mem_info = $mem_info."$PhysicalMemorycapacity GB at $PhysicalMemoryspeed MHz in slot $PhysicalMemoryDeviceLocator\n";
}
}
if ($NetworkAdapterConfigurationItems) {
foreach my $objNetworkAdapterConfiguration (in $NetworkAdapterConfigurationItems) 
{
	$NetworkAdapterConfigurationipaddress = join('.',$objNetworkAdapterConfiguration->{IPAddress}[0]);
	$NetworkAdapterConfigurationMACAddress = $objNetworkAdapterConfiguration->{MACAddress};
	$NetworkAdapterConfigurationDescription = $objNetworkAdapterConfiguration->{Description};
	print "\tNIC Description: $NetworkAdapterConfigurationDescription\n";
	print "\t\tIPAddress: $NetworkAdapterConfigurationipaddress\n";
	print "\t\tMACAddress: $NetworkAdapterConfigurationMACAddress\n";
	$nic_info = $nic_info."NIC Description: $NetworkAdapterConfigurationDescription\nIPAddress: $NetworkAdapterConfigurationipaddress\nMACAddress: $NetworkAdapterConfigurationMACAddress\n";
}
}
$mac = $NetworkAdapterConfigurationMACAddress;
$ip = $NetworkAdapterConfigurationipaddress;
print "\tDrives as presented to the OS - does not factor HW RAID:\n"; 
if ($DiskDriveItems) {
foreach my $objDiskDriveItems (in $DiskDriveItems) 
{
	$DiskDriveItemsDescription = $objDiskDriveItems->{Description};
	$DiskDriveItemsManufacturer = $objDiskDriveItems->{Manufacturer};
	$DiskDriveItemsMediaType = $objDiskDriveItems->{MediaType};
	$DiskDriveItemsPartitions = $objDiskDriveItems->{Partitions};
	$DiskDriveItemsSignature = $objDiskDriveItems->{Signature};
	$DiskDriveItemsSize = $objDiskDriveItems->{Size}/1024/1024/1024;
	$DiskDriveItemsStatus = $objDiskDriveItems->{Status};
	$DiskDriveItemsStatusInfo = $objDiskDriveItems->{StatusInfo};
	print "\t\t";printf("%.2f",$DiskDriveItemsSize);print" GB with $DiskDriveItemsPartitions partitions\n";
	$drive_info = $drive_info.sprintf("%.2f",$DiskDriveItemsSize)." GB with $DiskDriveItemsPartitions partitions\n";
}
}
print "\tLogical Drives:\n"; 
if ($LogicalDiskItems) {
foreach my $objLogicalDiskItems (in $LogicalDiskItems) 
{
	$LogicalDiskItemsName = $objLogicalDiskItems->{Name};
	$LogicalDiskItemsDriveType = $objLogicalDiskItems->{DriveType};
	$LogicalDiskItemsFileSystem = $objLogicalDiskItems->{FileSystem};
	$LogicalDiskItemsSize = $objLogicalDiskItems->{Size}/1024/1024/1024;
	$LogicalDiskItemsVolumeName = $objLogicalDiskItems->{VolumeName};
	$LogicalDiskItemsVolumeSerialNumber = $objLogicalDiskItems->{VolumeSerialNumber};
	print "\t\t$LogicalDiskItemsName ($LogicalDiskItemsVolumeName)-$LogicalDiskItemsFileSystem ";printf("%.2f",$LogicalDiskItemsSize); print "GB - Ser# $LogicalDiskItemsVolumeSerialNumber\n";
	$ldrive_info = $ldrive_info."$LogicalDiskItemsName ($LogicalDiskItemsVolumeName)-$LogicalDiskItemsFileSystem ".sprintf("%.2f",$LogicalDiskItemsSize)."GB - Ser# $LogicalDiskItemsVolumeSerialNumber\n";
}
}
if ($OperatingSystemItems) {
foreach my $objOperatingSystemItems (in $OperatingSystemItems) 
{
	$OperatingSystemItemsVersion = $objOperatingSystemItems->{Version};
	$OperatingSystemItemsCaption = $objOperatingSystemItems->{Caption};
	$OperatingSystemItemsCSDVersion = $objOperatingSystemItems->{CSDVersion};
	$OperatingSystemItemsInstallDate = $objOperatingSystemItems->{InstallDate};
	$OperatingSystemItemsLastBootUpTime = $objOperatingSystemItems->{LastBootUpTime};
	$OperatingSystemItemsName = $objOperatingSystemItems->{Name};
	$os = $OperatingSystemItemsName;
	$os =~ s/\\/\\\\/gi;
	$os_build = $OperatingSystemItemsVersion;
	$os_sp = $OperatingSystemItemsCSDVersion;
	$os_install_date = sprintf("%.0f",$OperatingSystemItemsInstallDate/1000000);
	$last_boot = sprintf("%.0f",$OperatingSystemItemsLastBootUpTime/1000000);
	print "\tOS: $OperatingSystemItemsName\n\t\tBuild:($OperatingSystemItemsVersion)\n\t\tService Pack: $OperatingSystemItemsCSDVersion\n";
	print "\t\tInstall Date: $os_install_date\n\t\tLast Boot time: $last_boot\n";
}
}


$date_time = strftime "%Y-%m-%d %H:%M:%S", localtime;
$DCdb->Sql("SELECT hostname,Serial,system_owner,asset_tag,purchase_date,id,server_type_area_id,support24x7,typeid FROM dcinventory.host where UCASE(hostname) = UCASE(\"$machine\")");	
		if ($DCdb->RowCount( ) >0) 					# If there is already a server with this name in the host table
			{
			#
			#			Grab host id from host table
			#
			while ($DCdb->FetchRow()) {
				@Data = $DCdb->Data ("id");
				$host_id = $Data[0];
				chomp ($host_id);
			}
			print "\nFound host with name $machine in host table with host_id = $host_id\n";
			$DCdb->Sql("SELECT host_id FROM dcinventory.host_info where host_id = \"$host_id\"");
			#
			#			If there is a host_id entry in the host_info table for that machine compare each column, otherwise just insert the new values
			#
			if ($DCdb->RowCount( ) >0) 
				{
				print "\nHost has entry in the host and host_info table, need to compare each bit of data to ensure there is not a change.\n";
				# query existing values of host_info table and load into variables
				# build a hash of variables and column names to compare
				# sub compare data
				%columncompare = ( 
						"hw_type" 	,$hw_type,
						"bios" 			,$bios,
						"proc_info"	,$proc_info,
						"mem_info"	,$mem_info,
						"nic_info"	,$nic_info,
						"drive_info",$drive_info,
						"ldrive_info",$ldrive_info,
						"os"				,$OperatingSystemItemsName,
						"os_build"	,$os_build,
						"os_sp"			,$os_sp,
						"os_install_date",$os_install_date,
						"last_boot"	,$last_boot,
						"last_seen"	,$date_time );				           
				while (($key, $value) = each(%columncompare))
					{
					$DCdb->Sql("SELECT $key FROM dcinventory.host_info where host_id = \"$host_id\""); 
					while ($DCdb->FetchRow()) {
						@Data = $DCdb->Data("$key");
						$tempval = $Data[0];
					}
					if (($key eq "os_install_date")||($key eq "last_boot"))
						{
						$tempval =~ s/-//gi;
						}
					chomp ($tempval);
					chomp ($value);
					if ($tempval eq $value) {print "No change in $key\n"}
					else 
						{
						$sql_command =  "UPDATE dcinventory.host_info SET $key=\"$value\" WHERE host_id = \"$host_id\"";
						print "$sql_command\n";
						$DCdbWrite->Sql("$sql_command");
						#
						#			Create log entry			
						#		
#						print "INSERT INTO dcinventory.log (host_id,description,sql_command,date_time,admin) VALUES ('$host_id','$machine value for host_info table column $key changed from $tempval to $value',\"$sql_command\",'$date_time','$admin')\n";
						unless ($key eq "last_seen") {
							$DCdbWrite->Sql("INSERT INTO dcinventory.log (host_id,description,sql_command,date_time,admin) VALUES ('$host_id','$machine value for host_info table column $key changed from $tempval to $value',\'$sql_command\','$date_time','$admin')");
							}						
						}
					}
				}
			else
				{
				print "\nHost is in the host table, but not in the host_info table, doing insert of host_info.\n";
				$sql_command = "INSERT INTO dcinventory.host_info (host_id,hw_type,bios,proc_info,mem_info,nic_info,drive_info,ldrive_info,os,os_build,os_sp,os_install_date,last_boot,last_seen) VALUES (\'$host_id\',\'$hw_type\',\'$bios\',\'$proc_info\',\'$mem_info\',\'$nic_info\',\'$drive_info\',\'$ldrive_info\',\'$os\',\'$os_build\',\'$os_sp\',\'$os_install_date\',\'$last_boot\',\'$date_time\')";
				print "$sql_command\n\n";
				$DCdbWrite->Sql("$sql_command");
				#
				#			Create log entry			
				#		
				$DCdbWrite->Sql("INSERT INTO dcinventory.log (host_id,description,sql_command,date_time,admin) VALUES ('$host_id','$machine is in host table but not host_info table, inserting into host_info',\"$sql_command\",'$date_time','$admin')");					
				}
			}
		else 											#  Machine not in Host table, add to host table and add to host_info table
			{
			&findRackU;							#  Find empty spot in Lost and Found rack to stick server into and do insert into host table
			&findHostID;						#  Query the host ID of the newly inserted device in the host table
			$sql_command = "INSERT INTO dcinventory.host_info (host_id,hw_type,bios,proc_info,mem_info,nic_info,drive_info,ldrive_info,os,os_build,os_sp,os_install_date,last_boot,last_seen) VALUES (\'$host_id\',\'$hw_type\',\'$bios\',\'$proc_info\',\'$mem_info\',\'$nic_info\',\'$drive_info\',\'$ldrive_info\',\'$os\',\'$os_build\',\'$os_sp\',\'$os_install_date\',\'$last_boot\',\'$date_time\')";
			print "$sql_command\n\n";
			$DCdbWrite->Sql("$sql_command");
			#
			#			Create log entry			
			#		
			$DCdbWrite->Sql("INSERT INTO dcinventory.log (host_id,description,sql_command,date_time,admin) VALUES ('$host_id','$machine found for first time, inserting into DCI databases host_info table',\"$sql_command\",'$date_time','$admin')");					
			}

}
	
############################################################################

sub findRackU
{
	$DCdb->Sql("SELECT id FROM dcinventory.host where rackid = \"99\"");
	$totalincount = $DCdb->RowCount() + 1;
	$iteration = 300;
	while ($iteration >= 0)
		{
		$DCdb->Sql("SELECT id FROM dcinventory.host where rackid = \"99\" and bottom_rack_u = \"$iteration\"");
		unless ($DCdb->RowCount( ) >0)
			{
				print "\n\nFound free spot in lost and found rack: $iteration\n";
				if ($machine)
					{
					$sql_command = "INSERT INTO dcinventory.host (hostname,rackid,bottom_rack_u,typeid,lastModifiedBy,os_id,candidate_id,shutdown_order,startup_order,Serial,mac,ip) VALUES ('$machine','99',$iteration,'140','$admin','5','1','10','10','$BIOSItemsSerialNumber','$mac','$ip')";
					print "$sql_command\n\n";
					$DCdbWrite->Sql("$sql_command");
					#
					#			Create log entry			
					#		
					$DCdbWrite->Sql("INSERT INTO dcinventory.log (host_id,description,sql_command,date_time,admin) VALUES ('$host_id','$machine found for first time, inserting into DCI databases host table',\"$sql_command\",'$date_time','$admin')");					
					last;
					}
				$iteration = $totalincount;
			}
		$iteration--;
		}
}


############################################################################

sub findHostID
{
$DCdb->Sql("SELECT hostname,id FROM dcinventory.host where UCASE(hostname) = UCASE(\"$machine\")");	
	if ($DCdb->RowCount( ) >0) 
		{
		#
		#			Grab host id from host table
		#
		while ($DCdb->FetchRow()) {
			@Data = $DCdb->Data ("id");
			$host_id = $Data[0];
			chomp ($host_id);
			}
		}
}

############################################################################

sub loadExceptions
	{
	$DCdb->Sql("SELECT hostname,discovery FROM dcinventory.exceptions WHERE discovery = True"); 
	while ($DCdb->FetchRow()) {
		@Data = $DCdb->Data("hostname");
		push(@globalexceptions, $Data[0]);
	}
	if ($debug) {
		print "Global Exceptions: @globalexceptions\n";
		if ($logfile) {	print FILE "Global Exceptions: @globalexceptions\n" };
		}
	}

############################################################################


sub openDBs
	{
	# Connecting to Databases via ODBC. 
	# It attempts to add the ODBC Datasource if it was not created (correct version drivers must already be installed). 
	# Most likely problem is not having MySQL ODBC driver 

	# ODBC variables
	my $DCdsn = "DCinventory";
	my $DCuid = "dcreader";
	my $DCpwd = "readonly";
	my $DCdsnWrite = "DCinventoryWrite";
	my $DCuidWrite = "dcuser";
	my $DCpwdWrite = "dcuserpw";
	my $magicdsn = "Magic";
	my $magicuid = "magic";
	my $magicpwd = "Matlab99";

	my $cont =0; 
	my $ODBC =0;   
	my @dsns = undef;
	
	# Checking ODBC DataSources
	 
	my (%dsn,$key) = Win32::ODBC::DataSources();  	
	if ($debug) {print "\nChecking DSN's to see if this connection already exists:\n"};
	foreach $key (sort keys %dsn) 
		{
		if ($debug) {print "\tODBC:$key\n"};
		push (@dsns,$key);
		} 

	if ($debug) {print "\tDSN array:@dsns\n"};
	
	# Creating ODBC DataSource for DCInventory if it doesn't exist
	unless (grep(/$DCdsn/,@dsns))
		{
		print "The ODBC Connection to $DCdsn was not established. Creating new ODBC Connection.....\n\n";
		$DSN      = "$DCdsn";
		$Driver   = 'MySQL ODBC 5.1 Driver';
		$Desc     = 'Connection to Datacenter inventory database';
		$Server   = 'mysql5-VIF.mathworks.com'; 
		$Database = 'dcinventory';
		if (Win32::ODBC::ConfigDSN(
	        	ODBC_ADD_DSN, 
			"$Driver", 
			("DSN=$DSN", 
			"DESCRIPTION=$Desc", 
			"SERVER=$Server", 	       # server name
			"DATABASE=$Database",         # our database
			#                    "NETWORK=DBMSSOCN",        # TCP/IP Socket Lib
			))){print "DSN $dsnname created\n";}
		}
	unless ($DCdb = new Win32::ODBC("DSN=$DCdsn;UID=$DCuid;PWD=$DCpwd")) {print "\nDCInventory DB connect error\n\n";exit;}


	# Creating ODBC DataSource for DCInventory_write if it doesn't exist
	unless (grep(/$DCdsnWrite/,@dsns))
		{
		print "The ODBC Connection to $DCdsnWrite was not established. Creating new ODBC Connection.....\n\n";
		$DSN      = "$DCdsnWrite";
		$Driver   = 'MySQL ODBC 5.1 Driver';
		$Desc     = 'Connection to Datacenter inventory database with write access';
		$Server   = 'mysql5-VIF.mathworks.com'; 
		$Database = 'dcinventory';
		if (Win32::ODBC::ConfigDSN(
	        	ODBC_ADD_DSN, 
			"$Driver", 
			("DSN=$DSN", 
			"DESCRIPTION=$Desc", 
			"SERVER=$Server", 	       # server name
			"DATABASE=$Database",         # our database
			#                    "NETWORK=DBMSSOCN",        # TCP/IP Socket Lib
			))){print "DSN $DSN created\n";}
		}
	unless ($DCdbWrite = new Win32::ODBC("DSN=$DCdsnWrite;UID=$DCuidWrite;PWD=$DCpwdWrite")) {print "\nDCInventoryWrite DB connect error\n\n";exit;}


	# Creating ODBC DataSource for MAGIC if it doesn't exist
	unless (grep(/$magicdsn/,@dsns))
		{
		print "The ODBC Connection to $momdsn was not established. Creating new ODBC Connection.....\n\n";
		$DSN      = "$magicdsn";
		$Driver   = 'SQL Server';
		$Desc     = 'Connection to Magic database';
		$Server   = 'SDEDB-00-ah.mathworks.com'; 
		$Database = 'Magic';
		if (Win32::ODBC::ConfigDSN(
	        	ODBC_ADD_DSN, 
			"$Driver", 
			("DSN=$DSN", 
			"DESCRIPTION=$Desc", 
			"SERVER=$Server", 	       # server name
			"DATABASE=$Database",         # our database
			#                    "NETWORK=DBMSSOCN",        # TCP/IP Socket Lib
			))){print "DSN $dsnname created\n";}
		}
	unless ($magicdb = new Win32::ODBC("DSN=$magicdsn;UID=$magicuid;PWD=$magicpwd")) {print "\Magic DB connect error\n\n";exit;}

	}

############################################################################

sub setPing
	{
	$p = Net::Ping->new('icmp',2);
	}

############################################################################
############################################################################

sub getClients
	{
	if ($file)					#Get input from file if exists
		{   
		(open(INFILE,"<$file"));
		@clients = <INFILE>;
		chomp @clients;
		close INFILE;
		foreach $client(@clients)
			{
			$client =~ tr/a-z/A-Z/;
			}
		}
	elsif ($domain)					#Get all servers
		{                                    		
		Win32::NetAdmin::GetServers('',$domain,SV_TYPE_SERVER_NT|SV_TYPE_DOMAIN_CTRL|SV_TYPE_DOMAIN_BAKCTRL,\@clients);
		foreach $client(@clients)
			{
			$client =~ s/\\//g;
			}
		}
	elsif ($singlemachine)				#Get single Machine
		{                                    		
		@clients = "$singlemachine";
		foreach $client(@clients)
			{
			$client =~ s/\\//g;
			}
		}
	elsif ($base)					#Get an ldap search of machines
		{
		if ($p->ping($dc) == 1) 
			{
			unless ($search)  {$search = "(&(objectCategory=computer)(operatingSystem=\*Server\*))"};	
#my $search = "(&(objectCategory=computer)(operatingSystem=\*Server\*))";	
			if ($debug) {print "\nBase:$base\t\t$search\n\n"};			
#print "\nBase:$base\t\t$search\n\n";			
			my $ldap = new Net::LDAP("$dc", port=>389) or die "$@";
			$ldap->bind(version=>3);
			my $msg = $ldap->search(
					base => "$base",
					filter=>"$search",
					callback => \&callback);
			die $msg->error if $msg->code;
			sub callback 
				{
				my ($search, $entry) = @_;
				$entry = $search->shift_entry;
				my @results=();
				if (defined $entry and $entry->get_value("cn")) 
					{
					$cn = $entry->get_value("cn");
					push @clients, "$cn";
					}
				}
			$ldap->unbind;
			}
		else { print "\n\nCannot access $dc!!!! Exiting program, dc $dc is not accessible.\n\n"; exit }				
		foreach $client(@clients)
			{
			$client =~ s/\\//g;
			}
		}
	elsif ($all)
		{
		Win32::NetAdmin::GetServers('',$domain,SV_TYPE_SERVER_NT|SV_TYPE_DOMAIN_CTRL|SV_TYPE_DOMAIN_BAKCTRL,\@clients);
		foreach $client(@clients)
			{
			$client =~ s/\\//g;
			}
		}	
	else	{					# Kick out if no correct options are selected
		usage();
		exit (1);
		};
	if ($tmwsearch)
		{
		Win32::NetAdmin::GetServers('',$domain,SV_TYPE_SERVER_NT|SV_TYPE_DOMAIN_CTRL|SV_TYPE_DOMAIN_BAKCTRL,\@winsclients);
		if ($debug) { print "WINS database clients: @winsclients\n\n"};
		foreach $winsclient(@winsclients)
			{
			unless (grep(/$winsclient/,@clients))
				{ push (@clients, $winsclient)	};
			}
		};
	if ($sort eq "f")  { @clients = sort(@clients) }
	elsif ($sort eq "r") { @clients = reverse sort(@clients)};
	}

############################################################################

# 		if ($Registry->{"||$host|LMachine"}) {

# C:\Perl\Perl2exe>snmpget.exe -v 2c -c tmw-public script-00-ah SNMPv2-SMI::enterprises.232.2.2.2.5.0
# C:\Perl\Perl2exe>snmpget.exe -v 2c -c tmw-public script-00-ah .1.3.6.1.4.1.232.2.2.2.5.0
#SNMPv2-SMI::enterprises.232.2.2.2.5.0 = STRING: "USM70800R8"

#snmpwalk -Os -v 2c -c public infnas-01-ah.mathworks.com .1.3.6.1.4.1.789.1.1.9
#
sub snmp_query
{
	my ( $machine,$community,$oid ) = @_;
	my($session, $error) = Net::SNMP->session(
	  -hostname  => $machine,
	  -community => $community,
	  -timeout   => "10",
	  -port      => 161 
	);
	if (!defined($session)) {
	  printf("ERROR: %s.\n", $error);
	#  exit 1;
	}
#	my $snmp_serial_num = '1.3.6.1.4.1.232.2.2.2.5.0';
#	my $oid = '1.3.6.1.4.1.232.2.2.2.5.0';
	if (!defined($response = $session->get_request($oid))) {
#	  printf("ERROR: %s.\n", $session->error());
	  $session->close();
	#  exit 1;
	}
#	printf("$community:$oid Serial Number via SNMP of host '%s' is %s\n", 
#	  $session->hostname(), 
#	  $response->{$oid}
#	);
	$machine_serial_num = $response->{$oid};
	$session->close();
	return $machine_serial_num
}

############################################################################################################## 
 
sub mail {                     

my ($subject,$body,$to) = @_;

$smtp = Net::SMTP->new($smtphost,Timeout =>30) or die "Can't connect to $smtphost";
$smtp->mail($from) or die "mail: @{[$smtp->message]}\n";
$smtp->to($to) or die "to: @{[$smtp->message]}\n";
$smtp->data();
$smtp->datasend(<<END_OF_MESSAGE);
To: $to
Subject: $subject

$body

END_OF_MESSAGE
$smtp->dataend;
$smtp->quit;

}   
 
##############################################################################################################