Tuesday, September 17, 2013

SCCM Patch Compliance Report for Machines by Wildcard

As an add on to a previous post I had for the patch compliance, the below query is to do the same thing but that it will allow you to list the all machines that you require information as well as using a wildcard (i.e for machine name starting with ABC change the wildcard underlined to 'ABC%')

select
   VRS.Name0,
   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
left join v_R_System VRS on VRS.ResourceID = css.ResourceID
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 = VRS.ResourceID
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 vrs.Active0 = 1and css.Status=2 and VRS.Name0 like '%'
order by VRS.name0 ,catinfo.CategoryInstanceName, catinfo2.CategoryInstanceName, ui.ArticleID

Friday, September 6, 2013

SCCM Patch Compliance - Listing required outstanding patches by server

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


Wednesday, August 28, 2013

SCCM Query to get Logon Information

There was an entry in the MSFT System Center forum was request for some form of assistance to retrieve information for the last console logon for machines which I posted .
Heaving posted that I decided to add in something extra to include the number of console logons to the machine as well as the Total Duration. Hopefully this helps anyone who needs this.

select VRS.Name0 as 'Computer Name'
, Resource_Domain_OR_Workgr0
, User_Name0 'Last Logon Name'
, VOS.Caption0
,VOS.CSDVersion0
,VSC.LastConsoleUse0
,VSC.NumberOfConsoleLogons0 'Total Console Logons Counts'
,VSC.TotalUserConsoleMinutes0 'Total Console Logon Minutes'

from v_R_System VRS 
left join dbo.v_GS_OPERATING_SYSTEM VOS on VRS.ResourceID = VOS.ResourceID
left join dbo.v_GS_SYSTEM_CONSOLE_USER VSC on VSC.ResourceID = VRS.ResourceID