‎09-07-2023 07:24 PM - last edited on ‎04-01-2024 12:40 PM by Mercedes_O
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:
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
Solved! Go to Solution.
‎09-08-2023 04:07 PM
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
‎09-08-2023 07:28 PM
This worked. Thank you very much!
‎09-08-2023 04:07 PM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now