Query Assets in Multiple Groups

[jletellier]

I have basically no knowledge of SQL and I'm trying to query assets that are in multiple groups. For instance: Both "Classroom" and "Longmeadow". I can only manage pulling a report of one AssetGroup. Using an AND command only returned an error.

Thanks

[Susan_A · Accepted solution]

Keep in mind that assets are always part of the Default group. For what you are trying to do, you should list assets that are in more than 2 groups. A sample report can be seen below. Instructions for running reports can be found here.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssetGroups.AssetGroup
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join (Select Top 1000000 tblAssetGroupLink.AssetID,
    Count(tblAssetGroupLink.AssetGroupID) As Count
  From tblAssetGroupLink
  Group By tblAssetGroupLink.AssetID) SubQuery1 On SubQuery1.AssetID =
    tblAssets.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 tblAssetCustom.State = 1 And SubQuery1.Count > 2
Order By tblAssets.IPNumeric,
  tblAssetGroups.AssetGroup