→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
pentel
Engaged Sweeper III
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
1 ACCEPTED SOLUTION
pentel
Engaged Sweeper III
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 🙂

View solution in original post

5 REPLIES 5
pentel
Engaged Sweeper III
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
Engaged Sweeper III
Below is a screenshot of the output

http://i.imgur.com/rrj1W.jpg
pentel
Engaged Sweeper III
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
Lansweeper Alumni
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
Lansweeper Alumni
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