Note that the below makes some assumptions on date/times for statistics due to the structure of Webroot data that will require modification of gauges if you switch to this dataset over stock. Use this at your own risk as it was created without official support and from a raw dive through the DB. Please post back any modifications for others.
SELECT
NEWID() AS id
,vm.machname AS machine_name
,CASE WHEN CHARINDEX('erver',vm.osinfo)>0 THEN 'Server'
WHEN vm.OsType = '2000' AND vm.OsInfo NOT like 'Professional%' THEN 'Server'
ELSE 'Workstation'
END AS machine_type
,k.orgname AS client_name
,vm.groupname AS group_name
,wrth.FileName AS file_path
,wrth.Infection AS virus_name
,CASE
WHEN (wrcs.Infected = 0) THEN ''
Else 'Infected'
END AS resolution
,CAST(wrth.EventTime AS Datetime) AS date_first_detected
,CAST(wrcs.LastThreatSeen AS Datetime) AS date_last_detected
,CAST(wrcs.LastThreatSeen AS Datetime) AS date_removed
,CASE
WHEN (wrcs.Infected = 0) THEN '0'
ELSE '1'
END AS quarantined
,vm.OsType AS os_version
,vm.osinfo AS os_description
FROM giWebroot.Clients avwr
INNER JOIN vMachine vm ON vm.agentGuid = avwr.Id
INNER JOIN machGroup mg ON mg.reverseName = vm.groupName
INNER JOIN kasadmin.org k ON k.id = mg.orgFK
Left JOIN giWebroot.ClientStatus wrcs ON avwr.Id = wrcs.ClientId
LEFT JOIN giWebroot.ThreatHistory wrth on avwr.ID = wrth.ClientId
WHERE wrth.EventTime >= DATEADD(dd,-(DAY(DATEADD(mm,1,getdate()))-1),DATEADD(mm,-5,getdate()))
Comments
0 comments