Computer report shows dual Asset Group Entries

[mwrobo09]

I created a report that I needed for an XP to Win7 upgrade. The report included the Asset Group name and computer name, user, Manufacturer, Model, Serial, a query to find out what version of Office, AssetTag. IP location, and purchase date.

The issue I am having is that all the PCs that are added to asset groups are shoiwing duplicates in the report, one for the asset and one for the Default group. Is there anyway to configure the computers to be removed from the default group once they are assigned to another asset group? Like a option to only allow an asset to be part of one asset group instead of being able to be a part of many.

Here is the report I am running.

Select Top 1000000 tblAssetGroups.AssetGroup,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Username,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblSoftwareUni.softwareName,
tblSystemEnclosure.SMBIOSAssetTag,
tsysIPLocations.IPLocation,
tblAssetCustom.PurchaseDate
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Outer Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where (tblSoftwareUni.softwareName Like 'Microsoft Office Professional%') Or
(tblSoftwareUni.softwareName Like 'Microsoft Office Standard%') Or
(tblSoftwareUni.softwareName Like 'Microsoft Office 2007%') Or
(tblSoftwareUni.softwareName Like 'Microsoft Office 2010%')
Order By tblAssetGroups.AssetGroup,
tblAssets.AssetName

Thanks
Marty

[Hemoco]

Assets assigned to one group:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tsysAssetTypes.AssetTypeIcon10 As icon
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
Where tblAssetCustom.State = 1 And SubQuery1.Count = 1
Order By tblAssets.IPNumeric

Assets assigned to multiple groups:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
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 > 1
Order By tblAssets.IPNumeric

[mwrobo09]

I am having issues getting a report that would show me assets that are assigned to more than one asset group, and also another report that would show just assets assigned only to the default group?

My SQL is not good.

Thanks

[Hemoco · Accepted solution]

All assets can be part of multiple asset groups. This is the setup. If you like you can add all assets to a non-default group and filter out the Default Group. They cannot be removed from the Default Group however.

Or you can still show the "leftover" Default Group entries by using a union query like the one below.

Select Top 1000000 tblAssetGroups.AssetGroup,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Username,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblSoftwareUni.softwareName,
tblSystemEnclosure.SMBIOSAssetTag,
tsysIPLocations.IPLocation,
tblAssetCustom.PurchaseDate
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Outer Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblAssetGroups.AssetGroup <> 'Default group' And
(tblSoftwareUni.softwareName Like 'Microsoft Office Professional%' Or
tblSoftwareUni.softwareName Like 'Microsoft Office Standard%' Or
tblSoftwareUni.softwareName Like 'Microsoft Office 2007%' Or
tblSoftwareUni.softwareName Like 'Microsoft Office 2010%')
Union
Select Top 1000000 tblAssetGroups.AssetGroup,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Username,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblSoftwareUni.softwareName,
tblSystemEnclosure.SMBIOSAssetTag,
tsysIPLocations.IPLocation,
tblAssetCustom.PurchaseDate
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Outer Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblAssetGroups.AssetGroup = 'Default group' And
(tblSoftwareUni.softwareName Like 'Microsoft Office Professional%' Or
tblSoftwareUni.softwareName Like 'Microsoft Office Standard%' Or
tblSoftwareUni.softwareName Like 'Microsoft Office 2007%' Or
tblSoftwareUni.softwareName Like 'Microsoft Office 2010%') And
tblAssets.AssetID Not In (Select tblAssetGroupLink.AssetID From tblAssetGroups
Where tblAssetGroups.AssetGroup <> 'Default group')