Report for computers with multiple Hard disks.

[SEC_ALERTS]

Hi Team ,

We want report for computers with multiple hard disks in below format.

1.Computer name
2.IP address
3.No of HDD
4.HDD capacity for all HDD
5.HDD names

Thanks In Advance!!!

Regards,
SEC_ALERTS

[SEC_ALERTS]

Many Thanks!!!

[Hemoco · Accepted solution]

A sample report can be seen below.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblFloppy.Name,
tblFloppy.Model,
tblFloppy.InterfaceType,
Ceiling(tblFloppy.Size / 1024 / 1024) As SizeMB,
SubQuery1.HDDcount
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblFloppy On tblAssets.AssetID = tblFloppy.AssetID
Inner Join (Select Top 1000000 tblFloppy.AssetID,
Count(tblFloppy.floppyID) As HDDcount
From tblFloppy
Group By tblFloppy.AssetID) SubQuery1 On SubQuery1.AssetID = tblAssets.AssetID
Where SubQuery1.HDDcount > 1 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
tblFloppy.Name