Why PowerShell "print server inventory script" is not working?

Hi

I found a script that is running against print servers to pull data like
(system name, sharename, portname, IP address, MAC address, location, comment, drivername,driver type, printprocessor, datatype, status, published)

I would like this script to pull print server name from .txt file and export result to cvs file.

Would you please assist?

servers.txt includes:

printserver1
printserver2
inventory.txt
michalek19Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

michalek19Author Commented:
Here is an error that what i got
The term '.\printser.ps1' is not recognized as the name of a cmdlet, function,
script file, or operable program. Check the spelling of the name, or if a path
was included, verify that the path is correct and try again.
At line:1 char:15
+ .\printser.ps1 <<<<
    + CategoryInfo          : ObjectNotFound: (.\printser.ps1:String) [], Comm
   andNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException
michalek19Author Commented:
when i ran this on Powershel v.3 i got this error

At C:\Temp\Scripts\test.ps1:3 char:40
+ foreach ($Printserver in $Printservers)
+                                        ~
Missing statement body in foreach loop.
    + CategoryInfo          : ParserError: (:) [], ParseException
    + FullyQualifiedErrorId : MissingForeachStatement
RobSampsonCommented:
Hi, try this revised code
# Set print server name
$Printservers = Get-Content -path c:\temp\servers.txt
foreach ($Printserver in $Printservers) {

    # Create new Excel workbook
    $excel = New-Object -COM Excel.Application
    $Excel.visible = $True
    $Excel = $Excel.Workbooks.Add()
    $Sheet = $Excel.Worksheets.Item(1)
    $Sheet.Cells.Item(1,1) = “Printer Name”
    $Sheet.Cells.Item(1,2) = “Location”
    $Sheet.Cells.Item(1,3) = “Comment”
    $Sheet.Cells.Item(1,4) = “IP Address”
    $Sheet.Cells.Item(1,5) = “Driver Name”
    $Sheet.Cells.Item(1,6) = “Modell”
    $intRow = 2
    $WorkBook = $Sheet.UsedRange
    $WorkBook.Font.Bold = $True

    # Get printer information
    $Printers = Get-WMIObject Win32_Printer -computername $Printserver
    foreach ($Printer in $Printers)
    {
        $Sheet.Cells.Item($intRow, 1) = $Printer.Name
        $Sheet.Cells.Item($intRow, 2) = $Printer.Location
        $Sheet.Cells.Item($intRow, 3) = $Printer.Comment
        $Ports = Get-WmiObject Win32_TcpIpPrinterPort -computername $Printserver
        foreach ($Port in $Ports)
        {
            if ($Port.Name -eq $Printer.PortName)
            {
                $Sheet.Cells.Item($intRow, 4) = $Port.HostAddress
            }
        }
        $Sheet.Cells.Item($intRow, 5) = $Printer.DriverName
        $Sheet.Cells.Item($intRow, 6) = $Printer.Modell
        $Sheet.Cells.Item($intRow, 7) = $Printer.ShareName
        $intRow = $intRow + 1
    }

    $WorkBook.EntireColumn.AutoFit()
    $intRow = $intRow + 1
    $Sheet.Cells.Item($intRow,1).Font.Bold = $True
    $Sheet.Cells.Item($intRow,1) = “Print server inventory"
}
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
Remove-Variable excel

Open in new window


Rob.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

David Johnson, CD, MVPOwnerCommented:
$excel = New-Object -ComObject Excel.Application
vice
    $excel = New-Object -COM Excel.Application
also added best practice of using indentation

also you were missing the { } in the foreach loop
At C:\Temp\Scripts\test.ps1:3 char:40
+ foreach ($Printserver in $Printservers)
+                                        ~
Missing statement body in foreach loop.

# Set print server name
$Printservers = Get-Content -path c:\temp\servers.txt

