cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Esben_D
Lansweeper Employee
Lansweeper Employee
This report is also highlighted in this blog post.

The report gives a top 5 of assets which have the highest average CPU usage as scanned by the performance counters. Please note that performance counter scanning has to be manually enabled. Additionally, this feature is only available in Lansweeper 7.1 or higher.

Select Top 5 tblAssets.AssetID,
tblAssets.AssetName,
Cast(Avg(tblPerformanceCountersScanMetric.Value) As int) As
[Average CPU usage in %],
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
Max(tblPerformanceCountersScan.ScanDateTime) As [Last scan]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblPerformanceCountersScan On tblAssets.AssetID =
tblPerformanceCountersScan.AssetId
Inner Join tblPerformanceCountersScanMetric On tblPerformanceCountersScan.Id =
tblPerformanceCountersScanMetric.PerformanceCountersScanId
Left Join tblPerformanceCountersScanMetricIdentifier On
tblPerformanceCountersScanMetricIdentifier.Id =
tblPerformanceCountersScanMetric.PerformanceCountersScanMetricIdentifierId
Inner Join tsysPerformanceCounterMetric On tsysPerformanceCounterMetric.Id =
tblPerformanceCountersScanMetric.Metric
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tsysPerformanceCounterMetric.[Group] = 'CPU' And tblAssetCustom.State = 1
Group By tblAssets.AssetID,
tblAssets.AssetName,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10)
Having Max(tblPerformanceCountersScan.ScanDateTime) > GetDate() - 7
Order By [Average CPU usage in %] Desc
3 REPLIES 3
dafydd_powell
Engaged Sweeper
Thats great that works now thank you!
Esben_D
Lansweeper Employee
Lansweeper Employee
It was a typo at the end of the report. I've updated the original.
dafydd_powell
Engaged Sweeper
Hi,

Whenever I try and create this report I get the following error message

"In aggregate and grouping expressions, the ORDER BY clause can contain only aggregate functions and grouping expressions"

Any ideas what is wrong?

Many thanks