Need help splitting up a report...

[jmcelvoy]

I have this report I use to give me the total disk in use space for all assets of a network discovery. What I need now is a report that will pull the disk size for only servers, a report that will pull the disk size of only workstations, and instead of it breaking down each individual drive, I need it to calculate the total disk size of each server/workstation. Right now if a server has 5 drives, the report I'm using lists out all 5 and I have an Excel formula I use to tally the total amount of disk usage. I now need a report that will combine those 5 drives and report on all them as 1 total disk size. Is that easy to do by modifying the query I currently use?

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress,
tblDiskdrives.Caption As [Disk],
Convert(Decimal(7,2),tblDiskdrives.Size / 1024 / 1024 / 1024) As TotalGB,
Convert(Decimal(7,2),tblDiskdrives.Freespace / 1024 / 1024 / 1024) As FreeGB,
Convert(Decimal(7,2),tblDiskdrives.Size / 1024 / 1024 / 1024) -
Convert(Decimal(7,2),tblDiskdrives.Freespace / 1024 / 1024 / 1024) As UsedGB
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblDiskdrives.DriveType = 3
Order By tblAssets.Domain,
tblAssets.AssetName

[jmcelvoy]

This appears to be exactly what I needed. Thank you so much.

[David_G · Accepted solution]

I have edited the report you have posted here to give back a total of GB, how many that is free in GB and how many that is used in GB per asset, by using the Sum function in SQL. Therefore, we had to remove the caption of the diskdrive as it would otherwise still give back each drive separately. Additionally, as you requested, I have made two reports, one for servers and one for workstations, by filtering on the domain roles. 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.

Report for Servers:

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  Sum(Convert(Decimal(7,2),tblDiskdrives.Size / 1024 / 1024 / 1024)) As TotalGB,
  Sum(Convert(Decimal(7,2),tblDiskdrives.Freespace / 1024 / 1024 /
  1024)) As FreeGB,
  Sum(Convert(Decimal(7,2),tblDiskdrives.Size / 1024 / 1024 / 1024) -
  Convert(Decimal(7,2),tblDiskdrives.Freespace / 1024 / 1024 / 1024)) As UsedGB
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblDomainroles On tblDomainroles.Domainrole =
    tblComputersystem.Domainrole
Where tblDiskdrives.DriveType = 3 And tblDomainroles.Domainrolename In
  ('Stand-alone server', 'Member server', 'Primary domain controller',
  'Backup domain controller')
Group By tsysOS.Image,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress
Order By tblAssets.Domain,
  tblAssets.AssetName

Report for Workstations:

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  Sum(Convert(Decimal(7,2),tblDiskdrives.Size / 1024 / 1024 / 1024)) As TotalGB,
  Sum(Convert(Decimal(7,2),tblDiskdrives.Freespace / 1024 / 1024 /
  1024)) As FreeGB,
  Sum(Convert(Decimal(7,2),tblDiskdrives.Size / 1024 / 1024 / 1024) -
  Convert(Decimal(7,2),tblDiskdrives.Freespace / 1024 / 1024 / 1024)) As UsedGB
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblDomainroles On tblDomainroles.Domainrole =
    tblComputersystem.Domainrole
Where tblDiskdrives.DriveType = 3 And tblDomainroles.Domainrolename In
  ('Stand-alone workstation', 'Member workstation')
Group By tsysOS.Image,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress
Order By tblAssets.Domain,
  tblAssets.AssetName