Notification

Icon
Error

Duplicate computers in Webcam query - When I run this query, it comes up with the same computer twice, but one webcam

Posted: Wednesday, January 13, 2021 8:17:07 PM(UTC)
TimHolmes1973

TimHolmes1973

Member Original PosterPosts: 30
0
Like
You amazing guys helped me with this report before.
https://www.lansweeper.c...rong-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
RC62N
#1RC62N Member Posts: 512  
posted: 1/13/2021 9:09:56 PM(UTC)
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.
TimHolmes1973
#2TimHolmes1973 Member Original PosterPosts: 30  
posted: 1/13/2021 10:45:58 PM(UTC)
Originally Posted by: RC62N Go to Quoted Post
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
RC62N
#3RC62N Member Posts: 512  
posted: 1/14/2021 4:36:50 PM(UTC)
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.
Code:
Lower(tblUSBDevices.Name) Not Like '%c922 pro stream webcam%'

or if you don't trust yourself not to remember to lowercase everything
Code:
Lower(tblUSBDevices.Name) Not Like Lower('%C922 Pro Stream Webcam%')

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

Active Discussions

Lansweeper Driver vendor
by  miharix   Go to last post Go to first unread
Last post: Today at 3:04:54 PM(UTC)
Lansweeper SSH Scanning Credentials
by  BastiOn  
Go to last post Go to first unread
Last post: Today at 9:22:46 AM(UTC)
Lansweeper lansweeperuser sql database permissions
by  FrankSc   Go to last post Go to first unread
Last post: Today at 8:54:22 AM(UTC)
Lansweeper Software not showing install date
by  Rocher Vincent  
Go to last post Go to first unread
Last post: Today at 8:18:30 AM(UTC)
Lansweeper LSAgent Rescan Asset
by  marck1024   Go to last post Go to first unread
Last post: Yesterday at 6:46:48 PM(UTC)
Report Center Shares on Windows computers and their share permissions
by  RC62N  
Go to last post Go to first unread
Last post: Yesterday at 4:51:50 PM(UTC)
Lansweeper Windows 10 20H2 issue
by  Mikey!   Go to last post Go to first unread
Last post: Yesterday at 2:49:26 PM(UTC)
Lansweeper Use case for MSSP
by  CHTSjohn  
Go to last post Go to first unread
Last post: 3/1/2021 10:49:11 PM(UTC)