Need Duplicates Report Help

[PapaTuck]

I have a report that shows duplicate assets by asset name. I am looking to create a report that shows duplicates by Asset ID. We see duplicates when devices are moved to different locations, etc.

I've tried altering this report to show any duplicate IDs, but haven't been successful. Does anyone have suggestions that might work?

Select Top 1000000 tsysAssetTypes.AssetTypename,
tblAssetCustom.AssetID As DB_AssetID,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Description,
tblAssets.IPAddress,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join (Select Top 1000000 tblAssets.AssetName
From tblAssets
Group By tblAssets.AssetName
Having Count(tblAssets.AssetID) > 1) Dups On tblAssets.AssetName =
Dups.AssetName
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName

[PapaTuck]

Yes, we are using Lansweeper Classic. We see duplicates when devices are moved to different switches/buildings due to the old switch retaining asset record data. We also see dups when a device is connected to an uplink switch that connects to another down stream switch device. This makes it appear that an asset is online and in two places at once. Some of the duplicates can be resolved by "removing disconnected devices" from the switches, but that isn't an automated process. We sometimes use a script that removes disconnected device data from all active switches at once.

[Tim_N]

Thanks for your reply @PapaTuck 

The switch's records shouldn't have an impact on Lansweeper's discovery process or duplicate assets. The switch data is important in the diagramming feature found in Lansweeper Sites, but for On-Prem, it shouldn't have an impact like you are experiencing. 

Sorry, more questions. 

Are these duplicates being seen in the main "inventory" list of assets? Or, are they being seen in a specific report? 

I ask these questions because based on your initial question and your reply, it sounds like they are showing in a report(s). This could make sense -- in your use case, the total list of assets might also include those discovered through scanning a Switch. BUT, they *shouldn't* show as a duplicate in the general Inventory list. As your original post indicates, we may only need to modify a report. 

In the end, if the asset is only shown duplicated because of the type of generated report, it shouldn't impact your asset count. For example, a report might show "Assets discovered in switches" - this type of report might show the same asset in different locations because of the switches being scanned. But, the duplicated asset shouldn't take more than one asset in this situation. 

I hope this makes sense. 

[PapaTuck]

We are using a report that shows all active hardware and includes a column for the Asset ID. Here is a copy of that report for your review. It's been in use with little modification since 2021.

Select Distinct Top 1000000 tblAssets.AssetID As [Asset ID],
tsysAssetTypes.AssetTypeIcon10 As icon,
Left(tsysAssetTypes.AssetTypename, 99) As [Asset Type],
Left(tblAssetCustom.Custom9, 99) As Environment,
Left(tblAssets.AssetName, 99) As AssetName,
tblAssets.IPAddress As [IP Address],
tblAssetCustom.Custom1 As [Virtual Asset],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber As [Serial Number],
Left(Coalesce(tsysOS.OSname, tblLinuxSystem.OSRelease,
tblMacOSInfo.SystemVersion, tblAssets.Description), 99) As OS,
tblAssetCustom.Custom4 As [Function],
(Case
When tsysIPLocations.IPLocation Like '%Telework VPN' Then
tsysIPLocations.IPLocation
When aaa.AssetName Is Not Null And CharIndex('.ds', aaa.AssetName) > 0 Then
SubString(aaa.AssetName, 0, CharIndex('.ds', aaa.AssetName))
Else aaa.AssetName
End) As [Building Number],
tblAssetCustom.Custom2 As [Critical System]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Left Join (Select tblAssets.AssetID,
tblAssets_1.AssetName
From tblAssets
Inner Join tblAssetMacAddress On
tblAssets.AssetID = tblAssetMacAddress.AssetID
Inner Join tblSNMPAssetMac On tblAssetMacAddress.Mac =
tblSNMPAssetMac.AssetMacAddress
Inner Join tblSNMPInfo On tblSNMPAssetMac.IfIndex = tblSNMPInfo.IfIndex
And tblSNMPAssetMac.AssetID = tblSNMPInfo.AssetID
Inner Join tblAssets tblAssets_1 On tblSNMPInfo.AssetID =
tblAssets_1.AssetID) As aaa On aaa.AssetID = tblAssets.AssetID
Where tsysAssetTypes.AssetTypename Not Like 'Monitor' And tblAssets.Assettype <>
66 And tblAssetCustom.State = 1
Order By [Asset Type]

It is difficult to compare this to the All Assets page since that page can't filter between "Active" and "Non-active" devices, and does not show the Asset ID.

Thanks for your help!

[Tim_N]

Hello @PapaTuck 

I have a couple questions about your duplicates. I know I'm not answering your question about reporting directly... I want to figure out why you are getting duplicates in the first place. 

1. What type of scanning are you doing? IP scanning? Active Directory scanning? 
2. Do you scan with agents? If yes, which agents? (LsAgent, LsPush, IT Agent)

Based on the report in your original post, I'm assuming we are only talking about On-Prem Lansweeper and not Lansweeper Sites (cloud-based). 

If the duplicated assets are scanned thoroughly, it *shouldn't* cause duplicates. So I'm wondering if either scan is not thoroughly scanning correctly. If this is the case, it could cause duplicates without having all the proper asset information to find the matches. 

Thanks for clarifying some of your scan and discovery configurations. 

Looking forward to hearing from you. 

Tim