Thanks to Mirus IT for the inspiration on this one.
This query will pull out the information from the Installed Security Product Dataset found in the Audit (based on WMI - Windows Security Center). It reports 3 type of products, Antivirus, Antispyware and Firewall. If there's none, you'll still have an entry with **NONE** in the field so you can easily identifiy machines without AV for example. On some occasion, you can have duplicates entry in the WMI registry, that you will need to clean up (or filter out). It will also tell you if the security product is active and up to date.
SELECT DISTINCT
vAuditSecurityProductsRpt.agentguid,
vAuditSecurityProductsRpt.MachineId,
vAuditSecurityProductsRpt.ComputerName AS 'Machine Name',
vAuditSecurityProductsRpt.productName AS 'Installed Security Product',
vAuditSecurityProductsRpt.ProductTypeDesc AS 'Product Type',
vAuditSecurityProductsRpt.active AS 'Active',
vAuditSecurityProductsRpt.uptodate AS 'Up to Date',
vAuditSecurityProductsRpt.manufacturer AS 'Manufacturer',
vAuditSecurityProductsRpt.version AS 'Version',
vAuditSecurityProductsRpt.groupName,
vAuditSecurityProductsRpt.LastLoggedOnUser AS 'User',
vAuditSecurityProductsRpt.OperatingSystem,
vAuditSecurityProductsRpt.OSInformation,
CASE
WHEN ((vAuditSecurityProductsRpt.OperatingSystem = 'Windows 2000' and vAuditSecurityProductsRpt.OSInformation not like 'Professional%') OR vAuditSecurityProductsRpt.OperatingSystem = 'Windows 2003' OR vAuditSecurityProductsRpt.OperatingSystem = 'Windows 2008' OR vAuditSecurityProductsRpt.OperatingSystem = 'Windows 2012') THEN 'Server'
ELSE 'Workstation'
END AS 'Machine Type'
FROM vAuditSecurityProductsRpt INNER JOIN
vMachine ON vAuditSecurityProductsRpt.agentguid = vMachine.agentGuid
INNER JOIN machGroup mg ON mg.orgFK = {client_service_id} AND mg.reverseName = vMachine.groupName
INNER JOIN kasadmin.org k ON k.id = mg.orgFK
WHERE (NOT (vAuditSecurityProductsRpt.productName = 'Windows Defender'))
ORDER BY 'Machine Name'
Happy Reporting !
Thomas
Comments
1 comment