Disk: Workstations with less than 1GB of Space

[wilkie1984]

Hi there,

The D drive is all that is showing on this report on my dash board and this is the recovery partition. How can i only have it monitoring the C drive on the machines as this is giving me false readings on my dash board when people login to look at it?

Thanks,
David

[Bruce_B]

I just tested the report in both SQL Compact and SQL Server, it shouldn't have any syntax errors. Do make sure you're copying just the report and nothing extra. I've pasted it again underneath without the formatting, copy and paste it directly into the bottom window under Reports\Create New Report.




Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
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],
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 tblDiskdrives.Caption Like '%C%' And Cast(Cast(tblDiskdrives.Freespace As
bigint) / 1024 / 1024 As numeric) < 1024 And Cast(Cast(tblDiskdrives.Size As
bigint) / 1024 / 1024 As numeric) > 1000 And tblComputersystem.Domainrole < 2
And tblDiskdrives.DriveType = 3 And tblAssetCustom.State = 1

[wilkie1984]

Hi there,

Thanks but I get the following error

Invalid SELECT statement. Unexpected token " And" at line 35, pos 1.: Unexpected token " And" at line 35, column 1

Thanks,
David

[Bruce_B · Accepted solution]

The adjusted version of the built-in report which we've pasted below will only check the C-drives of workstations. Instructions for adding this report to your Lansweeper installation can be found here.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  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],
  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 tblDiskdrives.Caption Like '%C%' And Cast(Cast(tblDiskdrives.Freespace As
  bigint) / 1024 / 1024 As numeric) < 1024 And Cast(Cast(tblDiskdrives.Size As
  bigint) / 1024 / 1024 As numeric) > 1000 And tblComputersystem.Domainrole < 2
  And tblDiskdrives.DriveType = 3 And tblAssetCustom.State = 1