→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
PaulWegs
Engaged Sweeper
Hi,
I'm hoping someone can help me out here. I have a report which shows the hard disk size and free space for each drive in each server.
A requirement has come up where I need to see the total size and total free space for each server - in other words, grouping the drives together for each server to show a total.
Here's the current SQL query I use. Any help would be appreciated.

Thanks.


Select Top 1000000
dbo.tblComputers.Computername,
dbo.tblComputers.ComputerUnique,
dbo.tblComputers.Domain,
tblADComputers.Description As Description,
Web40OSName.Compimage As icon,
tblDiskdrives.Caption As [Drive Letter],
Cast(Round(tblDiskdrives.Size / 1024 / 1024 / 1024, 2) As float) As Size,
Cast(Round(tblDiskdrives.Freespace / 1024 / 1024 / 1024, 2) As float) As [Free Space],
Cast(Round(((tblDiskdrives.Freespace / tblDiskdrives.Size) * 100), 2) As float) As [Percent Free] From dbo.tblComputersystem
Inner Join dbo.tblComputers On
dbo.tblComputersystem.Computername = dbo.tblComputers.Computername
Inner Join dbo.web40ActiveComputers On
dbo.tblComputers.Computername = dbo.web40ActiveComputers.Computername
Inner Join Web40OSName On
Web40OSName.Computername = dbo.tblComputers.Computername
Inner Join tblOperatingsystem On
dbo.tblComputers.Computername = tblOperatingsystem.Computername
Inner Join tblDiskdrives On
dbo.tblComputers.Computername = tblDiskdrives.Computername
Inner Join tblADComputers On
dbo.tblComputers.Computername = tblADComputers.Computername
Where
dbo.tblComputersystem.Domainrole > 1 And tblDiskdrives.DriveType = 3
Order By Round((Round(tblDiskdrives.Freespace, 2) / Round(tblDiskdrives.Size, 2)) * 100, 2), dbo.tblComputers.Computer


1 REPLY 1
Hemoco
Lansweeper Alumni
try this

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, tblADComputers.Description As Description,
Web40OSName.Compimage As icon, Sum(Cast(Round(tblDiskdrives.Size / 1024 /
1024 / 1024, 2) As float)) As Size, Sum(Cast(Round(tblDiskdrives.Freespace /
1024 / 1024 / 1024, 2) As float)) As [Free Space]
From tblComputersystem Inner Join
tblComputers On tblComputersystem.Computername = tblComputers.Computername
Inner Join
web40ActiveComputers On tblComputers.Computername =
web40ActiveComputers.Computername Inner Join
Web40OSName On Web40OSName.Computername = tblComputers.Computername Inner Join
tblOperatingsystem On tblComputers.Computername =
tblOperatingsystem.Computername Inner Join
tblDiskdrives On tblComputers.Computername = tblDiskdrives.Computername
Left Join
tblADComputers On tblComputers.Computername = tblADComputers.Computername
Where tblDiskdrives.DriveType = 3
Group By tblComputers.Computername, tblComputers.ComputerUnique,
tblComputers.Domain, tblADComputers.Description, Web40OSName.Compimage