foreach ($Printserver in $Printservers){

    # Create new Excel workbook
    $excel = New-Object -ComObject Excel.Application
    $Excel.visible = $True
    $Excel = $Excel.Workbooks.Add()
    $Sheet = $Excel.Worksheets.Item(1)
    $Sheet.Cells.Item(1,1) = “Printer Name”
    $Sheet.Cells.Item(1,2) = “Location”
    $Sheet.Cells.Item(1,3) = “Comment”
    $Sheet.Cells.Item(1,4) = “IP Address”
    $Sheet.Cells.Item(1,5) = “Driver Name”
    $Sheet.Cells.Item(1,6) = “Model”
    $Sheet.Cells.Item(1,7) = “Share Name”
    $intRow = 2
    $WorkBook = $Sheet.UsedRange
    $WorkBook.Font.Bold = $True

    # Get printer information
    $Printers = Get-WMIObject Win32_Printer -computername $Printserver
    foreach ($Printer in $Printers)
        {
        $Sheet.Cells.Item($intRow, 1) = $Printer.Name
        $Sheet.Cells.Item($intRow, 2) = $Printer.Location
        $Sheet.Cells.Item($intRow, 3) = $Printer.Comment
        $Ports = Get-WmiObject Win32_TcpIpPrinterPort -computername $Printserver
        foreach ($Port in $Ports){
            if ($Port.Name -eq $Printer.PortName)
                {
                $Sheet.Cells.Item($intRow, 4) = $Port.HostAddress
                }
            }
        $Sheet.Cells.Item($intRow, 5) = $Printer.DriverName
        $Sheet.Cells.Item($intRow, 6) = $Printer.Modell
        $Sheet.Cells.Item($intRow, 7) = $Printer.ShareName
        $intRow = $intRow + 1
        }

    $WorkBook.EntireColumn.AutoFit()
    $intRow = $intRow + 1
    $Sheet.Cells.Item($intRow,1).Font.Bold = $True
    $Sheet.Cells.Item($intRow,1) = “Print server inventory"
}

Open in new window

michalek19Author Commented:
Hi Rob

The report is generating but i am getting this error and also column F is not aline with column G.  Please check attachment.


Exception setting "Item": "Exception from HRESULT: 0x800AC472"
At C:\temp\Scripts\prinser.ps1:32 char:34
+                 $Sheet.Cells.Item <<<< ($intRow, 4) = $Port.HostAddress
    + CategoryInfo          : NotSpecified: (:) [], SetValueInvocationExceptio
   n
    + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertySetValu
   eTI

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

How  difficult would be to modify this script to overwrite location and comment entry for each printer objects on print servers. Would that be possible to  use this generated report and change information in these two " location and comment" and apply it back using powershell script.

Thx, Michal
result.xlsx
michalek19Author Commented:
Also, would that be possible to automatically  save each generate excel file  from each print server and save it as the name of the print server
RobSampsonCommented:
Hi, try this revision, and it should write a new excel file per print server.

# Set print server name
$Printservers = Get-Content -path c:\temp\servers.txt
# Create new Excel workbook
$objExcel = New-Object -COM Excel.Application
$objExcel.visible = $True
foreach ($Printserver in $Printservers) {

    $Excel = $objExcel.Workbooks.Add()
    $Sheet = $Excel.Worksheets.Item(1)
    $Sheet.Cells.Item(1,1) = “Printer Name”
    $Sheet.Cells.Item(1,2) = “Location”
    $Sheet.Cells.Item(1,3) = “Comment”
    $Sheet.Cells.Item(1,4) = “IP Address”
    $Sheet.Cells.Item(1,5) = “Driver Name”
    $Sheet.Cells.Item(1,6) = “Share Name”
    $intRow = 2
    $WorkBook = $Sheet.UsedRange
    $WorkBook.Font.Bold = $True

    # Get printer information
    $Printers = Get-WMIObject Win32_Printer -computername $Printserver
    foreach ($Printer in $Printers)
    {
        $Sheet.Cells.Item($intRow, 1) = $Printer.Name
        $Sheet.Cells.Item($intRow, 2) = $Printer.Location
        $Sheet.Cells.Item($intRow, 3) = $Printer.Comment
        $Ports = Get-WmiObject Win32_TcpIpPrinterPort -computername $Printserver
        foreach ($Port in $Ports)
        {
            if ($Port.Name -eq $Printer.PortName)
            {
                $Sheet.Cells.Item($intRow, 4) = $Port.HostAddress.ToString()
            }
        }
        $Sheet.Cells.Item($intRow, 5) = $Printer.DriverName
        $Sheet.Cells.Item($intRow, 6) = $Printer.ShareName
        $intRow = $intRow + 1
    }

    $WorkBook.EntireColumn.AutoFit()
    $intRow = $intRow + 1
    $Sheet.Cells.Item($intRow,1).Font.Bold = $True
    $Sheet.Cells.Item($intRow,1) = “Print server inventory"
    $Excel.SaveAs("$PrintServer.xlsx", $True)
    $Excel.Close($True)
}
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
Remove-Variable excel
Remove-Variable objExcel

Open in new window


As for writing back a new value, how would you supply those values?  Maybe you could use a second script to read the Excel file and write the values back to the printer?

Rob.
michalek19Author Commented:
That would be great , do you think you can help me with this? Do i need to open another request? please advice
RobSampsonCommented:
I can write a script to do the reverse.  I've made a couple of minor changes to the inventory script, so use this one.

