cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jmcelvoy
Engaged Sweeper II
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
1 ACCEPTED SOLUTION
David_G
Lansweeper Employee
Lansweeper Employee
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


View solution in original post

2 REPLIES 2
jmcelvoy
Engaged Sweeper II
This appears to be exactly what I needed. Thank you so much.
David_G
Lansweeper Employee
Lansweeper Employee
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


New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now