Query inside of another query?

[Automatic0953]

I am looking to modify the report from "Mister_Nobody" in this thread "https://community.lansweeper.com/t5/reports-analytics/smb-v1-report-that-has-it-enabled/m-p/67585/hi...".

It does a good job of finding systems with the various SMBv1 features installed but includes a lot of PC's that technically have it disabled. There are four possible features and the PCs will be listed multiple times for each of these they have installed which is not ideal. This would be fine normally except for one of the four disables the other 3 even if they are installed.

The features are:

SMB 1.0/CIFS Automatic Removal SMB1Protocol-Deprecation
SMB 1.0/CIFS Client SMB1Protocol-Client
SMB 1.0/CIFS File Sharing Support SMB1Protocol
SMB 1.0/CIFS Server SMB1Protocol-Server

There are quite a lot of PC's with "SMB 1.0/CIFS Automatic Removal SMB1Protocol-Deprecation" installed which disables the other three. How can modify the report to find all PC's that do not have "SMB1Protocol-Deprecation" installed and then search that list for the other three?

Here is the report from "Mister_Nobody":

Select Top (1000000) tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen As [Last successful scan],
  tblAssets.Lasttried As [Last scan attempt],
  tblFeatureUni.featureName,
  tblFeatureUni.featureCaption,
  tblFeatureUni.addedDate As DateAdded,
  tblFeature.Lastchanged
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblFeature On tblAssets.AssetID = tblFeature.AssetId
  Inner Join tblFeatureUni On tblFeatureUni.featUniID = tblFeature.featUniId
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblFeatureUni.featureName Like 'SMB1protocol%' And tblState.Statename =
  'Active'
Order By tblAssets.Domain,
  tblAssets.AssetName,
  tblFeatureUni.featureCaption

[KevinA-REJIS · Accepted solution]

Give this a try:

Select Top (1000000) tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen As [Last successful scan],
  tblAssets.Lasttried As [Last scan attempt],
  tblFeatureUni.featureName,
  tblFeatureUni.featureCaption,
  tblFeatureUni.addedDate As DateAdded,
  tblFeature.Lastchanged
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblFeature On tblAssets.AssetID = tblFeature.AssetId
  Inner Join tblFeatureUni On tblFeatureUni.featUniID = tblFeature.featUniId
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssets.AssetID Not In (Select tblAssets.AssetID
    From tblAssets Inner Join tblFeature On tblAssets.AssetID =
          tblFeature.AssetID Inner Join tblFeatureUni On
          tblFeatureUni.featUniID = tblFeature.featUniID
    Where tblFeatureUni.featureName Like 'SMB1Protocol-Deprecation%') And
  tblFeatureUni.featureName Like 'SMB1protocol%' And tblState.Statename =
  'Active'
Order By tblAssets.Domain,
  tblAssets.AssetName,
  tblFeatureUni.featureCaption

[Automatic0953]

Looks great, thank you!