K1000 Reports - Querying and Reporting on Network Printer Error and Status Codes
______________________________________________________________________________
______________________________________________________________________________

(0) Preface

Section I - Querying Printer Error/Status Codes
(01) DetectedErrorState Method
(02) Other Win32_Printer Class Methods
(03) VBScript to Output Printer Info to File

Section II - Getting VBScript Output into the K1000
(04) K1000 Script Setup
(05) K1000 Custom Inventory Item Setup
(06) Deployment Steps

Section III - SQL Reports and Example Output
(07) Report to List All Printer Codes
(08) Report to List All Printer Codes (Errors Only)

Section IV - Query Construction Steps and Analysis
(09) Determining the MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID Value
(10) Extracting the MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE String
(11) Base SUBSTRING_INDEX Queries for Extracting Target String
(12) Constructing a Temporary Table to Populate the SUBSTRING_INDEX Statements
(13) Adjusting Base Queries for Inline Views
(14) Removing Unutilized Rows
(15) Constructing a Counter Column for Joining Inline Views
(16) JOINing the MACHINE table
(17) CASE Statements to Clarify DetectedErrorState Codes
(18) WHERE Statement to Only List Error Codes

(X) Conclusion
______________________________________________________________________________
______________________________________________________________________________

(0) Preface

I received an interesting challenge yesterday morning from another sysadmin in my company - figure out some way to list/track status codes for

network printers.  Specifically, I needed to find some way to pull the Windows - Devices and Printers screen's Status column for each of the

printers on our main print server (which has *all* of the network printers installed), so we could track issues and conditions like low/no

toner, empty/open paper trays, etc.  Of course, the first things that popped in my head were a vbscript to query (and output to a file) and  a

K1000 SQL Report to parse the required strings (and create the report).  My (mental) checklist consisted of the following (each point assuming

the former was possible):

1) Is it possible to pull the Printers' Status column data via an WMI query?
2) Besides error/status codes, which data (columns) should be included in the final report?
3) How should the vbscript output be formatted to facilitate parsing the fields?
4) Is it possible to parse each data type using simple queries?
5) Is it possible to put all of the queries together in a single query (report)?

Although there were moments when I wasn't sure about certain points, I spent a good part of yesterday confirming that the answer to all is

"yes" - and that is why I'm writing this up now.
________________________

Please be aware that there are some tweaks required (and optional methods) for deploying this in your own environment.  For example, the

vbscript will require a couple of changes to output to your desired folder, and the SQL report will require updating a software ID number to

reflect what your K1000 uses.  There is also the question of running the query - this can be done from the K1000 as I have documented here,

but you could also set it up to run as a scheduled task from the server itself; in that case, you can skip section (04) and just make sure the

custom inventory rule listed in section (05) points to the correct location of the output file.

In regards to my environment, this is currently only being run against one "master" print server.  However, I did include the MACHINE table in

the report so that the SQL report can be used as is for multiple machines (i.e. if you want to track printer codes/errors for all of your

print servers or even *all* of your machines).  This would just require running the script against all of the machines, which should be easy

enough to do using the K1000 script.
________________________

As I have already covered many of the concepts used here in other articles, I'll be providing enough information to get this setup working,

but only go into explicit detail for new concepts and cover the rest generally.  My goals with this article are:

(1) Provide a workable solution you can use in your own environment
(2) Explain the construction issues specific to this query

If you want/need further details on certain items, please see these related articles:
________________________

For a full analysis and explanation of inline views, cartesian products, user variables and other techniques used in this query:

K1000 Reports - Advanced MySQL Query Techniques - Parsing Multiple Substrings from a Field Using Temporary Tables
http://www.itninja.com/blog/view/k1000-reports-advanced-mysql-query-techniques-parsing-multiple-substrings-from-a-field-using-temporary-tables
________________________

For more basic SQL query construction tips and MySQL Query Browser setup:

Primer for Writing SQL Select Statement Queries on the K1000 (w/Custom SQL Report example)
http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example
________________________

