Feature not installed report

[heybobby1]

Hi,

Can anyone help? I'm working on a report with criteria:

* Is server
* Has folder shares
* Feature 'FSRM Infrastructure' NOT installed

I'm struggling on the feature not installed bit. I think I need something like the example here but I'm new to SQL and can't figure it out: http://www.lansweeper.com/forum/yaf_postst4884_Software-not-installed-report.aspx#post22439

This is my query so far:

Select Top 100000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname,
  Count(tblSharesUni.Type) As TotalFolderShares
From tblAssets
  Inner Join tblFeature On tblFeature.AssetId = tblAssets.AssetID
  Inner Join tblFeatureUni On tblFeatureUni.featUniID = tblFeature.featUniId
  Inner Join tblShares On tblShares.AssetID = tblAssets.AssetID
  Inner Join tblSharesUni On tblShares.ShareUniqueID =
    tblSharesUni.ShareUniqueID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tblComputersystem On tblComputersystem.AssetID = tblAssets.AssetID
Where tblSharesUni.Name Not Like 'print$' And tblSharesUni.Name Not Like
  'CertEnroll' And tblSharesUni.Name Not Like 'prnproc$' And
  tblSharesUni.Name Not Like 'SYSVOL' And tblSharesUni.Name Not Like 'NETLOGON'
  And tblSharesUni.Name Not Like 'VBRCatalog' And tblSharesUni.Type = 0 And
  tblFeatureUni.featureName != 'FSRM-Infrastructure' And
  tblComputersystem.Domainrole > 1 And tblAssetCustom.State = 1
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname,
  tblFeatureUni.featureName,
  tblFeatureUni.featureCaption
Order By tblAssets.AssetName

I've realised featurename not equal to 'FSRM-Infrastructure' doesn't work as it brings back all the other non FSRM features and so still reports servers with FSRM installed.

Hope you can help!

[heybobby1]

I figured it out (using the linked example in OP).

This is my new query:

Select Top 100000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname As OSName,
  tblAssets.Lastseen As LastSeen,
  Count(tblSharesUni.Type) As TotalFileShares
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblShares On tblAssets.AssetID = tblShares.AssetID
  Inner Join tblSharesUni On tblShares.ShareUniqueID =
    tblSharesUni.ShareUniqueID
Where tblAssets.AssetID Not In (Select tblFeature.AssetId
  From tblFeature Inner Join tblFeatureUni On tblFeatureUni.featUniID =
      tblFeature.featUniId
  Where tblFeatureUni.featureName Like 'FSRM-Infrastructure%') And
  tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1 And
  tblSharesUni.Type = 0 And tblSharesUni.Name Not Like 'print$' And
  tblSharesUni.Name Not Like 'prnproc$' And tblSharesUni.Name Not Like 'SYSVOL'
  And tblSharesUni.Name Not Like 'NETLOGON' And tblSharesUni.Name Not Like
  'CertEnroll' And tblSharesUni.Name Not Like 'VBRCatalog'
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Lastseen
Order By tblAssets.AssetName

Changed to like 'FSRM-Infrastructure%' to include 'FSRM-Infrastructure-Core' on Windows Server Core installs.