Report on Devices found in Lansweeper but not found in Intune?

[LivingMachine]

I'm trying to figure out how to have a Lansweeper report that shows what devices in Lansweeper are NOT enrolled in Intune. Is that possible? I found a report that shows what devices are enrolled in Intune, but I'm trying to figure out which Devices may have been missed.

[Alig26]

I'm confused how would this report work? I thought Lansweeper does not scan Computers only Phones.

[rom · Accepted solution]

Basically you change the join on the intune table from INNER (a match) to LEFT (a match/no match that puts tblassets as the main table) -  you can put the other fields in if you want, I just whittled down the report to a simple yes or no -  it matches from tblassets to the intune table on the assetID.  If the assetID ain't in that intune table, it will return NULL - so I just said if its Null then 'no' else 'yes' 

You could add this LEFT JOIN on the intune table to anything related to tblassets (like an all assets report, or an all windows asset report, etc) and do the same comparison and add that yes/no as a field in the report.

Hope that helps - 

Try this:

Select Top (1000000) tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblADusers.Username,
  tsysAssetTypes.AssetTypename As AssetType,
case when tblIntuneDevice.AssetId is NULL then 'No' else 'Yes' end as [Enrolled in Intune],
  tblAssets.Firstseen As [Created at],
  tblAssets.Lastseen As [Last successful scan],
  tblAssets.Lasttried As [Last scan attempt]
From tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tblIntuneDevice On tblIntuneDevice.AssetId = tblAssets.AssetID
  Left Outer Join tblADusers On Lower(tblIntuneDevice.EmailAddress) In
      (Lower(tblADusers.email), Lower(tblADusers.UPN))
Where tblState.Statename = 'Active'
Order By tblAssets.AssetName