Setting Microsoft SCCM Report on Local Printers

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

 

18 thoughts on “Setting Microsoft SCCM Report on Local Printers”

  1. 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

    Reply
  2. 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?

    An error occurred when the report was run. The details are as follows:
    The multi-part identifier "PRINTER.DriverName0" could not be bound.

     

    Error Number: -2147217900
    Source: Microsoft OLE DB Provider for SQL Server
    Native Error: 4104

    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.

    Reply
  3. 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!

    Reply
  4. the error is [4200][134][Microsoft][ODBC SQL Server Driver][SQL Server] Must Declare the Scalar variable "@variable"

     

    please any one can help me.

    Reply
  5. 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

     

    Reply
  6. 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…

    Reply
  7. 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
     

    Reply
  8. 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
     

    Reply
  9. 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

    Reply
  10. 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 ??? 

    Reply
  11. 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

    Reply
  12. 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
     

    Reply
  13. 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.

    Reply
  14. 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:

    An error occurred when the report was run. The details are as follows:
    Invalid object name 'v_GS_SMX_Printers0'
    Any suggestions would be appreciated.  
     
    JL
    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.