Asset relations report- printers

[fbg]

Looking for a report for all printers and the devices connected to printer.

Example

Printer1

>Computer1

>Computer2

Printer2

>Computer1

>Computer2

[rom]

I'd recommend using a version of the regular printers report, as I'm guessing since you have multiple computers to a printer,  it's a network printer.   Since it's a network printer, you will want to query the printer port names, looking for an IP address, or a domain name printer.  That should get you what you're looking for:

Select Top (1000000) tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen As [Last successful scan],
  tblAssets.Lasttried As [Last scan attempt],
  tblPrinters.Caption As Printer,
  tblPrinters.Portname As PrinterPort,
  tblPrinters.Printprocessor,
  tblPrinters.Capabilitydescriptions As PrinterCapabilities,
  tblPrinters.Lastchanged
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblPrinters On tblAssets.AssetID = tblPrinters.AssetID
  Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where (tblPrinters.Portname Like '%.%.%.%' Or tblPrinters.Portname Like
    '%yourdomain.org%') And tblState.Statename = 'Active'
Order By tblAssets.Domain,
  tblAssets.AssetName,
  Printer

[fbg]

This works for what I need.  Thank you!

[rader]

This comes off of one of Esbens printer driver reports, modified for printers first, then attached data following. This should give you a good start. Delete the columns you don't want and filter on the report.

It's a quick and dirty report though.

Select Top 1000000 pdu.Name As [Printer Name],
  a.AssetID,
  a.AssetName,
  a.Domain,
  a.Username,
  a.Userdomain,
  Coalesce(os.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  a.IPAddress,
  ipl.IPLocation,
  ac.Manufacturer,
  ac.Model,
  os.OSname As OS,
  a.SP,
  a.Lastseen As [Last successful scan],
  a.Lasttried As [Last scan attempt],
  pd.DriverPath,
  pdu.Provider,
  pd.Version,
  pd.DriverVersion As [Full version],
  pd.PrintProcessor,
  pd.HardwareID
From tblAssets a
  Left Join tblAssetCustom ac On ac.AssetID = a.AssetID
  Inner Join tblPrinterDrivers pd On a.AssetID = pd.AssetID
  Inner Join tblPrinterDriversUni pdu On pd.PrinterDriverUniID =
      pdu.PrinterDriverUniID
  Left Join tsysAssetTypes tsysAssetTypes On tsysAssetTypes.AssetType =
      a.Assettype
  Left Join tsysIPLocations ipl On a.IPAddress = ipl.IPLocation
  Left Join tsysOS os On os.OScode = a.OScode
  Left Join tblState st On st.State = ac.State
Where Lower(st.Statename) = 'active'
Group By pdu.Name,
  a.AssetID,
  a.AssetName,
  a.Domain,
  a.Username,
  a.Userdomain,
  Coalesce(os.Image, tsysAssetTypes.AssetTypeIcon10),
  a.IPAddress,
  ipl.IPLocation,
  ac.Manufacturer,
  ac.Model,
  os.OSname,
  a.SP,
  a.Lastseen,
  a.Lasttried,
  pd.DriverPath,
  pdu.Provider,
  pd.Version,
  pd.DriverVersion,
  pd.PrintProcessor,
  pd.HardwareID
Order By [Printer Name],
  a.Domain,
  a.AssetName