Using tblO365User report for devices Out of warranty

[luiz_filipini]

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

[prighi61]

I believe in some cases a record in tblO365User doesn't exists. If you change the initial part of the "from" clause into that:

From tblAssets left outer join tblO365User On tblO365User.MailNickName = tblAssets.Username

you will get NULL for users not found in tblO365User, but the number of records will be the same.