Combine Registry Reports

[LP]

I was able to create a report for SMB Signing and another for SMB Signing Forced. They both work as separate reports. I need to combine the two reports into one. How can the below report (SMB Signing Enabled) be edited to add all of the SMB reg key values from the SMB Forced report?

Note: The only difference between the two existing reports is the reg key value shown in red below. The values for each are as follows:

• SMB Signing = EnableSecuritySignature
• SMB Forced = requiresecuritysignature

Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where
tblRegistry.Regkey Like
'%SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters' And
tblRegistry.Valuename = 'EnableSecuritySignature') SubQuery1 On
SubQuery1.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
tblAssets.AssetName

SMB Signing Report

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.Version,
  tblAssets.SP,
  Case
    When TsysLastscan.Lasttime < GetDate() - 1 Then
      'Last registry scan more than 24 hours ago! Information may not be up-to-date. Try rescanning this machine.'
  End As Comment,
  Case
    When SubQuery1.Value Is Not Null And SubQuery1.Value <> '0' Then 'Yes'
    Else 'No'
  End As [SMB Signing Enabled],
  Case
    When SubQuery1.Valuename Is Not Null And SubQuery1.Valuename <> ''
    Then 'Yes'
    Else 'No'
  End As [Reg Key Found],
  SubQuery1.Regkey As [SMB Signing Reg Key Name],
  SubQuery1.Valuename As [SMB Signing Reg Value Name],
  SubQuery1.Value As [Key Value],
  Case
    When tblErrors.ErrorText Is Not Null Or
      tblErrors.ErrorText != '' Then
      'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
    Else ''
  End As ScanningErrors,
  TsysLastscan.Lasttime As LastRegistryScan,
  SubQuery1.Lastchanged,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
      Max(tblErrors.Teller) As ErrorID
    From tblErrors
    Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
      ScanningError.ID
  Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
  Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
      tblErrors.ErrorType
  Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
  Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
  Left Join (Select Top 1000000 tblRegistry.AssetID,
      tblRegistry.Regkey,
      tblRegistry.Valuename,
      tblRegistry.Value,
      tblRegistry.Lastchanged
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      '%SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters' And
      tblRegistry.Valuename = 'EnableSecuritySignature') SubQuery1 On
      SubQuery1.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
  tblAssets.AssetName

[LP]

This worked. Thank you very much!

[KevinA-REJIS · Accepted solution]

I don't have the registry values in our environment so I not for certain this will work, but give it a try:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.Version,
  tblAssets.SP,
  Case
    When TsysLastscan.Lasttime < GetDate() - 1 Then
      'Last registry scan more than 24 hours ago! Information may not be up-to-date. Try rescanning this machine.'
  End As Comment,
  Case
    When SubQuery1.Value Is Not Null And SubQuery1.Value <> '0' Then 'Yes'
    Else 'No'
  End As [SMB Signing Enabled],
  Case
    When SubQuery1.Valuename Is Not Null And SubQuery1.Valuename <> ''
    Then 'Yes'
    Else 'No'
  End As [SMB Signing Reg Key Found],
  SubQuery1.Regkey As [SMB Signing Reg Key Name],
  SubQuery1.Valuename As [SMB Signing Reg Value Name],
  SubQuery1.Value As [SMB Signing Reg Key Value],
  Case
    When SubQuery2.Value Is Not Null And SubQuery2.Value <> '0' Then 'Yes'
    Else 'No'
  End As [SMB Signing Forced],
  Case
    When SubQuery2.Valuename Is Not Null And SubQuery2.Valuename <> ''
    Then 'Yes'
    Else 'No'
  End As [SMB Signing Forced Reg Key Found],
  SubQuery2.Regkey As [SMB Signing Forced Reg Key Name],
  SubQuery2.Valuename As [SMB Signing Forced Reg Value Name],
  SubQuery2.Value As [SMB Signing Forced Reg Key Value],
  Case
    When tblErrors.ErrorText Is Not Null Or
      tblErrors.ErrorText != '' Then
      'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
    Else ''
  End As ScanningErrors,
  TsysLastscan.Lasttime As LastRegistryScan,
  SubQuery1.Lastchanged As [SMB Signing Reg Key Last Changed],
  SubQuery2.Lastchanged As [SMB Signing Forced Reg Key Last Changed],
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
      Max(tblErrors.Teller) As ErrorID
    From tblErrors
    Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
      ScanningError.ID
  Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
  Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
      tblErrors.ErrorType
  Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
  Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
  Left Join (Select Top 1000000 tblRegistry.AssetID,
      tblRegistry.Regkey,
      tblRegistry.Valuename,
      tblRegistry.Value,
      tblRegistry.Lastchanged
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      '%SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters' And
      tblRegistry.Valuename = 'EnableSecuritySignature') SubQuery1 On
      SubQuery1.AssetID = tblAssets.AssetID
  Left Join (Select Top 1000000 tblRegistry.AssetID,
      tblRegistry.Regkey,
      tblRegistry.Valuename,
      tblRegistry.Value,
      tblRegistry.Lastchanged
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      '%SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters' And
      tblRegistry.Valuename = 'requiresecuritysignature') SubQuery2 On
      SubQuery2.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
  tblAssets.AssetName