Notification

Icon
Error

Top 5 CPU utilization

Posted: Tuesday, April 23, 2019 10:17:49 AM(UTC)
Esben.D

Esben.D

Member Administration Original PosterPosts: 1,738
0
Like
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.

Code:
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
no1welshboyo
#1no1welshboyo Member Posts: 2  
posted: 4/24/2019 10:04:47 AM(UTC)
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
Esben.D
#2Esben.D Member Administration Original PosterPosts: 1,738  
posted: 4/24/2019 10:43:04 AM(UTC)
It was a typo at the end of the report. I've updated the original.
no1welshboyo
#3no1welshboyo Member Posts: 2  
posted: 4/24/2019 10:52:35 AM(UTC)
Thats great that works now thank you!

Active Discussions

Lansweeper non-active computer report
by  Apaulcolypse   Go to last post Go to first unread
Last post: Yesterday at 5:16:21 PM(UTC)
Lansweeper Report for showing CPUs below or above
by  GBA Craig  
Go to last post Go to first unread
Last post: 7/18/2019 3:38:41 PM(UTC)
Lansweeper Hardware list
by  GBA Craig   Go to last post Go to first unread
Last post: 7/18/2019 3:34:43 PM(UTC)
Lansweeper Top 10 users submitting tickets
by  LGuth  
Go to last post Go to first unread
Last post: 7/17/2019 9:29:44 PM(UTC)
Lansweeper Microsoft Patch Tuesday Report - July 2019
by  Noobmode   Go to last post Go to first unread
Last post: 7/17/2019 8:48:08 PM(UTC)
Lansweeper Custom Scanning - file not existing
by  Bruce Garoutte  
Go to last post Go to first unread
Last post: 7/17/2019 8:19:50 PM(UTC)
Lansweeper MouseJack vulnerability affected devices report
by  Viper   Go to last post Go to first unread
Last post: 7/16/2019 5:19:14 PM(UTC)
Lansweeper TPM support
by  Sander Eerdekens  
Go to last post Go to first unread
Last post: 7/16/2019 1:38:17 PM(UTC)