PowerShell script for exporting Print Server info to Oracle

The example below covers how a PowerShell script can connect to a Windows Print Server, how it can query printers information, and how it can connect to an Oracle database to perform a query.

# Start configuring parameters
Param (
	[string]$Printservers = "printserver1",
	[string]$OracleServer = "orcl",
	[string]$OracleUser = "scott",
	[string]$OraclePassword = "tiger"
	[string]$sql = "insert into printer_list (print_server, printer_name, printer_location, printer_comment, printer_ip, printer_driver_name, printer_driver_version, printer_driver, entry_dt) values(:print_server, :printer_name, :printer_location, :printer_comment, :printer_ip, :printer_driver_name, :printer_driver_version, :printer_driver, sysdate) "
)
# End configuring parameters

ForEach ($Printserver in $Printservers) { # Start looping through each print server
	$Printers = Get-WmiObject Win32_Printer -ComputerName $Printserver

	ForEach ($Printer in $Printers) { # Start looping through each printer
		[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")
		$connectionString = "User Id=$OracleUser;Password=$OraclePassword;Data Source=$OracleServer;"
		$connection = $null
		$command = $null

		Try {
			$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)
			$command = New-Object System.Data.OracleClient.OracleCommand -ArgumentList $sql, $connection
			$connection.Open()
			
			$NoOutput = $command.Parameters.Add("print_server", $Printserver)
			$NoOutput = $command.Parameters.Add("printer_name", $Printer.Name)
			
			$Location = $Printer.Location
			if (!$Location) {
				$Location = " "
			}
			$NoOutput = $command.Parameters.Add("printer_location", $Location)
			
			$Comment = $Printer.Comment
			if (!$Comment) {
				$Comment = " "
			}
			$NoOutput = $command.Parameters.Add("printer_comment", $Comment)
			
			$NoOutput = $command.Parameters.Add("printer_ip", $Printer.Portname)
			$Drivers = Get-WmiObject Win32_PrinterDriver -Filter "__path like '%$($Printer.DriverName)%'" -ComputerName $Printserver
			
			$DriverVersion = " "
			$Driver = " "
			ForEach ($Driver in $Drivers) {
				$Drive = $Driver.DriverPath.Substring(0,1)
				$DriverVersion = (Get-ItemProperty ($Driver.DriverPath.Replace("$Drive`:","\\$PrintServer\$Drive`$"))).VersionInfo.ProductVersion
				$Driver = Split-Path $Driver.DriverPath -Leaf
			}
			
			$Drivername = $Printer.Drivername
			if (!$Drivername) {
				$Drivername = " "
			}
			$NoOutput = $command.Parameters.Add("printer_driver_name", $Drivername)
			
			$NoOutput = $command.Parameters.Add("printer_driver_version", $DriverVersion)
			$NoOutput = $command.Parameters.Add("printer_driver", $Driver)
			
			$command.ExecuteNonQuery()
		}
		Finally {
			if ($connection -ne $null) {
				$connection.Close()
				$connection.Dispose()
			}

			if ($command -ne $null) {
				$command.Dispose()
			}
		}
	} # End looping through each printer
} # End looping through each print server

Leave a Reply

Your email address will not be published.