Request ccmexec service not running OR Error: WMI access denied

[workbot]

I've built the report below to search for Windows desktops and servers scanned in the last 7 days that were not running the "ccmexec" service. This works very well, but I'd also like to include any systems that did not allow our global credentials to scan.

There's a built-in report called "Error: WMI access denied", which provides exactly this information. I'm certainly willing to learn a new trick here, but I haven't enough SQL experience to figure this out on my own. How can I successfully combine the two into a single report?

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tsysIPLocations.IPLocation,
  tblAssets.AssetName,
  tblADusers.Displayname,
  tblAssets.IPAddress,
  tsysOS.OSname,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Where tblAssets.AssetID Not In (Select tblServices.AssetID
  From tblServices Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
      tblServices.ServiceuniqueID
  Where tblServicesUni.Name = 'CcmExec') And tblAssets.Lastseen > GetDate() - 7
  And tblAssetCustom.State = 1
Order By tsysIPLocations.IPLocation,
  tblAssets.AssetName

[workbot]

Brilliant! It's working very well, and I'm learning some new tricks from your example.

I really appreciate your help. Thank you very much.

[MikeMc]

This reports combines your query and the built-in WMI access denied query with a few tweaks using a union statement.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tsysIPLocations.IPLocation,
  tblAssets.AssetName,
  tblADusers.Displayname,
  tblAssets.IPAddress,
  tsysOS.OSname,
  tblAssets.Lastseen,
  'Success' As Result
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Where tblAssets.AssetID Not In (Select tblServices.AssetID
  From tblServices Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
      tblServices.ServiceuniqueID
  Where tblServicesUni.Name = 'CcmExec') And tblAssets.Lastseen > GetDate() - 7
  And tblAssetCustom.State = 1
Union All
Select Top 1000000 (Case When tsysOS.Image Is Null Then 'notscanned.png'
    Else tsysOS.Image End) As icon,
  tblErrors.AssetID,
  tsysIPLocations.IPLocation,
  tblAssets.AssetName,
  (Null) As Displayname,
  tblAssets.IPAddress,
  tsysOS.OSname,
  tblAssets.Lastseen,
  (tsysasseterrortypes.ErrorMsg + ' - ' + tblErrors.ErrorText) As Result
From tblErrors
  Inner Join tblAssets On tblErrors.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysasseterrortypes On tblErrors.ErrorType =
    tsysasseterrortypes.Errortype
  Left Outer Join tblOperatingsystem On tblAssets.AssetID =
    tblOperatingsystem.AssetID
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssetCustom.State = 1 And tblOperatingsystem.AssetID Is Null And
  tblErrors.ErrorType = 1
Order By IPLocation,
  AssetName