Select Top 1000000 tsysASServers.Servername,
IsNull(iprange.iprange, 0) + IsNull(assetgroup.assetgroup, 0) +
IsNull(domain.domain, 0) + IsNull(azure.azure, 0) + IsNull(adsi.adsi, 0) +
IsNull(workgroup.workgroup, 0) + IsNull(logschedule.logschedule, 0) +
IsNull(userschedule.userschedule, 0) + IsNull(airwatch.airwatch, 0) +
IsNull(aws.aws, 0) + IsNull(chrome.chrome, 0) + IsNull(intune.intune, 0) +
IsNull(intune2.intune2, 0) + IsNull(o365.o365, 0) + IsNull(o3652.o3652, 0) +
IsNull(azuread.azuread, 0) + IsNull(sccm.sccm, 0) As [Scanning targets]
From tsysASServers
Left Join (Select Count(tsysIPScanRanges.IprangeID) As iprange,
tsysIPScanRanges.Servername
From tsysIPScanRanges
Group By tsysIPScanRanges.servername) As iprange On iprange.Servername =
tsysASServers.Servername
Left Join (Select Count(tsysAssetGroupScan.AssetGroupScanID) As assetgroup,
tsysAssetGroupScan.Servername
From tsysAssetGroupScan
Group By tsysAssetGroupScan.servername) As assetgroup On
assetgroup.Servername = tsysASServers.Servername
Left Join (Select Count(tsysASDomains.AsDomainId) As domain,
tsysASDomains.Servername
From tsysASDomains
Group By tsysASDomains.servername) As domain On domain.Servername =
tsysASServers.Servername
Left Join (Select Count(tsysAzureScanningTarget.ScheduleId) As azure,
tsysAzureScanningTarget.Servername
From tsysAzureScanningTarget
Group By tsysAzureScanningTarget.servername) As azure On
azure.Servername = tsysASServers.Servername
Left Join (Select Count(tsysAdsischedule.ScheduleId) As adsi,
tsysAdsischedule.Servername
From tsysAdsischedule
Group By tsysAdsischedule.servername) As adsi On adsi.Servername =
tsysASServers.Servername
Left Join (Select Count(tsysASWorkgroups.ASWorkgroupsId) As workgroup,
tsysASWorkgroups.Servername
From tsysASWorkgroups
Group By tsysASWorkgroups.servername) As workgroup On workgroup.Servername =
tsysASServers.Servername
Left Join (Select Count(tsyslogschedule.ScheduleID) As logschedule,
tsyslogschedule.Servername
From tsyslogschedule
Group By tsyslogschedule.servername) As logschedule On
logschedule.Servername = tsysASServers.Servername
Left Join (Select Count(tsysUserSchedule.ScheduleID) As userschedule,
tsysUserSchedule.Servername
From tsysUserSchedule
Group By tsysUserSchedule.servername) As userschedule On
userschedule.Servername = tsysASServers.Servername
Left Join (Select Count(tsysAirWatchScanningTarget.ScanningTargetId)
As airwatch,
tsysAirWatchScanningTarget.Servername
From tsysAirWatchScanningTarget
Group By tsysAirWatchScanningTarget.servername) As airwatch On
airwatch.Servername = tsysASServers.Servername
Left Join (Select Count(tsysAWSScanningRegion.ScanningRegionId) As aws,
tsysAWSScanningRegion.Servername
From tsysAWSScanningRegion
Group By tsysAWSScanningRegion.servername) As aws On aws.Servername =
tsysASServers.Servername
Left Join (Select Count(tsysChromeOsScanningTarget.ScanningTargetId)
As chrome,
tsysChromeOsScanningTarget.Servername
From tsysChromeOsScanningTarget
Group By tsysChromeOsScanningTarget.servername) As chrome On
chrome.Servername = tsysASServers.Servername
Left Join (Select Count(tsysIntuneScanningTarget.Id) As intune,
tsysIntuneScanningTarget.Servername
From tsysIntuneScanningTarget
Group By tsysIntuneScanningTarget.servername) As intune On
intune.Servername = tsysASServers.Servername
Left Join (Select Count(tsysIntuneV2ScanningTarget.ScanningTargetId)
As intune2,
tsysIntuneV2ScanningTarget.Servername
From tsysIntuneV2ScanningTarget
Group By tsysIntuneV2ScanningTarget.servername) As intune2 On
intune2.Servername = tsysASServers.Servername
Left Join (Select Count(tsysO365ScanningTarget.ScheduleId) As o365,
tsysO365ScanningTarget.Servername
From tsysO365ScanningTarget
Group By tsysO365ScanningTarget.servername) As o365 On o365.Servername =
tsysASServers.Servername
Left Join (Select Count(tsysSccmScanningTarget.ScheduleId) As sccm,
tsysSccmScanningTarget.Servername
From tsysSccmScanningTarget
Group By tsysSccmScanningTarget.servername) As sccm On sccm.Servername =
tsysASServers.Servername
Left Join (Select Count(tsysO365V2ScanningTarget.ScanningTargetId) As o3652,
tsysO365V2ScanningTarget.Servername
From tsysO365V2ScanningTarget
Group By tsysO365V2ScanningTarget.servername) As o3652 On
o3652.Servername = tsysASServers.Servername
Left Join (Select Count(tsysAzureAdScanningTarget.ScanningTargetId) As
azuread,
tsysAzureAdScanningTarget.Servername
From tsysAzureAdScanningTarget
Group By tsysAzureAdScanningTarget.servername) As azuread On
azuread.Servername = tsysASServers.Servername
Group By tsysASServers.Servername,
iprange.iprange,
assetgroup.assetgroup,
domain.domain,
azure.azure,
adsi.adsi,
workgroup.workgroup,
logschedule.logschedule,
userschedule.userschedule,
airwatch.airwatch,
aws.aws,
chrome.chrome,
intune.intune,
intune2.intune2,
o365.o365,
sccm.sccm,
o3652.o3652,
azuread.azuread