# Set print server name
$Printservers = Get-Content -path c:\temp\servers.txt
$CurrentFolder = Split-Path -Parent $MyInvocation.MyCommand.Definition
# Create new Excel workbook
$objExcel = New-Object -COM Excel.Application
$objExcel.visible = $True
foreach ($Printserver in $Printservers) {

    $Excel = $objExcel.Workbooks.Add()
    $Sheet = $Excel.Worksheets.Item(1)
    $Sheet.Cells.Item(1,1) = “Printer Name”
    $Sheet.Cells.Item(1,2) = “Location”
    $Sheet.Cells.Item(1,3) = “Comment”
    $Sheet.Cells.Item(1,4) = “IP Address”
    $Sheet.Cells.Item(1,5) = “Driver Name”
    $Sheet.Cells.Item(1,6) = “Share Name”
    $Sheet.Cells.Item(1,7) = “Device ID”
    $intRow = 2
    $WorkBook = $Sheet.UsedRange
    $WorkBook.Font.Bold = $True

    # Get printer information
    $Printers = Get-WMIObject Win32_Printer -computername $Printserver
    foreach ($Printer in $Printers)
    {
        $Sheet.Cells.Item($intRow, 1) = $Printer.Name
        $Sheet.Cells.Item($intRow, 2) = $Printer.Location
        $Sheet.Cells.Item($intRow, 3) = $Printer.Comment
        $Ports = Get-WmiObject Win32_TcpIpPrinterPort -computername $Printserver
        foreach ($Port in $Ports)
        {
            if ($Port.Name -eq $Printer.PortName)
            {
                $Sheet.Cells.Item($intRow, 4) = $Port.HostAddress.ToString()
            }
        }
        $Sheet.Cells.Item($intRow, 5) = $Printer.DriverName
        $Sheet.Cells.Item($intRow, 6) = $Printer.ShareName
        $Sheet.Cells.Item($intRow, 7) = $Printer.DeviceID
        $intRow = $intRow + 1
    }
    [void] $WorkBook.EntireColumn.AutoFit()
    $intRow = $intRow + 1
    $Sheet.Cells.Item($intRow,1).Font.Bold = $True
    $Sheet.Cells.Item($intRow,1) = “Print server inventory"
    $objExcel.DisplayAlerts = $False
    $Excel.SaveAs("$CurrentFolder\$PrintServer.xlsx")
    $objExcel.DisplayAlerts = $True
    $Excel.Close($True)
}
$objExcel.Quit()
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
Remove-Variable excel
Remove-Variable objExcel

Open in new window


I'll work on the one to write back the values.

Rob.
michalek19Author Commented:
It works like charm!!!
Can you help me write that will write back and modify some of the field?

Printer Name      
Location       
Comment      
Share Name
RobSampsonCommented:
OK, this should do the setting of those four properties.  Specify the InputFile for it to read, with the same format as the output file produced by the first script.

Note: I have updated the first script to include DeviceID in the seventh column, so please use the new code

Also, please try to test this on a test system to make sure it works as intended.

Regards,

Rob.

# Set print server name
$CurrentFolder = Split-Path -Parent $MyInvocation.MyCommand.Definition
$InputFile = "$CurrentFolder\PRINTSERVER.xlsx"
$ComputerName = Get-ChildItem $InputFile | Select -ExpandProperty BaseName
# Create new Excel workbook
$objExcel = New-Object -COM Excel.Application
$objExcel.visible = $True
$Excel = $objExcel.Workbooks.Open($InputFile, $False, $False)
$Sheet = $Excel.Worksheets.Item(1)
$intLastRow = $Sheet.Cells.Item(65536, 1).End(-4162).Row
For ($i = 2; $i -le $intLastRow; $i++) {
    $Name = $Sheet.Cells.Item($i,1).Text
    $Location = $Sheet.Cells.Item($i,2).Text
    $Comment = $Sheet.Cells.Item($i,3).Text
    $ShareName = $Sheet.Cells.Item($i,6).Text
    $DeviceID = $Sheet.Cells.Item($i,7).Text
    $Query = "SELECT * FROM Win32_Printer WHERE DeviceID='$DeviceID'"
    $Printer = Get-WMIObject -Query $Query -ComputerName $ComputerName | Set-WmiInstance -Arguments @{Name=$Name;Location=$Location;Comment=$Comment;ShareName=$ShareName}
}
$Excel.Close($False)
$objExcel.Quit()
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
Remove-Variable excel
Remove-Variable objExcel

Open in new window

michalek19Author Commented:
Hmm, perhaps i am doing something wrong.

I ran first script that created printer objects information it save automatically file as print server name.

