K1000 Reports - Print Server Printer List with Name, IP, Location & Comments Columns
______________________________________________________________________________

This solution basically converts the Windows' Devices and Printers screen (in Detail view with populated Location and Comments columns) into a SQL Report with discrete columns, something that's extremely useful as a reference tool both inside and outside the IT department.  It is very flexible for tweaking as desired.

Everything presented here is basically a reworking of everything detailed in this article, so I'll be very brief this time around:

K1000 Reports - Querying and Reporting on Network Printer Error and Status Codes
http://www.itninja.com/blog/view/k1000-reports-querying-and-reporting-on-network-printer-error-and-status-codes

I'll reiterate the following points and advise on the sections (##) to reference in the article above if you need further details:

-the SQL query is long but runs fast - 115 rows fetch in 0.0084s (0.1630s)
-I am only targeting our "master" printer server, which has all of our network printers installed
-you can change the vbscript output path and file name as desired
-be *sure* change the query's MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID value to match your own (09)
-run the query's outer inline views separately without the WHERE statements to determine what you'll need to filter out (14)
______________________________________________________________________________
______________________________________________________________________________

(1) VBScript
______________________________________________________________________________

Dim objFSO, newfolder
Dim strComputer, objWMIService
Dim fso, fsHandle, objShell,LogFileName, colItems, objItem
set objFSO=CreateObject("Scripting.FileSystemObject")
If Not objFSO.FolderExists("c:\KBOX") Then
   newfolder = objFSO.CreateFolder ("c:\KBOX")
End If
Set objShell = CreateObject("Wscript.Shell")
Set fso = Wscript.CreateObject("Scripting.FilesystemObject")
If objFSO.FileExists("c:\KBOX\printers_list_comments.txt") Then
fso.DeleteFile "c:\KBOX\printers_list_comments.txt", True
End If
LogFileName= "C:\KBOX\printers_list_comments.txt"
set fsHandle = fso.OpenTextFile (LogFileName,8,True)
fsHandle.Writeblanklines 1
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colInstalledPrinters =  objWMIService.ExecQuery _
("Select * from Win32_Printer")
For Each objPrinter in colInstalledPrinters
fsHandle.Writeline objPrinter.name & " * " & objPrinter.PortName & " * " & _
objPrinter.Location & " * " & objPrinter.Comment
Next
 fsHandle.close
set objShell = Nothing
set fso = Nothing
______________________________________________________________________________
______________________________________________________________________________

(2) K1000 Script Setup
______________________________________________________________________________

Scripting > Scripts > Choose Action > Add New Item

Script Type:   Online KScript
Name:          Printers List (Server)
Description:   Script to capture list of all printers with error codes for print servers.
Status:        Production
Enabled:       <checked>

Run As:        Run As User logged in to console

Dependencies:  printers_list_server.vbs

Task 1
Verify:        Always Fail

Remediation:   Launch a program...
  Directory:   $(KBOX_SYS_DIR)
  File:        cscript.exe
  <checked>    Wait for startup
  Parameters:  "$(KACE_DEPENDENCY_DIR)\printers_list_server.vbs"

On Remediation Success:
               Log message...
  Type:        status
  Message:     successfully ran printers_list_server script


On Remediation Failure:
               Log message...
  Type:        status
  Message:     failed to run printers_list_server script
______________________________________________________________________________
______________________________________________________________________________

(3) K1000 Custom Inventory Item Setup
______________________________________________________________________________

*Display Name (Title)*
* Printers List (Comments)

*Supported Operating Systems*
Microsoft Windows 2000 Advanced Server SP4
Microsoft Windows 2000 Server SP4
Microsoft Windows 2008 R2 Standard x64 SP1
Microsoft(R) Windows(R) Server 2003 Enterprise x64 Edition SP2
Microsoft(R) Windows(R) Server 2003 Standard x64 Edition SP2
Microsoft(R) Windows(R) Server 2003, Standard Edition SP2
Microsoft© Windows Server© 2008 Standard SP2
Microsoft© Windows Server© 2008 Standard x64 SP2

*Custom Inventory Rule*
ShellCommandTextReturn(cmd /c type c:\KBOX\printers_list_comments.txt)

*Category*
System Tool

*Threat Level*
1 - Safe
______________________________________________________________________________
______________________________________________________________________________

(4) Report to List All Printers with Name, IP, Location & Comments
______________________________________________________________________________

*Title*
Network Printers

*Report Category*
Printers (Custom)

*Description*
Lists all network printers with IP address, location and comments for PRINTSERVER.

