Link to home
Start Free TrialLog in
Avatar of usslindstrom
usslindstromFlag for Japan

asked on

VBScript create 2 arrays from data and act on differences?

Experts,

I have a bit of a challenge ahead of me in the folliowing:

I have a VBScript that runs against machines and queries a SQL Server for which printers it should install based on it's ComputerName/Room Number.  It's actually a very slick ordeal, and we're going to be implementing the code through group policy on each and every startup of a PC.  - And the scripts very first task each time it runs is to actually delete any network mapped printers accross the board on computers.

Right there, is where you can see a potential problem that's going to stem from running the script on startup, in that in it's current form it can take approximately 30 seconds to finish the process of installing printers (some units have 4 printers flagged for installation, so it must run through the installation on all of them individually).  - Even if it's the exact same 4 printers that have already been installed previously.

-------------------------

What I'd like to do, is change the way the script behaves completely - and have VB query the list of currently installed printers (script courtesy of "The Scripting Guys"  http://blogs.technet.com/b/heyscriptingguy/archive/2007/07/16/how-can-i-list-the-network-printers-for-the-logged-on-user.aspx).

Then hit SQL server for results and act on discrepancies (delete/add printers to the local computer).

Below is the code for acting on SQL (mostly pieced together by previous questions here on EE).  Would anybody be able to point me in a direction that I need to start looking in order to make this "dream" a reality?

As usual, ANY assistance that can be provided is way more than appreciated!


SQLServer = wshShell.ExpandEnvironmentStrings( "%SiteSQLServer%" )
        SQLDatabase = "Printers"


        Const adCmdStoredProc = 4, adVarChar = 200, adParamInput = 1

        Dim cn, cmd, rs

            Set cn = CreateObject("ADODB.Connection")
            Set cmd = CreateObject("ADODB.Command")

                cn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & SQLServer & ";" & _
                                      "Trusted_Connection=Yes;Initial Catalog=" & SQLDatabase & ";"

            cn.CursorLocation = adUseClient
            cn.Open

        ComputerName = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )

            ComputerLocation = Split(ComputerName, "-")(0)
            ComputerRoomNumber = Split(ComputerName, "-")(1)


                Select Case ComputerType

                    Case "Administrative"
                        objLogFile.WriteLine "Processing global 'Administrative' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Admin"
                    Case "Classroom"
                        objLogFile.WriteLine "Processing global 'Classroom' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Classroom"
                    Case "Information Center"
                        objLogFile.WriteLine "Processing global 'Information Center' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_IC"
                    Case "Lab"
                        objLogFile.WriteLine "Processing global 'Lab' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Lab"
                    Case "Notebook"
                        objLogFile.WriteLine "Processing global 'Notebook' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Notebook"
                    Case "Netbook"
                        objLogFile.WriteLine "Processing global 'Netbook' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Netbook"
                    Case "Teacher"
                        objLogFile.WriteLine "Processing global 'Teacher' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Teacher"
                    Case "Utility"
                        objLogFile.WriteLine "Processing global 'Utility' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Utility"
                    Case "Virtual School"
                        objLogFile.WriteLine "Processing global 'Virtual School' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_VirtualSchool"
                    Case Else
                        objLogFile.WriteLine "Processing without global group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Global"
                End Select

            With cmd
                Set .ActiveConnection = cn
                    .CommandText = StoredProcedure
                    .CommandType = adCmdStoredProc
                    .Parameters.Append .CreateParameter("@ComputerRoomNumber", adVarChar, adParamInput, 100, ComputerRoomNumber)
                    .Parameters.Append .CreateParameter("@ComputerName", adVarChar, adParamInput, 100, ComputerName)

                    Set rs = .Execute
            End With

            Set cmd = Nothing

            Do While Not rs.EOF			

            . . .   INSTALLING PRINTER SCRIPT  . . .

Open in new window

Avatar of Bill Prew
Bill Prew

What are the columns in the rs record set, I'm assuming there is data in that set that you would compare to the installed printers to make decisions about what is already installed?

What attribute(s) would you be basing the "match" on?  Meaning, if a printer with the same name is already found on the computer, don't install?  Or does it need to get down to device checking, or network path checking, etc?

~bp
I'd suggest building a dictionary of the installed printers, and then you can check against it when reading the sql rs to see what is already installed.  This should give you the idea.

' Create needed objects
Set dicPrinters = CreateObject("Scripting.Dictionary")

