cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
LivingMachine
Engaged Sweeper

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.

 

1 ACCEPTED SOLUTION
rom
Champion Sweeper II

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

 

View solution in original post

2 REPLIES 2
Alig26
Engaged Sweeper II

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

rom
Champion Sweeper II

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

 

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now