For a full breakdown of setting up vbscripts (printer and other), pulling them into the K1000 and reporting on them:

Inventorying & Reporting on User Profile-Specific & Other Non-Inventoried Data
http://www.itninja.com/blog/view/inventorying-reporting-on-user-profile-specific-other-non-inventoried-data
________________________

For some more advanced printer vbscripts and reports:

K1000 Reports - Default, All & Local Printers (w-vbscripts)
http://www.itninja.com/blog/view/k1000-reports-default-all-local-printers-w-vbscripts
________________________

For instructions on fixing the line break character bug in the main Reports module:

Workaround for Line Break Character Bug in Reports Generated on Inventory Custom Fields
http://www.itninja.com/blog/view/workaround-for-line-break-character-bug-in-reports-generated-on-inventory-custom-fields
______________________________________________________________________________
______________________________________________________________________________

Section I - Querying Printer Error/Status Codes
______________________________________________________________________________
______________________________________________________________________________

(01) DetectedErrorState Method
______________________________________________________________________________

To determine if pulling the printer status information from the print server would be possible, I first ran the following command to query all

of the print server's printers using all of the Win_32 Printer class methods:

C:\>wmic printer list full > c:\temp\wmic_printer.txt

Cross-checking the output against the printers' statuses, I found a perfect match with the DetectedErrorState method (although it wasn't clear

at first as this method outputs an integer value).
________________________

Digging further, I found what I was looking for - the integer value's corresponding meanings:

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

DetectedErrorState

Data type: uint16
Access type: Read-only

Printer error information. This property is inherited from CIM_Printer.

Value       Meaning
0  (0x0)   Unknown
1  (0x1)   Other
2  (0x2)   No Error
3  (0x3    Low Paper
4  (0x4)   No Paper
5  (0x5)   Low Toner
6  (0x6)   No Toner
7  (0x7)   Door Open
8  (0x8)   Jammed
9  (0x9)   Offline
10 (0xA)   Service Requested
11 (0xB)   Output Bin Full
______________________________________________________________________________
______________________________________________________________________________

(02) Other Win32_Printer Class Methods
______________________________________________________________________________

The vbscript uses three other Win32_Printer class methods (Name, PortName, DriverName) to query each printer's name, port name and drive name

respectively.  These are very straightforward and no further work was necessary, other than adding delimiters for a cleaner look and to make

parsing these values out later possible.
________________________

vbscript statement:

fsHandle.Writeline objPrinter.Name & " * " & objPrinter.PortName & " * " & _
objPrinter.DriverName & " * " & objPrinter.DetectedErrorState

Example output:

wwh50 * 172.16.8.50 * RICOH Aficio SP 3400SF PCL 6 * 0
______________________________________________________________________________
______________________________________________________________________________

(03) VBScript to Output Printer Info to File
______________________________________________________________________________

This is based off of my original printers_list script (prior to billprew helping me with the procedures to update the printers list for the

currently logged in user, while retaining historical data for other users).  This went through several iterations as I uploaded the output to

the K1000 and parsed it with SUBSTRING_INDEX functions to see what would work for *all* printer listings.  Initially, I had just added the

DetectedErrorState method (in place of Location, which is in my other printer scripts) and dropped the extraneous statements.  However, it

became apparent that in order for SUBSTRING_INDEX to work consistently (and without making the other SQL statements overly complex), I needed

the printer queries to be as uniform as possible.  Therefore I dropped the timestamp and the listing of the currently logged in user from the

vbscript so every line was homogeneous.  I then found that one of the base SUBSTRING_INDEX statements worked fine except for the first row, so

I added a preliminary carriage return to rectify this.
________________________

To use this script, just copy the code below, paste into Notepad (or your favorite text editor) and save as:
printers_list_errors.vbs

You'll need/want to adjust the "c:\KBOX" paths to wherever you want the output file to be created.  Also, note that when this script runs, it

will delete the file if already present - just something to be aware of if you need/want to keep a history.
________________________

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_errors.txt") Then
fso.DeleteFile "c:\KBOX\printers_list_errors.txt", True
End If
LogFileName= "C:\KBOX\printers_list_errors.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.DriverName & " * " & objPrinter.DetectedErrorState
Next
fsHandle.close
set objShell = Nothing
set fso = Nothing
________________________

Just for reference purposes, this is an example excerpt what the resulting output will resemble (obviously, the details will differ):

         ---> initial blank line intentional and required
wwh50 * 172.16.8.50 * RICOH Aficio SP 3400SF PCL 6 * 0
wel60c * 172.16.9.60 * HP Color LaserJet 2605dn_2605dtn PCL 6 * 0
wel58 * 172.16.9.58 * HP LaserJet 4240 PCL 6 * 9
war61 * 172.16.7.61 * HP LaserJet P2015 Series PCL 6 * 0
war57c * 172.16.7.57_1 * RICOH Aficio MP C3002 PCL 6 * 0
etc...
______________________________________________________________________________
______________________________________________________________________________

Section II - Getting VBScript Output into the K1000
______________________________________________________________________________
______________________________________________________________________________

(04) K1000 Script Setup
______________________________________________________________________________

This setup will deploy the vbscript above to whatever machines you decide to run it against.  Again, I'm only targeting our "master" print

server, but this is just a self-imposed limitation (for the time being).
________________________

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_errors.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_errors.vbs"

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


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

(05) K1000 Custom Inventory Item Setup
______________________________________________________________________________

This is what will pull the script output into the K1000.  If you change the output path of your script, be sure to update the path listed in

ShellCommandTextReturn to match.
________________________

Custom Software Inventory Item Setup

Inventory > Software > Choose Action > Add New Item

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

*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_errors.txt)

