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 Full asset report with and without ad relationship
by  teddyh2o   Go to last post Go to first unread
Last post: 6/18/2021 8:28:24 PM(UTC)
Report Center Show newly discovered software
by  CyberCitizen  
Go to last post Go to first unread
Last post: 6/16/2021 12:06:21 AM(UTC)
Lansweeper Multiple Devices Owned by Users (asset relations)
by  Charles S.   Go to last post Go to first unread
Last post: 6/15/2021 9:38:26 PM(UTC)
Lansweeper Show Date Without Time
by  RC62N  
Go to last post Go to first unread
Last post: 6/15/2021 9:04:47 PM(UTC)
Lansweeper Windows 10 Version Chart
by  RC62N   Go to last post Go to first unread
Last post: 6/14/2021 6:16:52 PM(UTC)
Lansweeper LSAgent Report
by  brodiemac-too  
Go to last post Go to first unread
Last post: 6/14/2021 5:27:29 PM(UTC)