How to remove duplicate assets in report

[tux182]

Hi All

I have an asset report for all domain machines.
Part of the report is to show the graphic card(s).

Some machines are listed as having 2 cards. Therefore the same machine gets listed twice in the report.
The first time with Graphics card A, the second with Graphics card B

Below is the report.
Please can someone assist with removing reporting the same machine twice?

Thanks in advance for any pointers


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblADusers.Displayname,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssets.Processor,
tblAssets.Memory,
Sum(Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 / 1024 / 1024 As Numeric))
As [Total size (GB)],
Sum(Cast(Cast(tblDiskdrives.Freespace As BigInt) / 1024 / 1024 /
1024 As Numeric)) As [Free in GB],
tblVideoController.Caption As [Windows Graphics Card],
tblAssets.IPAddress,
tsysOS.Image As icon,
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Join tblADusers On tblADusers.Userdomain = tblAssets.Userdomain
And tblADusers.Username = tblAssets.Username
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblVideoController On tblAssets.AssetID = tblVideoController.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblADusers.Displayname,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.Processor,
tblAssets.Memory,
tblVideoController.Caption,
tblAssets.IPAddress,
tsysOS.Image,
tblAssets.Lastseen
Order By tblAssets.AssetName

[Esben_D · Accepted solution]

If you have assets with 2 GPUs, it is normal for the report to give multiple lines per asset since both GPUs are requested.

The way you can get around it is to create a subquery which shows the second graphics card. However, to do it clean it's not as simple. The query below does show both GPU's in one line, but if an asset only has 1 GPU it will show that one twice.

I've looked for a while, but wasn't able to really figure out how to create a condition which allows you to only show 1 GPU if the asset only has 1.

I've highlighted the subquery used.

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblADusers.Displayname,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssets.Processor,
tblAssets.Memory,
Sum(Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 / 1024 / 1024 As Numeric))
As [Total size (GB)],
Sum(Cast(Cast(tblDiskdrives.Freespace As BigInt) / 1024 / 1024 /
1024 As Numeric)) As [Free in GB],
tblAssets.IPAddress,
tsysOS.Image As icon,
tblAssets.Lastseen,
SubQuery1.[Windows Graphics Card 2],
SubQuery1.[Windows Graphics Card]
From tblAssets
Left Join (Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
Max(tblVideoController.Caption) As [Windows Graphics Card],
Min(tblVideoController.Caption) As [Windows Graphics Card 2]
From tblVideoController
Inner Join tblAssets On tblAssets.AssetID = tblVideoController.AssetID
Group By tblAssets.AssetID,
tblAssets.AssetName) As SubQuery1 On SubQuery1.AssetID =
tblAssets.AssetID
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Join tblADusers On tblADusers.Userdomain = tblAssets.Userdomain
And tblADusers.Username = tblAssets.Username
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblADusers.Displayname,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.Processor,
tblAssets.Memory,
tblAssets.IPAddress,
tsysOS.Image,
tblAssets.Lastseen,
SubQuery1.[Windows Graphics Card 2],
SubQuery1.[Windows Graphics Card]

[tux182]

Charles.X wrote:
If you have assets with 2 GPUs, it is normal for the report to give multiple lines per asset since both GPUs are requested.

The way you can get around it is to create a subquery which shows the second graphics card. However, to do it clean it's not as simple. The query below does show both GPU's in one line, but if an asset only has 1 GPU it will show that one twice.

I've looked for a while, but wasn't able to really figure out how to create a condition which allows you to only show 1 GPU if the asset only has 1.

I've highlighted the subquery used.

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblADusers.Displayname,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssets.Processor,
tblAssets.Memory,
Sum(Cast(Cast(tblDiskdrives.Size As BigInt) / 1024 / 1024 / 1024 As Numeric))
As [Total size (GB)],
Sum(Cast(Cast(tblDiskdrives.Freespace As BigInt) / 1024 / 1024 /
1024 As Numeric)) As [Free in GB],
tblAssets.IPAddress,
tsysOS.Image As icon,
tblAssets.Lastseen,
SubQuery1.[Windows Graphics Card 2],
SubQuery1.[Windows Graphics Card]
From tblAssets
Left Join (Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
Max(tblVideoController.Caption) As [Windows Graphics Card],
Min(tblVideoController.Caption) As [Windows Graphics Card 2]
From tblVideoController
Inner Join tblAssets On tblAssets.AssetID = tblVideoController.AssetID
Group By tblAssets.AssetID,
tblAssets.AssetName) As SubQuery1 On SubQuery1.AssetID =
tblAssets.AssetID
Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Left Join tblADusers On tblADusers.Userdomain = tblAssets.Userdomain
And tblADusers.Username = tblAssets.Username
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblADusers.Displayname,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.Processor,
tblAssets.Memory,
tblAssets.IPAddress,
tsysOS.Image,
tblAssets.Lastseen,
SubQuery1.[Windows Graphics Card 2],
SubQuery1.[Windows Graphics Card]

Excellent work - thankyou - it works a treat