Kaseya BitDefender Current Statuses

Follow

Thanks to a client donating a few minutes of time, we have now been able to create a dataset looking at the current statuses of BitDefender machines as synced through Kaseya!

SQL is below:

Select ve.agentGuid
,ve.agentguid as id
,ve.machName as machine
,ve.groupName

,ve.ipAddress
,k.orgname AS client_name
,ve.pVersion as AV_Version
,ve.amEnabled as anti_malware_enabled
,cast(ve.lastUpdate as datetime) as last_updated
,vm.ProductName as model
,ve.OsType as os_version
,ve.OsInfo as os_info
,ul.lastLoginName AS 'last_user'
,CASE
WHEN ((vm.OsType = '2000' and vm.OsInfo not like 'Professional%') OR vm.OSType = '2003' OR vm.OSType = '2008' OR vm.OSType = '2012') THEN 'Server'
ELSE 'Workstation'
END AS machine_type
,case when ve.updateStatus = 0 then 'Up to Date'
when ve.updateStatus = 3 then 'Out of date (definitions)'
when ve.updateStatus = 7 then 'Restart Required'
when ve.updateStatus = 1 then 'Out of date'
when ve.updateStatus = 4 then 'Disabled'
when ve.updateStatus = 2 then 'Out of date (product)'
when ve.updateStatus = 8 then 'No Status'
else 'Other' end as Update_Status
,case when ve.endpointState = 3 then 'Managed'
when ve.endpointState = 10 then 'Discovering'
when ve.endpointState = 0 then 'Other'
when ve.endpointState = 12 then 'Uninstalling'
when ve.endpointState = 1 then 'Agent Not Installed'
when ve.endpointState = 13 then 'Updating License'
when ve.endpointState = -3 then 'System Error'
when ve.endpointState = 2 then 'Unmanaged, Inactive Agent'
when ve.endpointState = 11 then 'Installing'
when ve.endpointState = -1 then 'Unsupported'
else 'Other' end as endpoint_state
,cast(ve.online as bit) as online_flag
,pol.name as policy
,tas.taskName as pending_task
,cast(ve.currentLicenseExpirationDate as datetime) as license_expiration
,ve.currentLicenseActive as license_active_flag
,cast(ve.lastCheckIn as datetime) as last_checkin
,ve.isAgentInstalled as agent_installed_flag
from bitdef.vw_endpoints ve
left join bitdef.policies pol on pol.id = ve.currentPolicyId
left join bitdef.tasks tas on tas.id = ve.pendingPolicyTaskId
left join vMachine vm on vm.agentguid = ve.agentguid
INNER JOIN machGroup mg ON mg.reverseName = vm.groupName
INNER JOIN kasadmin.org k ON k.id = mg.orgFK
LEFT JOIN userIpInfo AS ul ON vm.agentguid = ul.agentguid