Report based on Asset Groups

[konstapel]

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!

[konstapel · Accepted solution]

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 🙂