→ 🚀What's New? Explore Lansweeper's Fall 2024 Updates! Fall Launch Blog !

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
poweld1
Champion Sweeper
I've got registry scanning enabled for the SusClientId

HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate\SusClientId

I can't figure how to create a report to find duplicate SusClientId values.

Cheers
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
I've included a sample report below, which you can add to your Lansweeper installation by following these instructions. If you are interested in building or modifying reports, we do recommend:
  • Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  • Making use of our database dictionary, more information on which can be found here. The dictionary explains in great detail what each table and field stores.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
SubQuery.Value,
SubQuery.Count
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join (Select Top 1000000 tblRegistry.Value,
Count(tblRegistry.AssetID) As Count
From tblRegistry
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblRegistry.AssetID
Where tblAssetCustom.State = 1 And
tblRegistry.Regkey =
'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate'
And tblRegistry.Valuename = 'susclientid'
Group By tblRegistry.Value,
tblRegistry.Regkey,
tblRegistry.Valuename) SubQuery On SubQuery.Value = tblRegistry.Value
Where SubQuery.Count > 1 And tblAssetCustom.State = 1
Order By SubQuery.Value,
tblAssets.Domain,
tblAssets.AssetName

View solution in original post

3 REPLIES 3
Khaneric
Engaged Sweeper
Sorry to bring an old topic back from the grave but I'm finding this report isn't working currently. Is there anyway this can be updated to work as I feel it's a VERY useful report especially with all the critical patches coming out lately.

Thanks

just wondering if this ever was resolved.  I can run a report, but it finds the multiple times a single asset has been scanned and counts itself as a duplicate.  

just need a report to show the duplicate assets with the same clientid's

 

Susan_A
Lansweeper Alumni
I've included a sample report below, which you can add to your Lansweeper installation by following these instructions. If you are interested in building or modifying reports, we do recommend:
  • Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  • Making use of our database dictionary, more information on which can be found here. The dictionary explains in great detail what each table and field stores.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
SubQuery.Value,
SubQuery.Count
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join (Select Top 1000000 tblRegistry.Value,
Count(tblRegistry.AssetID) As Count
From tblRegistry
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblRegistry.AssetID
Where tblAssetCustom.State = 1 And
tblRegistry.Regkey =
'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\WindowsUpdate'
And tblRegistry.Valuename = 'susclientid'
Group By tblRegistry.Value,
tblRegistry.Regkey,
tblRegistry.Valuename) SubQuery On SubQuery.Value = tblRegistry.Value
Where SubQuery.Count > 1 And tblAssetCustom.State = 1
Order By SubQuery.Value,
tblAssets.Domain,
tblAssets.AssetName