Symantec Management Platform (Notification Server)

Hi there. After rolling out Bitlocker to our machines, I am looking for any Laptops (LT%) that have not began encryption. I have a report that works well. However, I would like to add the users full name and email address rather than just their username. 

I use report builder 7.5 for any custom reports as my SQL is not the greatest. Here is the resolved query from the report. 

Any help would be great. 

DECLARE @v1_TrusteeScope nvarchar(350)
   SET @v1_TrusteeScope = N'{2E1F478A-4986-4223-9D1E-B5920A63AB41},{3621F153-EAC9-4627-B096-4449817F8787},{3D64F831-5C96-47AB-AF24-CD3F055CD872},{41F65AC1-F83D-4464-A6D5-31144BCAA0E4},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{7680CEF7-8518-44DE-AAC5-130D4702BF17},{9EC2A974-6EB3-4104-B2B7-1DC44FC5F19B},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F},{EF2A1637-9D7B-4543-B220-7244C34A0C32}'
SELECT
   [vri2_Computer].[Guid] AS [_ItemGuid],
   [vri2_Computer].[Name],
   [dca5_Expanded Bitlocker Status].[ConversionStatus],
   [dca5_Expanded Bitlocker Status].[DriveLetter],
   [dca3_AeX AC TCPIP].[IP Address],
   [dca6_Manufacturer].[Model],
   [dca5_Expanded Bitlocker Status].[EncryptionPercentage],
   [dca5_Expanded Bitlocker Status].[ProtectionStatus],
   [dca4_AeX AC Primary User].[User]
FROM
   [vRM_Computer_Item] AS [vri2_Computer]
      LEFT OUTER JOIN [Inv_AeX_AC_TCPIP] AS [dca3_AeX AC TCPIP]
         ON ([vri2_Computer].[Guid] = [dca3_AeX AC TCPIP].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_AeX_AC_Primary_User] AS [dca4_AeX AC Primary User]
         ON ([vri2_Computer].[Guid] = [dca4_AeX AC Primary User].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_Expanded_Bitlocker_Status] AS [dca5_Expanded Bitlocker Status]
         ON ([vri2_Computer].[Guid] = [dca5_Expanded Bitlocker Status].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_Manufacturer] AS [dca6_Manufacturer]
         ON ([vri2_Computer].[Guid] = [dca6_Manufacturer].[_ResourceGuid])
WHERE
   (
      (
         ([dca5_Expanded Bitlocker Status].[EncryptionPercentage] <> N'%NULL%')
         AND
         ([vri2_Computer].[Name] LIKE N'%LT%')
         AND
         ([dca5_Expanded Bitlocker Status].[ProtectionStatus] LIKE N'%off%')
         AND
         ([dca5_Expanded Bitlocker Status].[DriveLetter] LIKE N'%C:%')
      )
      AND
      ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
   )
ORDER BY
   [dca6_Manufacturer].[Model],
   [dca3_AeX AC TCPIP].[IP Address],
   [vri2_Computer].[Name]

If you are still looking for a solution, try adding (without the quotes):

1. "[vu].[Display Name]" to the bottom of the SELECT area.
2. "LEFT OUTER JOIN vUser [vu]
            ON ([dca4_AeX AC Primary User].[_ResourceGuid] = [vu].[Guid])" to the bottom of the FROM area.

Here is the modified query.  It's untested (we do not use bitlocker) but should work.

DECLARE @v1_TrusteeScope nvarchar(350)
   SET @v1_TrusteeScope = N'{2E1F478A-4986-4223-9D1E-B5920A63AB41},{3621F153-EAC9-4627-B096-4449817F8787},{3D64F831-5C96-47AB-AF24-CD3F055CD872},{41F65AC1-F83D-4464-A6D5-31144BCAA0E4},{582029E2-FC5B-4717-8808-B80D6EF0FD67},{7680CEF7-8518-44DE-AAC5-130D4702BF17},{9EC2A974-6EB3-4104-B2B7-1DC44FC5F19B},{B760E9A9-E4DB-404C-A93F-AEA51754AA4F},{EF2A1637-9D7B-4543-B220-7244C34A0C32}'
SELECT
   [vri2_Computer].[Guid] AS [_ItemGuid],
   [vri2_Computer].[Name],
   [dca5_Expanded Bitlocker Status].[ConversionStatus],
   [dca5_Expanded Bitlocker Status].[DriveLetter],
   [dca3_AeX AC TCPIP].[IP Address],
   [dca6_Manufacturer].[Model],
   [dca5_Expanded Bitlocker Status].[EncryptionPercentage],
   [dca5_Expanded Bitlocker Status].[ProtectionStatus],
   [dca4_AeX AC Primary User].[User],
   [vu].[Display Name]
