Kaseya On Premise Antivirus Status (from Windows Security Center)

Follow

Updated SQL Query for Birightgauge 4. Cleaned up a bit and added a few extras (Yes/No Installed field and Last checkin date).

Note : Only works for workstations

SELECT

NEWID() as 'id',

aspr.agentguid,

aspr.MachineId,

k.orgname AS client_name,

aspr.ComputerName AS 'Machine Name',

aspr.productName AS 'Installed Security Product',

aspr.ProductTypeDesc AS 'Product Type',

aspr.active AS 'Active',

aspr.uptodate AS 'Up to Date',

aspr.manufacturer AS 'Manufacturer',

aspr.version AS 'Version',

aspr.groupName,

aspr.LastLoggedOnUser AS 'User',

aspr.OperatingSystem,

aspr.OSInformation,

DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), stat.lastCheckinTime) AS last_checked_in,

CASE

WHEN ((aspr.OperatingSystem = 'Windows 2000' and aspr.OSInformation not like 'Professional%') OR aspr.OperatingSystem = 'Windows 2003' OR aspr.OperatingSystem = 'Windows 2008' OR aspr.OperatingSystem = 'Windows 2012') THEN 'Server'

ELSE 'Workstation'

END AS 'Machine Type',

CASE

WHEN (aspr.productName = '**NONE**') THEN 'Yes'

ELSE 'No'

END AS 'Installed'

FROM vAuditSecurityProductsRpt aspr

INNER JOIN vMachine vm

ON aspr.agentguid = vm.agentGuid

INNER JOIN machGroup mg

ON mg.reverseName = vm.groupName

INNER JOIN kasadmin.org k ON k.id = mg.orgFK

LEFT JOIN vAgents_AgentStatus stat ON vm.agentGuid = stat.agentGuid

WHERE (NOT (aspr.productName = 'Windows Defender'))