Select Distinct Top 1000000 Coalesce(tsysOS.Image,
tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblState.Statename As State,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
subquery1.SystemVersion,
subquery1.[MacOS Patch Status],
subquery2.softwareName,
subquery2.Version,
subquery2.[Safari Patch Status],
tblAssets.Lastseen,
tblAssets.Lasttried,
Case
When subquery1.SystemVersion Like 'macOS 12%' And
subquery1.[MacOS Patch Status] = 'Up to date' Then '#d4f4be'
When subquery1.SystemVersion Not Like 'macOS 12%' And
subquery2.[Safari Patch Status] = 'Up to date' Then '#d4f4be'
Else '#ffadad'
End As backgroundcolor
From tblAssets
Left Join (Select tblmacosinfo.AssetID,
tblmacosinfo.SystemVersion,
Case
When Cast(SubString(ParseName(tblmacosinfo.SystemVersion, 3),
PatIndex('%[0-9]%', ParseName(tblmacosinfo.SystemVersion, 3)),
Len(ParseName(tblmacosinfo.SystemVersion, 3))) As INT) > 12 Then
'Up to date'
When Cast(SubString(ParseName(tblmacosinfo.SystemVersion, 3),
PatIndex('%[0-9]%', ParseName(tblmacosinfo.SystemVersion, 3)),
Len(ParseName(tblmacosinfo.SystemVersion, 3))) As INT) = 12 And
Cast(ParseName(tblmacosinfo.SystemVersion, 2) As INT) > 2 Then
'Up to date'
When Cast(SubString(ParseName(tblmacosinfo.SystemVersion, 3),
PatIndex('%[0-9]%', ParseName(tblmacosinfo.SystemVersion, 3)),
Len(ParseName(tblmacosinfo.SystemVersion, 3))) As INT) = 12 And
Cast(ParseName(tblmacosinfo.SystemVersion, 2) As INT) = 2 And
Cast(Left(ParseName(tblmacosinfo.SystemVersion, 1), CharIndex(' (',
ParseName(tblmacosinfo.SystemVersion, 1) + ' (') - 1) As INT) >= 1
Then 'Up to date'
Else 'Out of date'
End As [MacOS Patch Status]
From tblmacosinfo
Where tblmacosinfo.SystemVersion Not Like '%Server%') As subquery1
On subquery1.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join (Select tblMacApplications.assetid,
tblSoftwareUni.softwareName,
tblMacApplications.Version,
Case
When Len(tblMacApplications.Version) -
Len(Replace(tblMacApplications.Version, '.', '')) = 1 And
Cast(ParseName(tblMacApplications.Version, 2) As INT) > 15 Then
'Up to date'
When Len(tblMacApplications.Version) -
Len(Replace(tblMacApplications.Version, '.', '')) = 2 And
Cast(ParseName(tblMacApplications.Version, 3) As INT) > 15 Then
'Up to date'
When Len(tblMacApplications.Version) -
Len(Replace(tblMacApplications.Version, '.', '')) = 1 And
Cast(ParseName(tblMacApplications.Version, 2) As INT) = 15 And
Cast(ParseName(tblMacApplications.Version, 1) As INT) >= 3 Then
'Up to date'
When Len(tblMacApplications.Version) -
Len(Replace(tblMacApplications.Version, '.', '')) = 2 And
Cast(ParseName(tblMacApplications.Version, 3) As INT) = 15 And
Cast(ParseName(tblMacApplications.Version, 2) As INT) >= 3 Then
'Up to date'
Else 'Out of date'
End As [Safari Patch Status]
From tblMacApplications
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblMacApplications.softid
Where tblSoftwareUni.softwareName = 'Safari') As subquery2 On
subquery2.assetid = tblAssets.assetid
Where tblAssetCustom.State = 1 And tsysAssetTypes.AssetTypename = 'Apple Mac'
Order By tblAssets.Domain,
tblAssets.AssetName