Select Top 1000000 tblAssets.AssetID,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
 tblAssets.AssetName,
 tblSoftwareUni.softwareName As Software,
 tblSoftwareUni.SoftwarePublisher As Publisher,
 tblSoftware.softwareVersion As Version,
 b.LatestVersion as [Highest Version Detected],
  tblSoftware.Lastchanged,
  tblassets.Lastseen
From tblAssets
 Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
 Inner Join tblState On tblState.State = tblAssetCustom.State
 Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
 Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
 Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
 Inner Join (Select __.SoftID,
   __.Major,
   __.Minor,
   __.Build,
   __.Revision,
   LatestVersion = __.softwareVersion
  From (Select *,
     r = Row_Number() Over (Partition By _.SoftID Order By _.Major Desc,
     _.Minor Desc, _.Build Desc, _.Revision Desc)
    From (Select Distinct tblSoftware.SoftID,
       Major = Try_Convert(int,ParseName(tblSoftware.softwareVersion, 4)),
       Minor = Try_Convert(int,ParseName(tblSoftware.softwareVersion, 3)),
       Build = Try_Convert(int,ParseName(tblSoftware.softwareVersion, 2)),
       Revision = Try_Convert(int,ParseName(tblSoftware.softwareVersion, 1)),
       tblSoftware.softwareVersion
      From tblSoftware
       Inner Join tblSoftwareUni On tblSoftware.SoftID =
         tblSoftwareUni.SoftID
      Where tblSoftwareUni.softwareName Like ('%Microsoft Edge')) _) __
  Where __.r = 1) b On tblSoftware.SoftID = b.SoftID And
   tblSoftware.softwareVersion <> b.LatestVersion
Where tblSoftwareUni.softwareName like ('%Microsoft Edge') And tblState.Statename = 'Active'

Union

Select Top 1000000 tblAssets.AssetID,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
 tblAssets.AssetName,
 tblSoftwareUni.softwareName As Software,
 tblSoftwareUni.SoftwarePublisher As Publisher,
 tblMacApplications.Version As Version,
 b.LatestVersion as [Highest Version Detected],
 tblMacApplications.Lastchanged,
  tblassets.Lastseen
From tblAssets
 Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
 Inner Join tblState On tblState.State = tblAssetCustom.State
 Inner Join tblMacApplications On tblAssets.AssetID = tblMacApplications.AssetID
 Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblMacApplications.softid
 Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
 Inner Join (Select __.SoftID,
   __.Major,
   __.Minor,
   __.Build,
   __.Revision,
   LatestVersion = __.Version
  From (Select *,
     r = Row_Number() Over (Partition By _.SoftID Order By _.Major Desc,
     _.Minor Desc, _.Build Desc, _.Revision Desc)
    From (Select Distinct tblMacApplications.SoftID,
       Major = Try_Convert(int,ParseName(tblMacApplications.Version, 4)),
       Minor = Try_Convert(int,ParseName(tblMacApplications.Version, 3)),
       Build = Try_Convert(int,ParseName(tblMacApplications.Version, 2)),
       Revision = Try_Convert(int,ParseName(tblMacApplications.Version, 1)),
       tblMacApplications.Version
      From tblMacApplications
       Inner Join tblSoftwareUni On tblMacApplications.SoftID =
         tblSoftwareUni.SoftID
      Where tblSoftwareUni.softwareName Like ('%Microsoft Edge')) _) __
  Where __.r = 1) b On tblMacApplications.SoftID = b.SoftID And
   tblMacApplications.Version <> b.LatestVersion
Where tblSoftwareUni.softwareName like ('%Microsoft Edge') And tblState.Statename = 'Active'

Union

Select Top 1000000 tblAssets.AssetID,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
 tblAssets.AssetName,
 tblSoftwareUni.softwareName As Software,
 tblSoftwareUni.SoftwarePublisher As Publisher,
 tblLinuxSoftware.Version As Version,
 b.LatestVersion as [Highest Version Detected],
  tblLinuxSoftware.Lastchanged,
  tblassets.Lastseen
