Assets Scanned by SCCM

Select Top 1000000 tblAssets.AssetID,
  tsysAssetTypes.AssetTypename As Type,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  Coalesce(tblAssets.AssetName, tblSccmAsset.Name) As AssetName,
  Coalesce(tblAssets.Domain, tblSccmAsset.FullDomainName) As Domain,
  Coalesce(tblAssetCustom.Manufacturer, tblSccmAsset.PcManufacturer) As
  Manufacturer,
  Coalesce(tblAssetCustom.Model, tblSccmAsset.PcModel) As Model,
  tblSccmAsset.LastScanDate,
  Coalesce(tblAssets.IPAddress, tblSccmAsset.IpAddress) As IpAddress,
  tblAssets.Description,
  tblSccmSite.Name As SCCMSite,
  tblSccmServer.ServerName As SCCMServer
From tblSccmAsset
  Inner Join tblSccmSite On tblSccmSite.SccmSiteId = tblSccmAsset.SccmSiteId
  Inner Join tblSccmServer On tblSccmSite.SccmServerId =
    tblSccmServer.SccmServerId
  Left Join tblAssets On tblSccmAsset.AssetId = tblAssets.AssetID
  Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysAssetTypes On tsysAssetTypes.AssetType = tblSccmAsset.Assettype
  Left Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Order By tblAssets.Assettype,
  tblAssets.AssetName