Report to find ScanServer 'not working'

[vrocher]

Hello,
I have created a reports to find ScanServer that 'stop' scanning because of WMI broken on windows assets.

The reports show the name of ScanServer where processing is 0 and In Queue more than 200.

I use it with Scheduled task to reboot the 'guilty', i know that is not THE solution, but i couln't repair broken WMI at night.

Vincent

Select Top 10000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysCurrentScans.ScanType,
  Count(tsysCurrentScans.ScanType) As Num
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tsysASServers On tsysASServers.Servername = tblAssets.AssetName
  Inner Join tsysCurrentScans On tsysASServers.Servername =
    tsysCurrentScans.Scanserver
Where tsysCurrentScans.ScanType = 1 And tsysASServers.Servicelastpolled >
  GetDate() - 7 And tblAssetCustom.State = 1 And Not Exists(Select Top
        10000 tsysOS.Image As icon,
        tblAssets.AssetID,
        tblAssets.AssetName,
        tsysCurrentScans.ScanType
      From tblAssets Inner Join tblAssetCustom On tblAssets.AssetID =
          tblAssetCustom.AssetID Inner Join tsysOS On tsysOS.OScode =
          tblAssets.OScode Inner Join tsysASServers On
          tsysASServers.Servername = tblAssets.AssetName Inner Join
        tsysCurrentScans On tsysASServers.Servername =
          tsysCurrentScans.Scanserver
      Where tsysCurrentScans.ScanType = 2 And tsysASServers.Servicelastpolled >
        GetDate() - 7 And tblAssetCustom.State = 1)
Group By tsysOS.Image,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysCurrentScans.ScanType
Having Count(tsysCurrentScans.ScanType) > 199