*Category*
System Tool

*Threat Level*
1 - Safe
______________________________________________________________________________
______________________________________________________________________________

(06) Deployment Steps
______________________________________________________________________________

Now that the setup details have been spelled out, here's how to actually use everything:

1) Push script to server/machines
2) Verify output text file created (C:\KBOX or wherever you specified)
3) Force inventory on server/machines
4) Go to target machine's Inventory screen, verify "* Printers List (Server) entry in Software > Custom Inventory Fields
5) Use the queries below (with adjustments), create reports (Reporting > Reports > Choose Action > Add New SQL Report)
6) Run the reports
7) Done
______________________________________________________________________________
______________________________________________________________________________

Section III - SQL Reports and Example Output
______________________________________________________________________________
______________________________________________________________________________

Note that in order to use these reports on your K1000, you'll need adjust the MCI.SOFTWARE_ID value (8470 in my queries) in all of the inline

views.  If you aren't sure how to get this value, see section (09) for steps.

Also, the reports may look involved, but they are efficient and execute very quickly - 116 rows fetched in 0.0040s (0.0553s) per the MySQL

Query Browser.  This time will increase based on the number of machines, but performance should not be an issue.
______________________________________________________________________________

(07) Report to List All Printer Codes
______________________________________________________________________________

*Title*
Network Printer Codes

*Report Category*
Printers (Custom)

*Description*
Lists all PRINTSERVER-installed network printers' codes.

*SQL Select Statement*
SELECT Machine, Printer, IP as IP_Address, Driver,
CASE
WHEN Error = 0 THEN 'Ready'
WHEN Error = 1 THEN 'Other'
WHEN Error = 2 THEN 'No Error'
WHEN Error = 3 THEN 'Low Paper'
WHEN Error = 4 THEN 'No Paper'
WHEN Error = 5 THEN 'Low Toner'
WHEN Error = 6 THEN 'No Toner'
WHEN Error = 7 THEN 'Door Open'
WHEN Error = 8 THEN 'Jammed'
WHEN Error = 9 THEN 'Offline'
WHEN Error = 10 THEN 'Service Requested'
WHEN Error = 11 THEN 'Output Bin Full'
END
as Printer_Code
FROM
 (SELECT Machine, Printer, @j:=@j+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 = 8470)
  Filter,
   (SELECT @j:=0)
  Define_Variables
  WHERE Printer != '')
