All Windows Servers and All Linux Red Hat Servers

[tomscott2340]

I've been trying to get a report that provides all my Windows servers and all my Red Hat Linux servers in one report..

Here is my "All Windows Servers" that has some custom fields returned..

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  Case
    When tblADComputers.Description Like '%DC1%' Then 'DC1'
    When tblADComputers.Description Like '%DC2%' Then 'DC2'
    When tblADComputers.Description Like '%PH1%A/A%' Then 'Phase 1 A/A'
    When tblADComputers.Description Like '%PH1%A/M%' Then 'Phase 1 A/M'
    When tblADComputers.Description Like '%PH2%A/M%' Then 'Phase 2 A/M'
    When tblADComputers.Description Like '%PH2%A/A%' Then 'Phase 2 A/A'
    When tblADComputers.Description Like '%PH3%A/M%' Then 'Phase 3 A/M'
    When tblADComputers.Description Like '%PH3%A/A%' Then 'Phase 3 A/A'
    When tblADComputers.Description Like '%PH4%A/M%' Then 'Phase 4 A/M'
    When tblADComputers.Description Like '%PH4%A/A%' Then 'Phase 4 A/A'
    When tblADComputers.Description Like '%PH4%M/M%' Then 'Phase 4 M/M'
    Else 'Phase UNKNOWN'
  End As 'Patching Phase',
  tsysIPLocations.IPLocation,
  tsysOS.OSname + ' SP' + Convert(VARCHAR,tblAssets.SP) As 'Operating System',
  tblAssets.IPAddress,
  tblAssetCustom.Custom1 As Purpose,
  tblAssetCustom.Custom2 As 'Business Owner',
  tblAssetCustom.Custom3 As 'IT Owner'
From tblAssets
  Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblOperatingsystem.Caption Like '%server%'
Order By tblAssets.AssetName

I tried creating one using the code below but it's not returning my Windows servers, just my Red Hat.. Not sure how to fix.. Thanks

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress,
    tblAssets.Domain,
  Case
    When tblADComputers.Description Like '%DC1%' Then 'DC1'
    When tblADComputers.Description Like '%DC2%' Then 'DC2'
    When tblADComputers.Description Like '%PH1%A/A%' Then 'Phase 1 A/A'
    When tblADComputers.Description Like '%PH1%A/M%' Then 'Phase 1 A/M'
    When tblADComputers.Description Like '%PH2%A/M%' Then 'Phase 2 A/M'
    When tblADComputers.Description Like '%PH2%A/A%' Then 'Phase 2 A/A'
    When tblADComputers.Description Like '%PH3%A/M%' Then 'Phase 3 A/M'
    When tblADComputers.Description Like '%PH3%A/A%' Then 'Phase 3 A/A'
    When tblADComputers.Description Like '%PH4%A/M%' Then 'Phase 4 A/M'
    When tblADComputers.Description Like '%PH4%A/A%' Then 'Phase 4 A/A'
    When tblADComputers.Description Like '%PH4%M/M%' Then 'Phase 4 M/M'
    Else 'Phase UNKNOWN'
  End As 'Patching Phase',
  tsysIPLocations.IPLocation,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblLinuxSystem.OSRelease,
  tblLinuxSystem.OperatingSystem,
  tblOperatingsystem.Caption

From tblAssets
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
    Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
    Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where OSRelease like '%Red%Hat%' OR tblOperatingsystem.Caption Like '%server%'

[tomscott2340 · Accepted solution]

Never mind.. Got it..

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  Case
    When tblADComputers.Description Like '%DC1%' Then 'DC1'
    When tblADComputers.Description Like '%DC2%' Then 'DC2'
    When tblADComputers.Description Like '%PH1%A/A%' Then 'Phase 1 A/A'
    When tblADComputers.Description Like '%PH1%A/M%' Then 'Phase 1 A/M'
    When tblADComputers.Description Like '%PH2%A/M%' Then 'Phase 2 A/M'
    When tblADComputers.Description Like '%PH2%A/A%' Then 'Phase 2 A/A'
    When tblADComputers.Description Like '%PH3%A/M%' Then 'Phase 3 A/M'
    When tblADComputers.Description Like '%PH3%A/A%' Then 'Phase 3 A/A'
    When tblADComputers.Description Like '%PH4%A/M%' Then 'Phase 4 A/M'
    When tblADComputers.Description Like '%PH4%A/A%' Then 'Phase 4 A/A'
    When tblADComputers.Description Like '%PH4%M/M%' Then 'Phase 4 M/M'
    Else 'Phase UNKNOWN'
  End As 'Patching Phase',
  tsysIPLocations.IPLocation,
  case when (OSRelease like '%Red%Hat%' OR OSRelease like '%ORACLE%' OR OSRelease like '%Debian%') then tbllinuxsystem.OSRelease
  else tsysOS.OSname + ' SP' + Convert(VARCHAR,tblAssets.SP)
  end As 'Operating System', 
  tblAssets.IPAddress,
  tblAssetCustom.Custom1 As Purpose,
  tblAssetCustom.Custom2 As 'Business Owner',
  tblAssetCustom.Custom3 As 'IT Owner'


From tblAssets
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
    And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
    Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
    Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  
  left outer Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where (OSRelease like '%Red%Hat%' OR OSRelease like '%ORACLE%' OR OSRelease like '%Debian%' OR tblOperatingsystem.Caption Like '%server%') 
Order By tblAssets.AssetName