Select assets where a particular "process" is running

[dipeshn]

Hi all I have the following Code but its retuning all results as "Yes" , which is not the case

(Select (Case
When Exists(Select Top 1 tblAssets.AssetName From tblAssets
Where Exists(Select tblProcesses.AssetID From tblProcesses
Where tblProcesses.AssetID = tblAssets.AssetID And
tblProcesses.Caption = 'SenseCE.exe')) Then 'Yes'
Else 'No'
End)) As MMDE_Installed,

Looking for assistance on how i can modify the search to ensure it reflects the results correctly

Regards

Dipesh

[dipeshn]

Hi RKCar, Thank you so much for the feedback.

I am hoping to list all assets, but then tag those assets that have the process with a 'Yes' value and those devices that are not running it with a 'No' Value.

I actually need the entire asset base in the report and not just those devices that have the process running.

Hope that clarifies further.

Thanks once again.

Regards

[grimstar]

dipeshn wrote:
Hi all I have the following Code but its retuning all results as "Yes" , which is not the case

(Select (Case
When Exists(Select Top 1 tblAssets.AssetName From tblAssets
Where Exists(Select tblProcesses.AssetID From tblProcesses
Where tblProcesses.AssetID = tblAssets.AssetID And
tblProcesses.Caption = 'SenseCE.exe')) Then 'Yes'
Else 'No'
End)) As MMDE_Installed,

Looking for assistance on how i can modify the search to ensure it reflects the results correctly

Regards

Dipesh

Is the case statement necessary? I just created a new report, added tblProcesses, made it distinct, and added a caption filter like you did. It listed out all devices running the process I defined.

Select Distinct Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tsysassettypes.AssetTypename,
  tsysassettypes.AssetTypeIcon10 As icon,
  tblassets.IPAddress,
  tblassets.Lastseen,
  tblassets.Lasttried,
  tblProcesses.Caption
From tblassets
  Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
  Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
  Inner Join lansweeperdb.dbo.tblProcesses On tblassets.AssetID =
      tblProcesses.AssetID
Where tblProcesses.Caption = 'iexplore.exe' And tblassetcustom.State = 1