
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎08-08-2017 04:27 PM
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
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
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎08-09-2017 11:51 AM
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:
Report for Workstations:
- 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 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
2 REPLIES 2

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎08-09-2017 11:52 PM
This appears to be exactly what I needed. Thank you so much.

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎08-09-2017 11:51 AM
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:
Report for Workstations:
- 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 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
