SCCM report Computers with the attached printers in the organisation

You might get a request to find out all the computers in organisation connected to which printers.There might be differetn printers in organisation and connection to the computers will vary.I just thought to post this qeury on the which will be useful for some one.

SELECT A.Name0, A.UserName0, B.Name0, B.DriverName0, B.Location0, B.PortName0, B.ShareName0

FROM v_GS_COMPUTER_SYSTEM A, v_GS_PRINTER_DEVICE B
WHERE A.ResourceID = B.ResourceID
AND B.Name0 != 'Microsoft Office Document Image Writer'
AND B.Name0 != 'Adobe PDF'
AND B.Name0 != 'Microsoft XPS Document Writer'
AND B.Name0 != 'PDF-XChange 3.0'
AND B.DriverName0 != 'Microsoft Shared Fax Driver'
AND B.DriverName0 != 'pdfFactory 2'
AND B.DriverName0 != 'CutePDF Writer'
ORDER BY A.Name0

If you have any other printers whcih should be not be displayed in the report let say ,some users installed Bullzip PDF Printer You can simply add AND b.Drivername0!='Printer name'.

14 Responses to "SCCM report Computers with the attached printers in the organisation"

  1. Hello all,

    Try this in your WQL query

    select SMS_R_System.NetbiosName, SMS_G_System_PRINTER_DEVICE.Name, SMS_G_System_PRINTER_DEVICE.DeviceID, SMS_G_System_PRINTER_DEVICE.DriverName, SMS_G_System_PRINTER_DEVICE.PortName from SMS_R_System inner join SMS_G_System_PRINTER_DEVICE on SMS_G_System_PRINTER_DEVICE.ResourceID = SMS_R_System.ResourceId order by SMS_R_System.NetbiosName, SMS_G_System_PRINTER_DEVICE.Name

    Reply
  2. Hi,

    I ran this query successfully, thanks for it. But it is showing the old printers too which has made the list useless for me because I am not sure whether it's a current printer or user has removed it now.

    Is there any way to check if that printer is currently connected to the computer?

    Reply
    1. During the inventory ,printers should get updated and same will be available in CM DB.You can pick one client and see if the printers that are removed on the PC is still available in CM or not.

      Reply
  3. Copying your text removes some spaces, this one works
    Also, to get a list of all printer devices in the database execute:
    SELECT * FROM v_GS_PRINTER_DEVICE

    =================================================================================
    SELECT A.Name0, A.UserName0, B.Name0, B.DriverName0, B.Location0, B.PortName0, B.ShareName0

    FROM v_GS_COMPUTER_SYSTEM A, v_GS_PRINTER_DEVICE B
    WHERE
    A.ResourceID = B.ResourceID
    AND B.Name0 != 'Microsoft Office Document Image Writer'
    AND B.Name0 != 'Adobe PDF'
    AND B.Name0 != 'Microsoft XPS Document Writer'
    AND B.Name0 != 'PDF-XChange 3.0'
    AND B.DriverName0 != 'Microsoft Shared Fax Driver'
    AND B.DriverName0 != 'pdfFactory 2'
    AND B.DriverName0 != 'CutePDF Writer'
    ORDER BY A.Name0

    Reply
  4. Hi whenever i try to run this report in SCCM 2012 i get an error along the lines of cannot have the same key. If i Remove B.Name0 it works if I just use B.DriverName0 maybe some sort of conflict with A.Name0 ?

    Reply
    1. A.Name0 is computer name from v_gs_computername view. I just ran the query in SQL Studio,i dont see any issues.all works good.

      Reply
    1. the report basically list down printers what are they attached to system with the exclusion of software printer as given in report like image writer,pdf etc.
      you can more other printers as well which u dont want to see them by default!

      Reply
  5. Hi Eswar,
    Nice post but unfortunately this query does not run in my SQL management studio.

    Am I missing any thing...?

    Many thanks,
    Ameen.

    Reply
    1. what is the error message do you get when you run this ?
      did you selected the correct database not master? should be like SMS_sitecode ?

      Reply

Leave a Reply to Eswar Koneti Cancel reply