Looking for a way to obtain number of endpoint’s patched date by source content. Believe we are on the right track, but could use some assistance in narrowing down the approach to determine patch date.
We are leveraging the Bigfix DB table “FIXLETRESULTS”
SELECT
s.DisplayName as 'PatchSource'
,COUNT(r.[ComputerID]) as 'Endpoints'
-- ,[FirstBecameRelevant]
-- ,[LastBecameRelevant]
-- ,[LastBecameNonRelevant]
,CAST(LastBecameNonRelevant as date) as 'FixDate'
FROM [BFEnterprise].[dbo].[FIXLETRESULTS] r
left join SITENAMEMAP s on r.SiteID = s.SiteID
left join COMPUTERS c on r.ComputerID = c.ComputerID
where s.DisplayName like '%Patches for%'
and c.AgentType like '%Native%'
and c.IsDeleted = 0
and IsRelevant = 0
Group by DisplayName,CAST(LastBecameNonRelevant as date)
order by CAST(LastBecameNonRelevant as date) ASC
This query works well, but can anyone provide details on how to obtain the actual fixdate? Assume it’s the difference between the LastBecameRelevant & LastBecameNonRelevant fields? Perhaps there is a way to simply pull the remediated flag of an endpoint compared to the source content?