Hard disk Report for Server HD partitions

[WildPh]

I would like to generate a report which shows all server partitions that have less than:
20% diskspace with a yellow "signal light"
10% diskspace with a red "signal light"

I would like to show them in the dashboard and have it send via Mail Report as Excel attachments.

Thanks for your support.

[WildPh]

Could we omit the floor function in order to get "better" results?

[Hemoco]

The "floor" function cuts off the numbers after the comma.

[WildPh]

I've just seen that in some cases the calculation ist not correct:

Computer OSname Description Caption Free Freepct Total size Date Custom1
Chsrv008 Win 2003File Server G: 52278 17 307196 17/08/2010 9001

In this case the free space (52278) is > 0.1 times 307196.

[WildPh]

Works great. But the label in the table is still called Custom1. How can I change the caption? I tried with As [Cost Center].

[Hemoco]

Try this:

Select Top 1000000 dbo.tblComputers.Computername,
  dbo.tblComputers.ComputerUnique, Web40OSName.OSname,
  dbo.tblOperatingsystem.Description, dbo.tblDiskdrives.Caption,
  Cast(Cast(dbo.tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As
  Free, Floor(Cast(Cast(dbo.tblDiskdrives.Freespace As bigint) / 1024 /
  1024 As numeric) / Cast(Cast(dbo.tblDiskdrives.Size As bigint) / 1024 /
  1024 As numeric) * 100) As Freepct, Cast(Cast(dbo.tblDiskdrives.Size
  As bigint) / 1024 / 1024 As numeric) As [Total size],
  dbo.tblDiskdrives.Lastchanged As [Last changed], Web40OSName.Compimage As
  icon, tblCompCustom.Custom1
From dbo.tblComputers Inner Join
  dbo.tblDiskdrives On dbo.tblComputers.Computername =
    dbo.tblDiskdrives.Computername Inner Join
  dbo.tblOperatingsystem On dbo.tblComputers.Computername =
    dbo.tblOperatingsystem.Computername Inner Join
  dbo.web40ActiveComputers On dbo.tblComputers.Computername =
    dbo.web40ActiveComputers.Computername Inner Join
  Web40OSName On Web40OSName.Computername = dbo.tblComputers.Computername
  Inner Join
  tblComputersystem On dbo.tblComputers.Computername =
    tblComputersystem.Computername Inner Join
  tblCompCustom On dbo.tblComputers.Computername = tblCompCustom.Computername
Where Floor(Cast(Cast(dbo.tblDiskdrives.Freespace As bigint) / 1024 /
  1024 As numeric) / Cast(Cast(dbo.tblDiskdrives.Size As bigint) / 1024 /
  1024 As numeric) * 100) <= 20 And Cast(Cast(dbo.tblDiskdrives.Size As bigint)
  / 1024 / 1024 As numeric) <> 0 And tblComputersystem.Domainrole > 1 And
  dbo.tblDiskdrives.DriveType = '3' And tblCompCustom.Custom1 = '1825'
Order By Floor(Cast(Cast(dbo.tblDiskdrives.Freespace As bigint) / 1024 /
  1024 As numeric) / Cast(Cast(dbo.tblDiskdrives.Size As bigint) / 1024 /
  1024 As numeric) * 100), Cast(Cast(dbo.tblDiskdrives.Freespace As bigint) /
  1024 / 1024 As numeric), dbo.tblDiskdrives.Caption

[WildPh]

I still have this issue with the where clause. How could help me with this?

[WildPh]

Thanks for sql statement. Works just fine. I would like filter the query with a where statement: where costcenter=1825. We renamed the custom field 1 in the extra field section.

[Hemoco]

try this:

Select Top 1000000 dbo.tblComputers.Computername,
  dbo.tblComputers.ComputerUnique, Web40OSName.OSname,
  dbo.tblOperatingsystem.Description, dbo.tblDiskdrives.Caption,
  Cast(Cast(dbo.tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As
  Free, Floor(Cast(Cast(dbo.tblDiskdrives.Freespace As bigint) / 1024 /
  1024 As numeric) / Cast(Cast(dbo.tblDiskdrives.Size As bigint) / 1024 /
  1024 As numeric) * 100) As Freepct, Cast(Cast(dbo.tblDiskdrives.Size
  As bigint) / 1024 / 1024 As numeric) As [Total size],
  dbo.tblDiskdrives.Lastchanged As [Last changed], Web40OSName.Compimage As icon
From dbo.tblComputers Inner Join
  dbo.tblDiskdrives On dbo.tblComputers.Computername =
    dbo.tblDiskdrives.Computername Inner Join
  dbo.tblOperatingsystem On dbo.tblComputers.Computername =
    dbo.tblOperatingsystem.Computername Inner Join
  dbo.web40ActiveComputers On dbo.tblComputers.Computername =
    dbo.web40ActiveComputers.Computername Inner Join
  Web40OSName On Web40OSName.Computername = dbo.tblComputers.Computername
  Inner Join
  tblComputersystem On dbo.tblComputers.Computername =
    tblComputersystem.Computername
Where Floor(Cast(Cast(dbo.tblDiskdrives.Freespace As bigint) / 1024 /
  1024 As numeric) / Cast(Cast(dbo.tblDiskdrives.Size As bigint) / 1024 /
  1024 As numeric) * 100) <= 20 And Cast(Cast(dbo.tblDiskdrives.Size As bigint)
  / 1024 / 1024 As numeric) <> 0 And tblComputersystem.Domainrole > 1 And
  dbo.tblDiskdrives.DriveType = '3'
Order By Floor(Cast(Cast(dbo.tblDiskdrives.Freespace As bigint) / 1024 /
  1024 As numeric) / Cast(Cast(dbo.tblDiskdrives.Size As bigint) / 1024 /
  1024 As numeric) * 100), Cast(Cast(dbo.tblDiskdrives.Freespace As bigint) /
  1024 / 1024 As numeric), dbo.tblDiskdrives.Caption

[WildPh]

That's OK with the wording. But which report generates information needed?