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()))

Didn't find what you were looking for?

New post
Would you like to vote for this feature?
0 out of 0 found this helpful

Comments

0 comments