Printer_Name
JOIN
 (SELECT IP, @k:=@k+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
    WHERE MCI.SOFTWARE_ID = 8470)
  Filter,
   (SELECT @k:=0)
  Define_Variables
  WHERE IP != '0<br/>')
IP_Address
ON (IP_Address.Counter2 = Printer_Name.Counter1)
JOIN
 (SELECT Driver, @l:=@l+1 as Counter3
  FROM
   (SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Target, Counter+2), "* ", -1) as 'Driver'
    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
    WHERE MCI.SOFTWARE_ID = 8470)
  Filter,
   (SELECT @l:=0)
  Define_Variables
  WHERE Driver != '0<br/>')
Printer_Driver
ON (Printer_Driver.Counter3 = Printer_Name.Counter1)
JOIN
 (SELECT Error, @m:=@m+1 as Counter4
  FROM
   (SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Target, Counter), "* ", -1) as 'Error'
    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
    WHERE MCI.SOFTWARE_ID = 8470)
  Filter,
   (SELECT @m:=0)
  Define_Variables
  WHERE Error != '0<br/>'
  AND Error != '')
Error_Code
ON (Error_Code.Counter4 = Printer_Name.Counter1)
WHERE Printer not rlike 'XPS'
ORDER BY Printer

*Break on Columns*
Machine
________________________

Example Output:

Title: Network Printer Codes
Description: Lists all PRINTSERVER-installed network printers' codes.
Category: Printers (Custom)
Server Hostname: kbox.company.net
Generated: 2012/09/12 22:54:13

116 of Machine: printserver
#  Printer   Ip Address    Driver                          Printer Code  
1  ec52      172.16.1.52   HP LaserJet P2035n              Ready         
2  ec53      172.16.1.53   HP LaserJet 5200 PCL 6          Ready         
3  ec54      172.16.1.54   HP LaserJet P2050 Series PCL6   Ready         
4  ec55      172.16.1.55   HP LaserJet 5000 Series PCL6    Ready         
5  ec56      172.16.1.56   Xerox WorkCentre 5225 PCL6      Ready         
6  ec57      172.16.1.57   HP LaserJet P2050 Series PCL6   Ready         
7  ec58      172.16.1.58   HP LaserJet P2035n              Ready  
etc...
______________________________________________________________________________
______________________________________________________________________________

(08) Report to List All Printer Codes (Errors Only)
______________________________________________________________________________

*Title*
Network Printer Codes (Errors Only)

*Report Category*
Printers (Custom)

*Description*
Lists all PRINTSERVER-installed network printers' codes (errors only).

*SQL Select Statement*
SELECT Machine, Printer, IP as IP_Address, Driver,
CASE
WHEN Error = 0 THEN 'Ready'
WHEN Error = 1 THEN 'Other'
WHEN Error = 2 THEN 'No Error'
WHEN Error = 3 THEN 'Low Paper'
WHEN Error = 4 THEN 'No Paper'
WHEN Error = 5 THEN 'Low Toner'
WHEN Error = 6 THEN 'No Toner'
WHEN Error = 7 THEN 'Door Open'
WHEN Error = 8 THEN 'Jammed'
WHEN Error = 9 THEN 'Offline'
WHEN Error = 10 THEN 'Service Requested'
WHEN Error = 11 THEN 'Output Bin Full'
END
as Printer_Code
FROM
 (SELECT Machine, Printer, @j:=@j+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 = 8470)
  Filter,
   (SELECT @j:=0)
  Define_Variables
  WHERE Printer != '')
Printer_Name
JOIN
 (SELECT IP, @k:=@k+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
    WHERE MCI.SOFTWARE_ID = 8470)
  Filter,
   (SELECT @k:=0)
  Define_Variables
  WHERE IP != '0<br/>')
