Finding out whats there on the network.


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
' Updated:
'
On Error Resume Next
Dim objWMIService, objItem, colItems
Dim objDrives, objPrinters
Dim objNetwork
Dim enumString,i
Dim sqlServer,sqlConnString,openConn,openRS,sqlStatement

'
' 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
'
Set objNetwork=WScript.CreateObject("WScript.Network")
Set objDrives=objNetwork.EnumNetworkDrives

For i = 0 to objDrives.Count -2 Step 2
enumString = objNetwork.ComputerName & ",D," & objDrives(i) & "," & objDrives(i+1)
'wScript.echo enumstring
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
Next

'
' 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
'Wscript.echo enumString
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
Next

Here is the SQL table definition:


USE [dpTrackDB]
GO
/****** Object: Table [dbo].[wsTrack] Script Date: 05/21/2012 14:13:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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]

Advertisements

About mglaske

Systems Engineer for Limelight Networks
This entry was posted in Scripting and Automation and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s