→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
09-28-2022 06:18 PM
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.
Solved! Go to Solution.
10-28-2022 10:50 PM - edited 10-28-2022 10:57 PM
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
12-27-2022 04:54 PM
I'm confused how would this report work? I thought Lansweeper does not scan Computers only Phones.
10-28-2022 10:50 PM - edited 10-28-2022 10:57 PM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now