Report not giving correct details

[atul557]

Hi Team,

We have report scheduled for laptop whose HDD is less than 20%.
the report was showing correct details but currently it is showing wrong info

Kindly help to resolve the issue.

Below is the query

Select Top 1000000 tblAssetCustom.Model,
tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname,
tblAssets.Description,
tblDiskdrives.Caption,
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 / 1024 As
numeric) As free,
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As numeric) As
[total size],
tblDiskdrives.Lastchanged As [last changed],
tblAssetCustom.Custom8 As Owner
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 (tblAssetCustom.Model = '20CLA36XIG' And Cast(Cast(tblDiskdrives.Freespace
As bigint) / 1024 / 1024 / 1024 As numeric) < (Cast(Cast(tblDiskdrives.Size As
bigint) / 1024 / 1024 / 1024 As numeric) * 0.2) And
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As numeric) <> 0
And tblDiskdrives.DriveType = 3 And tblAssetCustom.State = 1) Or
(tblAssetCustom.Model = 'ThinkPad X250')Select Top 1000000 tblAssetCustom.Model,
tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname,
tblAssets.Description,
tblDiskdrives.Caption,
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 / 1024 As
numeric) As free,
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As numeric) As
[total size],
tblDiskdrives.Lastchanged As [last changed],
tblAssetCustom.Custom8 As Owner
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 (tblAssetCustom.Model = '********' And Cast(Cast(tblDiskdrives.Freespace
As bigint) / 1024 / 1024 / 1024 As numeric) < (Cast(Cast(tblDiskdrives.Size As
bigint) / 1024 / 1024 / 1024 As numeric) * 0.2) And
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As numeric) <> 0
And tblDiskdrives.DriveType = 3 And tblAssetCustom.State = 1) Or
(tblAssetCustom.Model = '*********')

[KrisNelson · Accepted solution]

Your report is giving you devices with hard drives less then 20% full, or ThinkPad X250. Therefor it's not checking to see if the X250's are below 20% of not.

My guess is now that Lansweeper is converting Lenovo models from their sku's to their actual model name you want it to look for both X250 or 20CLA36XIG and then check to see if either of those are below 20% HDD space.

Here's that code:

Select Top 1000000 tblAssetCustom.Model,
tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname,
tblAssets.Description,
tblDiskdrives.Caption,
Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 / 1024 / 1024 As
numeric) As free,
Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As numeric) As
[total size],
tblDiskdrives.Lastchanged As [last changed],
tblAssetCustom.Custom8 As Owner
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 (tblAssetCustom.Model = '20CLA36XIG' Or tblAssetCustom.Model =
'ThinkPad X250') And Cast(Cast(tblDiskdrives.Freespace As bigint) / 1024 /
1024 / 1024 As numeric) < (Cast(Cast(tblDiskdrives.Size As bigint) /
1024 / 1024 / 1024 As numeric) * 0.2) And Cast(Cast(tblDiskdrives.Size As
bigint) / 1024 / 1024 / 1024 As numeric) <> 0 And tblDiskdrives.DriveType = 3
And tblAssetCustom.State = 1

[atul557]

Reports shows laptops whose HDD are greater than 20% and even less than 20%