Notification

Icon
Error

Webcam report, the wrong way - I have found a USB report and removed what I know is not a webcam from teh report

Posted: Wednesday, October 21, 2020 9:43:53 PM(UTC)
TimHolmes1973

TimHolmes1973

Member Original PosterPosts: 20
0
Like
This issue has been solved! Click here to view the solution
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
RC62N
#1RC62N Member Posts: 488  
posted: 10/22/2020 6:05:43 PM(UTC)
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.
Code:
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
RC62N
#2RC62N Member Posts: 488  
posted: 10/23/2020 4:29:08 PM(UTC)
Just checking against my inventory and I'm thinking that relaxing the device name might be in order. If I replace
Code:
AND (   tblUSBDevices.Name Like '%webcam%'
       OR tblUSBDevices.Name Like '%LifeCam%')

with
Code:
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.
TimHolmes1973
#3TimHolmes1973 Member Original PosterPosts: 20  
posted: 10/23/2020 4:38:45 PM(UTC)
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

Originally Posted by: RC62N Go to Quoted Post
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.
Code:
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


Active Discussions

Report Center Default Browser and Version
by  RC62N  
Go to last post Go to first unread
Last post: 11/26/2020 8:00:54 PM(UTC)
Lansweeper Trying to make a custom report that sorts assets by state
by  mzipperer   Go to last post Go to first unread
Last post: 11/25/2020 5:36:52 PM(UTC)
Lansweeper Report - All Assets with Specfic Default Gateway
by  RC62N  
Go to last post Go to first unread
Last post: 11/25/2020 4:18:08 PM(UTC)
Report Center Windows update report
by  ToeJoe   Go to last post Go to first unread
Last post: 11/24/2020 3:39:00 PM(UTC)
Lansweeper Report - assets
by  Andy.S  
Go to last post Go to first unread
Last post: 11/23/2020 2:42:01 PM(UTC)
Lansweeper Run Report on PCs from a list
by  RC62N   Go to last post Go to first unread
Last post: 11/16/2020 4:23:12 PM(UTC)