Hi,
I am using this report to see owners that owns 2 computers and more (owned by).
I am using Custom7 for "common computer" (checkbox yes/no).
Users that owns a common computer shows up in the report should be excluded. (Can that be done?
Select Top 1000000 tblAssets.AssetName,
tblAssetCustom.Custom1 As Inventarienummer,
tblAssetCustom.Custom3 As [Special Notes],
tblAssets.AssetID,
tblAssetCustom.Model,
tblADusers.Name,
Case
When tblADComputers.IsEnabled = 'True' Then 'Enabled'
Else 'Disabled'
End As [Computer Enabled/Disabled in AD]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join (Select tblAssetUserRelations.Username,
tblAssetUserRelations.AssetID
From tblAssetUserRelations
Group By tblAssetUserRelations.Username,
tblAssetUserRelations.AssetID) Query1 On Query1.AssetID =
tblAssets.AssetID
Inner Join (Select tblAssetUserRelations.Username As UserName_Count,
Count(tblAssetUserRelations.AssetID) As Counter,
tblAssets.Assettype
From tblAssetUserRelations
Inner Join tblAssets On tblAssets.AssetID = tblAssetUserRelations.AssetID
Where tblAssets.Assettype = -1
Group By tblAssetUserRelations.Username,
tblAssets.Assettype
Having Count(tblAssetUserRelations.AssetID) > 1) Counter On
Counter.UserName_Count = Query1.Username
Inner Join tblAssetUserRelations On
tblAssets.AssetID = tblAssetUserRelations.AssetID
Inner Join tblADusers On tblAssetUserRelations.Username = tblADusers.Username
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssetCustom.Custom7 = 'no'
