Select Top 1000000 tblAssets.AssetID,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
subquery1.Average As [Average last 7 days],
subquery2.Average As [Average between 14 and 7 days ago],
Round(Cast(Avg(subquery3.Average) As float),
0) As [Average between 6AM to 6PM last 14 days],
Round(Cast(Avg(subquery4.Average) As float),
0) As [Average between 6PM to 6AM last 14 days],
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.LastPerformanceScan
From tblAssets
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Left Join (Select Top 1000000 a.AssetID,
Min(pcm.Name) As MetricName,
Min(pcm.Unit) As Unit,
Round(Cast(Avg(pcsm.Value) As float), 0) As Average,
Min(pcsm.Value) As MinValue,
Max(pcsm.Value) As MaxValue,
Min(pcsmi.Name) As Identifier
From tblAssets a
Inner Join tblAssetCustom ac On a.AssetID = ac.AssetID
Inner Join tblState On tblState.State = ac.State
Left Join tblLinuxSystem ls On ls.AssetID = a.AssetID
Inner Join tblPerformanceCountersScan pcs On pcs.AssetId = a.AssetID
Inner Join tblPerformanceCountersScanMetric pcsm On
pcsm.PerformanceCountersScanId = pcs.Id
Inner Join tsysPerformanceCounterMetric pcm On pcm.Id = pcsm.Metric
Left Join tblPerformanceCountersScanMetricIdentifier pcsmi On
pcsmi.Id = pcsm.PerformanceCountersScanMetricIdentifierId
Where tblState.Statename = 'Active' And pcs.ScanDateTime > GetDate() - 7 And
pcm.Name Like '%CPU%'
Group By a.AssetID,
pcm.Id,
pcsmi.Id) As subquery1 On subquery1.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 a.AssetID,
Min(pcm.Name) As MetricName,
Min(pcm.Unit) As Unit,
Round(Cast(Avg(pcsm.Value) As float), 0) As Average,
Min(pcsm.Value) As MinValue,
Max(pcsm.Value) As MaxValue,
Min(pcsmi.Name) As Identifier
From tblAssets a
Inner Join tblAssetCustom ac On a.AssetID = ac.AssetID
Inner Join tblState On tblState.State = ac.State
Left Join tblLinuxSystem ls On ls.AssetID = a.AssetID
Inner Join tblPerformanceCountersScan pcs On pcs.AssetId = a.AssetID
Inner Join tblPerformanceCountersScanMetric pcsm On
pcsm.PerformanceCountersScanId = pcs.Id
Inner Join tsysPerformanceCounterMetric pcm On pcm.Id = pcsm.Metric
Left Join tblPerformanceCountersScanMetricIdentifier pcsmi On
pcsmi.Id = pcsm.PerformanceCountersScanMetricIdentifierId
Where tblState.Statename = 'Active' And pcs.ScanDateTime Between GetDate() -
14 And GetDate() - 7 And pcm.Name Like '%CPU%'
Group By a.AssetID,
pcm.Id,
pcsmi.Id) As subquery2 On subquery2.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 a.AssetID,
Min(pcm.Name) As MetricName,
Min(pcm.Unit) As Unit,
Round(Cast(Avg(pcsm.Value) As float), 0) As Average
From tblAssets a
Inner Join tblAssetCustom ac On a.AssetID = ac.AssetID
Inner Join tblState On tblState.State = ac.State
Left Join tblLinuxSystem ls On ls.AssetID = a.AssetID
Inner Join tblPerformanceCountersScan pcs On pcs.AssetId = a.AssetID
Inner Join tblPerformanceCountersScanMetric pcsm On
pcsm.PerformanceCountersScanId = pcs.Id
Inner Join tsysPerformanceCounterMetric pcm On pcm.Id = pcsm.Metric
Left Join tblPerformanceCountersScanMetricIdentifier pcsmi On
pcsmi.Id = pcsm.PerformanceCountersScanMetricIdentifierId
Where pcs.ScanDateTime > GetDate() - 14 And tblState.Statename = 'Active'
And pcm.Name Like '%CPU%' And DatePart(HOUR, pcs.ScanDateTime) Between 6
And 18
Group By a.AssetID,
pcm.Id,
pcsmi.Id,
pcs.ScanDateTime) As subquery3 On subquery3.AssetID = tblAssets.AssetID
Left Join (Select Top 1000000 a.AssetID,
Min(pcm.Name) As MetricName,
Min(pcm.Unit) As Unit,
Round(Cast(Avg(pcsm.Value) As float), 0) As Average,
DatePart(HOUR, pcs.ScanDateTime) As time
From tblAssets a
Inner Join tblAssetCustom ac On a.AssetID = ac.AssetID
Inner Join tblState On tblState.State = ac.State
Left Join tblLinuxSystem ls On ls.AssetID = a.AssetID
Inner Join tblPerformanceCountersScan pcs On pcs.AssetId = a.AssetID
Inner Join tblPerformanceCountersScanMetric pcsm On
pcsm.PerformanceCountersScanId = pcs.Id
Inner Join tsysPerformanceCounterMetric pcm On pcm.Id = pcsm.Metric
Left Join tblPerformanceCountersScanMetricIdentifier pcsmi On
pcsmi.Id = pcsm.PerformanceCountersScanMetricIdentifierId
Where (DatePart(HOUR, pcs.ScanDateTime) Between 17 And 24 And
pcs.ScanDateTime > GetDate() - 14 And tblState.Statename = 'Active' And
pcm.Name Like '%CPU%') Or
(DatePart(HOUR, pcs.ScanDateTime) Between 0 And 6 And
pcm.Name Like '%CPU%')
Group By a.AssetID,
pcm.Id,
pcsmi.Id,
pcs.ScanDateTime) As subquery4 On subquery4.AssetID = tblAssets.AssetID
Where subquery1.Average Is Not Null
Group By tblAssets.AssetID,
subquery1.Average,
subquery2.Average,
tsysAssetTypes.AssetTypeIcon10,
tsysOS.Image,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname,
tblErrors.ErrorText,
tsysasseterrortypes.ErrorMsg,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.LastPerformanceScan