K1000 Reports - Default, All & Local Printers (w-vbscripts)
_________________________________________________________________
Please consider this to be an addendum to previous my post on capturing printers and other non-inventoried data:
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
If you need any help with getting the items in this post working, please refer to the steps in the previous post.
_________________________________________________________________
The Custom Inventory Fields setup I created for printers has been working great, but on a handful of occasions I found the need to determine the
port, driver and location of the printer. Then I recently received a request for a list of all of the company's printers, including local USB
and LPT printers (which wouldn't be listed on our "master" print server, the machine that has all of our network printers shared out for client
machines and is a handy reference tool as well). Seeing an opportunity to kill two birds with one stone, I once again sought assistance from
Experts Exchange's billprew to tweak my printer script when I ran into some code horror that my tweaks unleashed and then picked up a couple
more MySQL tricks on text manipulation to clean up the resulting cumbersome field entries for a new local printers report. Enjoy!
_________________________________________________________________
_________________________________________________________________
Default Printer
_________________________________________________________________
As the name suggests, this lists the default printer and includes the model, port, driver and location (if any are unavailable, will be blank).
_________________________________________________________________
1) Entry format in Inventory > Computers > Detail Item > Custom Inventory Fields screen (list number may vary):
______________________
3) * Printer Default: 8/8/2012 4:05:03 PM - Logged in user: ajones
------------------------------------------------------
HP LaserJet P2015 * USB001 * HP LaserJet P2015 Series PCL 6 *
_________________________________________________________________
2) Report output format. I add a Break on Columns for Machine, so the output looks nicer in HTML. Someday the K1000 will render line breaks
correctly in the (new) Reports module, but until then the Classic Reports module can be used (if available on your K1000).
______________________
a) Reports (without Break on Columns):
# Machine Default Printer
1 AJONES 8/8/2012 4:05:03 PM - Logged in user: ajones ------------------------------------------------------ HP LaserJet P2015 * USB001 * HP
LaserJet P2015 Series PCL 6 *
______________________
b) Classic Reports (with Break on Columns):
# Default Printer
Machine AJONES
1 8/8/2012 4:05:03 PM - Logged in user: ajones
------------------------------------------------------
HP LaserJet P2015 * USB001 * HP LaserJet P2015 Series PCL 6 *
_________________________________________________________________
3) SQL Report Setup
Note:
Be sure to change the MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID number to match your own Custom Software Inventory item number.
______________________
*Title*
Printer Default
*Report Category*
Printers (Custom)
*Description*
Lists default printer for all machines.
*SQL Select Statement*
SELECT MACHINE.NAME AS 'Machine',
REPLACE((SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE FROM MACHINE_CUSTOM_INVENTORY WHERE MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID AND
MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=6913), '<br/>', '\r\n') AS 'Default Printer'
FROM MACHINE
ORDER BY Machine
*Break on columns*
Machine
_________________________________________________________________
4) vbscript. Use the post referenced at the top for instructions on deploying this via the K1000.
' Script reads a current log file of users and printers, eliminates dupes,
' adds in the current user and their printers, and then rewrites the log file.
'
' This script uses a dictionary object to store the printers for each user.
' There will be one dictionary item per user, and then the "data" of that
' user's dictionary item will have the format:
'
' date-string|printer 1|printer 2|printer 3|...|
' Require variables to be defined before usage
Option Explicit
' Define constants
Const ForReading = 1
Const ForWriting = 2
' Define variable used in mainline of script
Dim strBaseDir, strListFile, objFSO, dicUsers, objShell
' Define base folder and log file name
strBaseDir = "C:\KBOX\"
strListFile = strBaseDir & "printer_default.txt"
' Create needed objects
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject( "WScript.Shell" )
Set dicUsers = CreateObject("Scripting.Dictionary")
' Create the base folder if it doesn't exist
If Not objFSO.FolderExists(strBaseDir) Then
objFSO.CreateFolder(strBaseDir)
End If
' If we have a prior log file, load the users and printers from it first
If objFSO.FileExists(strListFile) Then
GetHistory(strListFile)
End If
' Add printers for the current user to the dictionary
GetPrinters(GetUser("."))
' Dump the contents of the dictionary to the log file
WriteLog(strListFile)
' Subroutnie to load prior history, elliminate any duplicates
Sub GetHistory(strFile)
Dim objFile, strUser, strLine, arrTemp
' Open the log file for reading
Set objFile = objFSO.OpenTextFile(strFile, ForReading)
strUser = ""
' Read each line, looking for user or printer lines, skip blank and dash lines
Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
If strLine <> "" And Mid(strLine, 1, 10) <> "----------" Then
If Instr(strLine, "Logged in user:") Then
arrTemp = Split(Replace(strLine, " ", " "), " ")
strUser = LCase(arrTemp(7))
' Try to add this user to the dictionary
AddUser strUser, arrTemp(0) & " " & arrTemp(1) & " " & arrTemp(2)
Else
If strUser <> "" Then
' Try to add this printer to the dictionary
AddPrinter strUser, strLine, "", "", ""
End If
End If
End If
Loop
objFile.Close
Set objFile = Nothing
End Sub
' Subroutine to get default printer for this user and add to dictionary
Sub GetPrinters(strUser)
Dim strComputer, objWMI, colPrinters, objPrinter
' Make sure we have a user
If strUser <> "" Then
' Add this user to the dictionary if needed
AddUser strUser, FormatDateTime(Now)
' Identify all printers for this user
strComputer = "."
Set objWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colPrinters = objWMI.ExecQuery("Select * from Win32_Printer where Default = 'True'")
For Each objPrinter in colPrinters
' Try to add each printer to dictionary
AddPrinter strUser, objPrinter.Name, objPrinter.PortName, objPrinter.DriverName, objPrinter.Location
Next
Set colPrinters = Nothing
Set objWMI = Nothing
End If
End Sub
' Subroutine to dump the accumulated data from the dictionary to the log file
Sub WriteLog(strFile)
Dim objFile, strUser, arrData, i
' Open the log file for writing
Set objFile = objFSO.OpenTextFile (strFile, ForWriting, True)
' Process each user in the dictionary
For Each strUser in dicUsers
arrData = Split(dicUsers.Item(strUser), "|")
' Header lines for a user
objFile.Writeline arrData(0) & " - " & "Logged in user: " & strUser
objFile.Writeline "------------------------------------------------------"
' Loop through all printers for this user and write to log file
If UBound(arrData) > 0 Then
For i = 1 to UBound(arrData)-1
objFile.Writeline arrData(i)
Next
End If
objFile.WriteBlankLines 1
Next
objFile.Close
Set objFile = Nothing
End Sub
' Function to get the current user id
Function GetUser(strHost)
GetUser = LCase(objShell.ExpandEnvironmentStrings("%UserName%"))
End Function
' Subroutine to add a user to the dictionary (if it doesn't already exist)
Sub AddUser(strUser, strDate)
If dicUsers.Exists(strUser) Then
dicUsers.Item(strUser) = strDate & "|"
Else
dicUsers.Add strUser, strDate & "|"
End If
End Sub
' Subroutine to add a printer to the dictionary for a user (if it doesn't already exist)
Sub AddPrinter(strUser, strPrinter, strPort, strDriverName, strLocation)
Dim strTemp, strAddInfo
strTemp = dicUsers.Item(strUser)
If strPort = "" And strDriverName = "" And strLocation = "" Then
strAddInfo = strPrinter
Else
strAddInfo = strPrinter & " * " & strPort & " * " & strDriverName & " * " & strLocation
End If
If Instr(strTemp, "|" & strAddInfo & "|") = 0 Then
dicUsers.Item(strUser) = strTemp & strAddInfo & "|"
End If
End Sub
_________________________________________________________________
_________________________________________________________________
All Printers
_________________________________________________________________
This lists all printers and includes the model, port, driver and location (if any are unavailable, will be blank).
_________________________________________________________________
1) Entry format in Inventory > Computers > Detail Item > Custom Inventory Fields screen (list number & number of printers may vary):
______________________
4) * Printers List: 8/8/2012 4:03:58 PM - Logged in user: aford
------------------------------------------------------
Microsoft XPS Document Writer * XPSPor...etc
Microsoft Office Document Image Writer...etc
HP LaserJet P2015 * USB001 * HP LaserJ...etc
Adobe PDF * My Documents\*.pdf * Adobe...etc
\\print\pgh157c * IP_172.0.57 * DocuCo...etc
\\print\pgh158c * IP_172.0.58 * PCL6 D...etc
\\print\pgh180 * IP_172.0.80 * LANIER ...etc
\\print\pgh180c * IP_172.0.80 * PCL6 D...etc
\\print\Lanier Mail * lanier-mlrm * LA...etc
\\print\ec156 * IP_172.1.56 * Xerox Wo...etc
\\print\ham156 * IP_172.3.56 * Xerox W...etc
\\print\oak150 * IP_172.4.50 * PCL6 Dr...etc
\\print\shn150 * IP_172.5.50 * HP Lase...etc
\\print\shn152 * IP_172.5.52 * Xerox W...etc
\\print\shn154 * IP_172.5.54 * HP Lase...etc
\\tserver\Color Copier * IP_172.6.50 *...etc
______________________
2) Report output format. I add a Break on Columns for Machine, so the output looks nicer in HTML. Someday the K1000 will render line breaks
correctly in the (new) Reports module, but until then the Classic Reports module can be used.
______________________
a) Reports (without Break on Columns):
# Machine Printers List
1 AJONES 8/8/2012 4:03:58 PM - Logged in user: ajones ------------------------------------------------------ Microsoft XPS Document Writer *
XPSPort: * Microsoft XPS Document Writer * Microsoft Office Document Image Writer * Microsoft Document Imaging Writer Port: * Microsoft Office
Document Image Writer Driver * HP LaserJet P2015 * USB001 * HP LaserJet P2015 Series PCL 6 * Adobe PDF * My Documents\*.pdf * Adobe PDF
Converter * My Documents \\print\pgh157c * IP_172.0.57 * DocuColor 242-252-260 PCL * Pittsburgh \\print\pgh158c * IP_172.0.58 * PCL6 Driver for
Universal Print * Pittsburgh \\print\pgh180 * IP_172.0.80 * LANIER LD145 PCL 6 * Pittsburgh - Mailroom \\print\pgh180c * IP_172.0.80 * PCL6
Driver for Universal Print * Pittsburgh \\print\Lanier Mail * lanier-mlrm * LANIER LD145 PCL 6 * Pittsburgh \\print\ec156 * IP_172.1.56 * Xerox
WorkCentre 5225 * East Canton \\print\ham156 * IP_172.3.56 * Xerox WorkCentre 5225 PCL6 * Hammond \\print\oak150 * IP_172.4.50 * PCL6 Driver
for Universal Print * Oak Hill \\print\shn150 * IP_172.5.50 * HP LaserJet P2015 Series PCL 5 * Shenango \\print\shn152 * IP_172.5.52 * Xerox
WorkCentre 3550 PCL 6 * Shenango \\print\shn154 * IP_172.5.54 * HP LaserJet 4 * Shenango \\tserver\Color Copier * IP_172.6.50 * Gestetner DSc428
PCL 5c * Tarentum
______________________
b) Classic Reports (with Break on Columns):
# Printers List
Machine AJONES
1 8/8/2012 4:03:58 PM - Logged in user: ajones
------------------------------------------------------
Microsoft XPS Document Writer * XPSPort: * Microsoft XPS Document Writer *
Microsoft Office Document Image Writer * Microsoft Document Imaging...etc
HP LaserJet P2015 * USB001 * HP LaserJet P2015 Series PCL 6 *
Adobe PDF * My Documents\*.pdf * Adobe PDF Converter * My Documents
\\print\pgh157c * IP_172.0.57 * DocuColor 242-252-260 PCL * Pittsburgh
\\print\pgh158c * IP_172.0.58 * PCL6 Driver for Universal Print * Pittsburgh
\\print\pgh180 * IP_172.0.80 * LANIER LD145 PCL 6 * Pittsburgh - Mailroom
\\print\pgh180c * IP_172.0.80 * PCL6 Driver for Universal Print * Pittsburgh
\\print\Lanier Mail * lanier-mlrm * LANIER LD145 PCL 6 * Pittsburgh
\\print\ec156 * IP_172.1.56 * Xerox WorkCentre 5225 * East Canton
\\print\ham156 * IP_172.3.56 * Xerox WorkCentre 5225 PCL6 * Hammond
\\print\oak150 * IP_172.4.50 * PCL6 Driver for Universal Print * Oak Hill
\\print\shn150 * IP_172.5.50 * HP LaserJet P2015 Series PCL 5 * Shenango
\\print\shn152 * IP_172.5.52 * Xerox WorkCentre 3550 PCL 6 * Shenango
\\print\shn154 * IP_172.5.54 * HP LaserJet 4 * Shenango
\\tserver\Color Copier * IP_172.6.50 * Gestetner DSc428 PCL 5c * Tarentum
_________________________________________________________________
3) SQL Report Setup
Note:
Be sure to change the MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID number to match your own Custom Software Inventory item number.
______________________
*Title*
Printers List
*Report Category*
Printers (Custom)
*Description*
Lists all printers for all machines.
*SQL Select Statement*
SELECT MACHINE.NAME AS 'Machine',
REPLACE((SELECT MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE FROM MACHINE_CUSTOM_INVENTORY WHERE MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID AND
MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=6560), '<br/>', '\r\n') AS 'Printers List'
FROM MACHINE
ORDER BY Machine
*Break on columns*
Machine
_________________________________________________________________
4) vbscript. Use the post referenced at the top for instructions on deploying this via the K1000.
' Script reads a current log file of users and printers, eliminates dupes,
' adds in the current user and their printers, and then rewrites the log file.
'
' This script uses a dictionary object to store the printers for each user.
' There will be one dictionary item per user, and then the "data" of that
' user's dictionary item will have the format:
'
' date-string|printer 1|printer 2|printer 3|...|
' Require variables to be defined before usage
Option Explicit
' Define constants
Const ForReading = 1
Const ForWriting = 2
' Define variable used in mainline of script
Dim strBaseDir, strListFile, objFSO, dicUsers, objShell
' Define base folder and log file name
strBaseDir = "C:\KBOX\"
strListFile = strBaseDir & "printers_list.txt"
' Create needed objects
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject( "WScript.Shell" )
Set dicUsers = CreateObject("Scripting.Dictionary")
' Create the base folder if it doesn't exist
If Not objFSO.FolderExists(strBaseDir) Then
objFSO.CreateFolder(strBaseDir)
End If
' If we have a prior log file, load the users and printers from it first
If objFSO.FileExists(strListFile) Then
GetHistory(strListFile)
End If
' Add printers for the current user to the dictionary
GetPrinters(GetUser("."))
' Dump the contents of the dictionary to the log file
WriteLog(strListFile)
' Subroutnie to load prior history, elliminate any duplicates
Sub GetHistory(strFile)
Dim objFile, strUser, strLine, arrTemp
' Open the log file for reading
Set objFile = objFSO.OpenTextFile(strFile, ForReading)
strUser = ""
' Read each line, looking for user or printer lines, skip blank and dash lines
Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
If strLine <> "" And Mid(strLine, 1, 10) <> "----------" Then
If Instr(strLine, "Logged in user:") Then
arrTemp = Split(Replace(strLine, " ", " "), " ")
strUser = LCase(arrTemp(7))
' Try to add this user to the dictionary
AddUser strUser, arrTemp(0) & " " & arrTemp(1) & " " & arrTemp(2)
Else
If strUser <> "" Then
' Try to add this printer to the dictionary
AddPrinter strUser, strLine, "", "", ""
End If
End If
End If
Loop
objFile.Close
Set objFile = Nothing
End Sub
' Subroutine to get all pronters for this user and add to dictionary
Sub GetPrinters(strUser)
Dim strComputer, objWMI, colPrinters, objPrinter
' Make sure we have a user
If strUser <> "" Then
' Add this user to the dictionary if needed
AddUser strUser, FormatDateTime(Now)
' Identify all printers for this user
strComputer = "."
Set objWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colPrinters = objWMI.ExecQuery("Select * from Win32_Printer")
For Each objPrinter in colPrinters
' Try to add each printer to dictionary
AddPrinter strUser, objPrinter.Name, objPrinter.PortName, objPrinter.DriverName, objPrinter.Location
Next
Set colPrinters = Nothing
Set objWMI = Nothing
End If
End Sub
' Subroutine to dump the accumulated data from the dictionary to the log file
Sub WriteLog(strFile)
Dim objFile, strUser, arrData, i
' Open the log file for writing
Set objFile = objFSO.OpenTextFile (strFile, ForWriting, True)
' Process each user in the dictionary
For Each strUser in dicUsers
arrData = Split(dicUsers.Item(strUser), "|")
' Header lines for a user
objFile.Writeline arrData(0) & " - " & "Logged in user: " & strUser
objFile.Writeline "------------------------------------------------------"
' Loop through all printers for this user and write to log file
If UBound(arrData) > 0 Then
For i = 1 to UBound(arrData)-1
objFile.Writeline arrData(i)
Next
End If
objFile.WriteBlankLines 1
Next
objFile.Close
Set objFile = Nothing
End Sub
' Function to get the current user id
Function GetUser(strHost)
GetUser = LCase(objShell.ExpandEnvironmentStrings("%UserName%"))
End Function
' Subroutine to add a user to the dictionary (if it doesn't already exist)
Sub AddUser(strUser, strDate)
If dicUsers.Exists(strUser) Then
dicUsers.Item(strUser) = strDate & "|"
Else
dicUsers.Add strUser, strDate & "|"
End If
End Sub
' Subroutine to add a printer to the dictionary for a user (if it doesn't already exist)
Sub AddPrinter(strUser, strPrinter, strPort, strDriverName, strLocation)
Dim strTemp, strAddInfo
strTemp = dicUsers.Item(strUser)
If strPort = "" And strDriverName = "" And strLocation = "" Then
strAddInfo = strPrinter
Else
strAddInfo = strPrinter & " * " & strPort & " * " & strDriverName & " * " & strLocation
End If
If Instr(strTemp, "|" & strAddInfo & "|") = 0 Then
dicUsers.Item(strUser) = strTemp & strAddInfo & "|"
End If
End Sub
_________________________________________________________________
_________________________________________________________________
Local Printers
_________________________________________________________________
This report uses the data from the All Printers Custom Inventory field to list all local USB and LPT (parallel port) printers for all computers.
I was unsatisfied with my previous approach, as (my gut and) further review indicated that I was missing many local printers that were not the
default - and as you can see from the report output below, this was definitely the case. So I sought assistance from Experts Exchange's
lwadwell, who has provided me a "fudge" to parse the potentially large text field for all local printer instances and remove duplicates. This
is by far the most sophisticated MySQL report code I've been involved with up to this point, especially the "fudged" procedure in the
_________________________________________________________________
1) Report output format.
Title: Local Printers (USB, LPT)
Description: Lists local USB and LPT1 (default printer) for all Company machines.
Category: Printers (Custom)
Server Hostname: kbox.company.net
Generated: 2012/08/15 12:33:46
# Machine User Location Local Printer
1 EWALLACE ewallace East Canton HP LaserJet 6P, HP LaserJet 2100 PCL6, hp deskjet 960c, HP LaserJet P2015 Series PCL 6
2 eshaffer eshaffer East Canton HP LaserJet P2015dn
3 GHOWARD GHOWARD East Canton HP LaserJet 1022, HP Deskjet 6940 series
4 mdunn mdunn East Canton HP Officejet Pro 8000 A809 Series
5 TGREEN tgreen East Canton HP LaserJet 4000, HP Photosmart C3100 series
6 gjones gjones Greensboro hp LaserJet 1320 PCL 5e, hp deskjet 960c
7 dsmith dsmith Hammond Brother HL-2040 series, HP DeskJet 970Cse, DYMO LabelWriter 400
_________________________________________________________________
2) SQL Report Setup
Notes:
I have a couple of machines with default printers that have the LPT port mapped to a network printer for legacy apps, so I used the 'M.NAME not
rlike' statement to exclude these. I also excluded specific printer names containing terms like "Fax" (common for many OfficeJet etc to have a
printer and a fax instance) using the 'AND UPPER(R.printer) NOT RLIKE 'fax|pdf..etc' line.
I would recommend running this report and then reviewing results to determine which ones you'll need to scrub. For example, if a printer is
installed twice using different drivers (i.e. PCL 5e, PCL 6), it will typically be listed twice (once for each driver and named as such).
I also used my "computers" (location) labels to include locations, and truncated the "_computers" part using SUBSTRING. For details on how I
set these up, please see this post:
K1000 Reports - Machine Lists and Counts by Site, Role & Type post:
http://www.itninja.com/blog/view/k1000-reports-machine-lists-and-counts-by-site-role-type
Be sure to change the MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID number to match your own Custom Software Inventory item number.
______________________
*Title*
Local Printers (USB, LPT)
*Category*
Printers (Custom)
*Description*
Lists local USB and LPT1 (default printer) for all Company machines.
*SQL Select Statement*
SELECT R.NAME as 'Machine', R.USER as 'User', R.LOCATION as 'Location',
GROUP_CONCAT(printer SEPARATOR ", ") as 'Local Printer'
FROM (SELECT DISTINCT M.NAME, M.USER, SUBSTRING(L.NAME, 1, LENGTH(L.NAME) - 10) as Location,
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(MCI.STR_FIELD_VALUE, stype, idx), "<br/>", -1)) as printer
FROM MACHINE_CUSTOM_INVENTORY MCI
JOIN MACHINE M ON (M.ID = MCI.ID)
JOIN MACHINE_LABEL_JT ML ON (ML.MACHINE_ID = M.ID)
JOIN LABEL L ON (L.ID = ML.LABEL_ID)
JOIN (SELECT stype, @i:=IF(stype=@l, @i+1, 1) as idx, @l:=stype
FROM (SELECT a.stype
FROM (SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')a,
(SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')b,
(SELECT '* USB' as stype UNION ALL SELECT '* DOT' UNION ALL SELECT '* LPT')c
ORDER BY a.stype)u,
(SELECT @i:=0,@l:='',@d)v)y
WHERE MCI.SOFTWARE_ID = 6560
AND MCI.STR_FIELD_VALUE RLIKE 'usb|dot|lpt'
AND L.NAME RLIKE 'computers'
AND M.NAME NOT RLIKE 'dwaters|gallison')R
WHERE R.printer <> ''
AND UPPER(R.printer) NOT RLIKE 'fax|pdf|intuit|univeral|copy 2|copy 3'
GROUP BY R.NAME, R.USER, R.LOCATION
ORDER by Location, Machine
_________________________________________________________________
Well, that's all this time. As usual, hope that helps someone!
John
John - jverbosk 12 years ago