Report help - Security patches - domain controllers only

[dawnlowery]

Hi I need a bit of help with a report that lists all the Microsoft security patches installed on my company assets.

I would like to be able to only include servers that are listed as domain controllers.

Any help is appreciated!


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon16 As icon,
tblOperatingsystem.Caption As OS,
tblOperatingsystem.ServicePackMajorVersion As SP,
tblQuickFixEngineering.InstalledOn,
tblQuickFixEngineering.Lastchanged,
tblQuickFixEngineeringUni.Description,
tblQuickFixEngineeringUni.FixComments,
tblQuickFixEngineeringUni.HotFixID,
tblQuickFixEngineeringUni.ServicePackInEffect,
TsysLastscan.Lasttime As [Last Scan For Updates]
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblQuickFixEngineering On tblAssets.AssetID =
tblQuickFixEngineering.AssetID
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Where tblQuickFixEngineering.Lastchanged > GetDate() - 30 And
TsysWaittime.CFGname = 'quickfix'
Order By tblAssets.AssetName,
tblQuickFixEngineeringUni.Description

[dawnlowery]

Perfect Thank You!

[RCorbeil · Accepted solution]

Add tblComputerSystem to your report. You'll find a numeric field, DomainRole, in there identifying the asset's role.

If you want to see role descriptions, add tblDomainRoles and the field DomainRoleName.

If it's just the domain controllers you want to see, filter the results for tblComputerSystem.DomainRole >= 4.

The contents of tblDomainRoles:

Domainrole  Domainrolename
----------  --------------
     0      Stand-alone workstation
     1      Member workstation
     2      Stand-alone server
     3      Member server
     4      Backup domain controller
     5      Primary domain controller