Notification

Icon
Error

Using tblO365User report for devices Out of warranty

Posted: Friday, May 14, 2021 5:15:37 PM(UTC)
QueryLSTech

QueryLSTech

Member Original PosterPosts: 4
0
Like
Hello, I do have a report for our Out of warranty devices which works fine, but I need to get the location so I've added the column tblO365User.City which also works ok.

But there is a difference of number of devices scanned when I add this tblO365User.City (24 devices to be more specific, a couple of them are servers)

The query is basically the same, any clue why the second report below is missing some devices?!

1 - 202 devices
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssetCustom.Serialnumber,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,
tblAssetCustom.PurchaseDate As PurchaseDate,
tblAssetCustom.Warrantydate As WarrantyExpiration,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Coalesce(tsysOS.OSname, tblMacOSInfo.SystemVersion, tblLinuxSystem.OSRelease)
As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where tblAssetCustom.Warrantydate >= Cast('01-01-2021' As DATETIME) And
tblAssetCustom.Warrantydate <= Cast('12-31-2021' As DATETIME) And
tblState.Statename = 'Active'
Order By WarrantyExpiration,
PurchaseDate,
tblAssets.Domain,
tblAssets.AssetName

2 - 178 Devices
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblO365User.City,
tblAssets.Domain,
tblAssetCustom.Serialnumber,
tsysAssetTypes.AssetTypename As AssetType,
tblAssetCustom.PurchaseDate As PurchaseDate,
tblAssetCustom.Warrantydate As WarrantyExpiration,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Coalesce(tsysOS.OSname, tblMacOSInfo.SystemVersion, tblLinuxSystem.OSRelease)
As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblO365User
Left Join tblAssets On tblO365User.MailNickName = tblAssets.Username
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where tblAssetCustom.Warrantydate >= Cast('01-01-2021' As DATETIME) And
tblAssetCustom.Warrantydate <= Cast('12-31-2021' As DATETIME) And
tblState.Statename = 'Active'
Order By WarrantyExpiration,
PurchaseDate,
tblAssets.Domain,
tblAssets.AssetName

Active Discussions

Lansweeper Enterprise Options in Menu Bar/Configuration
by  mk@allan   Go to last post Go to first unread
Last post: 6/18/2021 7:38:43 PM(UTC)
Lansweeper No One getting back to me from Lansweeper
by  Kenneth Lindsay  
Go to last post Go to first unread
Last post: 6/18/2021 3:31:06 PM(UTC)
Lansweeper INFO DateTimeService time refresh
by  miharix   Go to last post Go to first unread
Last post: 6/18/2021 10:48:57 AM(UTC)
Lansweeper RPC Unavailable error
by  Greeno  
Go to last post Go to first unread
Last post: 6/17/2021 7:15:07 PM(UTC)
Lansweeper Exclude Search
by  pryan67  
Go to last post Go to first unread
Last post: 6/16/2021 4:01:43 PM(UTC)
Lansweeper Report: All Apple Mac devices with Memory RAM asset
by  gabrielo   Go to last post Go to first unread
Last post: 6/16/2021 3:17:24 PM(UTC)
Lansweeper Does technical support for LS really respond?
by  tosch  
Go to last post Go to first unread
Last post: 6/16/2021 12:48:50 PM(UTC)