From tblAssets
 Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
 Inner Join tblState On tblState.State = tblAssetCustom.State
 Inner Join tblLinuxSoftware On tblAssets.AssetID = tblLinuxSoftware.AssetID
 Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblLinuxSoftware.SoftwareUniID
 Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
 Inner Join (Select __.SoftwareUniID,
   __.Major,
   __.Minor,
   __.Build,
   __.Revision,
   LatestVersion = __.Version
  From (Select *,
     r = Row_Number() Over (Partition By _.SoftwareUniID Order By _.Major Desc,
     _.Minor Desc, _.Build Desc, _.Revision Desc)
    From (Select Distinct tblLinuxSoftware.SoftwareUniID,
       Major = Try_Convert(int,ParseName(CASE
                WHEN tblLinuxSoftware.Version LIKE '%-1%' THEN LEFT(tblLinuxSoftware.Version, CHARINDEX('-', tblLinuxSoftware.Version) - 1)
                WHEN tblLinuxSoftware.Version LIKE '%R%' THEN LEFT(tblLinuxSoftware.Version, CHARINDEX('R', tblLinuxSoftware.Version) - 1)
                ELSE tblLinuxSoftware.Version
            END, 4)),
       Minor = Try_Convert(int,ParseName(CASE
                WHEN tblLinuxSoftware.Version LIKE '%-1%' THEN LEFT(tblLinuxSoftware.Version, CHARINDEX('-', tblLinuxSoftware.Version) - 1)
                WHEN tblLinuxSoftware.Version LIKE '%R%' THEN LEFT(tblLinuxSoftware.Version, CHARINDEX('R', tblLinuxSoftware.Version) - 1)
                ELSE tblLinuxSoftware.Version
            END, 3)),
       Build = Try_Convert(int,ParseName(CASE
                WHEN tblLinuxSoftware.Version LIKE '%-1%' THEN LEFT(tblLinuxSoftware.Version, CHARINDEX('-', tblLinuxSoftware.Version) - 1)
                WHEN tblLinuxSoftware.Version LIKE '%R%' THEN LEFT(tblLinuxSoftware.Version, CHARINDEX('R', tblLinuxSoftware.Version) - 1)
                ELSE tblLinuxSoftware.Version
            END, 2)),
       Revision = Try_Convert(int,ParseName(CASE
                WHEN tblLinuxSoftware.Version LIKE '%-1%' THEN LEFT(tblLinuxSoftware.Version, CHARINDEX('-', tblLinuxSoftware.Version) - 1)
                WHEN tblLinuxSoftware.Version LIKE '%R%' THEN LEFT(tblLinuxSoftware.Version, CHARINDEX('R', tblLinuxSoftware.Version) - 1)
                ELSE tblLinuxSoftware.Version
            END, 1)),
       CASE
                WHEN tblLinuxSoftware.Version LIKE '%-1%' THEN LEFT(tblLinuxSoftware.Version, CHARINDEX('-', tblLinuxSoftware.Version) - 1)
                WHEN tblLinuxSoftware.Version LIKE '%R%' THEN LEFT(tblLinuxSoftware.Version, CHARINDEX('R', tblLinuxSoftware.Version) - 1)
                ELSE tblLinuxSoftware.Version
            END as Version
      From tblLinuxSoftware
       Inner Join tblSoftwareUni On tblLinuxSoftware.SoftwareUniID =
         tblSoftwareUni.SoftID
      Where tblSoftwareUni.softwareName = ('microsoft-edge-stable')) _) __
  Where __.r = 1) b On tblLinuxSoftware.SoftwareUniID = b.SoftwareUniID And
   CASE
                WHEN tblLinuxSoftware.Version LIKE '%-1%' THEN LEFT(tblLinuxSoftware.Version, CHARINDEX('-', tblLinuxSoftware.Version) - 1)
                WHEN tblLinuxSoftware.Version LIKE '%R%' THEN LEFT(tblLinuxSoftware.Version, CHARINDEX('R', tblLinuxSoftware.Version) - 1)
                ELSE tblLinuxSoftware.Version
            END <> b.LatestVersion
Where tblSoftwareUni.softwareName = ('microsoft-edge-stable') And tblState.Statename = 'Active'