cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
konstapel
Engaged Sweeper
Hi all,

i gave the best i can do but now im stuck and i dont get the Report to work 😕 HALP!

What i want:
create a Report for all Assets that looks if the Asset is in more than one Asset Group, if it has 2 or more Asset groups assigned dont show it. So simply i want to see all Assets that are only in the Default Asset Group and need to get a Group assigned.

This is what i got so far:


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10,
tblAssets.IPAddress,
tblAssetCustom.PurchaseDate,
tblAssets.Username,
tblAssets.Assettype,
tblAssets.OScode,
tblAssetCustom.Manufacturer,
tblAssetGroups.AssetGroup,
tblAssetCustom.State,
tblAssetGroupLink.AssetGroupID,
tblAssetGroups.Builtin,
tblAssetGroups.Dynamic,
tblAssetGroupLink.AssetGroupLinkID,
Count(*) as countrow
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblAssets.AssetID In (Select tblAssets.AssetID
From tblAssets Group By tblAssets.AssetID Having count(*) > 1) And
tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10,
tblAssets.IPAddress,
tblAssetCustom.PurchaseDate,
tblAssets.Username,
tblAssets.Assettype,
tblAssets.OScode,
tblAssetCustom.Manufacturer,
tblAssetGroups.AssetGroup,
tblAssetCustom.State,
tblAssetGroupLink.AssetGroupID,
tblAssetGroups.Builtin,
tblAssetGroups.Dynamic,
tblAssetGroupLink.AssetGroupLinkID


but so far it doesnt show me any results 😕

please, HALP!
1 ACCEPTED SOLUTION
konstapel
Engaged Sweeper
ok, i found this marvelous link http://www.lansweeper.com/forum/yaf_postst7747_Computer-report-shows-dual-Asset-Group-Entries.aspx#post32455

it has done for me everything i wanted, thanks 🙂

View solution in original post

1 REPLY 1
konstapel
Engaged Sweeper
ok, i found this marvelous link http://www.lansweeper.com/forum/yaf_postst7747_Computer-report-shows-dual-Asset-Group-Entries.aspx#post32455

it has done for me everything i wanted, thanks 🙂