Voici une requête permettant de connaître l’état du déploiement des correctifs pour une collection spécifique, remplacé « xxxxxxx » par l’id de votre collection.
select sys.Name0,sys.resource_domain_or_workgr0 as ‘Domaine’, os.caption0 as ‘OS’,
SUM(case WHEN ctm.ResourceID IS NOT NULL THEN 1 ELSE 0 END) as ‘Approuvé’,
cast(sum(case when css.status=2 then 1 else 0 end)as varchar(10)) as ‘Manquants’,
cast(sum(case when pse.LastState=107 or pse.LastState=105 then 1 else 0 end)as varchar(10)) as ‘Installé’,
cast(sum(case when pse.LastState=101 then 1 else 0 end)as varchar(10)) as ‘Echec’,
cast(sum(case when pse.LastState=102 then 1 else 0 end)as varchar(10)) as ‘Attente Redémarrage’
from v_UpdateComplianceStatus css
join v_ClientCollectionMembers ccm on ccm.ResourceID=css.ResourceID
join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
join v_GS_PatchStatusEx pse on ui.CI_UniqueID=pse.UniqueUpdateID
Left join v_R_System sys on sys.ResourceID = ccm.ResourceID
left JOIN V_GS_Operating_system os on sys.resourceID = os.ResourceID
left join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = ccm.ResourceID
where ccm.CollectionID=’xxxxxxx‘ and pse.ResourceID=ccm.ResourceID
group by sys.Name0,sys.resource_domain_or_workgr0,os.caption0,ctm.ResourceID
having ctm.ResourceID>0