
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-23-2017 09:10 AM
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 = '*********')
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 = '*********')
Solved! Go to Solution.
Labels:
- Labels:
-
General Discussion
1 ACCEPTED SOLUTION

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-23-2017 10:50 PM
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
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
2 REPLIES 2

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-23-2017 10:50 PM
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
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

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-23-2017 10:41 AM
Reports shows laptops whose HDD are greater than 20% and even less than 20%
