Duplicate computers in Webcam query

[Tholmes]

You amazing guys helped me with this report before.
https://www.lansweeper.com/forum/yaf_postsm64039_Webcam-report--the-wrong-way.aspx#post64039
I have modified and added to it somewhat since then and whenever I run it, it now duplicates a pc, with the same webcam, with only one webcam attached, both Dell and HP, laptops and desktops, but bizarrely, some computers are shown only once, with the same hardware that is being duplicated! So to say I am lost is an understatement, I have had a look and browsed through the forum, but I was unable to see anything about duplicated results, any help greatly appreciated.

Here is my code

Select Top 1000000 Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As
icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
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 (tblUSBDevices.Manufacturer Like 'Microsoft%' Or
tblUSBDevices.Manufacturer Like 'Logitech%') And
Not (tblUSBDevices.Name Like '%microphone%') And
tblUSBDevices.Name Not Like '%Microsoft LifeCam Front%' And
tblUSBDevices.Name Not Like '%UVC Camera%' And tblUSBDevices.Name Not Like
'%Microsoft LifeCam Rear%' And tblUSBDevices.Name Not Like 'Integrated Webcam'
And tblUSBDevices.Name Not Like '%ConferenceCam Connect%' And
tblUSBDevices.Name Like '%cam%' And tblAssetCustom.State = 1
Order By USBDeviceManufacturer,
USBDeviceName,
tblAssets.Domain,
tblAssets.AssetName

[RCorbeil]

If your installation isn't configured to treat upper/lowercase string comparisons the same (I'm not an SQL Server admin, so I'm assuming it's an option, because my setup doesn't distinguish), you should be able to force the issue using Upper() or Lower().

e.g.

Lower(tblUSBDevices.Name) Not Like '%c922 pro stream webcam%'

or if you don't trust yourself not to remember to lowercase everything

Lower(tblUSBDevices.Name) Not Like Lower('%C922 Pro Stream Webcam%')

(or Upper() to force everything to uppercase).

[RCorbeil]

Are the duplications identical or are they just similar?

I ran your code against my inventory and did turn up multiple cameras listed on machines with just one, but they weren't identical. e.g. a computer was listed twice, one with the device name "HD Webcam C525", the second with "Logitech HD Webcam C525". If that's what you're seeing, the simplest approach will probably be to add one more filter to exclude one or the other entry from your results.

[Tholmes]

RC62N wrote:
Are the duplication's identical or are they just similar?

I ran your code against my inventory and did turn up multiple cameras listed on machines with just one, but they weren't identical. e.g. a computer was listed twice, one with the device name "HD Webcam C525", the second with "Logitech HD Webcam C525". If that's what you're seeing, the simplest approach will probably be to add one more filter to exclude one or the other entry from your results.

I double checked, some of them do that, I can exclude them, I have noticed it says the manufacturer is MS not Logitech for the manufacturer, also, how do I exclude the webcam by capilatized letter
'C922 Pro Stream Webcam' to be excluded
not
'c922 Pro Stream Webcam'

Is there a way to differentiate between the lowercase that starts just the name?
Thanks in advance.
Tim