Custom Report Request

[afinetycon]

SUPPORT,

would appreciate help with a report here are the following criteria
in order from top to bottom thanks a million

UserName
Computer NAme
Manufacturer
Model
processor
RAM
hard drive size (total)
montior serial
**(set for dual just in case some have it & if you could have it in a seperate column not another row)**
Bios Date
Printers (if possible only one connected via USB)

[afinetycon]

THANKS A MILLION! Just saved me hours of time
best support ive ever seen

[Hemoco · Accepted solution]

afinetycon wrote:
if you could have it in a seperate column not another row

SQL doesn't allow for this. Table records are displayed as lines, not columns. You will need to use a separate report for monitors or printers, as including both in a single report will lead to duplication. We recommend using the built-in "Monitor: information" report for monitors.

For the rest of the information you are after, use the report below.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.NrProcessors,
tblAssets.Processor,
tblAssets.Memory,
Convert(nvarchar,tblBIOS.ReleaseDate,101) As BIOSrelease,
Convert(Decimal(7,2),SubQuery1.Sum / 1024 / 1024 / 1024) As TotalDiskSizeGB,
tblAssets.Firstseen,
tblAssets.Lastseen,
SubQuery2.Caption As USBprinter,
SubQuery2.Portname,
SubQuery2.Lastchanged
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Inner Join (Select tblFloppy.AssetID,
Sum(tblFloppy.Size) As Sum
From tblFloppy
Group By tblFloppy.AssetID) SubQuery1 On SubQuery1.AssetID = tblAssets.AssetID
Left Join (Select tblPrinters.AssetID,
tblPrinters.Caption,
tblPrinters.Portname,
tblPrinters.Lastchanged
From tblPrinters
Where tblPrinters.Portname Like '%USB%') SubQuery2 On SubQuery2.AssetID =
tblAssets.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
USBprinter