FROM
   [vRM_Computer_Item] AS [vri2_Computer]
      LEFT OUTER JOIN [Inv_AeX_AC_TCPIP] AS [dca3_AeX AC TCPIP]
         ON ([vri2_Computer].[Guid] = [dca3_AeX AC TCPIP].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_AeX_AC_Primary_User] AS [dca4_AeX AC Primary User]
         ON ([vri2_Computer].[Guid] = [dca4_AeX AC Primary User].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_Expanded_Bitlocker_Status] AS [dca5_Expanded Bitlocker Status]
         ON ([vri2_Computer].[Guid] = [dca5_Expanded Bitlocker Status].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_Manufacturer] AS [dca6_Manufacturer]
         ON ([vri2_Computer].[Guid] = [dca6_Manufacturer].[_ResourceGuid])
      LEFT OUTER JOIN vUser [vu]
         ON ([dca4_AeX AC Primary User].[_ResourceGuid] = [vu].[Guid])
WHERE
   (
      (
         ([dca5_Expanded Bitlocker Status].[EncryptionPercentage] <> N'%NULL%')
         AND
         ([vri2_Computer].[Name] LIKE N'%LT%')
         AND
         ([dca5_Expanded Bitlocker Status].[ProtectionStatus] LIKE N'%off%')
         AND
         ([dca5_Expanded Bitlocker Status].[DriveLetter] LIKE N'%C:%')
      )
      AND
      ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
   )
ORDER BY
   [dca6_Manufacturer].[Model],
   [dca3_AeX AC TCPIP].[IP Address],
   [vri2_Computer].[Name]

Excellent Report! I've ran it in my environment, but it still returns 'NULL' for Display Name. Is vUser the correct table? I've also tried using Inv_Global_User_General_Details and it still returns 'NULL'.

Many thanks for taking the time to try it. I did manage to get it eventaully and I think it was on the same lines. Here is my own that seemed to work in my enviorment. 

I should say I modified this to look for laptops (LT%) and only C drives. 

DECLARE @v1_TrusteeScope nvarchar(max)
   SET @v1_TrusteeScope = N'%TrusteeScope%'
SELECT
   [vri2_Computer].[Guid] AS [_ItemGuid],
   [vri2_Computer].[Name],
   [dca5_Expanded Bitlocker Status].[ConversionStatus],
   [dca5_Expanded Bitlocker Status].[DriveLetter],
   [dca6_Manufacturer].[Model],
   [dca5_Expanded Bitlocker Status].[EncryptionPercentage],
   [dca5_Expanded Bitlocker Status].[ProtectionStatus],
   [dca3_AeX AC Primary User].[User],
   [ajs4_vUser].[Email]
FROM
   [vRM_Computer_Item] AS [vri2_Computer]
      LEFT OUTER JOIN ([Inv_AeX_AC_Primary_User] AS [dca3_AeX AC Primary User]
         LEFT OUTER JOIN [vUser] AS [ajs4_vUser]
            ON ([dca3_AeX AC Primary User].[User] = [ajs4_vUser].[Name]))
         ON ([vri2_Computer].[Guid] = [dca3_AeX AC Primary User].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_Expanded_Bitlocker_Status] AS [dca5_Expanded Bitlocker Status]
         ON ([vri2_Computer].[Guid] = [dca5_Expanded Bitlocker Status].[_ResourceGuid])
      LEFT OUTER JOIN [Inv_Manufacturer] AS [dca6_Manufacturer]
         ON ([vri2_Computer].[Guid] = [dca6_Manufacturer].[_ResourceGuid])
WHERE
   (
      (
         ([dca5_Expanded Bitlocker Status].[EncryptionPercentage] <> N'%NULL%')
         AND
         ([vri2_Computer].[Name] LIKE N'%LT%')
         AND
         ([dca5_Expanded Bitlocker Status].[ProtectionStatus] LIKE N'%Protection On%')
         AND
         ([dca5_Expanded Bitlocker Status].[DriveLetter] LIKE N'%C:%')
      )
      AND
      ([vri2_Computer].[Guid] IN (SELECT [ResourceGuid] FROM [ScopeMembership] WHERE [ScopeCollectionGuid] IN (SELECT [ScopeCollectionGuid] FROM dbo.fnGetTrusteeScopeCollections(@v1_TrusteeScope))))
   )
ORDER BY
   [dca6_Manufacturer].[Model],
   [vri2_Computer].[Name]

Glad to see you figured it out and thanks for posting the query.  In the query I posted, I was going based off the assumption that the user guid contained in the Inv_AeX_AC_Primary_User table was the same as the one in vUser.  Found that that is not the case and the only way to be able to do it was based on the username like you did.

Ray, User display name is only available if you are importing AD user information into Altiris.  You can set this up in Actions -> Discover -> Import AD.

Joe, I am importing AD user info. Thanks for the suggestion. The problem is what Justin mentioned below. I've instead joined vUser using the 'Name' attribute instead of the Guid.