→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
workbot
Engaged Sweeper
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
2 REPLIES 2
workbot
Engaged Sweeper
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
Champion Sweeper II
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