Space on C Drive (Variables)

[E__L__Gray]

In our company we name laptops LAP### and desktops CPU### I'm looking for a way to report the following information.

ONLY LAP and CPU (No servers/VMs/etc.) with less than 5 GB of Free Space ONLY on the C: Drive. I'd also like this to show the Free Space and the Total Size.

I can get most of this information in a report, but it seems as I add the variables it kicks out an error. I can get LAP/CPU with less than, but it kicks out all disk drives on a machine. I can get just the C: Drive but it kicks out all machines on the network. (Servers/VMs/Etc.)

Any help would be appreciated.

[Susan_A]

Just an FYI for everyone else: the instructions in my initial post will work as well. You just need to ensure, for the specific situation described here, that you're adding all criteria to the same Criteria column. The filter below should be in the same Criteria column as all the other filters. Criteria added to multiple columns are separated with OR operators, i.e. machines meeting requirement 1 OR requirement 2 will be listed. More information on SQL's AND and OR operators can be found here.

Like 'LAP%' or Like 'CPU%'

[E__L__Gray]

Perfect! Thank You!!

[RCorbeil]

Check your WHERE clause.

The conditions you've set up:

WHERE
  ( tblAssetCustom.State = 1
    AND tblComputersystem.Domainrole < 2
    AND tblDiskdrives.DriveType = 3
    AND tblDiskdrives.Caption = 'C:'
    AND Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) < 10000
    AND Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) <> 0
  )
  OR (tblAssets.AssetName Like 'LAP%')
  OR (tblAssets.AssetName Like 'CPU%')

What I think you mean:

WHERE
    tblAssetCustom.State = 1
    AND tblComputersystem.Domainrole < 2
    AND tblDiskdrives.DriveType = 3
    AND tblDiskdrives.Caption = 'C:'
    AND Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) < 10000
    AND Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) <> 0
    AND ( (tblAssets.AssetName Like 'LAP%')
          OR (tblAssets.AssetName Like 'CPU%')
        )

[E__L__Gray]

I enter it like that I get ALL the disk drives. I only want the C: drive on these machines.

This is what I have right now.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname,
tblDiskdrives.Caption,
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 As numeric) As
free,
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 As numeric) As
[total size],
tsysOS.Image As icon,
tblAssetCustom.Department,
tblAssetCustom.Contact,
tblAssetCustom.Building,
tblAssetCustom.Warrantydate,
tblAssets.AssetName
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
Where (tblDiskdrives.Caption = 'C:' And Cast(Cast(tblDiskdrives.Freespace As
bigint) / 1024 / 1024 As numeric) < 10000 And Cast(Cast(tblDiskdrives.Size As
bigint) / 1024 / 1024 As numeric) <> 0 And tblComputersystem.Domainrole < 2
And tblDiskdrives.DriveType = 3 And tblAssetCustom.State = 1) Or
(tblAssets.AssetName Like 'LAP%') Or
(tblAssets.AssetName Like 'CPU%')

[Susan_A · Accepted solution]

You can modify the built-in "Disk: Workstations less than 1 GB free HD" report in the Reports tab:

• Open the report in the Reports tab.
  
• Hit the Edit Report button on the left.
  
• Hit Save As to save a copy of the report under a different name.
  
• Tick the AssetName field in the tblAssets database table and add the following to the Criteria column of the AssetName expression: Like 'LAP%' or Like 'CPU%'
  
• Replace 1024 (= 1024MB) in the Criteria column with 5120.
  
• Add the following to the Criteria column of the tblDiskdrives.Caption expression, the same column as the other criteria: = 'C:'

If you are interested in building or modifying reports, we do recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  
• Making use of our database dictionary, more information on which can be found here. The dictionary explains in great detail what each table and field stores.