Bitlocker and McAfee Encryption report

[MPitts]

In our environment we have both BitLocker and McAfee Encryption for our end user computing devices.  When the report Computer: Encryptable Volumes is run, it does not give the full picture but only for BitLockered machines.  I have modified the report in SQL to give the results and report I would like, to include the registry value of which drive (s) are McAfee Encrypted; however, the report writer does not seem to appreciate the temp tables that are created.  I'm hoping someone here has a better solution on what it is I am trying to accomplish.  Here is the code that does work in SQL but not within LanSweeper.  Any assistance would be appreciated.  Thank you.

Select Top (1000000) tsysAssetTypes.AssetTypeIcon10 As icon,

  tblAssets.AssetID,

  tblAssets.AssetName,

  tblEncryptableVolume.DriveLetter,

  Case

    When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'

    When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'

    Else 'UNKNOWN'

  End As ProtectionStatus,

  tblEncryptableVolume.LastChanged,

  tblAssets.Domain,

  tblAssets.Username,

  tblAssets.Userdomain,

  tblAssets.IPAddress,

  tblAssets.Description,

  tblAssetCustom.Manufacturer,

  tblAssetCustom.Model,

  tblAssetCustom.Location,

  tsysIPLocations.IPLocation,

  tsysOS.OSname As OS,

  tblAssets.SP,

  tblAssets.Firstseen As [Created at],

  tblAssets.Lastseen As [Last successful scan],

  tblRegistry.Value As CryptState,

  tblRegistry.Valuename 

  INTO #tmpEncryptiontbl

From tblEncryptableVolume

  Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID

  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType

  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID

  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode

  Left Outer Join tsysIPLocations On tblAssets.LocationID =

      tsysIPLocations.LocationID

  Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID

Where tblRegistry.Valuename = 'CryptState'

Order By tblAssets.AssetName

Select Top (1000000) tsysAssetTypes.AssetTypeIcon10 As icon,

  tblAssets.AssetID,

  tblAssets.AssetName,

  tblEncryptableVolume.DriveLetter,

  Case

    When tblEncryptableVolume.ProtectionStatus = 0 Then 'OFF'

    When tblEncryptableVolume.ProtectionStatus = 1 Then 'ON'

     Else 'UNKNOWN'

  End As ProtectionStatus,

  tblEncryptableVolume.LastChanged,

  tblAssets.Domain,

  tblAssets.Username,

  tblAssets.Userdomain,

  tblAssets.IPAddress,

  tblAssets.Description,

  tblAssetCustom.Manufacturer,

  tblAssetCustom.Model,

  tblAssetCustom.Location,

  tsysIPLocations.IPLocation,

  tsysOS.OSname As OS,

  tblAssets.SP,

  tblAssets.Firstseen As [Created at],

  tblAssets.Lastseen As [Last successful scan],

  tblRegistry.Value,

  tblRegistry.Valuename 

INTO #tmpEncrypttbl2

From tblEncryptableVolume

  Inner Join tblAssets On tblEncryptableVolume.AssetId = tblAssets.AssetID

  Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType

  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID

  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode

  Left Outer Join tsysIPLocations On tblAssets.LocationID =

      tsysIPLocations.LocationID

  Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID

Order By tblAssets.AssetName

DELETE tmp2 

FROM #tmpEncrypttbl2 tmp2

INNER JOIN #tmpEncryptiontbl tmp1

ON tmp1.AssetID = tmp2.AssetID

AND tmp1.DriveLetter = tmp2.DriveLetter

AND tmp1.Valuename <> tmp2.Valuename

UPDATE #tmpEncrypttbl2

SET Value = NULL, ValueName = NULL

WHERE Valuename <> 'CryptState'

SELECT * FROM #tmpEncrypttbl2 ORDER BY AssetID

DROP TABLE #tmpEncryptiontbl

DROP TABLE #tmpEncrypttbl2

[ErikT]

@MPitts 

This is advanced SQL scripting and can indeed not be done in the built-in report builder. This code, unfortunately, can only be run outside of Lansweeper.