' Identify all printers for this user
Set objWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2") 
Set colPrinters =  objWMI.ExecQuery("Select * from Win32_Printer") 
For Each objPrinter in colPrinters 
    If Not dicPrinters.Exists(objPrinter.Name) Then
        dicPrinters.Add objPrinter.Name, objPrinter.Description
    End If
Next
Set colPrinters = Nothing
Set objWMI = Nothing


        SQLServer = wshShell.ExpandEnvironmentStrings( "%SiteSQLServer%" )
        SQLDatabase = "Printers"


        Const adCmdStoredProc = 4, adVarChar = 200, adParamInput = 1

        Dim cn, cmd, rs

            Set cn = CreateObject("ADODB.Connection")
            Set cmd = CreateObject("ADODB.Command")

                cn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & SQLServer & ";" & _
                                      "Trusted_Connection=Yes;Initial Catalog=" & SQLDatabase & ";"

            cn.CursorLocation = adUseClient
            cn.Open

        ComputerName = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )

            ComputerLocation = Split(ComputerName, "-")(0)
            ComputerRoomNumber = Split(ComputerName, "-")(1)


                Select Case ComputerType

                    Case "Administrative"
                        objLogFile.WriteLine "Processing global 'Administrative' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Admin"
                    Case "Classroom"
                        objLogFile.WriteLine "Processing global 'Classroom' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Classroom"
                    Case "Information Center"
                        objLogFile.WriteLine "Processing global 'Information Center' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_IC"
                    Case "Lab"
                        objLogFile.WriteLine "Processing global 'Lab' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Lab"
                    Case "Notebook"
                        objLogFile.WriteLine "Processing global 'Notebook' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Notebook"
                    Case "Netbook"
                        objLogFile.WriteLine "Processing global 'Netbook' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Netbook"
                    Case "Teacher"
                        objLogFile.WriteLine "Processing global 'Teacher' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Teacher"
                    Case "Utility"
                        objLogFile.WriteLine "Processing global 'Utility' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Utility"
                    Case "Virtual School"
                        objLogFile.WriteLine "Processing global 'Virtual School' group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_VirtualSchool"
                    Case Else
                        objLogFile.WriteLine "Processing without global group scoped printers..."
                        StoredProcedure = "SP_PrinterMap_Global"
                End Select

            With cmd
                Set .ActiveConnection = cn
                    .CommandText = StoredProcedure
                    .CommandType = adCmdStoredProc
                    .Parameters.Append .CreateParameter("@ComputerRoomNumber", adVarChar, adParamInput, 100, ComputerRoomNumber)
                    .Parameters.Append .CreateParameter("@ComputerName", adVarChar, adParamInput, 100, ComputerName)

                    Set rs = .Execute
            End With

            Set cmd = Nothing

            Do While Not rs.EOF                 

            . . .   INSTALLING PRINTER SCRIPT  . . .
            ' in the loop do something like:
            ' if not objPrinters.Exists(rs.Name) then
               ' Install
            ' end if

Open in new window

~bp
Avatar of usslindstrom

ASKER

Thanks for jumping on my question here BillPrew.  You've helped me quite a bit in the past here on EE and it's always appreciated when you do.

Alright.  I think I see where you're going with this.  I have two questions at this point about implementation of that script.

1.  I'm going to be needing to target the machine level, versus having it enumerate against user logons (if that makes sense).  In your post you mention "user printers" - and in the comments section from the link I posted from 29 Sep 2010 was an individual that was describing what I'm fearing here, in that doing a script of that nature required a user account logging into PCs to be able to grab data.  I'm not 100% up to speed on this yet, becuase of right this second - I haven't tested anything yet.

2.  Working with the example script you gave me definately would solve the issue of adding printers on machines - but would you know how I would go about with logic on in the reverse direction?  Meaning, a PC that has a network printer installed that SQL server doesn't have listed for that particular unit - the script would then delete the printer in question.     I already have the script to delete network printers, so that's the the area I need, but I definately would need help with the logic of how to make that determination.

You asked what SQL was being returned to the PCs...  The SQL data is layed out in the following columns, and handed back to the PC when each unit executes the StoredProcedure, sending it's %ComputerName% and room number as variables:

        PrinterName,
        DriverFileName,
        DriverTextString,
        ModelNumber,
        Protocol,
        PortNumber,
        SNMPEnabled,
        ExtendedAttributes,
        Queue,
        DoubleSpool,
        IsDefault,
        PrinterSerialNumber,
        PrinterLocation,
        Manufacturer,
        IsColor