Then i ran second script and i got this?
What should be in \PRINTSERVER.xlsx?  


Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x80070
6BA)
At C:\temp\Scripts\writeprintobject.ps1:18 char:29
+     $Printer = Get-WMIObject <<<<  -Query $Query -ComputerName $ComputerName
| Set-WmiInstance -Arguments @{Name=$Name;Location=$Location;Comment=$Comment;S
hareName=$ShareName}
    + CategoryInfo          : InvalidOperation: (:) [Get-WmiObject], COMExcept
   ion
    + FullyQualifiedErrorId : GetWMICOMException,Microsoft.PowerShell.Commands
   .GetWmiObjectCommand

Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x80070
6BA)
At C:\temp\Scripts\writeprintobject.ps1:18 char:29
+     $Printer = Get-WMIObject <<<<  -Query $Query -ComputerName $ComputerName
| Set-WmiInstance -Arguments @{Name=$Name;Location=$Location;Comment=$Comment;S
hareName=$ShareName}
    + CategoryInfo          : InvalidOperation: (:) [Get-WmiObject], COMExcept
   ion
    + FullyQualifiedErrorId : GetWMICOMException,Microsoft.PowerShell.Commands
   .GetWmiObjectCommand

Get-WmiObject : The RPC server is unavailable. (Exception from HRESULT: 0x80070
6BA)
At C:\temp\Scripts\writeprintobject.ps1:18 char:29
+     $Printer = Get-WMIObject <<<<  -Query $Query -ComputerName $ComputerName
| Set-WmiInstance -Arguments @{Name=$Name;Location=$Location;Comment=$Comment;S
hareName=$ShareName}
    + CategoryInfo          : InvalidOperation: (:) [Get-WmiObject], COMExcept
   ion
    + FullyQualifiedErrorId : GetWMICOMException,Microsoft.PowerShell.Commands
   .GetWmiObjectCommand
michalek19Author Commented:
I think i got it.

I changed  Location      Comment and i ran script.
The result both fields changed "Location      Comment" to test
Unfortunately, Printer name did not get rename and share name did not change either.
Is it possible to modify script so also can be apply and modify print name and share name?
RobSampsonCommented:
The PRINTERSHARE.xlsx should be an input file with the same name as a file that was output by the first script.

I didn't test the name properties. I'll set up a test printer and see what we need.

Rob.
RobSampsonCommented:
Hi, it needed a bit of a rework after more testing, but this should write the properties back.

Regards,

Rob.

