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)?