IP_Address
ON (IP_Address.Counter2 = Printer_Name.Counter1)
JOIN
 (SELECT Driver, @l:=@l+1 as Counter3
  FROM
   (SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Target, Counter+2), "* ", -1) as 'Driver'
    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
    WHERE MCI.SOFTWARE_ID = 8470)
  Filter,
   (SELECT @l:=0)
  Define_Variables
  WHERE Driver != '0<br/>')
Printer_Driver
ON (Printer_Driver.Counter3 = Printer_Name.Counter1)
JOIN
 (SELECT Error, @m:=@m+1 as Counter4
  FROM
   (SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Target, Counter), "* ", -1) as 'Error'
    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
    WHERE MCI.SOFTWARE_ID = 8470)
  Filter,
   (SELECT @m:=0)
  Define_Variables
  WHERE Error != '0<br/>'
  AND Error != '')
Error_Code
ON (Error_Code.Counter4 = Printer_Name.Counter1)
WHERE Printer not rlike 'XPS'
AND Error != 0
ORDER BY Printer

*Break on Columns*
Machine
________________________

Example Output:

Title: Network Printer Codes (Errors Only)
Description: Lists all PRINTSERVER-installed network printers' codes (errors only).
Category: Printers (Custom)
Server Hostname: kbox.company.net
Generated: 2012/09/12 22:55:54

9 of Machine: printserver
#  Printer   Ip Address    Driver                           Printer Code  
1  ham53   172.16.3.53     Xerox WorkCentre 3550 PCL 6      No Toner      
2  mln56   172.16.4.56     HP LaserJet 5200 PCL 6           Low Toner     
3  pgh58   172.16.0.58     RICOH Aficio MP C5000 PCL 6      Low Toner     
4  pgh58c  172.16.0.58_1   RICOH Aficio MP C5000 PCL 6      Low Toner     
5  pgh70   172.16.0.70     HP Color LaserJet 2600n          Offline       
6  pgh70c  172.16.0.70     HP Color LaserJet 2600n          Offline       
7  pgh73   172.16.0.73     HP LaserJet P2015 Series PCL 6   Offline       
etc...
______________________________________________________________________________
______________________________________________________________________________

Section IV - Query Construction Steps and Analysis
______________________________________________________________________________
______________________________________________________________________________

Constructing the main queries was analogous to building a house - first, pick the plot (ID value and raw string), then build the foundation

(base queries), next setup the frame (plan the views and cartesian tables), then run all utility lines (tweak the base queries to work with

the cartesian table data), next build out the rooms and exterior (construct inline views), and finally finish up with trimmings (add other

tables, clarify with CASE statements, clean up with a WHERE statement).  This resulted in the following steps, testing/tweaking aside:

-write a query to find MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID value
-extract the string from MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE
-work out base SUBSTRING_INDEX queries to extract target substring
-create a cartesian product table sufficient for 116 printers with room to grow
-construct a temporary table to populate the SUBSTRING_INDEX statements
-adjust SUBSTRING_INDEX statements to work properly inside inline views
-remove leftover (unused) rows (cartesian product - printer count)
-add a Counter column for joining outer inline views
-add MACHINE table to pull server name (and later for sorting if more added)
-use a CASE statement to clarify DetectedErrorState codes
-add WHERE statement to exclude printers with code 0 (for error-only report)
______________________________________________________________________________

(09) Determining the MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID Value
______________________________________________________________________________

The very first step was determining the specific ID value assigned to the "* Printers List (Server)" custom inventory rule.  This was done via

the following query, which lists all MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID values for the target machine (you'll want to put your own machine's

name in the last line):

SELECT * FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN MACHINE M on (M.ID = MCI.ID)
WHERE M.NAME = 'printserver'

Reviewing the MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE column, I found the string that matched the contents of the vbscript output file and

then looked at the MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID column to find the corresponding value (8470 in this case).
______________________________________________________________________________
______________________________________________________________________________

(10) Extracting the MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE String
______________________________________________________________________________