# Set print server name
$CurrentFolder = Split-Path -Parent $MyInvocation.MyCommand.Definition
$InputFile = "$CurrentFolder\MCCWIN702.xlsx"
$ComputerName = Get-ChildItem $InputFile | Select -ExpandProperty BaseName
# Create new Excel workbook
$objExcel = New-Object -COM Excel.Application
$objExcel.visible = $True
$Excel = $objExcel.Workbooks.Open($InputFile, $False, $False)
$Sheet = $Excel.Worksheets.Item(1)
$intLastRow = $Sheet.Cells.Item(65536, 1).End(-4162).Row
For ($i = 2; $i -le $intLastRow; $i++) {
    $Name = $Sheet.Cells.Item($i,1).Text
    $Location = $Sheet.Cells.Item($i,2).Text
    $Comment = $Sheet.Cells.Item($i,3).Text
    $ShareName = $Sheet.Cells.Item($i,6).Text
    $DeviceID = $Sheet.Cells.Item($i,7).Text
    $Query = "SELECT * FROM Win32_Printer WHERE DeviceID='$DeviceID'"
    Get-WMIObject -Query $Query -ComputerName $ComputerName | ForEach {
        $PropsToChange = ""
        If (($_.Location) -ne $Location) {
            Write-Output "Updating location from `"$($_.Location)`" to `"$Location`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";Location=`$Location"
        } Else {
            Write-Output "Not changing location from `"$($_.Location)`" for device `"$($_.DeviceID)`""
        }
        If (($_.Comment) -ne $Comment) {
            Write-Output "Updating comment from `"$($_.Comment)`" to `"$Comment`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";Comment=`$Comment"
        } Else {
            Write-Output "Not changing comment from `"$($_.Comment)`" for device `"$($_.DeviceID)`""
        }
        If (($_.ShareName) -ne $ShareName) {
            Write-Output "Updating sharename from `"$($_.ShareName)`" to `"$ShareName`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";ShareName=`$ShareName"
        } Else {
            Write-Output "Not changing sharename from `"$($_.ShareName)`" for device `"$($_.DeviceID)`""
        }
        If ($PropsToChange.Length -gt 0) {
            $Expression = "`$_ | Set-WmiInstance -Arguments @{$($PropsToChange.Substring(1))} | Out-Null"
            Invoke-Expression $Expression
        }
        If (($_.Name) -ne $Name) {
            Write-Output "Renaming device `"$($_.Name)`" to `"$Name`""
            $_.RenamePrinter($Name) | Out-Null
        } Else {
            Write-Output "Not renaming device `"$($_.Name)`""
        }
    }
}
$Excel.Close($False)
$objExcel.Quit()
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Sheet)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
Remove-Variable Sheet
Remove-Variable Excel
Remove-Variable objExcel

Open in new window

michalek19Author Commented:
It doesn't work

i got this error

Not changing sharename from "SNV-YLABS" for device "SNV-YLABS"
Not renaming device "SNV-YLABS"
Updating location from "B 4rd Floor 4616" to "Test" for device "SNV-YESSTRONG"
Updating comment from "B 4rd Floor 4616" to "Test" for device "SNV-YESSTRONG"
Not changing sharename from "SNV-YESSTRONG" for device "SNV-YESSTRONG"
Set-WmiInstance : Generic failure
At line:1 char:21
+ $_ | Set-WmiInstance <<<<  -Arguments @{Location=$Location;Comment=$Comment}
| Out-Null
    + CategoryInfo          : InvalidOperation: (:) [Set-WmiInstance], Managem
   entException
    + FullyQualifiedErrorId : SetWMIManagementException,Microsoft.PowerShell.C
   ommands.SetWmiInstance

Not renaming device "SNV-YESSTRONG"
Updating location from ""D, 2nd Floor, 2322 - Private printer - D not use"" to
"Test" for device "SNV-YANKEE"
Updating comment from ""D, 2nd Floor, 2322 - Private printer - D not use"" to "
Test" for device "SNV-YANKEE"
Not changing sharename from "SNV-YANKEE" for device "SNV-YANKEE"
Set-WmiInstance : Generic failure
At line:1 char:21
+ $_ | Set-WmiInstance <<<<  -Arguments @{Location=$Location;Comment=$Comment}
| Out-Null
RobSampsonCommented:
Are you running Powershell "As Administrator"?  You will need admin rights to the server to make changes to the printer objects.

Rob.
michalek19Author Commented:
i am logged in as domain admin but not as local admin
David Johnson, CD, MVPOwnerCommented:
you have to explicitly run as an administrator
run as administrator
michalek19Author Commented:
so, it change only location and comment for one printer but for the reset nothing

Updating location from ""B, 3rd Floor, 3327 - Private printer - Do not use""
 "man" for device "SNV-USUALGONE"
Updating comment from ""B, 3rd Floor, 3327 - Private printer - Do not use"" t
"man" for device "SNV-USUALGONE"
Not changing sharename from "SNV-USUALGONE" for device "SNV-USUALGONE"
Set-WmiInstance : Generic failure
At line:1 char:6
+ $_ | Set-WmiInstance -Arguments @{Location=$Location;Comment=$Comment} |
Out-Nul ...
+      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Set-WmiInstance], Manage
   entException
    + FullyQualifiedErrorId : SetWMIManagementException,Microsoft.PowerShell.
   ommands.SetWmiInstance

Not renaming device "SNV-USUALGONE"
Updating location from ""D, 2nd Floor, 240 - Private printer - Do not use"" t
"test" for device "SNV-UNDERDOG"
Updating comment from ""D, 2nd Floor, 240 - Private printer - Do not use"" to
test" for device "SNV-UNDERDOG"
Not changing sharename from "SNV-UNDERDOG" for device "SNV-UNDERDOG"
Set-WmiInstance : Generic failure
At line:1 char:6
+ $_ | Set-WmiInstance -Arguments @{Location=$Location;Comment=$Comment} |
Out-Nul ...
+      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Set-WmiInstance], Manage
   entException
    + FullyQualifiedErrorId : SetWMIManagementException,Microsoft.PowerShell.
   ommands.SetWmiInstance

Not renaming device "SNV-UNDERDOG"
Not changing location from "rock" for device "SNV-UBERWOODLED"
Not changing comment from "rock" for device "SNV-UBERWOODLED"
Not changing sharename from "SNV-UBERWOODLED" for device "SNV-UBERWOODLED"
Not renaming device "SNV-UBERWOODLED"
Not changing location from ""D, 3rd Floor, 3343"" for device "SNV-TANNER"
Not changing comment from ""D, 3rd Floor, 3343"" for device "SNV-TANNER"
Not changing sharename from "SNV-TANNER" for device "SNV-TANNER"
Not renaming device "SNV-TANNER"
Not changing location from ""D, 3rd Floor, 3343"" for device "SNV-TANNER"
Not changing comment from ""D, 3rd Floor, 3343"" for device "SNV-TANNER"
Not changing sharename from "SNV-TANNER" for device "SNV-TANNER"
Not renaming device "SNV-TANNER"
Not changing location from ""D, 3rd Floor, 3343"" for device "SNV-TANNER"
Not changing comment from ""D, 3rd Floor, 3343"" for device "SNV-TANNER"
Not changing sharename from "SNV-TANNER" for device "SNV-TANNER"
Not renaming device "SNV-TANNER"
Not changing location from ""D, 3rd Floor, 3343"" for device "SNV-TANNER"
Not changing comment from ""D, 3rd Floor, 3343"" for device "SNV-TANNER"
Not changing sharename from "SNV-TANNER" for device "SNV-TANNER"
Not renaming device "SNV-TANNER"
PS C:\Temp\Scripts>
michalek19Author Commented:
So, I executed more test and i run script against 2 different printer servers and scripts work perfect without any issues.

I could change location, comments, share name and display name.

Would that be possible to add another function to this list to modify IP address.
Also, can we export to log file all changes was made and not made?

How would be difficult to add another script that would pull from spread sheet list of printers that needs to be unshared "disable sharing"   - do you want me open another request?
RobSampsonCommented:
OK, so to add the "Shared" property, use this as script 1 that gathers the inventory:
# Set print server name
$Printservers = Get-Content -path c:\temp\servers.txt
$CurrentFolder = Split-Path -Parent $MyInvocation.MyCommand.Definition
# Create new Excel workbook
$objExcel = New-Object -COM Excel.Application
$objExcel.visible = $True
foreach ($Printserver in $Printservers) {

    $Excel = $objExcel.Workbooks.Add()
    $Sheet = $Excel.Worksheets.Item(1)
    $Sheet.Cells.Item(1,1) = “Printer Name”
    $Sheet.Cells.Item(1,2) = “Location”
    $Sheet.Cells.Item(1,3) = “Comment”
    $Sheet.Cells.Item(1,4) = “IP Address”
    $Sheet.Cells.Item(1,5) = “Driver Name”
    $Sheet.Cells.Item(1,6) = “Shared”
    $Sheet.Cells.Item(1,7) = “Share Name”
    $Sheet.Cells.Item(1,8) = “Device ID”
    $intRow = 2
    $WorkBook = $Sheet.UsedRange
    $WorkBook.Font.Bold = $True

    # Get printer information
    $Printers = Get-WMIObject Win32_Printer -computername $Printserver
    foreach ($Printer in $Printers)
    {
        $Sheet.Cells.Item($intRow, 1) = $Printer.Name
        $Sheet.Cells.Item($intRow, 2) = $Printer.Location
        $Sheet.Cells.Item($intRow, 3) = $Printer.Comment
        $Ports = Get-WmiObject Win32_TcpIpPrinterPort -computername $Printserver
        foreach ($Port in $Ports)
        {
            if ($Port.Name -eq $Printer.PortName)
            {
                $Sheet.Cells.Item($intRow, 4) = $Port.HostAddress.ToString()
            }
        }
        $Sheet.Cells.Item($intRow, 5) = $Printer.DriverName
        $Sheet.Cells.Item($intRow, 6) = $Printer.Shared
        $Sheet.Cells.Item($intRow, 7) = $Printer.ShareName
        $Sheet.Cells.Item($intRow, 8) = $Printer.DeviceID
        $intRow = $intRow + 1
    }
    [void] $WorkBook.EntireColumn.AutoFit()
    $intRow = $intRow + 1
    $Sheet.Cells.Item($intRow,1).Font.Bold = $True
    $Sheet.Cells.Item($intRow,1) = “Print server inventory"
    $objExcel.DisplayAlerts = $False
    $Excel.SaveAs("$CurrentFolder\$PrintServer.xlsx")
    $objExcel.DisplayAlerts = $True
    $Excel.Close($True)
}
$objExcel.Quit()
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
Remove-Variable excel
Remove-Variable objExcel

Open in new window


And this as script 2 to write the changes back:
# Set print server name
$CurrentFolder = Split-Path -Parent $MyInvocation.MyCommand.Definition
$InputFile = "$CurrentFolder\PrintServerName.xlsx"
$ComputerName = Get-ChildItem $InputFile | Select -ExpandProperty BaseName
# Create new Excel workbook
$objExcel = New-Object -COM Excel.Application
$objExcel.visible = $True
$Excel = $objExcel.Workbooks.Open($InputFile, $False, $False)
$Sheet = $Excel.Worksheets.Item(1)
$intLastRow = $Sheet.Cells.Item(65536, 1).End(-4162).Row
For ($i = 2; $i -le $intLastRow; $i++) {
    $Name = $Sheet.Cells.Item($i,1).Text
    $Location = $Sheet.Cells.Item($i,2).Text
    $Comment = $Sheet.Cells.Item($i,3).Text
    $Shared = $Sheet.Cells.Item($i,6).Text
    If ($Shared -eq "TRUE") { $Shared = $True } Else { $Shared = $False }
    $ShareName = $Sheet.Cells.Item($i,7).Text
    $DeviceID = $Sheet.Cells.Item($i,8).Text
    $Query = "SELECT * FROM Win32_Printer WHERE DeviceID='$DeviceID'"
    Get-WMIObject -Query $Query -ComputerName $ComputerName | ForEach {
        $PropsToChange = ""
        # --- Location property ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.Location)) -and ([string]::IsNullOrEmpty($Location))) -eq $False) {
            If (($_.Location) -ne $Location) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Updating location from `"$($_.Location)`" to `"$Location`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";Location=`$Location"
        } Else {
            Write-Output "Not changing location from `"$($_.Location)`" to `"$Location`" for device `"$($_.DeviceID)`""
        }
        # --- Comment property ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.Comment)) -and ([string]::IsNullOrEmpty($Comment))) -eq $False) {
            If (($_.Comment) -ne $Comment) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Updating comment from `"$($_.Comment)`" to `"$Comment`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";Comment=`$Comment"
        } Else {
            Write-Output "Not changing comment from `"$($_.Comment)`" to `"$Comment`" for device `"$($_.DeviceID)`""
        }
        # --- Shared property ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.Shared)) -and ([string]::IsNullOrEmpty($Shared))) -eq $False) {
            If ($($_.Shared) -ne $Shared) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Updating shared property from `"$($_.Shared)`" to `"$Shared`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";Shared=`$Shared"
        } Else {
            Write-Output "Not changing shared property from `"$($_.Shared)`" to `"$Shared`" for device `"$($_.DeviceID)`""
        }
        # --- ShareName property ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.ShareName)) -and ([string]::IsNullOrEmpty($ShareName))) -eq $False) {
            If (($_.ShareName) -ne $ShareName) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Updating sharename from `"$($_.ShareName)`" to `"$ShareName`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";ShareName=`$ShareName"
        } Else {
            Write-Output "Not changing sharename from `"$($_.ShareName)`" to `"$ShareName`" for device `"$($_.DeviceID)`""
        }
        # --- Set all properties ---
        If ($PropsToChange.Length -gt 0) {
            $Expression = "`$_ | Set-WmiInstance -Arguments @{$($PropsToChange.Substring(1))} | Out-Null"
            Invoke-Expression $Expression
        }
        # --- Name property (for renaming) ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.Name)) -and ([string]::IsNullOrEmpty($Name))) -eq $False) {
            If (($_.Name) -ne $Name) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Renaming device `"$($_.Name)`" to `"$Name`""
            $_.RenamePrinter($Name) | Out-Null
        } Else {
            Write-Output "Not renaming device `"$($_.Name)`" to `"$Name`""
        }
    } | Out-File "$CurrentFolder\Printer_Change_Log.txt" -Append
}
$Excel.Close($False)
$objExcel.Quit()
Invoke-Item "$CurrentFolder\Printer_Change_Log.txt"
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Sheet)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
Remove-Variable Sheet
Remove-Variable Excel
Remove-Variable objExcel

