→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
E__L__Gray
Engaged Sweeper II
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.
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
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.

View solution in original post

5 REPLIES 5
Susan_A
Lansweeper Alumni
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
Engaged Sweeper II
Perfect! Thank You!!
RCorbeil
Honored Sweeper II
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
Engaged Sweeper II
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
Lansweeper Alumni
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.