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
		$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
			$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)
		Finally {
			if ($connection -ne $null) {

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

Using VBScript to run Excel macro

I have a folder containing several Excel files, each containing a macro named “FETCH_LATEST_DATA”. The following steps will detail how I had scheduled a VBScript to run these Excel macros nightly.

First, I created the VBScript below that opens each Excel file successively and calls the particular macro. Note that I have disabled alerts and events since I intended to run it as an unattended job, and that I am using “Wscript.Echo” to output some start/end info for debugging purposes should something go wrong in the future.

excelFolder = "\\fileserver\share\folder"

Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder(excelFolder)

Set excelApp = CreateObject("Excel.Application")
excelApp.DisplayAlerts = False
excelApp.EnableEvents = False

for each objFile In objFso.GetFolder(excelFolder).files
	Wscript.Echo "Starting " & objFile.name & " " & now
	filename = objFile.name
	Set excelBook = excelApp.Workbooks.Open(excelFolder & "\" & filename)

	If excelBook.ReadOnly Then
		Wscript.Echo "Readonly mode detected, skipping"
		excelApp.Run "FETCH_LATEST_DATA"
	End if

	Wscript.Echo "Ending " & objFile.name & " " & now
	excelBook.Close False
	set excelBook = Nothing

Set excelApp = Nothing
set objFso = nothing

Next, I inserted a line in my already-existing batch file that contains all the other jobs that I typically run on this Windows server every evening. Note that I am using “>>” to direct any console output (see “Wscript.Echo” above) to a log file should there be any debugging needs.

C:\windows\syswow64\cscript.exe C:\scripts\update_excel.vbs >> update_excel.log

Before we can run Excel unattended, we must create these two folders. If these two folders are missing, the scheduled job would sit perpetually in Windows Task Scheduler as if running, but it would never actually complete; in the way that the logging info is setup above, you would see something like “Starting file1.xlsm 6/20/2019 10:15:00 AM” without any additional lines being logged. If your Windows server is running in 32-bit mode, you can ignore the second folder.



Running VBScript in 32-bit mode on a 64-bit Windows machine

I have a legacy VBScript program that has been recently been migrated to a new 64-bit Windows server by a system administrator due to a policy set by a higher power. By default, VBScript executed in 64-bit Windows automatically runs as a 64-bit program. Because I have reasons to have this program run in 32-bit mode, I modified my batch file to execute my program in a different way.

This is the BEFORE picture. When this batch file is run in 64-bit Windows, it will execute in 64-bit mode.

time /t
cscript ThisIsMyVbscriptProgram.vbs
time /t

Below is the AFTER picture. Note that I am passing my script into a new command prompt environment in the Windows SYSWOW64 folder; this new environment is strictly in 32-bit mode.

time /t
%WINDIR%SYSWOW64cmd.exe /c cscript ThisIsMyVbscriptProgram.vbs
time /t

As a side note: WOW stands for “Windows on Windows”.

Batch file to create regular backups with cleanup

I run a version of the Windows batch script below to create a regular backup of one of the folders that contains my daily work; for this illustration, we will call the source folder c:source. Every night, this batch file is triggered by Windows Scheduled Tasks to create a folder in my backup drive with the timestamp in the folder name; let us call this target folder D:target. Before creating this daily backup, the script also checks to see how many backup sets are already existing in the backup folder, and if above the threshhold, the oldest one(s) are deleted; this feature helps making sure the space usage of redundant backup sets do not get out of control. When scheduling this job with Windows Scheduled Tasks, you may wish to output to a file as well to create a log file of what old folders were removed and what files were copied (eg. “c:scriptsbackup.bat > logfile.log”).

@echo off

:: Start Variables

set NumberToKeep=5
set BackupCmd=xcopy /s /c /d /e /h /i /r /y

set BackupSource=C:\source\
set BackupTarget=D:\target\

:: End Variables

:: Actual Script Starts Here!
echo %date% %time%

:: 1. Delete older backup set(s) beyond the NumberToKeep
for /F "tokens=* skip=%NumberToKeep%" %%I In ('dir "%BackupTarget%" /AD /B /O-D /TW') do (
	echo DELETING OLD BACKUP SET %BackupTarget%%%~I
	rd /s /q "%BackupTarget%%%~I"

:: 2. Create new backup set
set bkuphour=%time:~0,2%
if "%bkuphour:~0,1%"==" " set bkuphour=0%time:~1,1%
set bkupfldr=%date:~10,4%_%date:~4,2%_%date:~7,2%_%bkuphour%_%time:~3,2%

echo CREATING FOLDER %BackupTarget%%bkupfldr%\
if not exist "%BackupTarget%%bkupfldr%\" mkdir "%BackupTarget%%bkupfldr%\"

%BackupCmd% "%BackupSource%*.*" "%BackupTarget%%bkupfldr%\"

echo %date% %time%

Hope you will find this useful!

Logging Windows events with VBScript

The VBScript code to perform the insertion into the event log is shown below. Please note that the “event_type” input variable will only take the following three values, in upper case letters:

  • “ERROR” – Use when logging a problem
  • “WARN” – Use when logging a minor issue
  • “INFO” – Use when logging miscellaneous information

When we run the example code further below, we will find the log as seen here.

Example Code

' Description: This sample code demonstrates how to log information 
'       to the Windows application event log
' Author: C. Peter Chen, http://dev-notes.com
' Note: Allowable event_type values:
'         "ERROR" - Use when logging a problem
'         "WARN"  - Use when logging a minor issue
'         "INFO"  - Use when logging miscellaneous information
' Revision History:
'	1.0	20090310	Original release
option explicit

sub logWindowsEventViewer(event_type, event_text)
	dim WshShell, tp
	select case event_type
	case "ERROR"
		tp = "1"
	case "WARN"
		tp = "2"
	case "INFO"
		tp = "4"
	case else
		tp = "0"
	end select

	set WshShell = CreateObject("WScript.Shell")
	wshshell.Logevent tp, event_text
	set wshshell=nothing
end sub

' Usage example:
dim event_type, event_text
event_type = "ERROR"
event_text = "Application error: GUI crashed!"

call logWindowsEventViewer(event_type, event_text)

Checking for unauthorized local Windows administrator group members with VBScript

The only item to configure is the arrRealAdmins array of strings, where you may put in a list of user names that you do not wish to show in the report. The example already include two common administrator names that should be valid, “Administrator” and “Domain Administrators”.

Note that the sample code below outputs the invalid local administrator group members in a msgbox() pop-up box. You may wish to substitute this output method with something that may be more useful to you, such as outputting them to a report, write into a database, send email, etc.

' List local admin users                                        '
'                                                               '
' Description: Finds a list of local admin users on a Windows   '
'     machine                                                   '
' Author: C. Peter Chen, http://dev-notes.com                   '
' Version Tracker:                                              '
'       1.0   20081021   Base version                           '

option explicit
dim arrRealAdmins

' Configuration '
arrRealAdmins = Array("Administrator","Domain Admins")  ' List of users that *are* supposed to be administrators; we'll ignore these people later

' End configuration '

dim adminGroup, groupMember, ret

function isPermitedAdmin(MemberName)
	dim i
	for i = lbound(arrRealAdmins) to ubound(arrRealAdmins)
		if ucase(MemberName) = ucase(arrRealAdmins(i)) then
			isPermitedAdmin = true
			exit function
		end if

	isPermitedAdmin = false
end function

set adminGroup = getObject("WinNT://./Administrators, group")
for each groupMember in adminGroup.members
	if not isPermitedAdmin(groupMember.name) then
    		ret = ret & groupMember.name & ","
	end if

if ret = "" then
	msgbox("No invalid local administrators found.")
	ret = mid(ret, 1, len(ret)-1) ' To get rid of the last comma
	msgbox("The following users are in the local admin group: " & vbcrlf & ret)
end if

Format Windows command line date

The need for me to format date in the Windows command line environment came up when I needed to write a batch file to automatically copy the latest copies of certain files in a large folder. The files are named in the format of ???YYYYMMDD.txt, which presented itself as one easy way for me to query for latest files.

Built in to Windows command line is the %date% variable, which displays the system date as follows based on my regional setting.

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:>echo %date%
Wed 10/08/2008

Please note that your regional settings might be different. For example, for those in Australia, the date might be presented in a DD/MM/YYYY format rather than the typical US MM/DD/YYYY format. If this is the case, you may wish to adjust the code below accordingly.

To suit my needs, I need to format this date to YYYYMMDD. Knowing that %date%’s format is consistent in format, we can just parse it as a string.

C:>echo %date:~10,4%

C:>echo %date:~4,2%

C:>echo %date:~7,2%

Bringing it together, I will have the YYYYMMDD format I am looking for.

C:>echo %date:~10,4%%date:~4,2%%date:~7,2%

Once again, you may need to adjust the above command based on your own regional settings.

Here is how I put this code in action in the form of a batch file that copied only the files with today’s date.

set backupcmd=c:windowssystem32xcopy.exe /c /d /e /h /r /y
set dt=%date:~10,4%%date:~4,2%%date:~7,2%
%backupcmd% C:stuff*%dt%.txt D:backupstuff

Set up a log off script to do backup

One possibility for the Windows log off script is to copy some data from the machine being logged off (perhaps being shut down?) to another for backup purposes. For example, I grab my Firefox settings and make a copy of it at “D:\systemBackup\windows\mozilla\”. You may wish to expand this to include others; maybe your FTP program’s configuration file that is found in “C:\Program Files”? Or progress of your favorite games? Below is what I have in my log off script, which is written as a batch file.

@echo off
set backupcmd=xcopy /c /d /e /h /r /y

echo Backing up Firefox settings...
%backupcmd% "C:\Documents and Settings\me\Application Data\Mozilla\*.*" "D:\systemBackup\windows\mozilla\"

echo Backing up my docs...
%backupcmd% "C:\docs\*.*" "D:\docs\*.*"

echo Backing up a file...
%backupcmd% "C:\john\doe.txt" "D:\john\doe.txt"

Note that in this example, I made use of the xcopy command found in all versions of Windows. The switches I used with the command allow us to copy only those files that had been changed, so the amount of time required to copy files may not be so much.

To set up this log off script, type in “c:\windows\system32\gpedit.msc” in your Run dialogue and click OK. Go to the User Configuration-Scripts (Logon/Logoff) section and add your script. This script will now fire the next time you log off.

Checking admin share free space and folder space usage

The code to perform these two space checks is below, with the output file being generated in comma-delimited CSV format. When we use this, we need to first set a few configuration items in the first section.

  • outputFile : Enter the path and file name of the output report.
  • i : The number of items you wish to check; in the example, the number is 4 (2 drives and 2 folders)
  • arrayList(x, y) : These are the details of each drive/folder you wish to check…

The code is as follows:

option explicit
dim outputFile, arrayList, fso, obj, fl, j, drivepath, driveName, status, msg

' Where do you want to output the results to?
outputFile = "checkSpace.log"

' How many drives do you want to check for free space?
const i = 4
redim arrayList(i,5) 'Please do not touch this line.

' List the server drives you want to check for free space or folders to check for space usage
' Each set has 5 values:
' a. D=drive, F=folder
' b. server hostname
' c. admin share drive letter (applicable for type D only)
' d. warning level in gb
' e. alarm level in gb
' f. common name of this share

arrayList(0,0) = "D"
arrayList(0,1) = "web1"
arrayList(0,2) = "c"
arrayList(0,3) = 10
arrayList(0,4) = 5
arrayList(0,5) = "Windows 2003 web server, C-drive"

arrayList(1,0) = "D"
arrayList(1,1) = "exch2"
arrayList(1,2) = "d"
arrayList(1,3) = 200
arrayList(1,4) = 100
arrayList(1,5) = "Windows 2003 Exchange server, D-drive"

arrayList(2,0) = "F"
arrayList(2,1) = "\fileserverpublicfileShare1"
arrayList(2,2) = ""
arrayList(2,3) = 1
arrayList(2,4) = 2
arrayList(2,5) = "File share 1"

arrayList(3,0) = "F"
arrayList(3,1) = "\fileserverpublicfileShare2"
arrayList(3,2) = ""
arrayList(3,3) = 100
arrayList(3,4) = 200
arrayList(3,5) = "File share 2"

'''''''End Config'''''''''

set fso = CreateObject("Scripting.FileSystemObject")
set fl = fso.CreateTextFile(outputFile, true)


j = 0
do while j <= i-1
	if arrayList(j,0) = "D" then
		drivepath = "\\" & arrayList(j,1) & "\" & arrayList(j,2) & "$"
		set obj = fso.GetDrive(fso.GetDriveName(drivepath))
	elseif arrayList(j,0) = "F" then
		drivepath = arrayList(j,1)
		set obj = fso.GetFolder(drivepath)
		' shouldn't really get in here...
	end if
	driveName = arrayList(j,5)
	if arrayList(j,0) = "D" then
		if round(obj.FreeSpace/1024/1024/1024) < arrayList(j,4) then
			status = "alarm"
			msg = drivepath & " (" & driveName & ") only has " & round(obj.FreeSpace/1024/1024/1024) & "gb free"
			if round(obj.FreeSpace/1024/1024/1024) < arrayList(j,3) then
				status = "warning"
				msg = drivepath & " (" & driveName & ") only has " & round(obj.FreeSpace/1024/1024/1024) & "gb free"
				status = "ok"
				msg = drivepath & " (" & driveName & ") is ok with " & round(obj.FreeSpace/1024/1024/1024) & "gb free"
			end if
		end if
	elseif arrayList(j,0) = "F" then
		if round(obj.size/1024/1024/1024) > arrayList(j,4) then
			status = "alarm"
			msg = drivepath & " (" & driveName & ") has reached " & round(obj.size/1024/1024/1024) & "gb"
		elseif round(obj.size/1024/1024/1024) > arrayList(j,3) then
			status = "warning"
			msg = drivepath & " (" & driveName & ") has reached " & round(obj.size/1024/1024/1024) & "gb"
			status = "ok"
			msg = drivepath & " (" & driveName & ") is ok at " & round(obj.size/1024/1024/1024) & "gb used"
		end if
		status = "error"
		msg = "Configuration error"
	end if
	fl.writeline("""" & drivepath & """,""" & status & """,""" & msg & """")
	set obj = nothing
	j = j+1

set fl=nothing
set fso=nothing

The output CSV file should look something like this.

"\\web1\c$","ok","\\web1\c$ (Windows 2003 web server, C-drive) is ok with 10gb free"
"\\exch2\c$","alarm","\\exch2\c$ (Windows 2003 Exchange server, D-drive) only has 16gb free"
"\\fileserver\public\fileShare1","warning","\\fileserver\public\fileShare1 (File share 1) has reached 2gb"
"\\fileserver\public\fileShare2","ok","\\fileserver\public\fileShare2 (File sahre2) is ok at 91gb used"