Select Top 1000000 tsysASServers.Servername,
Case
When airwatch.airwatch > 0 Then 'Yes'
Else ''
End As Airwatch,
Case
When aws.aws > 0 Then 'Yes'
Else ''
End As AWS,
Case
When chrome.chrome > 0 Then 'Yes'
Else ''
End As ChromeOS,
Case
When intune.intune > 0 Then 'Yes'
Else ''
End As Intune,
Case
When intune2.intune2 > 0 Then 'Yes'
Else ''
End As IntuneV2,
Case
When o365.o365 > 0 Then 'Yes'
Else ''
End As O365,
Case
When o3652.o3652 > 0 Then 'Yes'
Else ''
End As O365V2,
Case
When azuread.azuread > 0 Then 'Yes'
Else ''
End As AzureAD,
Case
When azure.azure > 0 Then 'Yes'
Else ''
End As Azure,
Case
When warrantyscanning.warrantyscanning > 0 Then 'Yes'
Else ''
End As WarrantyScanning,
Case
When mailserver.mailserver > 0 Then 'Yes'
Else ''
End As Mailserver,
Case
When EnableAssetRecognition.EnableAssetRecognition > 0 Then 'Yes'
Else ''
End As CDR,
Case
When AutoUpdateType.AutoUpdateType > 0 Then 'Yes'
Else ''
End As AutoUpdate
From tsysASServers
Left Join (Select Count(tsysASServers.enableWarrantyScanning) As
warrantyscanning,
tsysASServers.Servername
From tsysASServers
Where tsysASServers.enableWarrantyScanning = 1
Group By tsysASServers.Servername) As warrantyscanning On
warrantyscanning.Servername = tsysASServers.Servername
Left Join (Select Count(tsysASServers.mailserver) As mailserver,
tsysASServers.Servername
From tsysASServers
Where tsysASServers.mailserver = 1
Group By tsysASServers.Servername) As mailserver On mailserver.Servername =
tsysASServers.Servername
Left Join (Select Count(tsysASServers.EnableAssetRecognition) As
EnableAssetRecognition,
tsysASServers.Servername
From tsysASServers
Where tsysASServers.EnableAssetRecognition = 1
Group By tsysASServers.Servername) As EnableAssetRecognition On
EnableAssetRecognition.Servername = tsysASServers.Servername
Left Join (Select Count(tsysASServers.IsAutoUpdateFetcher) As AutoUpdateType,
tsysASServers.Servername
From tsysASServers
Where tsysASServers.AutoUpdateType <> 0 And
tsysASServers.IsAutoUpdateFetcher = 1
Group By tsysASServers.Servername) As AutoUpdateType On
AutoUpdateType.Servername = tsysASServers.Servername
Left Join (Select Count(tsysAzureScanningTarget.ScheduleId) As azure,
tsysAzureScanningTarget.Servername
From tsysAzureScanningTarget
Where tsysAzureScanningTarget.Enabled = 1
Group By tsysAzureScanningTarget.servername) As azure On
azure.Servername = tsysASServers.Servername
Left Join (Select Count(tsysAirWatchScanningTarget.ScanningTargetId)
As airwatch,
tsysAirWatchScanningTarget.Servername
From tsysAirWatchScanningTarget
Where tsysAirWatchScanningTarget.Enabled = 1
Group By tsysAirWatchScanningTarget.servername) As airwatch On
airwatch.Servername = tsysASServers.Servername
Left Join (Select Count(tsysAWSScanningRegion.ScanningRegionId) As aws,
tsysAWSScanningRegion.Servername
From tsysAWSScanningRegion
Where tsysAWSScanningRegion.Enabled = 1
Group By tsysAWSScanningRegion.servername) As aws On aws.Servername =
tsysASServers.Servername
Left Join (Select Count(tsysChromeOsScanningTarget.ScanningTargetId)
As chrome,
tsysChromeOsScanningTarget.Servername
From tsysChromeOsScanningTarget
Where tsysChromeOsScanningTarget.Enabled = 1
Group By tsysChromeOsScanningTarget.servername) As chrome On
chrome.Servername = tsysASServers.Servername
Left Join (Select Count(tsysIntuneScanningTarget.Id) As intune,
tsysIntuneScanningTarget.Servername
From tsysIntuneScanningTarget
Where tsysIntuneScanningTarget.Enabled = 1
Group By tsysIntuneScanningTarget.servername) As intune On
intune.Servername = tsysASServers.Servername
Left Join (Select Count(tsysIntuneV2ScanningTarget.ScanningTargetId)
As intune2,
tsysIntuneV2ScanningTarget.Servername
From tsysIntuneV2ScanningTarget
Where tsysIntuneV2ScanningTarget.Enabled = 1
Group By tsysIntuneV2ScanningTarget.servername) As intune2 On
intune2.Servername = tsysASServers.Servername
Left Join (Select Count(tsysO365ScanningTarget.ScheduleId) As o365,
tsysO365ScanningTarget.Servername
From tsysO365ScanningTarget
Where tsysO365ScanningTarget.Enabled = 1
Group By tsysO365ScanningTarget.servername) As o365 On o365.Servername =
tsysASServers.Servername
Left Join (Select Count(tsysO365V2ScanningTarget.ScanningTargetId) As o3652,
tsysO365V2ScanningTarget.Servername
From tsysO365V2ScanningTarget
Where tsysO365V2ScanningTarget.Enabled = 1
Group By tsysO365V2ScanningTarget.servername) As o3652 On
o3652.Servername = tsysASServers.Servername
Left Join (Select Count(tsysAzureAdScanningTarget.ScanningTargetId) As
azuread,
tsysAzureAdScanningTarget.Servername
From tsysAzureAdScanningTarget
Where tsysAzureAdScanningTarget.Enabled = 1
Group By tsysAzureAdScanningTarget.servername) As azuread On
azuread.Servername = tsysASServers.Servername
Group By tsysASServers.Servername,
airwatch.airwatch,
aws.aws,
chrome.chrome,
intune.intune,
intune2.intune2,
o365.o365,
o3652.o3652,
azuread.azuread,
azure.azure,
warrantyscanning.warrantyscanning,
mailserver.mailserver,
EnableAssetRecognition.EnableAssetRecognition,
AutoUpdateType.AutoUpdateType