cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
blighter
Engaged Sweeper III
Hi 🙂

I have the below report which pulls the auto login details from the registry of scanned computers, but as we have multiple machines per site it lists duplicate values. An example output is:


Username Displayname Value
computer1 Comp1 pass1
computer1 Comp1 pass1
computer2 Comp2 pass2
computer3 Comp3 pass3
computer3 Comp3 pass3


Is it possible to remove the duplicates or just return the first result per username?

Select Top 1000000 tblAssets.Username,
tblADusers.Displayname,
tblRegistry.Value
From tblAssets
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblRegistry.Valuename = 'DefaultPassword'
Order By tblAssets.AssetUnique


Thanks!
1 ACCEPTED SOLUTION
blighter
Engaged Sweeper III
I have found a way to do this now

For those interested:

Select Top 1000000 tblADusers.Displayname As Store,
tblADusers.Username As Username,
tblRegistry.Value As Password,
tblADusers.Userdomain
From tblAssets
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblRegistry.Valuename = 'DefaultPassword'
Group By tblADusers.Displayname,
tblADusers.Username,
tblRegistry.Value,
tblADusers.Userdomain

View solution in original post

1 REPLY 1
blighter
Engaged Sweeper III
I have found a way to do this now

For those interested:

Select Top 1000000 tblADusers.Displayname As Store,
tblADusers.Username As Username,
tblRegistry.Value As Password,
tblADusers.Userdomain
From tblAssets
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblRegistry.Valuename = 'DefaultPassword'
Group By tblADusers.Displayname,
tblADusers.Username,
tblRegistry.Value,
tblADusers.Userdomain

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now