percentage in report

[pentel]

Hi,

Could someone write me an example of a query to get a percentage of the free space available on a computer? I have tried to get it working in the report builder but every percentage technique I try throws back an error.

Here is what I have right now (with no percentage test added)

Select Top 100 Percent dbo.tblComputers.Computername As Computer, tblComputersystem.Model As [Computer Model], dbo.tblDiskdrives.Caption As Drive, Cast(Cast(dbo.tblDiskdrives.Size As BigInt) / 1024 / 1024 / 1024 As Numeric) As [Total size (GB)], Cast(Cast(dbo.tblDiskdrives.Freespace As BigInt) / 1024 / 1024 / 1024 As Numeric) As [Free in GB], Cast((Cast(dbo.tblDiskdrives.Size As BigInt) - Cast(dbo.tblDiskdrives.Freespace As BigInt)) / 1024 / 1024 / 1024 As Numeric) As [Used Space (GB)], tblFloppy.Model As [HDD Model], dbo.tblDiskdrives.Lastchanged As [Last changed] 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 tblComputersystem On dbo.tblComputers.Computername = tblComputersystem.Computername Inner Join tblFloppy On dbo.tblComputers.Computername = tblFloppy.Computername Where dbo.tblDiskdrives.DriveType = '3' Order By dbo.tblComputers.Computername, dbo.tblDiskdrives.Caption, tblFloppy.Model

[pentel · Accepted solution]

We figured out the problem, there was actually 2 of them :

1. one of our computers has some sort of storage drive that is detected as a HDD but the "freespace" and "size" fields were empty in the database. the first step is to remove this computer from our report or this drive. We checked this using SQL Management studio.

2. Some removable storage devices were detected as HDD as well, and since these were under 1 GB and the SQL query made by Lansweeper support was doing the division in GB, it was trying to divide by 0 (0/0). Since leaving the values at bytes level instead of Gigabytes gives the same percentage result, we removed the Gigabyte conversion.

Here is what our final report comes out like :

Select Top 100 Percent dbo.tblComputers.Computername As Computer,
  tblComputersystem.Model As [Computer Model], dbo.tblDiskdrives.Caption As
  Drive, Cast(Cast(dbo.tblDiskdrives.Size As BigInt) / 1024 / 1024 /
  1024 As Numeric) As [Total size (GB)], Cast(Cast(dbo.tblDiskdrives.Freespace
  As BigInt) / 1024 / 1024 / 1024 As Numeric) As [Free in GB],
  Cast((Cast(dbo.tblDiskdrives.Size As BigInt) -
  Cast(dbo.tblDiskdrives.Freespace As BigInt)) / 1024 / 1024 / 1024 As
  Numeric) As [Used Space (GB)], tblFloppy.Model As [HDD Model],
  dbo.tblDiskdrives.Lastchanged As [Last changed],
  Cast(Cast(Cast(dbo.tblDiskdrives.Freespace As BigInt) As Numeric) /
  Cast(Cast(dbo.tblDiskdrives.Size As BigInt) As Numeric) * 100 As BigInt) As
  [Used PCT]
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
  tblComputersystem On dbo.tblComputers.Computername =
    tblComputersystem.Computername Inner Join
  tblFloppy On dbo.tblComputers.Computername = tblFloppy.Computername
Where dbo.tblComputers.Computername Not Like 'COMPUTER' And
  dbo.tblDiskdrives.DriveType = '3'
Order By dbo.tblComputers.Computername, dbo.tblDiskdrives.Caption,
  tblFloppy.Model

Hopefully this will help others that are having the same needs as us 🙂

[pentel]

Below is a screenshot of the output

http://i.imgur.com/rrj1W.jpg

[pentel]

Nope doesn't work 😞 gives back 0 results

I think this might have something to do with null values, would that make sense?

Thanks by the way for the quick reply!

[Hemoco]

pentel wrote:
Nope doesn't work 😞 gives back 0 results

I think this might have something to do with null values, would that make sense?

Thanks by the way for the quick reply!

Did you get any results without the percentage column?

[Hemoco]

try this:

Select Top 100 Percent dbo.tblComputers.Computername As Computer,
tblComputersystem.Model As [Computer Model], dbo.tblDiskdrives.Caption As
Drive, Cast(Cast(dbo.tblDiskdrives.Size As BigInt) / 1024 / 1024 /
1024 As Numeric) As [Total size (GB)], Cast(Cast(dbo.tblDiskdrives.Freespace
As BigInt) / 1024 / 1024 / 1024 As Numeric) As [Free in GB],
Cast((Cast(dbo.tblDiskdrives.Size As BigInt) -
Cast(dbo.tblDiskdrives.Freespace As BigInt)) / 1024 / 1024 / 1024 As
Numeric) As [Used Space (GB)], tblFloppy.Model As [HDD Model],
dbo.tblDiskdrives.Lastchanged As [Last changed],
Cast(Cast(Cast(dbo.tblDiskdrives.Freespace As BigInt) / 1024 / 1024 /
1024 As Numeric) / Cast(Cast(dbo.tblDiskdrives.Size As BigInt) / 1024 / 1024 /
1024 As Numeric) * 100 As bigint) As [Used PCT]
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
tblComputersystem On dbo.tblComputers.Computername =
tblComputersystem.Computername Inner Join
tblFloppy On dbo.tblComputers.Computername = tblFloppy.Computername
Where dbo.tblDiskdrives.DriveType = '3'
Order By dbo.tblComputers.Computername, dbo.tblDiskdrives.Caption,
tblFloppy.Model