Need to create a new report but now sure how

[HasayeretFMG]

Hi guys,

Here's my first post here 🙂 So glad I've found this place.
I'm really new to Lansweeper and have a question which hopefully someone will be able to answer me with:
There's a built-in report called Disk: Servers less than 1 GB free HD
I'd like to create another one but with a different quota... let's say 20 GB free.
Can anybody walk me through on how to do that?
Like I said, I'm really new to this so go easy on me 🙂

Thanks!

[RCorbeil · Accepted solution]

You only want to make one change to the original code:

Where
  Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) < 1024

becomes

Where
  Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) < (20 * 1024)

The division by 1024 everywhere else is being done to convert bytes to megabytes.
e.g. tblDiskdrives.Freespace is the free capacity in bytes, so

Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As free

is saying (free space in bytes) / 1024 = free space in kilobytes,
then ((free space in bytes) / 1024) / 1024 = free space in megabytes

[HasayeretFMG]

Really appreciate your answer!
What I ended up doing is copying all the SQL code to a new report and just changed the 1024 to 20480 anywhere I saw 1024 but for some reason now it shows me a lot of servers which I know for sure that have more than 20 free gigs. What did I miss?
Here's my code just in case you want to see it. Thanks so much!

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblDiskdrives.Caption,
Cast(Cast(tblDiskdrives.Freespace As bigint) / 20480 / 20480 As numeric) As
free,
Cast(Cast(tblDiskdrives.Size As bigint) / 20480 / 20480 As numeric) As
[total size],
tblDiskdrives.Lastchanged As [last changed],
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
And tsysIPLocations.EndIP >= tblAssets.IPNumeric
Where Cast(Cast(tblDiskdrives.Freespace As bigint) / 20480 / 20480 As numeric) <
20480 And Cast(Cast(tblDiskdrives.Size As bigint) / 20480 / 20480 As
numeric) <> 0 And tblComputersystem.Domainrole > 1 And
tblDiskdrives.DriveType = 3 And tblAssetCustom.State = 1

[RCorbeil]

Get to know SQL. That's all the "reports" in LANSweeper are: SQL queries.

With respect to your specific example, look at the WHERE clause on the query/report:

Where
  Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) < 1024
  And Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) <> 0
  And tblComputersystem.Domainrole > 1
  And tblDiskdrives.DriveType = 3
  And tblAssetCustom.State = 1

The first condition is the one you're interested in. It converts the bytes free to megabytes (/1024 = kilobytes, /1024 again = megabytes). So if (free megabytes) < 1024 is saying if free space < 1 gigabyte. If you want to bump it up to 20GB, you could math it out manually or just add "* 20" after the "< 1024" and let the computer do the work.