The below query will enable you to get the patches that are required but has yet to be installed by server.
Replace the parameter @machinename with the server that you are referencing against.
declare @RscID int;
select @RscID=ResourceID from v_R_System where ((Name0 = @machinename) and (Active0 = 1));
select
catinfo2.CategoryInstanceName as UpdateClassification,
ui.BulletinID as BulletinID,
ui.DatePosted,
ui.DateRevised,
ui.ArticleID as ArticleID,
ui.Title as Title,
ui.InfoURL as InformationURL
from v_UpdateComplianceStatus css
join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
join v_CICategories_All catall on catall.CI_ID=ui.CI_ID
join v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company'
join v_CICategories_All catall2 on catall2.CI_ID=ui.CI_ID
join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification'
left join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = @RscID
left join (
select atc.CI_ID, Deadline=min(a.EnforcementDeadline) from v_CIAssignment a
join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID
group by atc.CI_ID) cdl on cdl.CI_ID=css.CI_ID
where css.ResourceID = @RscID
and ((css.Status=2) )
order by catinfo.CategoryInstanceName, catinfo2.CategoryInstanceName, ui.ArticleID
Output of the report will be in the format as below when performed via SSRS
No comments:
Post a Comment