How to exclude optical drives in Performance Monitoring

[mike_stewart]

Thanks to Lansweeper's community for being so responsive! Looking for help with the Performance Monitoring report that shows disks running out of space. It's currently showing optical drives and sending reports with the misleading information. Is there a way to exclude optical drives from the report Performance: Over 95% disk space used last day

I've tried looking at the query to adjust for drive types, but I don't see where that might be possible. It's also pulling from these tsysPerformanceCounter tables that don't show in the Database Documentation? Not sure where to get the schema for those tables. 

Thanks in advance 

[mike_stewart]

Thanks for checking this out. 

[ErikT · Accepted solution]

@mike_stewart 

My apologies for the late reply. The sample report should give you the results you are looking for.  

Select Top (1000000) a.AssetID,
  Min(a.AssetName) As AssetName,
  Min(a.Domain) As Domain,
  Min(Coalesce(os.Image, [at].AssetTypeIcon10)) As icon,
  Min(a.IPAddress) As IPAddress,
  Min(ipl.IPLocation) As IPLocation,
  Min(ac.Manufacturer) As Manufacturer,
  Min(ac.Model) As Model,
  Case Min(a.Assettype)
    When -1 Then Min(os.OSname)
    When 11 Then Min(ls.OSRelease)
    Else ''
  End As OsName,
  Min(a.Lastseen) As [Last successful scan],
  Min(a.Lasttried) As [Last scan attempt],
  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,
  tblDiskdrives.DriveType,
  tblLinuxOpticalDrives.AssetID As AssetID1
From tblAssets As a
  Inner Join tblAssetCustom As ac On a.AssetID = ac.AssetID
  Inner Join tsysAssetTypes As [at] On [at].AssetType = a.Assettype
  Inner Join tsysIPLocations As ipl On ipl.LocationID = a.LocationID
  Inner Join tblState On tblState.State = ac.State
  Left Outer Join tsysOS As os On os.OScode = a.OScode
  Left Outer Join tblLinuxSystem As ls On ls.AssetID = a.AssetID
  Inner Join tblPerformanceCountersScan As pcs On pcs.AssetId = a.AssetID
  Inner Join tblPerformanceCountersScanMetric As pcsm On
      pcsm.PerformanceCountersScanId = pcs.Id
  Inner Join tsysPerformanceCounterMetric As pcm On pcm.Id = pcsm.Metric
  Left Outer Join tblPerformanceCountersScanMetricIdentifier As pcsmi On
      pcsmi.Id = pcsm.PerformanceCountersScanMetricIdentifierId
  Inner Join tblDiskdrives On a.AssetID = tblDiskdrives.AssetID
  Inner Join tblLinuxOpticalDrives On a.AssetID = tblLinuxOpticalDrives.AssetID
Where tblDiskdrives.DriveType <> 5 And tblLinuxOpticalDrives.AssetID Not In
  (Select tbllinuxOpticaldrives.assetid
    From tbllinuxOpticaldrives) And pcm.Id = 128 And tblState.Statename =
  'Active' And pcs.ScanDateTime > GetDate() - 1
Group By a.AssetID,
  tblDiskdrives.DriveType,
  pcm.Id,
  pcsmi.Id,
  tblLinuxOpticalDrives.AssetID
Having Avg(pcsm.Value) > 95
Order By AssetName,
  MetricName