Report - Office 2016, Professional Plus 2016 migration to Microsoft 365 Apps for Enterprise

[duster1]

I made the report to help with automation with locating assets that were missed, or not assigned to a specific user during migration. Our specific software packages were Office 2016 and Office Professional Plus 2016.

The first report gives a list of Office 2016, Professional Plus 2016 and Micrsoft 365 Apps for Enterprise.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.Username,
  tblAssets.Userdomain,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  Case
    When tblSoftwareUni.softwareName Like '%Microsoft 365 App%' Then
      'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftware.Lastchanged,
  Case
    When tblSoftwareUni.softwareName Like '%Microsoft 365 App%' Then '#d4f4be'
    Else '#ffadad'
  End As backgroundcolor
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
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where (tblSoftwareUni.softwareName Like '%Microsoft 365 Apps%' And
    tsysOS.OSname = 'Win 10' And tblSoftwareUni.SoftwareName Like
    '%Microsoft 365 App%') Or
  (tblSoftwareUni.SoftwareName Like '%Microsoft Office Professional Plus 2016%'
    And tblState.Statename = 'Active') Or
  (tblSoftwareUni.SoftwareName Like '%Microsoft Office Standard 2016%'
    And tblAssetCustom.State = 1)
Order By tblAssets.AssetName

The next section is just for the "update needed" report. We will use this for setting an automated deployment at "next scan".

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.Username,
  tblAssets.Userdomain,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  Case
    When tblSoftwareUni.softwareName Like '%Microsoft 365 App%' Then
      'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftware.Lastchanged,
  Case
    When tblSoftwareUni.softwareName Like '%Microsoft 365 App%' Then '#d4f4be'
    Else '#ffadad'
  End As backgroundcolor
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
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where (tblSoftwareUni.softwareName Like '%Microsoft 365 Apps%' And
    tblSoftwareUni.SoftwareName Like '%Microsoft 365 App%' And
    tblState.Statename = 'Out of date') Or
  (tblSoftwareUni.SoftwareName Like '%Microsoft Office Professional Plus 2016%')
  Or
  (tblSoftwareUni.SoftwareName Like '%Microsoft Office Standard 2016%'
    And tblAssetCustom.State = 1)
Order By tblAssets.AssetName

I hope these reports can help others that are migrating to M365.

[Mercedes_O]

Hi @duster1  much appreciated thank you for sharing these new reports