The aim of this article is to configure Microsoft System Centre Configuration Manager 2007 SP1 or R2 to report on the locally installed printers, this in my case is very useful for reporting on printers installed and shared on our Print Server.
The fields we will report on are Netbios name of the computer, ShareName, Driver Name, Location, Port Name and Print processor but there are several other fields you can add.
This article assumes you already have SCCM up and running
Step1: Modify SMS_Def.mof on the Server
The SMS_Def.mof file basically details what hardware information stored in WMI you want to inventory from the clients.
SMS_Def.mof can be expanded to add new reported information but in the case of local printers the SMS_Def.mof file has all the configuration we need we just need to to tell SCCM that we want to report on “Printer Devices”
Open the SMS_Def.mof file on your server normally under \\severname\sms_sitecode\inboxes\cliefiles.src\hiv
Find the SMS Group “Printer Device”
Set the SMS_Report field just above to TRUE and set each of the feild below that you want to report on to TRUE
My SMS_Def.mof looks like the below
[ SMS_Report (TRUE),
SMS_Group_Name (“Printer Device”),
SMS_Class_ID (“MICROSOFT|PRINTER_DEVICE|1.0”) ]
class Win32_Printer : SMS_Class_Template
{
[SMS_Report (FALSE) ]
uint32 Attributes;
[SMS_Report (FALSE) ]
uint16 Availability;
[SMS_Report (FALSE) ]
uint32 AveragePagesPerMinute;
[SMS_Report (FALSE) ]
uint16 Capabilities[];
[SMS_Report (FALSE) ]
string CapabilityDescriptions[];
[SMS_Report (FALSE) ]
string Caption;
[SMS_Report (FALSE) ]
uint32 ConfigManagerErrorCode;
[SMS_Report (FALSE) ]
boolean ConfigManagerUserConfig;
[SMS_Report (FALSE) ]
uint32 DefaultPriority;
[SMS_Report (FALSE) ]
string Description;
[SMS_Report (FALSE) ]
uint16 DetectedErrorState;
[SMS_Report (FALSE), key]
string DeviceID;
[SMS_Report (TRUE) ]
string DriverName;
[SMS_Report (FALSE) ]
boolean ErrorCleared;
[SMS_Report (FALSE) ]
string ErrorDescription;
[SMS_Report (FALSE) ]
uint32 HorizontalResolution;
[SMS_Report (FALSE) ]
datetime InstallDate;
[SMS_Report (FALSE) ]
uint32 JobCountSinceLastReset;
[SMS_Report (FALSE) ]
uint16 LanguagesSupported[];
[SMS_Report (FALSE) ]
uint32 LastErrorCode;
[SMS_Report (TRUE) ]
string Location;
[SMS_Report (TRUE) ]
string Name;
[SMS_Report (FALSE) ]
uint16 PaperSizesSupported[];
[SMS_Report (FALSE) ]
string PNPDeviceID;
[SMS_Report (TRUE) ]
string PortName;
[SMS_Report (FALSE) ]
uint16 PowerManagementCapabilities[];
[SMS_Report (FALSE) ]
boolean PowerManagementSupported;
[SMS_Report (FALSE) ]
string PrinterPaperNames[];
[SMS_Report (FALSE) ]
uint32 PrinterState;
[SMS_Report (FALSE) ]
uint16 PrinterStatus;
[SMS_Report (FALSE) ]
string PrintJobDataType;
[SMS_Report (TRUE) ]
string PrintProcessor;
[SMS_Report (FALSE) ]
string SeparatorFile;
[SMS_Report (TRUE) ]
string ServerName;
[SMS_Report (TRUE) ]
string ShareName;
[SMS_Report (FALSE) ]
boolean SpoolEnabled;
[SMS_Report (FALSE) ]
datetime StartTime;
[SMS_Report (FALSE) ]
string Status;
[SMS_Report (FALSE) ]
uint16 StatusInfo;
[SMS_Report (FALSE) ]
string SystemName;
[SMS_Report (FALSE) ]
datetime TimeOfLastReset;
[SMS_Report (FALSE) ]
datetime UntilTime;
[SMS_Report (FALSE) ]
uint32 VerticalResolution;
};
Save the file.
You will be unable to report on the new fields util clients get the new policy and start sending up data, to speed things up you can one one of the Clients that has the agent installed go into Control Pannel => Configuration Manager =>Actions Tab => Select Machine Policy Retrieve => Initiate Action
Step 2: Creating the Report
Within the SCCM console Site Database => Computer Management => Reporting
Right-client Reports => New Report
Give it a name and select a category
Select Edit SQL Statement
My SQL statement is as below
SELECT SYS.Netbios_Name0,printer.ShareName0,printer.DriverName0, printer.Location0,printer.PortName0,printer.PrintProcessor0 FROM v_GS_PRINTER_DEVICE printer JOIN v_R_System SYS ON SYS.ResourceID = printer.ResourceID ORDER BY SYS.Netbios_Name0
However some people have reported problem with this SQL and instead have found the below works better
SELECT Distinct SYS.Netbios_Name0, PRINTER.Name0, PRINTER.DeviceID0, PRINTER.DriverName0, PRINTER.PortName0 FROM v_R_System SYS JOIN v_GS_PRINTER_DEVICE PRINTER ON SYS.ResourceID = PRINTER.ResourceID Where SYS.Netbios_Name0 LIKE @variable ORDER BY SYS.Netbios_Name0, PRINTER.Name0
You spammer filter messed up my comment, but there is a typo in this post. you sms_def.mof edit is incorrect.
Thanks for sharing.
I have question about printer's serials.What do i have to add this information?
Thanks
Hi,
I do not believe SCCM can recieve the serial number directly
A work around may be; SCCM can recieve what has been set in the description feild on the printer queue, maybe manually or by script add the serial number into the description field of the printer queues
I cannot get this to work at all.
SCCM complains that 'DriverName0' and each of those other fields does not exist.
I vaguely remember some notes about, if changing the MOF file(s), there is then some kind of re-compilation that you have to do – does that sound right, or am I doing something else wrong?
I changed the MOF file as you mentioned, and I saved that, so… what am I missing? (Same error on each of the values, such as DeviceID0)
???
Any help appreciated.
This is nice, but if we have only "local printers" via USB, and no print queues – how would we accomplish the same thing?
If I move printer to another local workstation, then it becomes a "new [local] print queue" and so the description does not follow the printer.
We do not have a print server, and instead, we have 180 local printers – each person has his/her own printer.
And NONE of them are connected via ethernet/tcp-ip – ALL printers are local/USB only.
Thoughts?
I'm thinking maybe update something from the printer's own control panel, so we have the Ser # somewhere there,
Or put a 2-port network switch at each person's desk?
Any help would be appreciated – these are Dell 2330dn printers.
Thanks!
the error is [4200][134][Microsoft][ODBC SQL Server Driver][SQL Server] Must Declare the Scalar variable "@variable"
please any one can help me.
This is the query I use in my standard reports for local printers. It will report on any local printers connected to the machine including any IP based connected printers since they show up as being a local printer. For network printers on the machine (such as those that users connect to via a print server), I have had to generate a custom MOF file and have SCCM collect it. Anyways, here is the one for the local printers. This query should also work if using SSRS.
SELECT SYS.Netbios_Name0,printer.ShareName0,printer.DriverName0, printer.Location0,printer.PortName0,printer.PrintProcessor0
FROM v_GS_PRINTER_DEVICE printer
JOIN v_R_System SYS ON SYS.ResourceID = printer.ResourceID
ORDER BY SYS.Netbios_Name0
Cool, thanks for sharing!
When I run any of the above scripts I keep getting "Invalid location" I've run the DataShift script locally on my laptop and edited the sms_def.mof file on my sccm server but cannot seem to generate any reports ?
Any ideas?
Ta in advance…
Hi,
That is not a message I have come across before, can I clarify a few bits.
– When are you getting the “invalid location” message is that when you are running the report or creating the report?
– Are you getting the “invalid location” message with this one report or all reports?
– How are you tring to run the reports? From within the “Configuration Management Console” or from the webpage?
– Has this only just started happening or has this always been the case?
Phil
Hi,
In your SMS_Def.mof is report set to true for location?
[SMS_Report (TRUE) ]
string Location;
As a test try creating the report without location and see what happens
SELECT SYS.Netbios_Name0,printer.ShareName0,printer.DriverName0,printer.PortName0,printer.PrintProcessor0
FROM v_GS_PRINTER_DEVICE printer
JOIN v_R_System SYS ON SYS.ResourceID = printer.ResourceID
ORDER BY SYS.Netbios_Name0
If you have access to the SQL server take a look at the view dbo.v_GS_PRINTER_DEVICE and see what columns and data you have?
You should have a column for each of the items with is set to true in SMS_Def.mof
Finally after modifying SMS_Def.mof it takes a bit of time for data to start coming in from clients, maybe that.
Phil
Hi,
I am not totally sure what you are tring to do?
My modified SMS_Def.mof and report will show all local printers on a client or server and also tell you if they are sharded and it does this without any third party scripts etc.
What is does not do is show you want network printers the user is connected to, is that what you are tring to get?
I have not used the datashift scripts before, I believe that will give you local and network printers. I should be able to help with an SQL query to report on what is in the SMX_Printers table, is that what you are after?
Phil
Yep and Yep,
After network printers a user is connected to ? and a SQL report on whats in SMX_Printer by collection or client if possible ???
Hi,
I think this should do the basis of what you are after.
I have guessed the view name will be v_GS_SMX_Printers0, have a check if that view exits it should be named something similar and change the scripts as needed
To create a report that lists all data
SELECT SYS.Netbios_Name0, DeviceID0, DriverName0, Comment0, Location0, Network0, Shared0, ServerName0, ShareName0
FROM v_GS_SMX_Printers0 printer JOIN v_R_System SYS ON SYS.ResourceID = printer.ResourceID
ORDER BY SYS.Netbios_Name0
To create a report will a computer name prompt
SELECT SYS.Netbios_Name0, DeviceID0, DriverName0, Comment0, Location0, Network0, Shared0, ServerName0, ShareName0
FROM v_GS_SMX_Printers0 printer JOIN v_R_System SYS ON SYS.ResourceID = printer.ResourceID
WHERE sys.Netbios_Name0=@ComputerName
ORDER BY SYS.Netbios_Name0
Then add a prompt
– Click prompts
– Click the star/ new icon
Name: ComputerName
Prompt text: Computer Name
Allow an empty value: not checked
Provides a SQL statement: checked
– Paste the below
begin
IF (@__filterwildcard = '')
SELECT DISTINCT SYS.Netbios_Name0 FROM v_R_System SYS ORDER BY SYS.Netbios_Name0
else
SELECT DISTINCT SYS.Netbios_Name0 FROM v_R_System SYS
WHERE SYS.Netbios_Name0 LIKE @__filterwildcard
ORDER BY SYS.Netbios_Name0
end
– Ok / Yes to all prompts
Let me know how you get on?
Phil
Cool, glad it is working!
The below should create a report filtered on collection again change as needed.
SELECT SYS.Netbios_Name0, DeviceID0, DriverName0, Comment0, Location0, Network0, Shared0, ServerName0, ShareName0
FROM v_GS_SMX_Printers0 printer
JOIN v_R_System SYS ON SYS.ResourceID = printer.ResourceID
JOIN v_FullCollectionMembership collection ON SYS.ResourceID = collection.ResourceID
WHERE collection.CollectionID=@Id
ORDER BY SYS.Netbios_Name0
Then add a prompt
– Click prompts
– Click the star/ new icon
Name: Id
Prompt text: Select a Collection to View
Allow an empty value: not checked
Provides a SQL statement: checked
– Paste the below
begin
IF (@__filterwildcard = '')
SELECT DISTINCT CollectionID, Name FROM v_Collection ORDER BY Name
else
SELECT DISTINCT CollectionID, Name FROM v_Collection
WHERE CollectionID LIKE @__filterwildcard
ORDER BY Name
end
– Ok / Yes to all prompts
Ah bonza !! Cheers Phil, I owe you two pints now !! 🙂
Paul.
Hello when trying to run sql I am receiving the following error.
An error occurred when the report was run. The details are as follows:
Invalid object name ‘v_GS_PRINTER_DEVICE’.
Error Number: -2147217865
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 208
Can anybody help with this?
Thank You for any help.
I know this may be an old thread, but its great. I am trying to build a report based on collection but I am getting the following error: