Custom Query Report - Need Help Including New Information

Question

I have a custom query report that I'm using to show information from lots of different tables for each computer. Now I need to add registry information, but I can't quite figure out how to do it. Can someone assist?

Here's what I have today:

-- Declare parameters
DECLARE @Domain TABLE (DomainName NVARCHAR(max))
DECLARE @Computer TABLE (ComputerName NVARCHAR(max))
DECLARE @ServiceName NVARCHAR(max)
DECLARE @ServiceName2 NVARCHAR(max)
-------------------------------------------------------------------------------
--Handle the wildcards

UPDATE @Domain
SET DomainName = CASE WHEN DomainName = '*' THEN NULL ELSE DomainName END

UPDATE @Computer
SET ComputerName = CASE WHEN ComputerName = '*' THEN NULL ELSE ComputerName END

IF (@ServiceName IS NULL) OR (@ServiceName = '') OR (@ServiceName = '*')
Set @ServiceName = ''

IF (@ServiceName2 IS NULL) OR (@ServiceName2 = '') OR (@ServiceName2 = '*')
Set @ServiceName2 = ''

------------------------------------------------------- Policies--------------------------------------------------------------------------

SELECT [tblComputer].[DomainName] as [Domain (Computer)], [tblComputer].[ComputerName] as [Computer Name (Computer)],
dbo.udfBitmaskLookup('ComputerPolicy', 'Audit_AccountLogon', 1033, [tblComputerPolicy].[Audit_AccountLogon]) as [Audit - Account Logon (Security Policy)], dbo.udfBitmaskLookup('ComputerPolicy', 'Audit_AccountManagement', 1033, [tblComputerPolicy].[Audit_AccountManagement]) as [Audit - Account Management (Security Policy)], dbo.udfBitmaskLookup('ComputerPolicy', 'Audit_DetailedTracking', 1033, [tblComputerPolicy].[Audit_DetailedTracking]) as [Audit - Detailed Tracking (Security Policy)], dbo.udfBitmaskLookup('ComputerPolicy', 'Audit_DirectoryServicesAccess', 1033, [tblComputerPolicy].[Audit_DirectoryServicesAccess]) as [Audit - Directory Services Access (Security Policy)], dbo.udfBitmaskLookup('ComputerPolicy', 'Audit_Logon', 1033, [tblComputerPolicy].[Audit_Logon]) as [Audit - Logon (Security Policy)], dbo.udfBitmaskLookup('ComputerPolicy', 'Audit_ObjectAccess', 1033, [tblComputerPolicy].[Audit_ObjectAccess]) as [Audit - Object Access (Security Policy)], dbo.udfBitmaskLookup('ComputerPolicy', 'Audit_PolicyChange', 1033, [tblComputerPolicy].[Audit_PolicyChange]) as [Audit - Policy Change (Security Policy)], dbo.udfBitmaskLookup('ComputerPolicy', 'Audit_PrivilegeUse', 1033, [tblComputerPolicy].[Audit_PrivilegeUse]) as [Audit - Privilege Use (Security Policy)], dbo.udfBitmaskLookup('ComputerPolicy', 'Audit_System', 1033, [tblComputerPolicy].[Audit_System]) as [Audit - System (Security Policy)], [tblComputerPolicy].[Lockout_Duration] as [Lockout - Duration (Security Policy)], [tblComputerPolicy].[Lockout_LockoutThreshold] as [Lockout - Lockout Threshold (Security Policy)], [tblComputerPolicy].[Lockout_ResetAccountAfter] as [Lockout - Reset Account After (Security Policy)], [tblComputerPolicy].[Pwd_EnforcePasswordHistory] as [Password - Enforce Password History (Security Policy)], [tblComputerPolicy].[Pwd_ForceLogoff] as [Password - Force Logoff (Security Policy)], [tblComputerPolicy].[Pwd_MaxPasswordAge] as [Password - MaxPassword Age (Security Policy)], [tblComputerPolicy].[Pwd_MinPasswordAge] as [Password - Min Password Age (Security Policy)], [tblComputerPolicy].[Pwd_MinPasswordLen] as [Password - Min Password Length (Security Policy)], [tblComputerPolicy].[Pwd_UseReversibleEncryption] as [Password - Use Reversible Encryption (Security Policy)], [tblComputerPolicy].[Pwd_MustMeetComplexityReq] as [Password - Must Meet Complexity Requirements (Security Policy)], DATEADD(minute, -180, [base].[CollectedTime])as [Last Collected Time (Physical Computer)]
FROM
[tblComputerPhysical] base
INNER JOIN [tblComputerPolicy] tblComputerPolicy
ON ([base].[PhysicalComputerID] = [tblComputerPolicy].[PhysicalComputerID] AND ([tblComputerPolicy].[LDM_TOMBSTONED] = 0 OR [tblComputerPolicy].[LDM_TOMBSTONED] IS NULL))
LEFT OUTER JOIN [tblComputer] tblComputer
ON ([base].[ComputerID] = [tblComputer].[ComputerID] AND ([tblComputer].[LDM_TOMBSTONED] = 0 OR [tblComputer].[LDM_TOMBSTONED] IS NULL))
WHERE (
  (SELECT TOP 1 DomainName FROM @Domain) IS NULL
OR
  (
                  [tblComputer].[DomainName] IN (SELECT DomainName FROM @Domain)
  )
)
AND
(
  (SELECT TOP 1 ComputerName FROM @Computer) IS NULL
      OR
  (
   [tblComputer].[ComputerName] IN (SELECT ComputerName FROM @Computer)
  )
)
AND
              (                 base.LDM_TOMBSTONED = 0 OR base.LDM_TOMBSTONED IS NULL              )
ORDER BY [tblComputer].[DomainName] ASC, [tblComputer].[ComputerName] ASC;

------------------------------------------------------- Volumes--------------------------------------------------------------------------
SELECT [tblComputer].[DomainName] as [Domain (Computer)], [tblComputer].[ComputerName] as [Computer Name (Computer)], [tblComputerVolume].[VolumeName] as [Volume Name (Volume)], [tblComputerVolume].[FileSystem] as [File System (Volume)]
FROM
[tblComputerPhysical] base
INNER JOIN [tblComputerVolume] tblComputerVolume
ON ([base].[PhysicalComputerID] = [tblComputerVolume].[PhysicalComputerID] AND ([tblComputerVolume].[LDM_TOMBSTONED] = 0 OR [tblComputerVolume].[LDM_TOMBSTONED] IS NULL))
LEFT OUTER JOIN [tblComputer] tblComputer
ON ([base].[ComputerID] = [tblComputer].[ComputerID] AND ([tblComputer].[LDM_TOMBSTONED] = 0 OR [tblComputer].[LDM_TOMBSTONED] IS NULL))
WHERE
                 (
  (SELECT TOP 1 DomainName FROM @Domain) IS NULL
                                                                   OR
  (
              [tblComputer].[DomainName] IN (SELECT DomainName FROM @Domain)
  )
)
AND
(
  (SELECT TOP 1 ComputerName FROM @Computer) IS NULL
      OR
  (
   [tblComputer].[ComputerName] IN (SELECT ComputerName FROM @Computer)
  )
)
AND
               (              base.LDM_TOMBSTONED = 0 OR base.LDM_TOMBSTONED IS NULL        )
AND
(
                              tblComputerVolume.FileSystem <> ''
)
AND
(
                              tblComputerVolume.FileSystem <> 'CDFS'
)
ORDER BY [tblComputer].[DomainName] ASC, [tblComputer].[ComputerName] ASC, [tblComputerVolume].[VolumeName] ASC

------------------------------------------------------- Services--------------------------------------------------------------------------
SELECT [tblComputer].[DomainName] as [Domain (Computer)], [tblComputer].[ComputerName] as [Computer Name (Computer)], [tblComputerService].[DisplayName] as [Display Name (Service)], dbo.udfBitmaskLookup('ComputerService', 'CurrentState', 1033, [tblComputerService].[CurrentState]) as [Current State (Service)]
FROM
[tblComputerPhysical] base
LEFT OUTER JOIN [tblComputerService] tblComputerService
ON ([base].[PhysicalComputerID] = [tblComputerService].[PhysicalComputerID] AND ([tblComputerService].[LDM_TOMBSTONED] = 0 OR [tblComputerService].[LDM_TOMBSTONED] IS NULL))
LEFT OUTER JOIN [tblComputer] tblComputer
ON ([base].[ComputerID] = [tblComputer].[ComputerID] AND ([tblComputer].[LDM_TOMBSTONED] = 0 OR [tblComputer].[LDM_TOMBSTONED] IS NULL))
WHERE
(
  (SELECT TOP 1 DomainName FROM @Domain) IS NULL
OR
  (
                 [tblComputer].[DomainName] IN (SELECT DomainName FROM @Domain)
  )
)
AND
(
  (SELECT TOP 1 ComputerName FROM @Computer) IS NULL
      OR
  (
   [tblComputer].[ComputerName] IN (SELECT ComputerName FROM @Computer)
  )
)
AND
(
  ([tblComputerService].[ServiceName] LIKE '%'+@ServiceName)
      OR
  ([tblComputerService].[ServiceName] LIKE '%'+@ServiceName2+'%')

)
AND
              (                   base.LDM_TOMBSTONED = 0 OR base.LDM_TOMBSTONED IS NULL    )
ORDER BY [tblComputer].[DomainName] ASC, [tblComputer].[ComputerName] ASC, [tblComputerService].[DisplayName] ASC;

I'd like to add the information found in the canned "Registry Permissions" report template to my existing report. The additional registry information will be for one specific key (no sub-keys). For this example, let's assume it's "KEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control".

Can someone help me add a "Registry" section of this report (pretty much duplicate one of the Policies, Services or Volumes section)?

gpinkrott · Accepted Answer

Aravind, 
 Thanks for the response, but it does not include the registry permissions that I originally requested. 
 Using your query as a guide, I was able to take the query from the canned report and come up with this: 
 -- Declare parameters 
 DECLARE @Domain TABLE ( DomainName NVARCHAR ( max )) 
 DECLARE @Computer TABLE ( ComputerName NVARCHAR ( max )) 
 DECLARE @Path TABLE ( Path nvarchar ( max )) 
 ------------------------------------------------------------------------------- 
 --Handle the wildcards 
 UPDATE @Domain 
 SET DomainName = CASE WHEN DomainName = '*' THEN NULL ELSE DomainName END 
 UPDATE @Computer 
 SET ComputerName = CASE WHEN ComputerName = '*' THEN NULL ELSE ComputerName END

------------------------------------------------------- Registry-------------------------------------------------------------------------- 
 select 
 base . [LDM_TOMBSTONED] , [tblComputer] . [ComputerName] as [Computer Name (Computer)] , [tblRegistryPath] . [Path] as [Path (Registry Key)] , [tblComputer] . [DomainName] as [Domain (Computer)] , CASE WHEN LEN ( [tblAccount_1] . [SAMAccountDomain] ) = 0 THEN [tblAccount_1] . [SAMAccountName] ELSE [tblAccount_1] . [SAMAccountDomain] + '\' + [tblAccount_1] . [SAMAccountName] END as [Account Name (Account)] , [Type (ACE)] = 
 CASE [vwRegistryACE] . [AceType] 
 WHEN 0 THEN N'Allow' 
 WHEN 1 THEN N'Deny' 
 ELSE '' 
 END , dbo . udfBitmaskLookup ( 'RegistryAce' , 'AccessMask' , 1033 , [vwRegistryACE] . [AccessMask] ) as [Permissions (ACE)] , [Applies To (ACE)] = 
 CASE [vwRegistryACE] . [AppliesTo] 
 WHEN 1 THEN N'This key only' 
 WHEN 3 THEN N'This key and subkeys' 
 WHEN 2 THEN N'Subkeys only' 
 ELSE '' 
 END , [vwRegistryACE] . [IsInherited] as [Is Inherited (ACE)] 
 
 from 
 [tblRegistryKeyComputer] base 
 LEFT OUTER JOIN [tblRegistryKey] tblRegistryKey 
 ON ( [base] . [RegistryKeyComputerID] = [tblRegistryKey] . [RegistryKeyComputerID] AND ( [tblRegistryKey] . [LDM_TOMBSTONED] = 0 OR [tblRegistryKey] . [LDM_TOMBSTONED] IS NULL)) 
 LEFT OUTER JOIN [tblRegistryPath] tblRegistryPath 
 ON ( [tblRegistryKey] . [RegistryPathID] = [tblRegistryPath] . [RegistryPathID] AND ( [tblRegistryPath] . [LDM_TOMBSTONED] = 0 OR [tblRegistryPath] . [LDM_TOMBSTONED] IS NULL)) 
 LEFT OUTER JOIN [tblACL] tblACL 
 ON ( [tblRegistryKey] . [ACLID] = [tblACL] . [ACLID] AND ( [tblACL] . [LDM_TOMBSTONED] = 0 OR [tblACL] . [LDM_TOMBSTONED] IS NULL)) 
 LEFT OUTER JOIN [tblAccount] tblAccount 
 ON ( [tblACL] . [OwnerAccountID] = [tblAccount] . [AccountID] AND ( [tblAccount] . [LDM_TOMBSTONED] = 0 OR [tblAccount] . [LDM_TOMBSTONED] IS NULL)) 
 LEFT OUTER JOIN [vwRegistryACE] vwRegistryACE 
 ON ( [tblACL] . [ACLID] = [vwRegistryACE] . [ACLID] AND ( [vwRegistryACE] . [LDM_TOMBSTONED] = 0 OR [vwRegistryACE] . [LDM_TOMBSTONED] IS NULL)) 
 LEFT OUTER JOIN [tblAccount] tblAccount_1 
 ON ( [vwRegistryACE] . [AccountID] = [tblAccount_1] . [AccountID] AND ( [tblAccount_1] . [LDM_TOMBSTONED] = 0 OR [tblAccount_1] . [LDM_TOMBSTONED] IS NULL)) 
 LEFT OUTER JOIN [tblComputer] tblComputer 
 ON ( [base] . [ComputerID] = [tblComputer] . [ComputerID] AND ( [tblComputer] . [LDM_TOMBSTONED] = 0 OR [tblComputer] . [LDM_TOMBSTONED] IS NULL)) 
 WHERE 
 ( 
 ( SELECT TOP 1 Path FROM @Path ) IS NULL 
 OR 
 ( 
 [tblRegistryPath] . [Path] IN ( SELECT Path FROM @Path ) 
 ) 
 ) 
 and 
 ( 
 ( SELECT TOP 1 DomainName FROM @Domain ) IS NULL 
 OR 
 ( 
 [tblComputer] . [DomainName] IN ( SELECT DomainName FROM @Domain ) 
 ) 
 ) 
 AND 
 ( 
 ( SELECT TOP 1 ComputerName FROM @Computer ) IS NULL 
 OR 
 ( 
 [tblComputer] . [ComputerName] IN ( SELECT ComputerName FROM @Computer ) 
 ) 
 ) 
 and 
 ( base . LDM_TOMBSTONED is null or base . LDM_TOMBSTONED = 0 ) 
 ORDER BY [tblComputer] . [DomainName] ASC , [tblComputer] . [ComputerName] ASC , [tblRegistryPath] . [Path] ASC ; 
 
 The only problem I have is that when I use it in Report Manager and run the report, each server's results include the permissions for all the servers in the list. So, if I have ServerA and Server B, the results for ServerA show both ServerA and ServerB's ACEs. Can you take a look at my query and help me figure out why this is happening?