Just for reference, this is part of the beast of a string that ended up in the K1000's database - the size is due to having 116 printers on

the print server.  To get this string using the MySQL Query Browser:

- run the query in section (09)
- right-click on the cell with the appropriate string (for your own SOFTWARE_ID)
- select Copy Field Content
- paste it into Notepad (my favorite workspace)

Example extracted MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE string (116 printers total - 16 listed):

<br/>wwh50 * 172.16.8.50 * RICOH Aficio SP 3400SF PCL 6 * 0<br/>wel60c * 172.16.9.60 * HP Color LaserJet 2605dn_2605dtn PCL 6 * 0<br/>wel58 *

172.16.9.58 * HP LaserJet 4240 PCL 6 * 0<br/>war61 * 172.16.7.61 * HP LaserJet P2015 Series PCL 6 * 0<br/>war57c * 172.16.7.57_1 * RICOH

Aficio MP C3002 PCL 6 * 0<br/>war57 * 172.16.7.57 * RICOH Aficio MP C3002 PCL 6 * 0<br/>war56 * 172.16.7.56 * HP LaserJet P2050 Series PCL6 *

0<br/>war55 * 172.16.7.55 * HP LaserJet P2050 Series PCL6 * 0<br/>war53 * 172.16.7.53 * HP LaserJet P2050 Series PCL6 * 0<br/>war52 *

172.16.7.52 * HP LaserJet 5200 PCL 6 * 0<br/>war51 * 172.16.7.51 * HP LaserJet P2035n * 0<br/>tar61 * 172.16.6.61 * HP LaserJet P2035n *

0<br/>tar60 * 172.16.6.60 * HP LaserJet P2015 Series PCL 6 * 0<br/>tar59 * 172.16.6.59 * HP LaserJet P1500 Series PCL 5e * 9<br/>tar56 *

172.16.6.56 * HP LaserJet 400 M401 PCL 6 * 0<br/>shn56c * 172.16.5.56 * Dell Laser Printer 3100cn PCL6 * 0<br/>.............etc
______________________________________________________________________________
______________________________________________________________________________

(11) Base SUBSTRING_INDEX Queries for Extracting Target String
______________________________________________________________________________

These are the basic queries which parse the specified substring from the first printer in the string above, i.e.:

<br/>wwh50 * 172.16.8.50 * RICOH Aficio SP 3400SF PCL 6 * 0

As I mentioned earlier, in order for the Printer Name query (below) to work properly, I had modify the vbscript to add a carriage return prior

to enumerating the printer data so that I could use the leading line break character (<br/>) in the Printer query's outer SUBSTRING_INDEX

function.
________________________

Printer Name Query:

SELECT M.NAME as 'Machine',
SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, ' *', 1), "<br/>", -1) as 'Printer'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN MACHINE M on (M.ID = MCI.ID)
WHERE MCI.ID=M.ID and MCI.SOFTWARE_ID = 8470
ORDER BY Machine

Output:
wwh50
________________________

Error Code Query:

SELECT M.NAME as 'Machine',
SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, '<br/>', 2), "* ", -1) as 'Error Code'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN MACHINE M on (M.ID = MCI.ID)
WHERE MCI.ID=M.ID and MCI.SOFTWARE_ID = 8470
ORDER BY Machine

Output:
0
________________________

Printer IP Address Query:

SELECT M.NAME as 'Machine',
SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, ' *', 2), "* ", -1) as 'IP'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN MACHINE M on (M.ID = MCI.ID)
WHERE MCI.ID=M.ID and MCI.SOFTWARE_ID = 8470
ORDER BY Machine

Output:
172.16.8.50
________________________

Printer Driver Query:

SELECT M.NAME as 'Machine',
SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, ' *', 3), "* ", -1) as 'Driver'
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN MACHINE M on (M.ID = MCI.ID)
WHERE MCI.ID=M.ID and MCI.SOFTWARE_ID = 8470
ORDER BY Machine

