cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Tholmes
Engaged Sweeper III
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
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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

View solution in original post

3 REPLIES 3
RCorbeil
Honored Sweeper II
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
Honored Sweeper II
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
Engaged Sweeper III
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