Open in new window

RobSampsonCommented:
So the above scripts give you an output file with the logging detail, and the ability to change the Shared status.

Changing the IP address would require a fair bit more code, because you would need to create the TCP/IP port before assigning the printer to it.  

Rob.
michalek19Author Commented:
i forgot to ask you something.  Would that be possible to add to the script another function to change print driver for some of the printers.
 
That would save me time if could change that in excel file and push it back against print server.
RobSampsonCommented:
Not really sure whether DriverName would change successfully this way, but you can try it.

# Set print server name
$CurrentFolder = Split-Path -Parent $MyInvocation.MyCommand.Definition
$InputFile = "$CurrentFolder\PrintServerName.xlsx"
$ComputerName = Get-ChildItem $InputFile | Select -ExpandProperty BaseName
# Create new Excel workbook
$objExcel = New-Object -COM Excel.Application
$objExcel.visible = $True
$Excel = $objExcel.Workbooks.Open($InputFile, $False, $False)
$Sheet = $Excel.Worksheets.Item(1)
$intLastRow = $Sheet.Cells.Item(65536, 1).End(-4162).Row
For ($i = 2; $i -le $intLastRow; $i++) {
    $Name = $Sheet.Cells.Item($i,1).Text
    $Location = $Sheet.Cells.Item($i,2).Text
    $Comment = $Sheet.Cells.Item($i,3).Text
    $DriverName = $Sheet.Cells.Item($i, 5).Text
    $Shared = $Sheet.Cells.Item($i,6).Text
    If ($Shared -eq "TRUE") { $Shared = $True } Else { $Shared = $False }
    $ShareName = $Sheet.Cells.Item($i,7).Text
    $DeviceID = $Sheet.Cells.Item($i,8).Text
    $Query = "SELECT * FROM Win32_Printer WHERE DeviceID='$DeviceID'"
    Get-WMIObject -Query $Query -ComputerName $ComputerName | ForEach {
        $PropsToChange = ""
        # --- Location property ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.Location)) -and ([string]::IsNullOrEmpty($Location))) -eq $False) {
            If (($_.Location) -ne $Location) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Updating location from `"$($_.Location)`" to `"$Location`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";Location=`$Location"
        } Else {
            Write-Output "Not changing location from `"$($_.Location)`" to `"$Location`" for device `"$($_.DeviceID)`""
        }
        # --- Comment property ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.Comment)) -and ([string]::IsNullOrEmpty($Comment))) -eq $False) {
            If (($_.Comment) -ne $Comment) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Updating comment from `"$($_.Comment)`" to `"$Comment`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";Comment=`$Comment"
        } Else {
            Write-Output "Not changing comment from `"$($_.Comment)`" to `"$Comment`" for device `"$($_.DeviceID)`""
        }
        # --- DriverName property ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.DriverName)) -and ([string]::IsNullOrEmpty($DriverName))) -eq $False) {
            If (($_.DriverName) -ne $DriverName) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Updating driver name from `"$($_.DriverName)`" to `"$DriverName`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";DriverName=`$DriverName"
        } Else {
            Write-Output "Not changing driver name from `"$($_.DriverName)`" to `"$DriverName`" for device `"$($_.DeviceID)`""
        }
        # --- Shared property ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.Shared)) -and ([string]::IsNullOrEmpty($Shared))) -eq $False) {
            If ($($_.Shared) -ne $Shared) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Updating shared property from `"$($_.Shared)`" to `"$Shared`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";Shared=`$Shared"
        } Else {
            Write-Output "Not changing shared property from `"$($_.Shared)`" to `"$Shared`" for device `"$($_.DeviceID)`""
        }
        # --- ShareName property ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.ShareName)) -and ([string]::IsNullOrEmpty($ShareName))) -eq $False) {
            If (($_.ShareName) -ne $ShareName) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Updating sharename from `"$($_.ShareName)`" to `"$ShareName`" for device `"$($_.DeviceID)`""
            $PropsToChange += ";ShareName=`$ShareName"
        } Else {
            Write-Output "Not changing sharename from `"$($_.ShareName)`" to `"$ShareName`" for device `"$($_.DeviceID)`""
        }
        # --- Set all properties ---
        If ($PropsToChange.Length -gt 0) {
            $Expression = "`$_ | Set-WmiInstance -Arguments @{$($PropsToChange.Substring(1))} | Out-Null"
            Invoke-Expression $Expression
        }
        # --- Name property (for renaming) ---
        $blnUpdate = $False
        If ((([string]::IsNullOrEmpty($_.Name)) -and ([string]::IsNullOrEmpty($Name))) -eq $False) {
            If (($_.Name) -ne $Name) {
                $blnUpdate = $True
            }
        }
        If ($blnUpdate -eq $True) {
            Write-Output "Renaming device `"$($_.Name)`" to `"$Name`""
            $_.RenamePrinter($Name) | Out-Null
        } Else {
            Write-Output "Not renaming device `"$($_.Name)`" to `"$Name`""
        }
    } | Out-File "$CurrentFolder\Printer_Change_Log.txt" -Append
}
$Excel.Close($False)
$objExcel.Quit()
Invoke-Item "$CurrentFolder\Printer_Change_Log.txt"
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Sheet)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)
[void] [System.Runtime.Interopservices.Marshal]::ReleaseComObject($objExcel)
Remove-Variable Sheet
Remove-Variable Excel
Remove-Variable objExcel

Open in new window


The driver that you enter into the Excel sheet must already exist on the server for that to work though.....then it might work.

Regards,

Rob.
michalek19Author Commented:
Let me check
michalek19Author Commented:
Thank you that works
RobSampsonCommented:
Good to know!!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
michalek19Author Commented:
Hi Rob

Would that be possible to generate csv output instead xlxs output?

Thx
RobSampsonCommented:
Yes. Excel automation in Powershell is quite slow due to a lack of compatibility with COM objects. It would be best to raise a new question for that conversion.

Rob.
michalek19Author Commented:
i will do that . thx
michalek19Author Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.