Output:
RICOH Aficio SP 3400SF PCL 6
______________________________________________________________________________
______________________________________________________________________________

(12) Constructing a Temporary Table to Populate the SUBSTRING_INDEX Statements
______________________________________________________________________________

As I discussed in the MySQL Advanced Query Techniques article, a temporary table (inline view) consisting of a function argument column (' *'

in this example) and a counter column (created and populated by the IF statement using user variables and the cartesian product) is a terrific

tool for populating other query statements.  Since I had 116 printers to parse out of the raw string, I decided on a 125 row product cartesian

table, which worked out nicely via a 5*5*5 construction.
________________________

Example Temp_Table_to_Populate_Substring_Index subquery:

SELECT Target, @i:=IF(Target=@l, @i+1, 1) as Counter1, @l:=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 @i:=0, @l:='')
Define_Variables
________________________

Example Output:

Target   Counter1   @l:=Target
 *       1           *
 *       2           *
 *       3           *
etc...
 *       124         *
 *       125         *
______________________________________________________________________________
______________________________________________________________________________

(13) Adjusting Base Queries for Inline Views
______________________________________________________________________________

The next step was getting the SUBSTRING_INDEX statements working with the arguments supplied by the Temp_Table_to_Populate_Substring_Index

subquery, as the third argument (count) was now coming from the resulting Counter column (i.e. 1, 2, 3 ... 125).  For the Printer, IP and

Driver columns, I had to adjust the increment statement from @i+1 to @i+3 as I needed to skip to every third instance of " *".  Seeing a full

"raw" printer string should clarify this - the IP occurs before the second instance of " *", while the driver occurs before the third :

<br/>wwh50 * 172.168.8.50 * RICOH Aficio SP 3400SF PCL 6 * 0
________________________

Also, since the original inner IP and Driver SUBSTRING_INDEX statements target the second and third instances of " *", I adjusted the inline

view versions (that use the IF statement quasi-loop to populate the Target and Counter variables) to skip the first and second rows by

incrementing Counter (+1 and +2, respectively).  For comparison's sake:

Original vs Populated Populated SUBSTRING_INDEX statement:

SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, ' *', 1), "<br/>", -1) as 'Printer'
SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Target, Counter), "<br/>", -1) as 'Printer'
________________________

Original vs Populated IP SUBSTRING_INDEX statement:

SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, ' *', 2), "* ", -1) as 'IP'
SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Target, Counter+1), "* ", -1) as 'IP'
________________________

Original vs Populated Driver SUBSTRING_INDEX statement:

SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, ' *', 3), "* ", -1) as 'Driver'
SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Target, Counter+2), "* ", -1) as 'Driver'
________________________

Note that this wasn't an issue for the Error SUBSTRING_INDEX statement, as it targeted "<br/>", which only occurs once per printer string.

Original vs Populated Error SUBSTRING_INDEX statement:

SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, '<br/>', 2), "* ", -1) as 'Error Code'
SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, Target, Counter), "* ", -1) as 'Error'
______________________________________________________________________________
______________________________________________________________________________

(14) Removing Unutilized Rows
______________________________________________________________________________

As the Cartesian product table has more rows than there are printers (125 - 116), nine of the rows in each outer inline view ended up with

garbage and need to be removed.  Fortunately, these extra rows held identical data, so excluding them just required a WHERE statement (or two)

added to each view.
________________________

Printer_Name View cleanup statement:

WHERE Printer != ''
________________________

Error_Code View cleanup statements:

WHERE Error != '0<br/>'
AND Error != ''
________________________

IP_Address View cleanup statement:

WHERE IP != '0<br/>'
________________________

Printer_Driver View cleanup statement:

WHERE Driver != '0<br/>'
______________________________________________________________________________
______________________________________________________________________________

(15) Constructing a Counter Column for Joining Inline Views
______________________________________________________________________________

Add a Counter column for joining Printer_Name, Error_Code, IP_Address and Printer_Driver views

