The aim of this article is to configure Microsoft System Centre Configureation 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
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 Retrivel => Initiate Action
Step 2: Creating the Report
Within the SCCM console Site Database => Computer Managment => Reporting
Right-client Reports => New Report
Give it a name and select a category
Select Edit SQL Statement
My SQL statement is as below
However some people have reported problem with this SQL and instead have found the below works better
Comments
You spammer filter messed up my comment, but there is a typo in this post. you sms_def.mof edit is incorrect.
Do you have a correction? I get this error when I generate the report:
Garth: Do you have a correction? This is the error I get:
This works for me:
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
Excellent, thanks for letting us know!
I have updated the post
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.
@ Guest on Fri, 02/11/2011 - 19:39.
Hi,
Try this updated SQL?
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?
Any chance you can logon in and attach a screenshot or mail one to webmaster@geeksahngout.com
Phil
I run the following report inside config mgr...
and get this error...
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
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
Looked in that dbo.v_GS_PRINTER_DEVICE
And there's a lot of data in there,
The one's i'm interested are per client more than anything in the table dbo.smx_printers_Data (via using the datashift_printers script on each client)
I can see SCCM pulling the data through from my laptop as I run the script locally as a test...I think all I need now is a custom report that shows that data and allows me to select which collection/client ???????
Sorry if that doesn't make sense?
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
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
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
Legend !!!
A little bit of tinkering with the field names etc but that's working a treat !! I owe you a pint sir.... :-)
Just another quick question?
If I wanted to change the prompt to ask for a collection rather than a single machine is that possible?
Cheers,
Paul.
Cool, glad it is working!
The below should create a report filtered on collection again change as needed.
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
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.
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:
Add new comment