Webcam report, the wrong way

[Tholmes]

And then when I view it, I have to sort the view by manufacturer, type in Logitech and get all the Logitech webcams in there. Have I gone round this a long convoluted way, or is this the best?
Is there a way for me to just have the Logitech sorted on its own. (I also need from the Name column to search for 'Microsoft LifeCam Front' as well.)

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblUSBDevices.Name,
tblUSBDevices.Manufacturer
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblUSBDevices On tblAssets.AssetID = tblUSBDevices.AssetID
Inner Join web50repwinusbdevices On tblUSBDevices.AssetID =
web50repwinusbdevices.AssetID And
tblAssets.AssetID = web50repwinusbdevices.AssetID
Where tblUSBDevices.Name Not Like '%mouse%' And tblUSBDevices.Name Not
Like '%keyboard%' And tblUSBDevices.Name Not Like 'usb root hub' And
tblUSBDevices.Name Not Like 'generic usb hub' And
tblUSBDevices.Name Not Like '%usb root hub%' And
tblUSBDevices.Name Not Like 'usb composite device' And
tblUSBDevices.Name Not Like '%hid-compliant%' And
tblUSBDevices.Name Not Like 'usb input device' And
tblUSBDevices.Name Not Like 'Generic SuperSpeed USB Hub' And
tblUSBDevices.Name Not Like 'Integrated Webcam' And
tblUSBDevices.Name Not Like 'IBtUsb_Filter_00' And
tblUSBDevices.Name Not Like 'USB Input Device (Logitech Download Assistant)'
And tblUSBDevices.Name Not Like 'Headset Microphone (Jabra Link 370)' And
tblUSBDevices.Name Not Like 'Headset Earphone (Jabra Link 370)' And
tblUSBDevices.Name Not Like 'Intel(R) Wireless Bluetooth(R)' And
tblUSBDevices.Name Not Like 'Jabra Link 370' And
tblUSBDevices.Name Not Like 'Logitech USB Input Device' And
tblUSBDevices.Name Not Like 'Logitech Download Assistant' And
tblUSBDevices.Name Not Like 'Microsoft Bluetooth Enumerator' And
tblUSBDevices.Name Not Like 'Microsoft Bluetooth LE Enumerator' And
tblUSBDevices.Name Not Like 'USB Mass Storage Device' And
tblUSBDevices.Name Not Like 'Intel(R) USB 3.0 Root Hub' And
tblUSBDevices.Name Not Like 'VMware USB Pointing Device' And
tblUSBDevices.Name Not Like 'usb printing support' And tblAssetCustom.State =
1

[RCorbeil]

Just checking against my inventory and I'm thinking that relaxing the device name might be in order. If I replace

AND (   tblUSBDevices.Name Like '%webcam%'
       OR tblUSBDevices.Name Like '%LifeCam%')

with

AND tblUSBDevices.Name Like '%cam%'

I turn up a Logitech QuickCam and a few variations on generic "USB Camera" as devices.

Just an observation. Going with an inclusive filter simplifies the query, useful if you know exactly what you want, where going with a long exclusion list makes it easier to see oddballs pop up.

[RCorbeil · Accepted solution]

Since you know that you want to find Microsoft and Logitech webcams, it would probably be simpler to filter by inclusion than by exclusion, i.e. "show me anything like this" vs "show me anything except this list".

If you only want Microsoft and Logitech devices, that's your first filter: USB device manufacturer.

Since you know the various model names of their webcams, you can make a short list of device name filters. In my inventory, "webcam" is common to most webcams and "LifeCam" to several Microsoft webcams. A few of them include "webcam" as part of the mic's description, so I've added an extra filter to eliminate those.

Adding an ORDER BY clause lets you specify how you want the results sorted.

Select Top 1000000
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.Version,
  tblAssets.Lastseen,
  tblUSBDevices.Manufacturer As USBDeviceManufacturer,
  tblUSBDevices.Name As USBDeviceName
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblUSBDevices On tblAssets.AssetID = tblUSBDevices.AssetID
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where
  tblAssetCustom.State = 1
  AND (   tblUSBDevices.Manufacturer LIKE 'Microsoft%'
       OR tblUSBDevices.Manufacturer LIKE 'Logitech%')
  AND (   tblUSBDevices.Name Like '%webcam%'
       OR tblUSBDevices.Name Like '%LifeCam%')
  AND NOT (tblUSBDevices.Name Like '%microphone%')
Order By
  tblUSBDevices.Manufacturer,
  USBDeviceName,
  tblAssets.Domain,
  tblAssets.AssetName

[Tholmes]

This has made my life so much easier and I can send the report over, as always, amazing support from you guys, thanks again.
Tim

RC62N wrote:
Since you know that you want to find Microsoft and Logitech webcams, it would probably be simpler to filter by inclusion than by exclusion, i.e. "show me anything like this" vs "show me anything except this list".

If you only want Microsoft and Logitech devices, that's your first filter: USB device manufacturer.

Since you know the various model names of their webcams, you can make a short list of device name filters. In my inventory, "webcam" is common to most webcams and "LifeCam" to several Microsoft webcams. A few of them include "webcam" as part of the mic's description, so I've added an extra filter to eliminate those.

Adding an ORDER BY clause lets you specify how you want the results sorted.

Select Top 1000000
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.Version,
  tblAssets.Lastseen,
  tblUSBDevices.Manufacturer As USBDeviceManufacturer,
  tblUSBDevices.Name As USBDeviceName
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblUSBDevices On tblAssets.AssetID = tblUSBDevices.AssetID
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where
  tblAssetCustom.State = 1
  AND (   tblUSBDevices.Manufacturer LIKE 'Microsoft%'
       OR tblUSBDevices.Manufacturer LIKE 'Logitech%')
  AND (   tblUSBDevices.Name Like '%webcam%'
       OR tblUSBDevices.Name Like '%LifeCam%')
  AND NOT (tblUSBDevices.Name Like '%microphone%')
Order By
  tblUSBDevices.Manufacturer,
  USBDeviceName,
  tblAssets.Domain,
  tblAssets.AssetName