Getting the target strings into discrete inline views was nice, but the ultimate goal was to get them all in a single view (report) - and with

all of the rows in the original order (so any sorting was off limits within the individual views).  Since these didn't share a common column

(index) to JOIN them together, I used a simple user variable statement to count the rows and then aliased this as a (unique) Counter column.  

As there was already a set number of rows (116, after the "cleanup" statements), this approach works just fine.  

It's worth mentioning that this simpler statement wouldn't work for the inner substring extraction queries, because they use a statement that

both creates and populates the counter column simultaneously, which means that the final count isn't actually determined until the statement

finishes running through the Cartesian product table.
________________________

Another thing worth mentioning is that since the views would be joined on this "generic" counter column, the name had to be unique for each

view (i.e. Counter1, Counter2, etc).  I also used unique user variables in these outer views for further differentiation.  In simplest terms,

the construction looks like this:

SELECT Printer, @j:=@j+1 as Counter
FROM
  (SELECT ... statements to extract strings... ) Filter,
  (SELECT @j:=0) Define_Variables

Example Output:

Printer   Counter
wwh150    1
wel160c   2
wel158    3
war161    4
war157c   5
war157    6
war156    7
etc...
______________________________________________________________________________
______________________________________________________________________________

(16) JOINing the MACHINE table
______________________________________________________________________________

I wanted to include the MACHINE table in case the scope of this grows to include other machines (so I could sort and break on MACHINE.NAME),

but joining it to another table requires a column/index that matches up with a MACHINE column/index.  This only gave me one option of where to

make the join, as the only other K1000 table called in this query is MACHINE_CUSTOM_INVENTORY, and it is only called in one inline view

(Filter).  With that determined, it was just a typical JOIN.  

A couple of things to mention here - if any other tables need joined in order to have their columns used (ex: LABEL), this is where to do it.  

Also, any columns that are selected should be aliased so they can be (easily) selected in the main query.  For example, if two columns were

selected in this view with similar names (i.e. MACHINE.NAME, LABEL.NAME) without being aliased, calling one (or both) in the main query

(VIEW.NAME) would lead to an "ambiguous error" condition.

SELECT M.NAME as Machine,
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN ... Temp_Table_to_Populate_Substring_Index
JOIN MACHINE M on (M.ID = MCI.ID)
______________________________________________________________________________
______________________________________________________________________________

(17) CASE Statements to Clarify DetectedErrorState Codes
______________________________________________________________________________

As I showed earlier, there are a number of possible DetectedErrorState code values, so I added the following CASE statement to the main query

to clarify their meaning in the report.  Note that I changed code 0's description from "Unknown" to "Ready", as this is the default state of a

working printer and "Unknown" implies some type of problem.

CASE
WHEN Error = 0 THEN 'Ready'
WHEN Error = 1 THEN 'Other'
WHEN Error = 2 THEN 'No Error'
WHEN Error = 3 THEN 'Low Paper'
WHEN Error = 4 THEN 'No Paper'
WHEN Error = 5 THEN 'Low Toner'
WHEN Error = 6 THEN 'No Toner'
WHEN Error = 7 THEN 'Door Open'
WHEN Error = 8 THEN 'Jammed'
WHEN Error = 9 THEN 'Offline'
WHEN Error = 10 THEN 'Service Requested'
WHEN Error = 11 THEN 'Output Bin Full'
END
______________________________________________________________________________
______________________________________________________________________________

(18) WHERE Statement to Only List Error Codes
______________________________________________________________________________

Since the majority of the printers are typically online and working fine, it's nice to run another version of the report with this line in the

main query to exclude them (i.e. those with a code of "0").

WHERE Error != 0
______________________________________________________________________________
______________________________________________________________________________

(X) Conclusion

I hope this might be as useful for others as it is for us.  The alternatives were either spending a *lot* of time configuring SNMP on all of

the printers and trapping it, or expensive applications.  I'm glad to say the K1000 helped us add another tool in our toolbox.

Hope that helps!

John