Combine 3 reports into 1

[tux182]

Hi all

I'm having trouble combining 3 reports into 1.
In short i'm looking for a report to give all asset spec info.

So to include - hostname, user, Make, model, CPU, RAM, graphics card, HDD total size, Serial number, Purchase date, Warranty expiration

I have 3 reports i run seperately. I've tried and failed to combine them. I dont need the linux info, this is a Windows environment
Please can someone assist and improve if they see fit! 🙂

Here are my 3 reports

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,
tblAssets.IPAddress,
tsysOS.Image As icon
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
Where tblAssetCustom.State = 1
Order By tblAssets.AssetName


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Lastseen As [last scan],
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]
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Lastseen
Order By tblAssets.AssetName

Select Top 10000 tblAssets.AssetID,
tblAssets.AssetName,
tblVideoController.Caption As [Windows Graphics Card],
tblLinuxGraphicsCards.Name As [Linux Graphics Card]
From tblAssets
Left Join tblVideoController On tblAssets.AssetID = tblVideoController.AssetID
Left Join tblLinuxGraphicsCards
On tblAssets.AssetID = tblLinuxGraphicsCards.AssetID

[tux182]

that works a treat - thankyou 🙂

[NicholeKaligian · Accepted solution]

Using the reports you listed, we've combined all requirements into one report. We combined the necessary joins and grouped all the fields that are not part of a sum.

Instructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.

• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.

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
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
Order By tblAssets.AssetName