CPU/Memory report

[milos_r]

Hello all,

can someone help me create report with this info (14 days period):

• AssetName {where Asset Type is Windows or Linux and OS is Win2002R2, Win2012, Win2019, Win2022, any Linux distro and asset scan issues is 0)
• IPAddress
• OSname
• Avarage CPU usage(%)
• Maximum CPU usage(%)
• Avarage RAM usage(%)
• Maximum RAM usage(%)

[rader]

I believe I got this from an old forum post some years ago, and while mine is listed as Average CPU Utilization - Servers, I think you can modify it as a starting point for your report. If I remember correctly you also have to have performance scanning on too. It's under Scanning, Performance Scanning Targets, then add your targets here.

I haven't used it in some time because it will fill the database quicker than normal, so keep an eye on that.

Good luck.

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