I'm not sure how we can get printers at the computer level versus the user level.  How do you install the printers so that all users see them, is it the technique mentioned here:

http://social.technet.microsoft.com/Forums/en-IE/ITCG/thread/321a2993-3886-43a8-8cb2-3f53b46890db

As far as (2) above, that did cross my mind after I posted, but we can work around that pretty easy. The simplest idea I can think of would be to just remove a printer from the dictionary when it is found in the sql results set, and then when all the sql printers have been processed, if there are any printers left in the dict, we delete them.

What attribute will you be matching on to determine if a printer is already installed?

~bp
That's exactly right...

Here's the installation command that installs the printers:

    'Driver information
        DriverLocation = "\\" & wshShell.ExpandEnvironmentStrings( "%SiteDSServer%" ) & "\eXpress\PAC_Printer\Drivers"
        BaseCommandString = "rundll32.exe printui.dll,PrintUIEntry"



And then when it gets results from SQL here's the command that gets run against units to install each printer.

                InstallPrinterCommand = BaseCommandString & " /u /b " & """" & strPrinterName & """" & " /n " & """" & strPrinterName & """" & " /m " & """" & strDriverTextString & """" & " /if /f " & """" & strDriverLocation & """" & " /r " & """" & strPrinterName

                    wshShell.Run InstallPrinterCommand,0,True


For the script to see if the printer's been installed already I would imagine that matching on PrinterName would be the best possible solution here, as it's probably the easiest and as they're IP based printers there's no possible chance to have two separate printers with the same name.
Try running this script on one of the computers involved where you will be checking the printers, and see what it displays.  From what I gather this should show all network printers not just the logged on user ones.

Set WshNetwork = WScript.CreateObject("WScript.Network")
Set oPrinters = WshNetwork.EnumPrinterConnections
WScript.Echo "Network printer mappings:"
For i = 0 to oPrinters.Count - 1 Step 2
   WScript.Echo "Port " & oPrinters.Item(i) & " = " & oPrinters.Item(i+1)
Next

Open in new window


~bp
It sure did.  It grabbed all printers too, so I'd imagine I could just tack on a filter to only include network printers.

:)  - Don't want to mess with people's AdobePDF or SnagIt Printing.  They're teachers and any that's 'taken away' even by accident is grounds for coimplaints.  :)
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry for the delayed response here...

Just trying to take in what you're trying to show me.  Give me a bit to fully understand it.

I'll keep you in the loop on testing...
I think we're getting an amazing product here.  Alright, here's where I'm at currently:

I've created the dictionary based on local printers, and the script is now smart enough to skip a unit if it's already installed.  That alone has dropped the processing time down to a VERY reasonable amount, and I'm feeling really good about being able to implement this in a production environment here.

That being said, I'm having some issues about loop processing when removing the item from the dictionary once they've been installed.  It's hard to explain, so please bear with me.

When I use the following lines of code, the script errors with

"Object doesn't support this property or method:  'objDictionaryPrinters.Delete' "



            If Not objDictionaryPrinters.Exists(rs.Fields("PrinterName").Value) Then

                *****INSTALL SCRIPT*****

                objDictionaryPrinters.Delete(rs.Fields("PrinterName").Value)

                objLogFile.WriteLine "Local printer removed from dictionary."

            Else

                objLogFile.WriteLine "    Printer " & rs.Fields("PrinterName").Value & " has been identified for installation, but is already present on this unit."

                objDictionaryPrinters.Delete(rs.Fields("PrinterName").Value)

                objLogFile.WriteLine "Local printer removed from dictionary."


The code block below is the log file output of when I comment out the code above about deleting.
Processing global 'Administrative' group scoped printers...
    Printer DPDO-103-383BA has been identified for installation, but is already present on this unit.
    Printer DPDO-103A-503CA has been identified for installation, but is already present on this unit.
    Printer DPDO-103C-383BA has been identified for installation and is not already present on this unit.  Installing...
        Driver location set to:  '\\DPDO-DS01\eXpress\PAC_Printer\Drivers\FS-3830n\oemsetup.inf'
        Driver listed in inf as:  'Kyocera Mita FS-3830N'
        Identified printer model:  'FS-3830n'
        Targeted registry keys:
            HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Print\Monitors\Standard TCP/IP Port\Ports\DPDO-103C-383BA\
            HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Print\Printers\DPDO-103C-383BA\
            HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\Windows NT\CurrentVersion\Print\Printers\DPDO-103C-383BA\
        Installing printer...
            Creating printer port...
                Port information:
                    Protocol version:  1
                    Port number:  9100
                    SNMP enabled:  1
                    Extended attributes:  0
                    Queue:  
                    Double spool:  
                Printer port created.
            Creating printer map...
                Executing the following command:  "rundll32.exe printui.dll,PrintUIEntry /u /b "DPDO-103C-383BA" /n "DPDO-103C-383BA" /m "Kyocera Mita FS-3830N" /if /f "\\DPDO-DS01\eXpress\PAC_Printer\Drivers\FS-3830n\oemsetup.inf" /r "DPDO-103C-383BA"
                Printer map created.
            Setting printer information...
                Executing the following command:  "rundll32.exe printui.dll,PrintUIEntry /Xs /n "DPDO-103C-383BA" Comment "Kyocera FS-3830n (B/W)  S/N: ABU5412565" Location "Room 103C""
                Printer information set.
                Processing duplex/stapling settings (if applicable)...
                    Printer not default with duplex/stapling features.
    Printer DPDO-103C-501CA has been identified for installation, but is already present on this unit.
 
Printers installed.

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That worked perfectly.  Thank you.

I think we're almost 100% done here.  I just need to work out how I'm going to filter network printers versus local printers.

Unfortunately, since they're IP based printers (Ports), the PC thinks they're all local vice network...
We *might* have to use some of the original code.  There is a "Local" attribute in that collection of printer info, and perhaps we could use that to identify the local printers?

I'd start by comparing the printer names returned from the first approach, to the names from the second approach and see how they compare.  If they do match up then we could remove the printers that are local from the dictionary based on that, before doing the "what printers do we need to delete" check.

Let me know if this doesn't make sense, of if you want some coding help.

~bp
With your help, this script is absolutely amazing now!

I've pasted the latest version in the code block below for anybody else that would like to learn from Mr. BillPrew.

You were exactly right with the fact it's going to be hard to tell the difference, and I still don't have any perfect solution.  Would you happen to have any ideas in this area?  - I started looking and came up with the following if/then, and it so far it filters local printers (Adobe being the exception, so I added that as it's own line item).

If InStr(1, objItem.Name, "Adobe") Then
    objLogFile.WriteLine "Not removing " & objItem.Name
ElseIf objDictionaryPrinters.Exists(objItem.Name) And objItem.Attributes > 64 And Not InStr(1, objItem.PortName, "LPT") And Not InStr(1, objItem.PortName, "USB") Then
    objLogFile.WriteLine "Deleting " & objItem.Name
    objItem.Delete_
Else
    objLogFile.WriteLine "Not removing " & objItem.Name
End If
****Create the local printer dictionary****

Sub CreateLocalPrinterDictionary

        LogSubStart

        objLogFile.WriteLine "Sub-Routine handle:  CreateLocalPrinterDictionary"
        LogLineBreak
        objLogFile.WriteLine "Creating localized printer dictionary..."

    Set objPrinters = wshNetwork.EnumPrinterConnections

        objLogFile.WriteLine "    This unit currently has the following printer(s) installed:"

    For i = 0 to objPrinters.Count - 1 Step 2
        If Not objDictionaryPrinters.Exists(objPrinters.Item(i+1)) Then
            objDictionaryPrinters.Add objPrinters(i+1), objPrinters.Item(i)
            objLogFile.WriteLine "        " & objPrinters.Item(i)
        End If
    Next

        LogLineBreak
        objLogFile.WriteLine "Dictionary created."

        LogSubEnd

End Sub


****IF/THEN PRIOR TO PRINTER INSTALLATION****

            If Not objDictionaryPrinters.Exists(rs.Fields("PrinterName").Value) Then


****PRINTER REMOVAL****

Sub PrinterRemoval

        LogSubStart

        objLogFile.WriteLine "Sub-Routine handle:  PrinterRemoval"
        LogLineBreak
        objLogFile.WriteLine "Removing any previously mapped network printers not assigned by SQL..."
        LogLineBreak

        objLogFile.WriteLine "    Processing the following changes to locally installed printers:"

            Set colItems = objWMIService.ExecQuery("Select * from Win32_Printer",,48)
                For Each objItem in colItems

                    If InStr(1, objItem.Name, "Adobe") Then

                        objLogFile.WriteLine "        Not removing " & objItem.Name

                    ElseIf objDictionaryPrinters.Exists(objItem.Name) And objItem.Attributes > 64 And Not InStr(1, objItem.PortName, "LPT") And Not InStr(1, objItem.PortName, "USB") Then

                        objLogFile.WriteLine "        Deleting " & objItem.Name

                        objItem.Delete_

                    Else

                        objLogFile.WriteLine "        Not removing " & objItem.Name

                    End If

            Next

        LogLineBreak
        objLogFile.WriteLine "Processed printer removal(s)."

        LogSubEnd

  End Sub

Open in new window

Here's a logfile output of the script...
Initializing Sub-Routine at 9/12/2011 5:24:43 PM.
 
Sub-Routine handle:  PrinterMap
 
Processing global 'Administrative' group scoped printers...
    Printer DPDO-103-383BA has been identified for installation, but is already present on this unit.
        Printer removed from locally created dictionary.
    Printer DPDO-103A-503CA has been identified for installation, but is already present on this unit.
        Printer removed from locally created dictionary.
    Printer DPDO-103C-383BA has been identified for installation, but is already present on this unit.
        Printer removed from locally created dictionary.
    Printer DPDO-103C-501CA has been identified for installation, but is already present on this unit.
        Printer removed from locally created dictionary.
 
Printers installed.
 
Process Completed at 9/12/2011 5:24:43 PM.
---------------------------------------------------------------------
Initializing Sub-Routine at 9/12/2011 5:24:43 PM.
 
Sub-Routine handle:  PrinterRemoval
 
Removing any previously mapped network printers not assigned by SQL...
 
    Processing the following changes to locally installed printers:
        Not removing SnagIt 8
        Not removing Send To OneNote 2007
        Not removing PrimoPDF
        Not removing Microsoft XPS Document Writer
        Not removing Microsoft Office Document Image Writer
        Not removing DPDO-103C-501CA
        Not removing DPDO-103C-383BA
        Not removing DPDO-103A-503CA
        Not removing DPDO-103-383BA
        Not removing Adobe PDF
 
Processed printer removal(s).
 
Process Completed at 9/12/2011 5:24:43 PM.

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for the tip on the printer port names to exclude.  I've added them to the If/Then logic...

For the Attribute > 64 it was actually the first correlation I could make on software -vs- hardware printers.

All of the Microsoft printers, as well as PrimoPDF and SnagIt all had exactly 64 attributes, where as all the physical printers (minus Adobe) had upwards of 512-4K.  I imagine that's just the number of options available to the OS for printing, and it would make sense that software printers have less than hardware ones...  So I threw it on the filter.

I like your idea on 'not' actually removing any printers in the first test stage of this script, and just using it to document what printers would have been removed...  Then we can take samplings around the enterprise and find out if there was going to be any horrible damage done by the script.

It really is a pleasure to work with you billprew.  I can't thank you enough for assisting me in this scripting solution.
If InStr(1, objItem.Name, "Adobe") Then
    objLogFile.WriteLine "Not removing " & objItem.Name
ElseIf objDictionaryPrinters.Exists(objItem.Name) And objItem.Attributes > 64 And Not InStr(1, objItem.PortName, "LPT") And Not InStr(1, objItem.PortName, "USB") And Not InStr(1, objItem.PortName, "nul") And Not InStr(1, objItem.PortName, "XPS") And Not InStr(1, objItem.PortName, "FILE") And Not InStr(1, objItem.PortName, "SHRFAX") And Not InStr(1, objItem.PortName, " My Documents") Then
    objLogFile.WriteLine "Deleting " & objItem.Name
    objItem.Delete_
Else
    objLogFile.WriteLine "Not removing " & objItem.Name
End If

Open in new window

Outstanding assistance by BillPrew.

He is an amazing asset to EE!
Just FYI, take a look at this page documenting the win32_printers data structure, and scroll down just a bit to see the Attributes element. The deal with it's values is that they are bit mapped flags.  So for example if you saw an Attribute value of 12 that would equal 4 + 8 and would mean "default" and "shared". Don't put a lot of stock int eh"local" and "network" flags though, they don't seem to be accurate.

http://msdn.microsoft.com/en-us/library/aa394363%28v=vs.85%29.aspx

Glad to be of some help, I've enjoyed the interaction with you as well, you've done your share in the exchange and been patient.

~bp