cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jletellier
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
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

View solution in original post

1 REPLY 1
Susan_A
Lansweeper Alumni
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