Locally Connected Devices

[tbeau1]

Hello,

I'm looking for a report that could generate information about devices connected to each computer in our domain. I've recently found a SQL query on these forums which allowed me to pull accurate information on USB/Local printers installed on each computer in Lansweeper. However, I've also been tasked with pulling information on locally installed ADF scanners.

I've got it in my head now that maybe it would be easier if I was able to just generate a report about all locally connected devices and just sort on the columns afterwards. I think just a list of devices that are attached to each USB port would probably work fine. We don't really utilize any other ports for devices, so I think that would cover all grounds. Is this possible to do within LS?

Any help is greatly appreciated! Thanks!

[AZHockeyNut]

No worries! happy to help!

[tbeau1]

Hey, thanks for your help! I think I've got a pretty good starting point here, at least for the scanners. I ended up taking your more refined query and adding just one line to it:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblUSBDevices.Name,
  tblUSBDevices.Manufacturer,
  tblUSBDevices.DeviceID,
  tblUSBDevices.LastChanged
From tblAssets
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblUSBDevices On tblAssets.AssetID = tblUSBDevices.AssetID
Where (tblUSBDevices.Name Like '%scan%') Or
  (tblUSBDevices.Manufacturer Like '%fujitsu%')
Order By tblAssets.AssetName

[AZHockeyNut]

there is a canned report Port: USB but it did not give me much I considered valuable.
So I figured out what gets run when I click and asset and it's USB Devices more or less.

Select
tblAssets.AssetID ,
tblAssets.AssetName,
tblUSBDevices.name,
[tblUSBDevices].[Manufacturer],
[tblUSBDevices].[DeviceID],
[tblUSBDevices].[LastChanged]
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblusbdevices On tblAssets.AssetID = tblUSBDevices.AssetID
Order By
tblAssets.AssetName


you could add a where clause above the order by

such as

Select
tblAssets.AssetID ,
tblAssets.AssetName,
tblUSBDevices.name,
[tblUSBDevices].[Manufacturer],
[tblUSBDevices].[DeviceID],
[tblUSBDevices].[LastChanged]
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblusbdevices On tblAssets.AssetID = tblUSBDevices.AssetID
Where tblUSBDevices.name like '%scan%'
Order By
tblAssets.AssetName

depends on how specific your data is... if they called it Scanner.. or that is in the name. If you have all ADF as a specific manufacturer, say Canon, you could change the Where to tblUSBDevices.Manufacturer like '%Canon% I guess.

hope that helps get you closer. I don't have any USB scanners so I had to guess a bit.

[tbeau1]

This is the one that I found on these forums for just printers:

Select Top 1000000 tsysOS.Image As Icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.IPAddress As IP,
  tblPrinters.Caption As Printer,
  tblPrinters.Portname As [Printer Port],
  tblPrinters.Capabilitydescriptions As [Printer Capabilities],
  tblPrinters.Lastchanged
From tblAssets
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblPrinters On tblAssets.AssetID = tblPrinters.AssetID
Where (tblPrinters.Portname Like 'DOT4%' Or tblPrinters.Portname Like 'USB%')
  And tblPrinters.Local = 1
Order By tblAssets.Domain,
  tblAssets.AssetName,
  Printer

[AZHockeyNut]

it might help if you post the query you currently have so we can see what you are starting with. happy to give it a go if you start me off with what you have.