cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
PeterJG
Champion Sweeper II
I have assets that share multiple groups, I would like to create reports showing assets that are in GROUP1 but not in GROUP2. Tried different things but I'm not SQL guru just yet.. any help would be greatly appreciated.

-Thank YOu
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
For this you'll need to add subqueries to your report's SQL query which searches for assets in groups. Asset groups are stored in tblAssetgroups and the membership of assets in groups is stored in tblAssetGroupLink.
Here is an example report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where
tblAssets.AssetID In (Select tblAssetGroupLink.AssetID
From tblAssetGroupLink Inner Join tblAssetGroups
On tblAssetGroupLink.AssetGroupID = tblAssetGroups.AssetGroupID
Where tblAssetGroups.AssetGroup Like 'GROUP1')
And
tblAssets.AssetID Not In (Select tblAssetGroupLink.AssetID
From tblAssetGroupLink Inner Join tblAssetGroups
On tblAssetGroupLink.AssetGroupID = tblAssetGroups.AssetGroupID
Where tblAssetGroups.AssetGroup Like 'GROUP2')
And tblAssetCustom.State = 1

View solution in original post

2 REPLIES 2
PeterJG
Champion Sweeper II
Thank You.. exactly what I wanted..
Daniel_B
Lansweeper Alumni
For this you'll need to add subqueries to your report's SQL query which searches for assets in groups. Asset groups are stored in tblAssetgroups and the membership of assets in groups is stored in tblAssetGroupLink.
Here is an example report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where
tblAssets.AssetID In (Select tblAssetGroupLink.AssetID
From tblAssetGroupLink Inner Join tblAssetGroups
On tblAssetGroupLink.AssetGroupID = tblAssetGroups.AssetGroupID
Where tblAssetGroups.AssetGroup Like 'GROUP1')
And
tblAssets.AssetID Not In (Select tblAssetGroupLink.AssetID
From tblAssetGroupLink Inner Join tblAssetGroups
On tblAssetGroupLink.AssetGroupID = tblAssetGroups.AssetGroupID
Where tblAssetGroups.AssetGroup Like 'GROUP2')
And tblAssetCustom.State = 1