At work, I am moving around a bunch of systems on the network, most of which are file servers and print servers. So, since the network is so large, and most of the stuff was not setup to best practices, and many systems are mapped manually, I needed a way to enumerate what printers and drives that people were using. I figure this information will be used shortly after to create a ‘update mapping’ script, that will un-map old printers and re-map them to their new locations (as well as drives).
The other idea I had was to use the database to automatically put users into security groups for their assigned printers and / or drives, and then update the logon scripts to map those printers based upon security group enumeration.
Either way, all of this is designed around a plan to be able to replace servers on the network without any end-user interaction.
Here is the version 1.0 of the drive-printer enumeration script to SQL server, but see below for pre-requisites, you’ll have to create a new database and table, as well as setup usernames and passwords on your sql server for the script to use. I recommend setting the username/password and giving it just db_Writer to the database (not sys_admin):
' Script to enumerate drives and printers from client workstations
' Created: 5/21/2010
' By: Michael Glaske
On Error Resume Next
Dim objWMIService, objItem, colItems
Dim objDrives, objPrinters
' Open SQL Connection
sqlServer = "<SQL Server>"
sqlConnString = "provider=sqloledb;data source=" & sqlServer & ";initial catalog=<Database>"
Set openConn = CreateObject("ADODB.Connection")
openConn.Open sqlConnString, "<username>", "<password>"
Set openRS = CreateObject("ADODB.Recordset")
' Create Object, and enumerate drives
For i = 0 to objDrives.Count -2 Step 2
enumString = objNetwork.ComputerName & ",D," & objDrives(i) & "," & objDrives(i+1)
sqlStatement = "insert into wsTrack(workstation,username,type,date,drive,uncPath) values ('" & objNetwork.ComputerName & _
"', '" & objNetwork.UserName & "', 'D', '" & now() & "','" & objDrives(i) & "', '" & objDrives(i+1) & "')"
openRS.Open sqlStatement, openConn
' Enumerate printer information from WMI, because there is more detail
Set objWMIService = GetObject("winmgmts:\\" & objNetwork.ComputerName & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_Printer")
For Each objItem In colItems
enumString = objNetwork.ComputerName & ",P," & objItem.DeviceID & _
"," & objItem.ShareName & "," & objItem.DriverName & _
"," & objItem.PrinterStatus & "," & objItem.Description & _
"," & objItem.Location & "," & objItem.Status & _
"" & VbCr
sqlStatement = "insert into WSTrack(workstation,username,type,date,printer,uncPath,pStatus,pDescription,pLocation,pDriver) values ('" _
& objNetwork.ComputerName & "', '" & objNetwork.UserName & "', 'P', '" & now() & "', '" & objItem.DeviceID & "', '" & objItem.ShareName & "', '" _
& objItem.PrinterStatus & "', '" & objItem.Description & "', '" & objItem.Location & "', '" & objItem.DriverName & "')"
openRS.Open sqlStatement, openCOnn
Here is the SQL table definition:
/****** Object: Table [dbo].[wsTrack] Script Date: 05/21/2012 14:13:14 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[wsTrack](
[workstation] [nvarchar](50) NOT NULL,
[username] [nvarchar](150) NULL,
[type] [nchar](1) NOT NULL,
[date] [datetime] NOT NULL,
[drive] [nvarchar](3) NULL,
[uncPath] [nvarchar](100) NULL,
[printer] [nvarchar](100) NULL,
[pStatus] [nvarchar](100) NULL,
[pDescription] [nvarchar](150) NULL,
[pLocation] [nvarchar](150) NULL,
[pDriver] [nvarchar](150) NULL
) ON [PRIMARY]