→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
afinetycon
Engaged Sweeper
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)





1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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

View solution in original post

2 REPLIES 2
afinetycon
Engaged Sweeper
THANKS A MILLION! Just saved me hours of time
best support ive ever seen
Hemoco
Lansweeper Alumni
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