*SQL Select Statement*
SELECT Printer, IP as IP_Address, Location, Comment
FROM
 (SELECT Machine, Printer, @w:=@w+1 as Counter1
  FROM
   (SELECT M.NAME as Machine,
    SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Target, Counter), "<br/>", -1) as 'Printer'
    FROM MACHINE_CUSTOM_INVENTORY MCI
    JOIN
     (SELECT Target, @c:=IF(Target=@t, @c+3, 1) as Counter, @t:=Target
      FROM
       (SELECT A.Target
        FROM
         (SELECT ' *' as Target UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *')
        A,
         (SELECT ' *' as Target UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *')
        B,
         (SELECT ' *' as Target UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *')
        C
        ORDER BY A.Target)
      Cartesian_Product,
       (SELECT @c:=0, @t:='')
      Define_Variables)
    Temp_Table_to_Populate_Substring_Index
    JOIN MACHINE M on (M.ID = MCI.ID)
    WHERE MCI.SOFTWARE_ID = 8493
    AND M.NAME = 'srv-print02')
  Filter,
   (SELECT @w:=0)
  Define_Variables
  WHERE Printer != ''
  AND Printer != 'Microsoft XPS Document Writer')
Printer_Name
JOIN
 (SELECT IP, @x:=@x+1 as Counter2
  FROM
   (SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Target, Counter+1), "* ", -1) as 'IP'
    FROM MACHINE_CUSTOM_INVENTORY MCI
    JOIN
     (SELECT Target, @c:=IF(Target=@t, @c+3, 1) as Counter, @t:=Target
      FROM
       (SELECT A.Target
        FROM
         (SELECT ' *' as Target UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *')
        A,
         (SELECT ' *' as Target UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *')
        B,
         (SELECT ' *' as Target UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *')
        C
        ORDER BY A.Target)
      Cartesian_Product,
       (SELECT @c:=0, @t:='')
      Define_Variables)
    Temp_Table_to_Populate_Substring_Index
    JOIN MACHINE M on (M.ID = MCI.ID)
    WHERE MCI.SOFTWARE_ID = 8493
    AND M.NAME = 'srv-print02')
  Filter,
   (SELECT @x:=0)
  Define_Variables
  WHERE IP != 'XPSPort:'
  AND IP != 'QA Lab (HP LaserJet P2035n)<br/>')
IP_Address
ON (IP_Address.Counter2 = Printer_Name.Counter1)
JOIN
 (SELECT Location, @y:=@y+1 as Counter3
  FROM
   (SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Target, Counter+2), "* ", -1) as 'Location'
    FROM MACHINE_CUSTOM_INVENTORY MCI
    JOIN
     (SELECT Target, @c:=IF(Target=@t, @c+3, 1) as Counter, @t:=Target
      FROM
       (SELECT A.Target
        FROM
         (SELECT ' *' as Target UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *')
        A,
         (SELECT ' *' as Target UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *')
        B,
         (SELECT ' *' as Target UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *' UNION ALL
          SELECT ' *')
        C
        ORDER BY A.Target)
      Cartesian_Product,
       (SELECT @c:=0, @t:='')
      Define_Variables)
    Temp_Table_to_Populate_Substring_Index
    JOIN MACHINE M on (M.ID = MCI.ID)
    WHERE MCI.SOFTWARE_ID = 8493
    AND M.NAME = 'srv-print02')
  Filter,
   (SELECT @y:=0)
  Define_Variables
  WHERE Location != ''
  AND Location != 'QA Lab (HP LaserJet P2035n)<br/>')
Printer_Location
ON (Printer_Location.Counter3 = Printer_Name.Counter1)
JOIN
 (SELECT Comment, @z:=@z+1 as Counter4
  FROM
   (SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Target, Counter), "* ", -1) as 'Comment'
    FROM MACHINE_CUSTOM_INVENTORY MCI
    JOIN
     (SELECT Target, @c:=IF(Target=@t, @c+1, 1) as Counter, @t:=Target
      FROM
       (SELECT A.Target
        FROM
         (SELECT '<br/>' as Target UNION ALL
          SELECT '<br/>' UNION ALL
          SELECT '<br/>' UNION ALL
          SELECT '<br/>' UNION ALL
          SELECT '<br/>')
        A,
         (SELECT '<br/>' as Target UNION ALL
          SELECT '<br/>' UNION ALL
          SELECT '<br/>' UNION ALL
          SELECT '<br/>' UNION ALL
          SELECT '<br/>')
        B,
         (SELECT '<br/>' as Target UNION ALL
          SELECT '<br/>' UNION ALL
          SELECT '<br/>' UNION ALL
          SELECT '<br/>' UNION ALL
          SELECT '<br/>')
        C
        ORDER BY A.Target)
      Cartesian_Product,
       (SELECT @c:=0, @t:='')
      Define_Variables)
    Temp_Table_to_Populate_Substring_Index
    JOIN MACHINE M on (M.ID = MCI.ID)
    WHERE MCI.SOFTWARE_ID = 8493
    AND M.NAME = 'srv-print02')
  Filter,
   (SELECT @z:=0)
  Define_Variables
  WHERE Comment != ''
  AND Comment != 'QA Lab (HP LaserJet P2035n)<br/>')
Printer_Comment
ON (Printer_Comment.Counter4 = Printer_Name.Counter1)
ORDER BY Printer
______________________________________________________________________________
______________________________________________________________________________

(5) Example Output

Title: Network Printers
Description: Lists all network printers with IP address, location and comments for PRINTSERVER.
Category: Printers (Custom)
Server Hostname: kbox.company.net
Generated: 2012/09/15 00:01:05

#  Printer   Ip Address     Location         Comment
1  ec52      172.16.1.152   East Canton OH   QA Lab (HP LaserJet P2035n)
2  ec53      172.16.1.153   East Canton OH   Shipping Pallet Labels (HP LaserJet 5200)
3  ec54      172.16.1.154   East Canton OH   Production Office (HP LaserJet P2055dn)
4  ec55      172.16.1.155   East Canton OH   Mary Carter (HP LaserJet 5000)
5  ec56      172.16.1.156   East Canton OH   Main Office (Xerox WorkCentre 5225)
6  ec57      172.16.1.157   East Canton OH   Maintenance Office (HP LaserJet P2055dn)
7  ec58      172.16.1.158   East Canton OH   Allen Grey (HP LaserJet P2035n)
etc...
______________________________________________________________________________

Hope this helps someone!

John