I’ve had multiple Bigfix users inquire about the ability to programmatically obtain Patch Policy data such as policy schedule, policy state, target type, targeted endpoints, etc so I thought it would be more than worthwhile to share a quick/dirty SQL query to allow you the ability to obtain these datasets. This query will provide a comprehensive breakdown of Patch Policy ID, Name, associated schedule ID/Name, Policy state, target type (Group/Computer), target count, occurrence, as well as the next run date/time (SQL Server time conversion).
With that said, I’ve had a long standing RFE submitted for an API for WebUI Patch Policies so if this kind of data/management is meaningful to you please take the time to upvote the RFE - https://bigfix-ideas.hcltechsw.com/ideas/BFP-I-108
WITH apSCHEDULE AS (
SELECT
[policyID]
,[scheduleID]
,[purpose]
,[name]
,REPLACE([content], 'flattenJson|||', '') AS [content]
FROM [BFEnterprise].[webui].[apSCHEDULE]
where purpose = 'rollout'
),
aptarget as (
SELECT [scheduleID]
,[policyID]
,[targetID]
,TRIM('flattenJson|||' from computerList) AS computerList
,TRIM('flattenJson|||' from computerGroupList) AS computerGroupList
,[Sequence]
,[ManyVersion]
,[OriginServerID]
,[OriginSequence]
,[bypassLock]
FROM [BFEnterprise].[webui].[apTARGET]
),
CTarget AS (
SELECT scheduleID,
JSON_VALUE(computerList, '$.name') as 'Computer'
, JSON_VALUE(computerList, '$.id') as 'ComputerID'
FROM apTARGET
where ISJSON(computerList)=1
),
CTarget1 as (
select scheduleID,
JSON_QUERY(computerList, '$') as Computer
from apTARGET),
CTarget2 as (
select scheduleID,Computer,ComputerID, Computer1
from CTarget1 a
CROSS APPLY OPENJSON(Computer, '$')
with (
Computer1 varchar(MAX) '$.name',
ComputerID varchar(MAX) '$.id'
)),
GTarget AS (
SELECT scheduleID,
JSON_VALUE(computerGroupList, '$.name') as 'Computer Group'
FROM apTARGET
where ISJSON(computerGroupList)=1
),
cgroups as (
SELECT c.ComputerName, cg.ComputerID
,g.Name
,cg.[ContentID]
FROM [BFEnterprise].[dbo].[COMPUTER_GROUPS] cg
left join [BFEnterprise].[dbo].[GROUPS] g ON cg.ContentID = g.ContentID
left join [BFEnterprise].[dbo].COMPUTERS c on cg.ComputerID = c.ComputerID
where IsMember = 1
and g.IsDeleted = 0
),
aptarget1 as (
select distinct scheduleID,
JSON_QUERY(computerGroupList, '$') as ComputerGroup
from aptarget
),
aptarget2 as (
select distinct scheduleID,ComputerGroup1,GroupID
from aptarget1 a
CROSS APPLY OPENJSON(ComputerGroup, '$')
with (
GroupID varchar(MAX) '$.id',
ComputerGroup1 varchar(MAX) '$.name'
)),
NextRun AS (
SELECT
E.policyID,
P.name AS 'policyName',
S.name AS 'scheduleName',
S.scheduleID,
CONVERT(VARCHAR(10), SWITCHOFFSET(CONVERT(datetimeoffset, triggerTime), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS NextStartDate,
CONVERT(time(0), SWITCHOFFSET(CONVERT(datetimeoffset, triggerTime), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS NextStartTime
FROM
webui.apENGINEJOB E
INNER JOIN
webui.apPOLICY P
ON
P.policyID = E.policyID
INNER JOIN
webui.apSCHEDULE S
ON
S.scheduleID = E.scheduleID
WHERE
P.status = 'active'
AND
E.status = 'new'
AND
E.jobType = 'ROLLOUT')
select distinct
t.policyID as 'PolicyID',
p.name as 'Policy Name',
t.scheduleID as 'ScheduleID',
ap.name as 'Schedule Name',
p.status as 'Policy State',
case when string_agg(a.[ComputerGroup1],'') is not null THEN 'Group' else 'Computer' end as 'Target Type',
case when string_agg(a.[ComputerGroup1],'') is null THEN string_agg(Cast(c.Computer1 as NVARCHAR(MAX)),'') else string_agg(Cast(cg.ComputerName as NVARCHAR(MAX)),'') end as 'Target Members',
case when string_agg(a.[ComputerGroup1],'') is null THEN count(c.computer) else count(cg.ComputerName) end as 'Target Count',
JSON_VALUE(ap.[content],'$.time.type') as Occurrence,
n.NextStartDate as 'Next Run Date',
n.NextStartTime as 'Next Run Time'
FROM [BFEnterprise].[webui].[apTARGET] t
left join aptarget2 a on a.scheduleID = t.scheduleID
left join (select distinct ContentID,ComputerID,ComputerName,name from cgroups)cg ON a.GroupID = cg.ContentID
left join apSCHEDULE ap on t.scheduleID = ap.scheduleID
left join [BFEnterprise].[webui].apPOLICY as p on p.policyID = t.policyID
left join CTarget2 as c on t.scheduleID = c.scheduleID
left join NextRun as n on t.scheduleID = n.scheduleID
group by t.scheduleID,t.policyID, p.name, p.status, ap.content, n.NextStartDate, n.NextStartTime, ap.name
order by n.NextStartDate asc