Create a report to show users with more than 1 asset (Computer/Laptop) assigned/Logged onto by them.

[justanotherbod]

Hi All

I'm trying to create a report that shows a list of users who have more than 1 asset (Computer/Laptop) assigned to their ID in LanSweeper as users are only meant to be assigned one device (but some have managed t.

So far I have the below which is looking promising, but having done a spot check, it seems to be missing some users who have logged onto more than 1 device and I cant see the reason/pattern behind it!

Select Top 1000000 tblAssets.AssetID,
tblAssets.Username,
tblAssets.AssetName,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssetCustom.State,
tblState.Statename
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputerSystem On tblComputerSystem.AssetID = tblAssets.AssetID
Inner Join (Select tblAssets.Userdomain,
tblAssets.Username,
Count(*) As UserCount
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputerSystem On tblComputerSystem.AssetID =
tblAssets.AssetID
Where tblAssets.Userdomain Is Not Null And tblAssets.Username Is Not Null
And tblAssetCustom.State = 1 And tblComputerSystem.DomainRole < 2
Group By tblAssets.Userdomain,
tblAssets.Username) As UserCount On UserCount.UserDomain =
tblAssets.UserDomain And UserCount.UserName = tblAssets.UserName
And UserCount.UserCount > 1
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblAssets.Username Not Like '%L_A%' And tblAssets.AssetName Not Like
'%vdi%' And tblComputerSystem.DomainRole < 2
Order By tblAssets.Userdomain,
tblAssets.Username,
tblAssets.AssetName

Any ideas?