The EmptyGarden – Page 4 – Planting seeds of knowledge so your brain can grow.

June 19, 2012

Windows 8 with Hyper-V

Last Friday at the Minnesota System Center Users Group I had the pleasure of seeing Wally Mead speak! I noticed that Wally had Windows 8 on his laptop, but he was also using Virtual Machines. I asked him if those were Hyper-V machines and he said yes. At this point I was confused! He told me that Windows 8 has a Hyper-V feature. He went on to say that Windows 8 is the first client OS he has had on his laptop in a long time, since before he had to use a server OS.

I can’t wait to throw Windows 8 on my new Malibal Satori laptop with 256GB mSATA drive (for the OS), three 750GB drives (two of which are RAID 0), 32 GB RAM, Intel Core i7-3920XM and (most importantly) a matte screen. Perhaps now Brian Mason (Config Manager MVP) will stop teasing me about my current EliteBook 2540p!

May 24, 2012

SCCM Client Inventory and WSUS Scan state

Here is a view that you can use to see SCCM Client inventory and WSUS scan state information.

CREATE view [dbo].[v_SCCM_Health_Check] as

select distinct a.name0 as 'MachineName'
, a.itemkey
, a.operating_system_name_and0 as 'OperatingSystem'
, os.CSDVersion0 as 'OS_ServicePack'
, a.client0 as 'SCCM_Client'
, a.Client_Version0 as 'SCCM_ClientVersion'
, case when a.User_Name0 like '%\%' then substring(a.User_Name0,charindex('\',a.User_Name0)+ 1,charindex('\',a.User_Name0))
    else a.User_Name0 end AS 'LastLoggedOnUser'
, case when scud.TopConsoleUser0 like '%\%' then substring(scud.TopConsoleUser0,charindex('\',scud.TopConsoleUser0)+ 1,charindex('\',scud.TopConsoleUser0)) 
    else scud.TopConsoleUser0 end as 'MainUser'
, nlp.FullName0 as 'MainUserFullName'
, a.AD_Site_Name0 as 'AD_Site'
, ou.OU
, a.Resource_Domain_OR_Workgr0 as 'Domain'
, e.SiteCode as 'SCCM_Site_Code'
, b.LastHWScan as 'SCCM_LastHWscan'
, c.LastUpdateDate as 'SCCM_LastSWscan'
, Dateadd(hour,(datediff(hour,getutcdate(),getdate())),d.lastscantime) as 'Last_WSUSscan'
, scan.StateName 'WSUSscan_State'
, scan.ErrorStatusID
, scan.ErrorCode
, scan.HexErrorCode
, uagent.version00 'WSUS_AgentVersion'
, d.lastlocation as 'WSUSserver'
, CCMService.State0 as 'SMS_Agent_Host_Service'
, AutoUpdates.State0 as 'Auto_Updates_Service'
from SMS_CEN.dbo.System_DISC a 
left join SMS_CEN.dbo.WorkstationStatus_DATA b
on a.itemkey = b.machineid  
left join SMS_CEN.dbo.SoftwareInventoryStatus c
on a.itemkey = c.clientid  
left join SMS_CEN.dbo.Update_ScanStatus d
on a.itemkey = d.machineid 
left join SMS_CEN.dbo.Windows_Update_Agent__DATA uagent
on a.itemkey = uagent.machineid 
left join SMS_CEN.dbo.Sites_DATA e  
on a.itemkey = e.machineid  
left join dbo.SCCM_Health_Check_DATA f
on a.itemkey = f.machineid 
left join dbo.Operating_System_DATA os
on a.itemkey = os.machineid  
left join
(
select uss.ResourceID, SN.StateName, uss.LastStatusMessageID&0x0000FFFF as ErrorStatusID,
    isnull(uss.LastErrorCode,0) as ErrorCode,
    dbo.fnConvertBinaryToHexString(convert(VARBINARY(8), isnull(uss.LastErrorCode,0))) as HexErrorCode
 from v_UpdateScanStatus uss
    join v_R_System rsys on rsys.ResourceID = uss.ResourceID and isnull(rsys.Obsolete0,0)<>1
    join v_SoftwareUpdateSource sus on uss.UpdateSource_ID = sus.UpdateSource_ID 
    join v_RA_System_SMSAssignedSites sass on uss.ResourceID = sass.ResourceID
    join v_StateNames sn on sn.TopicType = 501 and 
        sn.StateID = (case when (isnull(uss.LastScanState, 0)=0 and Left(isnull(rsys.Client_Version0, '4.0'), 1)<'4') then 7 else isnull(uss.LastScanState, 0) end)
) scan
on a.itemkey = scan.ResourceID  
left JOIN dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUD
on a.itemkey = scud.resourceID
left join dbo.v_GS_NETWORK_LOGIN_PROFILE nlp
on nlp.name0 = scud.TopConsoleUser0 and nlp.resourceID = scud.resourceid
left join
    (
    select machineid, State0
    from     dbo.Services_DATA
    where name0 = 'ccmexec'
    ) CCMService
on a.itemkey = CCMService.machineid
left join
    (
    select machineid, State0
    from     dbo.Services_DATA
    where name0 = 'wuauserv'
    ) AutoUpdates
on a.itemkey = AutoUpdates.machineid
left join
    (
    select itemKey, max(system_ou_name0) as 'OU'
    from dbo.System_System_OU_Name_ARR
    group by itemkey
    ) OU
on a.itemkey = ou.itemkey

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

May 16, 2012

TechFuseMN – 2012

I will be speaking at TechFuseMN on Thursday May 17th.

Here are links to a couple of the SCCM 2012 tools that I will be using.

Right Click Tools – http://sms-hints-tricks.blogspot.com/2011/08/configmgr-2012-console-extensions.html

More Right Click Tools (since I have no idea how to get the above one’s to work!) – http://eskonr.com/2012/05/sccm-2012-right-click-tools/

RegKeyToMOF – http://myitforum.com/cs2/files/folders/proddocs/entry152945.aspx

Config Manager 2012 Technet – http://technet.microsoft.com/en-us/library/gg682129

Config Manager 2012 Virtual Labs – http://technet.microsoft.com/en-us/systemcenter/bb539977

MMS2012 – FREE Content – http://www.mms-2012.com/

April 20, 2012

Powershell–Schedule a SCCM Advertisement

One of the business units I work with needed to deploy an application using SCCM. So far so good right? Then they said that they only wanted it to run between 23:00 and 06:00. So now you are thinking about putting a Maintenance Window on the collection to accomplish the time restriction requirement right? Well, what about all of the other distributions (like Software Updates)? They wouldn’t run on the machines that are in the collection with the Maintenance Window on it. I guess we could go into each and every deployment and flag it to ignore maintenance windows, but that would cause even more headaches.

With a little help from PowerShell and SQL we can change the advertisement properties. Specifically we need to change the following:

Advertisement Start Time
Advertisement Expires
Mandatory Assignments

Example of what we need to accomplish:

Today the Advertisement has the following settings

Advertisement Start Time – 4/20/2012 23:00.
Advertisement Expires – 4/21/2012 06:00
Mandatory Assignments – 4/20/2012 23:00

Tomorrow the Advertisement will have the following settings

Advertisement Start Time – 4/21/2012 23:00.
Advertisement Expires – 4/22/2012 06:00
Mandatory Assignments – 4/21/2012 23:00

Well, I could go into the properties of the advertisement and change them, but that isn’t my style.

What I did was write a PowerShell script to change the above times and then created a SQL Job to run the PowerShell script on a schedule.

PowerShell Script:

Param([String[]] $AdvertisementID)

$SCCM_SERVER = "[server name without the brackets]"
$SCCM_SITECODE = "[three letter site code without brackets]"
#$AdvertisementID = "[AdvertisementID without the brackets]"

#Foreach ($AdvertisementID in $AdvertisementIDs)
#	{
	$AdvertisementSettings = ([WMIClass] "\\$SCCM_SERVER\root\SMS\site_$($SCCM_SITECODE):SMS_Advertisement").CreateInstance()
	$AdvertisementSettings.AdvertisementID = $AdvertisementID
	#Get lazy properties
	$AdvertisementSettings.Get()
	#Build the Scheduled Time
#	$NewDate = (Get-Date).adddays(1)
	$year = [string](Get-Date).Year
	$month = [String](Get-Date).Month
	if ($month.Length -eq 1) {$month = "0" + $month}
	$day = [string](Get-Date).Day
	if ($day.Length -eq 1) {$day = "0" + $day}
	$ScheduledTime = $year + $month + $day + "230000.000000+***"
	#Build the Expiration Time
	$ExpireDate = (Get-Date).AddDays(1)
	$day = [string]$ExpireDate.Day
	if ($day.Length -eq 1) {$day = "0" + $day}
	$ExpirationTime = $year + $month + $day + "060000.000000+***"	
	#Apply the settings to the advertisement
	$AdvertisementSettings.PresentTime = $ScheduledTime
	$AdvertisementSettings.ExpirationTime = $ExpirationTime
	$AdvertisementSettings.ExpirationTimeEnabled = $true
	$AdvertisementSettings.put()

	$AssignedSchedule = ([WMIClass] "\\$SCCM_SERVER\root\SMS\site_$($SCCM_SITECODE):SMS_ST_NonRecurring").CreateInstance() 
  $AssignedSchedule.StartTime = $ScheduledTime
  $AdvertisementSettings.AssignedSchedule = $AssignedSchedule
  $AdvertisementSettings.AssignedScheduleEnabled = $true
  $AdvertisementSettings.put() 
#	}

Then we need to create a SQL Job to run this PowerShell script nightly:

-- USE [msdb]
GO

/****** Object:  Job [Job Name Here.  Keep the brackets]    Script Date: 04/20/2012 14:34:08 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 04/20/2012 14:34:08 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Job name inside the ticks', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'[Domain\UserName without the brackets]', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CWT - Express 1.2 Advertisement]    Script Date: 04/20/2012 14:34:09 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Job step name inside the ticks', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'CmdExec', 
		@command=N'C:\WINDOWS\system32\windowspowershell\v1.0\powershell.exe -command "& ''I:\SCCMScripts\SCCM Powershell Production Scripts\Set-Advertisement.ps1'' -AdvertisementID ''[AdvertisementID without the brackets]''"', 
		@flags=0, 
		@proxy_name=N'[proxy name so that the job can execute]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Job name inside the ticks', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20120419, 
		@active_end_date=20120503, 
		@active_start_time=220000, 
		@active_end_time=235959, 
		@schedule_uid=N'2fd7b8b0-f749-4022-a806-3410372c6f4e'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

April 18, 2012

Moving the SMSSIG$ and SMSPKGSIG folders in SCCM

Just by reading the title of this post you can rest assured that the s**t hit the fan on one of my SCCM Primary Sites today! One of our drives was almost full and it turned out that the SMSSIG$ folder was the culprit.

First you should ask yourself, how did this happen? How did I get here? Same as it ever was. Ahh….I digress.

Well, for starters you should have the No_SMS_on_drive.sms file in the root of all drives that you don’t want SCCM to use. For Distribution Points SCCM uses the drive with the most amount of free space. Once that drive fills up it will use the next drive with the most freespace. I am not sure that was my issue since I did have that file in the root of the drive that was filling up. But, I certainly could have put it there after the SMSSIG$ folder was created.

The next step is to ensure that the Site Settings > Component Configuration > Software Distribution > Location of stored packages has the drive letter of the drive you want the SMSSIG$ folder on. In my case it was set to Z:\. I don’t have a Z: drive. At this point you are probably asking yourself “Hey Matthew, what kind of shop are you running there?”. I don’t know really! But, I do know how to fix the issue.

First I Googled “moving SMSSIG$ folder” and came across this article: http://www.myitforum.com/articles/1/view.asp?id=12833

You will notice that the article states that the SQL Update command has to be run for each packageID. Well, I had over 300, so like, um….yeah…that wasn’t happening.

Enter our best friend PowerShell. Here is the PowerShell script based on the article above. You will see that I am changing the drive from I$ to H$. Note: read the article above, don’t just execute the PowerShell code. The article states that this should be done on all of the parents of the site in question.

Add-PSSnapin SqlServerCmdletSnapin100
$SQLCMD = Invoke-Sqlcmd "SELECT * FROM PkgStatus WHERE Sitecode = 'p01' and Type = 1 and location like '%i$%'"
foreach ($i in $SQLCMD)
	{
	$NewLocation = $i.location.replace("I$", "H$")
	$NewLocation = "'" + $NewLocation + "'"
	$i.ID = "'" + $i.ID + "'"
#	$i.ID
#	$NewLocation
	$NewSQLCMD = "UPDATE PkgStatus SET Location = $NewLocation WHERE Sitecode = 'P01' and Type = 1 and ID = " + $i.ID
	$NewSQLCMD
	Invoke-Sqlcmd $NewSQLCMD
	}

Then I figured I better move the SMSPKGSIG folder as well. Instructions here: http://www.myitforum.com/articles/1/view.asp?id=11981

I followed all of this up by creating a package and sending it to the DP in question and all is well.

January 18, 2012

Patch Compliance Calendar

I received a request a few weeks ago to create a report that had one row for each server and then one column per month / year. In the column would be an asterisks if the server is compliant for all that patches for that month (meaning, all that months patches are installed).

Well, after a ton of work I came up with something that meets their requirements. However, the tables that the report creates takes around 90 minutes to complete for around 750 servers!! So, I would recommend scheduling a job to run at night.

-- *** Starting with [Your Company Name]_ServerPatchStatus ***

-- Create a temp table to hold the ResourceID and machinename
Create table #ResourceID (ResourceID int, name varchar(64))
Insert into #ResourceID (ResourceID, name)
select resourceID, name from dbo.v_FullCollectionMembership where CollectionID = '[Enter the collectionID that you deploy patches to]'
--select * from #ResourceID
--drop table #ResourceID

-- Create a table to dump the results of the query to
drop table [Your Company Name]_ServerPatchStatus
create table [Your Company Name]_ServerPatchStatus (
    MachineName varchar(255), 
    BulletinID varchar(64),
    ArticleID varchar(64),
    Title varchar(512),
    DatePosted datetime,
    DateRevised datetime,
    Targeted varchar(1),
    Installed varchar(1),
    IsRequired varchar(1),
    Notrequired varchar(1),
    LastBootUpTime datetime,
    SCCM_Last_Hardware_Inventory datetime,
    Last_WSUS_Scan datetime,
    WSUSscan_State varchar(255),
    ErrorStatusID int,
    ErrorCode int,
    HexErrorCode nvarchar(10),
    MaintenanceWindowName varchar(200), 
    Description varchar(512),
    StartTime datetime, 
    DurationMinutes int, 
    MaintenanceWindowEnabled bit)
    
Insert into [Your Company Name]_ServerPatchStatus (
    MachineName, 
    BulletinID,
    ArticleID,
    Title,
    DatePosted,
    DateRevised,
    Targeted,
    Installed,
    IsRequired,
    Notrequired,
    LastBootUpTime,
    SCCM_Last_Hardware_Inventory,
    Last_WSUS_Scan,
    WSUSscan_State,
    ErrorStatusID,
    ErrorCode,
    HexErrorCode,
    MaintenanceWindowName, 
    Description,
    StartTime, 
    DurationMinutes, 
    MaintenanceWindowEnabled)
    
select distinct
            #ResourceID.name as 'MachineName',
            ui.BulletinID as BulletinID,
            ui.ArticleID as ArticleID,
            ui.Title as Title,      
            ui.dateposted,
            ui.daterevised,  
            Targeted=(case when ctm.ResourceID is not null then '*' else '' end),
            Installed=(case when css.Status=3 then '*' else '' end),
            IsRequired=(case when css.Status=2 then '*' else '' end),
            NotRequired= (case when css.Status=1 then '*' else '' end),
            os.LastBootUpTime0 as 'LastBootUpTime',
            ws.LastHWScan as 'SCCM_Last_Hardware_Inventory',
            Dateadd(hour,(datediff(hour,getutcdate(),getdate())),uss.lastscantime) as 'Last_WSUS_Scan'
            , scan.StateName 'WSUSscan_State'
            , scan.ErrorStatusID
            , scan.ErrorCode
            , scan.HexErrorCode,
            maint.MaintenanceWindowName as 'MaintenanceWindowName', maint.Description, maint.StartTime, maint.DurationMinutes as 'DurationMinutes', maint.Enabled as 'CEPSWEnabled'
from v_Update_ComplianceStatusall css
join #ResourceID on css.ResourceID = #ResourceID.ResourceID
join v_UpdateInfo ui on ui.CI_ID=css.CI_ID
join v_CIRelation cir on cir.ToCIID = ui.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 = #ResourceID.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
left join (
            select fcm.resourceid, sw.Name as 'MaintenanceWindowName', sw.Description, sw.StartTime, sw.Duration as 'DurationMinutes', sw.IsEnabled as 'Enabled',case when sw.RecurrenceType = 1 then 'No Recurrence'
            when sw.RecurrenceType = 2 then 'Daily'
            when sw.RecurrenceType = 3 then 'Weekly'
            when sw.RecurrenceType = 4 then 'Monthly'
            else 'Unkwnon' end as RecurrenceType, case when sw.ServiceWindowType=5 then '*' else ' ' end as 'OSDServiceWindow'
            , col.name as 'CollectionName'
            , col.CollectionID
            from v_ServiceWindow sw
            join v_FullCollectionMembership fcm on sw.CollectionID = fcm.CollectionID
            join v_Collection col
            on sw.collectionid = col.collectionid
            where sw.Name <> 'NO RUN'
            ) Maint on maint.ResourceID = css.ResourceID
left join     v_GS_WORKSTATION_STATUS ws on css.ResourceID = ws.ResourceID
left join     v_UpdateScanStatus uss on css.ResourceID = uss.ResourceID
left join   v_GS_Operating_System os on css.ResourceID = os.resourceid
left join
(
select uss.ResourceID, SN.StateName, uss.LastStatusMessageID&0x0000FFFF as ErrorStatusID,
    isnull(uss.LastErrorCode,0) as ErrorCode,
    dbo.fnConvertBinaryToHexString(convert(VARBINARY(8), isnull(uss.LastErrorCode,0))) as HexErrorCode
 from v_UpdateScanStatus uss
    join v_R_System rsys on rsys.ResourceID = uss.ResourceID and isnull(rsys.Obsolete0,0)<>1
    join v_SoftwareUpdateSource sus on uss.UpdateSource_ID = sus.UpdateSource_ID 
    join v_RA_System_SMSAssignedSites sass on uss.ResourceID = sass.ResourceID
    join v_StateNames sn on sn.TopicType = 501 and 
        sn.StateID = (case when (isnull(uss.LastScanState, 0)=0 and Left(isnull(rsys.Client_Version0, '4.0'), 1)<'4') then 7 else isnull(uss.LastScanState, 0) end)
where 1= 1
    and (sus.UpdateSource_UniqueID = '{BE893E98-BD57-4E80-93F6-5D38B61C6069}')
) scan
on css.ResourceID = scan.ResourceID 
where  css.ResourceID = #ResourceID.ResourceID
and (css.Status=2 or css.Status=3 or css.Status=1)
and cir.FromCIID in 
(
select CI_ID
from v_AuthListInfo where Title like '%[enter the name of the update list(s) that you are inquiring about]%'
)
and cir.RelationType=1

drop table #ResourceID

-- *** Done with [Your Company Name]_ServerPatchStatus ***

-- *** Starting [Your Company Name]_ServerPatchComplianceCalender ***

-- Create a tempory table to hold the distinct months from the [Your Company Name]_ServerPatchStatus table
Create table #Month (ID int identity(1,1), Month int)
Insert into #Month (Month)
select distinct datepart(month,DatePosted)
from [Your Company Name]_ServerPatchStatus
order by datepart(month,DatePosted)
-- Select * from #Month
-- drop table #Month

-- Create a tempory table to hold the distinct years from the [Your Company Name]_ServerPatchStatus table
Create table #Year (ID int identity(1,1), Year int)
Insert into #Year (Year)
select distinct datepart(year,DatePosted)
from [Your Company Name]_ServerPatchStatus
order by datepart(year,DatePosted)
-- Select * from #Year
-- drop table #Year

drop table [Your Company Name]_ServerPatchComplianceCalender  --  This is the calender table that needs to be recreated each time this job runs
create table [Your Company Name]_ServerPatchComplianceCalender (Machinename varchar(255), Last_WSUS_Scan datetime, WSUSscan_State varchar(255))
-- select * from [Your Company Name]_ServerPatchComplianceCalender
-- drop table [Your Company Name]_ServerPatchComplianceCalender

-- Create a tempory table to hold the distinct machines from the [Your Company Name]_ServerPatchStatus table
Create table #Machines (ID int identity(1,1), MachineName varchar(255), Last_WSUS_Scan datetime, WSUSscan_State varchar(255))
Insert into #Machines (MachineName, Last_WSUS_Scan, WSUSscan_State)
select distinct machinename, Last_WSUS_Scan, WSUSscan_State
from [Your Company Name]_ServerPatchStatus
order by machinename
-- select * from #Machines
-- drop table #Machines 

-- Create a variable to hold the minimum month.  This is used so that the While loop knows when to end.  Note, the table is in reverse chronological order
DECLARE @MinMonth int
SET @MinMonth = (Select MIN(ID) FROM #Month)
-- Select @MinMonth

-- Create a variable to iterate through the months
DECLARE @IterMonth int
SET @IterMonth = (SELECT MAX(ID) FROM #Month)
-- Select @IterMonth

-- Create a variable to hold the maximum year.  This is used so that the While loop knows when to end.  Note, the table is in reverse chronological order
DECLARE @MinYear int
SET @MinYear = (Select MIN(ID) FROM #Year)
-- Select @MinYear

-- Create a variable to iterate through the years
DECLARE @IterYear int
SET @IterYear = (SELECT MAX(ID) FROM #Year)
-- Select @IterYear

-- Create a variable to hold the maximum machine count.  This is used so that the While loop knows when to end
DECLARE @MaxMachines int
SET @MaxMachines = (SELECT MAX(ID) FROM #Machines)
-- select @MaxRownum

-- Create a variable to iterate through the months
DECLARE @IterMachines int
SET @IterMachines = (SELECT MIN(ID) FROM #Machines)
-- select @IterMachines

-- Build the columns of the table
WHILE @IterYear >= @MinYear
BEGIN
    DECLARE @Year int
    set @Year = (Select YEAR from #Year where ID = @IterYear)
    -- Start a new loop with one year and one month at a time
    WHILE @IterMonth >= @MinMonth
    BEGIN
        DECLARE @Month int
        Set @Month = (Select Month from #Month where ID = @IterMonth)
        DECLARE @MonthYear varchar(20)
        Set @MonthYear = convert(varchar,@Month) + '_' + convert(varchar,@Year)
        SET @MonthYear = '[' + @MonthYear + ']'
        DECLARE @SQL VARCHAR(MAX)
        -- Build the [Your Company Name]_ServerPatchComplianceCalender by adding the current month and year as a column name
        IF @Year < DATEPART(YEAR,GETDATE()) or (@Year = DATEPART(year,Getdate()) and @Month = DATEPART(month,Getdate()))
            SET @SQL = 'ALTER TABLE [Your Company Name]_ServerPatchComplianceCalender ADD ' + @MonthYear + ' Varchar'
            -- Select @SQL
            EXEC(@SQL)
        Set @IterMonth = @IterMonth - 1
        --select 'This is IterMonth - ' + convert(varchar,@IterMonth)
    end
    --select 'This is IterYear - ' + convert(varchar,@IterYear)
    SET @IterMonth = (SELECT MAX(ID) FROM #Month)
    Set @IterYear = @IterYear - 1
end

SET @IterMonth = (SELECT MAX(ID) FROM #Month)
SET @IterYear = (SELECT MAX(ID) FROM #Year)
SET @SQL = ''

-- Start the loop with one machine
WHILE @IterMachines <= @MaxMachines
BEGIN
    DECLARE @MachineName varchar(255)
    set @MachineName = (SELECT machinename FROM #Machines WHERE ID = @IterMachines)
    --select @MachineName
    DECLARE @Last_WSUS_Scan datetime
    set @Last_WSUS_Scan = (SELECT Last_WSUS_Scan from #Machines where ID = @IterMachines)
    --select @Last_WSUS_Scan
    DECLARE @WSUSscan_State varchar(255)
    set @WSUSscan_State = (SELECT WSUSscan_State from #Machines where ID = @IterMachines)
    --select @WSUSscan_State
    insert into [Your Company Name]_ServerPatchComplianceCalender (Machinename, Last_WSUS_Scan, WSUSscan_State)
    Values (@MachineName, @Last_WSUS_Scan, @WSUSscan_State)
    -- Start a new loop with the one machine and one year at a time
    WHILE @IterYear >= @MinYear
    BEGIN
        set @Year = (Select YEAR from #Year where ID = @IterYear)
        -- Start a new loop with the one machine,one year and one month at a time
        WHILE @IterMonth >= @MinMonth
        BEGIN
            Set @Month = (Select Month from #Month where ID = @IterMonth)
            Set @MonthYear = convert(varchar,@Month) + '_' + convert(varchar,@Year)
            SET @MonthYear = '[' + @MonthYear + ']'
            --Select @MonthYear 
            -- If the machinename has at least one required patch for the given month and year then the column in the table will remain NULL
            IF @Year < DATEPART(YEAR,GETDATE()) or (@Year = DATEPART(year,Getdate()) and @Month = DATEPART(month,Getdate()))
                --SET @SQL = 'update [Your Company Name]_ServerPatchComplianceCalender set ' + @MonthYear + ' = ''*'' where Machinename = ''' 
                --    + @MachineName + ''' and (' + cast(@year as varchar(4)) + ' < datepart(year,getdate()) OR ( ' + cast(@month as varchar(2)) + ' = datepart(month,getdate()) and ' + cast(@year as varchar(4)) + ' = datepart(year,getdate()))) and Machinename not in (select machinename from [Your Company Name]_ServerPatchStatus where datepart(month,DatePosted) = ' 
                --    + CAST(@Month as varchar(2)) + ' and datepart(year,dateposted)= ' + CAST(@Year as varchar(4)) + ' and isrequired = ''*''    and machinename = ''' + @MachineName + ''' )'
                SET @SQL = 'update [Your Company Name]_ServerPatchComplianceCalender set ' + @MonthYear + ' = ''*'' where Machinename = ''' 
                    + @MachineName + ''' and Machinename not in (select machinename from [Your Company Name]_ServerPatchStatus where datepart(month,DatePosted) = ' 
                    + CAST(@Month as varchar(2)) + ' and datepart(year,dateposted)= ' + CAST(@Year as varchar(4)) + ' and isrequired = ''*''    and machinename = ''' + @MachineName + ''' )'    
        -- Select @SQL
                EXEC(@SQL)
            Set @IterMonth = @IterMonth - 1
            --select 'This is IterMonth - ' + convert(varchar,@IterMonth)
        end
        --select 'This is IterYear - ' + convert(varchar,@IterYear)
        SET @IterMonth = (SELECT MAX(ID) FROM #Month)
        Set @IterYear = @IterYear - 1
    end
    SET @IterYear = (SELECT MAX(ID) FROM #Year)
    SET @IterMachines = @IterMachines + 1
END

drop table #Year
drop table #Month
drop table #Machines

-- *** done with [Your Company Name]_ServerPatchComplianceCalender ***

January 18, 2012

Software Update Reports

Do you ever use the Software Updates reports that are built into SCCM? Have you ever wanted a report that details information about patch compliance on a particular collection of machines?

List Of All Updates

This report will list the following information:

Bulletin ID – example MS12-006
Article ID – example 2638806
Date Posted
Date Revised
Title
Infromation URL
Expired – yes or no
Superseded – yes or no
IsDeployed – yes or no

The report will have five prompts

Enter a month – example 10 for October or % for ALL
Enter a year – example 2012 or % for ALL
Enter an ArticleID – example 2638806 or % for ALL
Enter an Update Title – Example %Windows 7% for all Windows 7 related patches
Enter a BulletinID – Example MS12-006 or % for ALL

This report does not link to any other reports

Here is the report. You will need to do the following:

Copy the contents of the report into Notepad
Chose File > Save As > Save as type “All Files”
Chose Unicode for the encoding type
Save the file with a .MOF extension

// *********************************************************************************
//
//		Created by SMS Export object wizard
//
//		Tuesday, January 17, 2012 created
//
//		File Name: List of all updates.MOF
//
// Comments :
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
	Category = "Software Updates";
	Comment = "This report lists the updates that were deployed for a given month and year.";
	GraphCaption = "";
	GraphXCol = 1;
	GraphYCol = 2;
	MachineDetail = FALSE;
	MachineSource = FALSE;
	Name = "* List of all updates *";
	NumPrompts = 5;
	RefreshInterval = 0;
	ReportGUID = "{11EA627B-6499-4C60-9D4F-C980E30B630F}";
	
	ReportParams = {
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Enter a month";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n   select distinct DATEPART(month,ui.DatePosted) as Month
\n   from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   order by DATEPART(month,ui.DatePosted)
\n else
\n   select distinct DATEPART(month,ui.DatePosted) as Month from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   where DATEPART(month,ui.DatePosted) like @__filterwildcard
\n   order by DATEPART(month,ui.DatePosted)
\nend";
	VariableName = "Month";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Enter a year";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select distinct DATEPART(year,ui.DatePosted) as Year from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   order by DATEPART(year,ui.DatePosted)
\n else
\n  select distinct DATEPART(year,ui.DatePosted) as Year from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   where DATEPART(year,ui.DatePosted) like @__filterwildcard
\n  order by DATEPART(year,ui.DatePosted)
\nend";
	VariableName = "Year";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Enter an ArticleID or % for all.  Example:  987654";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n   select distinct ui.articleID
\n   from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   order by ui.articleID
\n else
\n   select distinct ui.articleID
\n   from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   where ui.articleID like @__filterwildcard
\n   order by ui.articleID
\nend";
	VariableName = "articleID";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Enter an update title or % for all.  Example:  %microsoft%";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n   select distinct ui.title
\n   from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   order by ui.title
\n else
\n   select distinct ui.title
\n   from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   where ui.title like @__filterwildcard
\n   order by ui.title
\nend";
	VariableName = "title";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Enter a BulletinID or % for all.  Example:  MSxx-xxx";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n   select distinct ui.BulletinID
\n   from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   order by ui.BulletinID
\n else
\n   select distinct ui.BulletinID
\n   from v_CIRelation cir 
\n   join  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\n   where ui.BulletinID like @__filterwildcard
\n   order by ui.BulletinID
\nend";
	VariableName = "BulletinID";
}};
	SecurityKey = "";
	SQLQuery = "select ui.BulletinID, ui.ArticleID, ui.DatePosted, ui.DateRevised, ui.title, ui.infoURL,
\n\tExpired = case when ui.Isexpired = 0 then 'NO' else 'YES' end,
\n\tSuperseded = case when ui.isSuperseded = 0 then 'NO' else 'YES' end,
\n\tIsDeployed = case when ui.IsDeployed = 0 then 'NO' else 'YES' end
\nfrom v_UpdateInfo ui
\nwhere  (datepart(month, ui.DatePosted) like @Month and datepart(year,ui.DatePosted) like @Year
\nor
\ndatepart(month, ui.DateRevised) like @Month and datepart(year,ui.DateRevised) like @Year )
\nand ui.articleID like @articleID
\nand ui.title like @title
\nand ui.BulletinID like @BulletinID
\norder by ui.bulletinID desc, ui.articleID desc";
	StatusMessageDetailSource = FALSE;
	UnicodeData = FALSE;
	XColLabel = "";
	YColLabel = "";
};
// ***** End *****

Overall Software Update Status

This report will display the patch status for a particular collection of machines.

This report will list the following information

CollectionID
Business Unit – the name of the collection
Venor
Article ID – example 2638806
Bulletin ID – example MS12-006
Date Posted
Date Revised
Title
Expired – yes or no
Superseded – yes or no
IsDeployed – yes or no
Approved – yes or no
Installed – quantity of the machines in the collection that have installed the patch
Required – quantity of the machines in the collection that require the patch
Not Required – quantity of the machines in the collection that do not require the patch
Unknown – quantity of the machines in the collection that have a software update status of unknown for the patch
Total – total number of machines in the collection
%Compliant
%Not Compliant
%Unknown
Update ID – this is used for links to other reports.

The report will have two prompts

Select a Business Unit by clicking on the values button or enter % for ALL
Enter YES for patches that are being pushed, NO for ALL

This report will link to the “Specific Software Updates States” report

UpdateID – Column 21
CollectionID – Column 1

Here is the report. You will need to do the following:

Copy the contents of the report into Notepad
Chose File > Save As > Save as type “All Files”
Chose Unicode for the encoding type
Save the file with a .MOF extension

// *********************************************************************************
//
//		Created by SMS Export object wizard
//
//		Wednesday, January 18, 2012 created
//
//		File Name: Overall Software Update Status.MOF
//
// Comments :
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
	Category = "Software Updates";
	Comment = "This report displays all updates for a particular buisness unit and  the percentage of compliant machines.";
	DrillThroughColumns = {};


	GraphCaption = "";
	GraphXCol = 1;
	GraphYCol = 2;
	MachineDetail = FALSE;
	MachineSource = FALSE;
	Name = "Overall Software Update Status";
	NumPrompts = 2;
	RefreshInterval = 0;
	ReportGUID = "{F18FAD6D-0BDD-4697-82C8-D96F6FF39922}";
	
	ReportParams = {
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Select a Buisness Unit by clicking the values button";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  where b.parentcollectionID = '[Enter the parent collection ID of your business unit collections.  Example:  Create an empty collection called "Business Units".  Then create multiple sub collections that represent your various business units]' and a.name not like '%No Client%'
\n  order by a.Name
\n else
\n  select a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  WHERE a.CollectionID like @__filterwildcard
\n  order by a.Name
\nend";
	VariableName = "CollectionID";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Enter YES for patches that are being pushed.  NO for all";
	SampleValueSQL = "create table #temp (Choice char(3))
\ninsert into #temp (choice)
\nselect 'yes'
\ninsert into #temp (choice)
\nselect 'no'
\n
\nselect choice from #temp
\n";
	VariableName = "IsPushed";
}};
	SecurityKey = "";
	SQLQuery = "if @ISpushed = 'yes'
\nselect distinct us.CollectionID,
\nus.CollectionName as 'Business Unit',
\ncatinfo.CategoryInstanceName as Vendor0,
\n\tui.ArticleID as ArticleID,
\n\tui.BulletinID as BulletinID,
\nui.DatePosted,
\n\tui.DateRevised,
\n\tui.Title as Title,
\n\tExpired = case when ui.Isexpired = 0 then 'NO' else 'YES' end,
\n\tSuperseded = case when ui.isSuperseded = 0 then 'NO' else 'YES' end,
\n\tIsDeployed = case when ui.IsDeployed = 0 then 'NO' else 'YES' end,
\n\tApproved = case when col.CollectionID is not null then 'YES' else 'NO' end,\t
\n\tus.NumPresent as Present,
\n\tus.NumMissing as Missing,
\n\tus.NumNotApplicable as NotApplicable, 
\n\tus.NumUnknown as Unknown,
\n\tus.NumTotal as Total,
\n\tPCompliant=case when NumTotal<>0 then CAST((NumPresent+NumNotApplicable)*100.0/NumTotal as numeric(5,2)) else 0.0 end,
\n\tPNotCompliant=case when NumTotal<>0 then CAST((NumMissing)*100.0/NumTotal as numeric(5,2)) else 0.0 end, 
\n\tPUnknown=case when NumTotal<>0 then CAST((NumUnknown)*100.0/NumTotal as numeric(5,2)) else 0.0 end,
\nUniqueUpdateID=ui.CI_UniqueID
\nfrom v_CIRelation cir 
\njoin  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\njoin (v_CICategories_All catall join v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company') 
\n\ton catall.CI_ID=ui.CI_ID
\n--left join v_CITargetedCollections col on col.CI_ID=ui.CI_ID and col.CollectionID='[enter the collectionID of the collection that you target software updates to]'
\n--join v_UpdateSummaryPerCollection us on us.CI_ID=ui.CI_ID and us.CollectionID='[enter the collectionID of the collection that you target software updates to]'
\nleft join v_CITargetedCollections col on col.CI_ID=ui.CI_ID and col.CollectionID in (
\n\tselect a.collectionID
\n\tfrom v_collection a
\n\tjoin v_CollectToSubCollect b
\n\ton a.collectionID = b.subcollectionid
\n\twhere b.parentcollectionID = '[Enter the parent collection ID of your business unit collections.  Example:  Create an empty collection called "Business Units".  Then create multiple sub collections that represent your various business units]' )
\njoin v_UpdateSummaryPerCollection us on us.CI_ID=ui.CI_ID and us.CollectionID = @CollectionID
\n--where cir.FromCIID=@AuthListLocalID and cir.RelationType=1
\nwhere cir.FromCIID in 
\n(
\nselect CI_ID
\nfrom v_AuthListInfo where Title like '%[Enter the portion of the title of the update lists you want displayed. Example:  if you had update lists titled "Servers - 2010", "Servers 2011", etc you could just enter %Servers%]%'
\n)
\nand cir.RelationType=1
\nand IsDeployed = 1
\norder by bulletinID desc, articleID desc
\nelse
\nselect distinct us.CollectionID,
\nus.CollectionName as 'Business Unit',
\ncatinfo.CategoryInstanceName as Vendor0,
\n\tui.ArticleID as ArticleID,
\n\tui.BulletinID as BulletinID,
\nui.DatePosted,
\n\tui.DateRevised,
\n\tui.Title as Title,
\n\tExpired = case when ui.Isexpired = 0 then 'NO' else 'YES' end,
\n\tSuperseded = case when ui.isSuperseded = 0 then 'NO' else 'YES' end,
\n\tIsDeployed = case when ui.IsDeployed = 0 then 'NO' else 'YES' end,
\n\tApproved = case when col.CollectionID is not null then 'YES' else 'NO' end,\t
\n\tus.NumPresent as Present,
\n\tus.NumMissing as Missing,
\n\tus.NumNotApplicable as NotApplicable, 
\n\tus.NumUnknown as Unknown,
\n\tus.NumTotal as Total,
\n\tPCompliant=case when NumTotal<>0 then CAST((NumPresent+NumNotApplicable)*100.0/NumTotal as numeric(5,2)) else 0.0 end,
\n\tPNotCompliant=case when NumTotal<>0 then CAST((NumMissing)*100.0/NumTotal as numeric(5,2)) else 0.0 end, 
\n\tPUnknown=case when NumTotal<>0 then CAST((NumUnknown)*100.0/NumTotal as numeric(5,2)) else 0.0 end,
\nUniqueUpdateID=ui.CI_UniqueID
\nfrom v_CIRelation cir 
\njoin  v_UpdateInfo ui on cir.ToCIID = ui.CI_ID
\njoin (v_CICategories_All catall join v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company') 
\n\ton catall.CI_ID=ui.CI_ID
\n--left join v_CITargetedCollections col on col.CI_ID=ui.CI_ID and col.CollectionID='[enter the collectionID of the collection that you target software updates to]'
\n--join v_UpdateSummaryPerCollection us on us.CI_ID=ui.CI_ID and us.CollectionID='[enter the collectionID of the collection that you target software updates to]'
\nleft join v_CITargetedCollections col on col.CI_ID=ui.CI_ID and col.CollectionID in (
\n\tselect a.collectionID
\n\tfrom v_collection a
\n\tjoin v_CollectToSubCollect b
\n\ton a.collectionID = b.subcollectionid
\n\twhere b.parentcollectionID = '[enter the collectionID of the collection that you target software updates to]' )
\njoin v_UpdateSummaryPerCollection us on us.CI_ID=ui.CI_ID and us.CollectionID = @CollectionID
\n--where cir.FromCIID=@AuthListLocalID and cir.RelationType=1
\nwhere 1=1
\norder by bulletinID desc, articleID desc";
	StatusMessageDetailSource = FALSE;
	UnicodeData = FALSE;
	XColLabel = "";
	YColLabel = "";
};
// ***** End *****

Specific Software Updates States

This report will show the various states of a particular update for a certain collection.

This report will list the following information:

CollectionID
Business Unit – the name of the collection
State Name – Required, installed, unknown or not required
Count – The count of update states for the collection
%Total – percent of update states for the collection
UpdateID – this is used for linking to other reports

The report will have the following prompts

UpdateID – ie: MS12-006
CollectionID – The collection ID of a particular business unit

The report will link to the “Computers in a specific compliance state for an update” report

UpdateID – column 6
Status – column 3
CollectionID – column 1

Here is the report. You will need to do the following:

Copy the contents of the report into Notepad
Chose File > Save As > Save as type “All Files”
Chose Unicode for the encoding type
Save the file with a .MOF extension

// *********************************************************************************
//
//		Created by SMS Export object wizard
//
//		Wednesday, January 18, 2012 created
//
//		File Name: Specific software updates states.MOF
//
// Comments :
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
	Category = "Software Updates";
	Comment = "This report returns the count and percentage of computers in each compliance state for the specified software update.";
	DrillThroughColumns = {};


	GraphCaption = "";
	GraphXCol = 1;
	GraphYCol = 2;
	MachineDetail = FALSE;
	MachineSource = FALSE;
	Name = "Specific software updates states";
	NumPrompts = 2;
	RefreshInterval = 0;
	ReportGUID = "{7197C921-F2D4-4D59-A838-56B9868EF503}";
	
	ReportParams = {
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Update ID (Required)";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select distinct CI_UniqueID, ArticleID, BulletinID, Title from v_UpdateInfo
\n\twhere title not like '%server%'
\n\torder by BulletinID desc, ArticleID desc
\n else
\n  select distinct CI_UniqueID, ArticleID, BulletinID, Title from v_UpdateInfo
\n  WHERE (CI_UniqueID like @__filterwildcard
\n OR Title like @__filterwildcard
\n OR BulletinID like @__filterwildcard
\n OR ArticleID like @__filterwildcard)
\nand title not like '%server%'
\n  order by BulletinID desc, ArticleID desc
\nend";
	VariableName = "UpdateID";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Enter a collection ID";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  where b.parentcollectionID = '[Enter the parent collection ID of your business unit collections.  Example:  Create an empty collection called "Business Units".  Then create multiple sub collections that represent your various business units]' and a.name not like '%No Client%'
\n  order by a.Name
\n else
\n  select a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  WHERE a.CollectionID like @__filterwildcard
\n  order by a.Name
\nend";
	VariableName = "CollectionID";
}};
	SecurityKey = "";
	SQLQuery = "declare @Unknown as varchar (256)
\ndeclare @NotRequired as varchar (256)
\ndeclare @Missing as varchar (256)
\ndeclare @Installed as varchar (256)
\ndeclare @Total as int
\n
\nselect @Unknown = StateName from v_StateNames where TopicType=500 and StateID=0
\nselect @NotRequired = StateName from v_StateNames where TopicType=500 and StateID=1
\nselect @Missing = StateName from v_StateNames where TopicType=500 and StateID=2
\nselect @Installed = StateName from v_StateNames where TopicType=500 and StateID=3
\n
\nselect @Total = NumTotal 
\n\tfrom v_UpdateSummaryPerCollection us
\n\tjoin v_Collection col on col.CollectionID=us.CollectionID
\n\tjoin v_UpdateInfo ui on us.CI_ID=ui.CI_ID
\nwhere col.CollectionID = @CollectionID
\nand (@UpdateID='' or ui.CI_UniqueID = @UpdateID)
\n
\nselect\tui.ArticleID as ArticleID, 
\n\tui.BulletinID as BulletinID,
\n\tui.Title as Title
\nfrom v_UpdateInfo ui where ui.CI_UniqueID = @UpdateID
\n
\nselect @Total as NumberInCollection
\n
\nselect 
\n         col.CollectionID as CollectionID,
\n\t\t col.Name as 'Business Unit',
\n         Status=case a.SetVariable 
\n\t\t\twhen 1 then @Unknown 
\n\t        when 2 then @NotRequired 
\n\t        when 3 then @Missing 
\n\t        else @Installed
\n\t        end,
\n         NumberOfComputers=case a.SetVariable 
\n\t\t\twhen 1 then NumUnknown 
\n\t  \t\twhen 2 then NumNotApplicable 
\n\t\t\twhen 3 then NumMissing
\n\t\t\telse NumPresent
\n \t\t    end,
\n         PComputers=case a.SetVariable 
\n\t\t\twhen 1 then convert(numeric(5,2), (isnull(NumUnknown, 0)*100.0) / isnull(nullif(@Total, 0), 1))
\n\t  \t\twhen 2 then convert(numeric(5,2), (isnull(NumNotApplicable, 0)*100.0) / isnull(nullif(@Total, 0), 1))
\n\t\t\twhen 3 then convert(numeric(5,2), (isnull(NumMissing, 0)*100.0) / isnull(nullif(@Total, 0), 1))
\n\t\t\telse convert(numeric(5,2), (isnull(NumPresent, 0)*100.0) / isnull(nullif(@Total, 0), 1))
\n \t\t    end,\t\t  
\n          UniqueUpdateID= ui.CI_UniqueID
\nfrom (select SetVariable=3 union all 
\n          select SetVariable=4 union all 
\n          select SetVariable=1 union all
\n          select SetVariable=2 )as a 
\ncross join v_UpdateSummaryPerCollection us 
\njoin v_Collection col on col.CollectionID=us.CollectionID
\njoin v_UpdateInfo ui on us.CI_ID=ui.CI_ID
\nwhere col.CollectionID = @CollectionID
\nand (@UpdateID='' or ui.CI_UniqueID = @UpdateID)";
	StatusMessageDetailSource = FALSE;
	UnicodeData = FALSE;
	XColLabel = "";
	YColLabel = "";
};
// ***** End *****

Computers in a specific compliance state for an update

This report lists the specific software update status for each machine in a collection

This report will list the following information:

Computer Name
AD Site
Business Unit – The name of the collection
Article ID – example 2585542
Bulletin ID – MS12-006
Title
Update Status
SCCM Last Hardwar Inventory – date
Last WSUS Scan – date
WSUSscan_State
Error Status ID – If the WSUSscan_State is not “scan completed” then this column will display the ErrorID for troubleshooting
Error Code – If the WSUSscan_State is not “scan completed” then this column will display the ErrorCode for troubleshooting
HexErrorCode – If the WSUSscan_State is not “scan completed” then this column will display the HexCode for troubleshooting
Last Logged On User
Assigned Site – SCCM Assigned Site
Client Version – SCCM Client Version

The report will have three prompts

UpdateID – example MS12-006
State Name – Example “Update is installed” or “Update is Required”
CollectionID – The collection ID of a particular business unit

The report will link to the “Compliance for a specific computer” report

Here is the report. You will need to do the following:

Copy the contents of the report into Notepad
Chose File > Save As > Save as type “All Files”
Chose Unicode for the encoding type
Save the file with a .MOF extension

// *********************************************************************************
//
//		Created by SMS Export object wizard
//
//		Wednesday, January 18, 2012 created
//
//		File Name: Computers in a specific compliance state for an update.MOF
//
// Comments :
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
	Category = "Software Updates";
	Comment = "This report returns all computers in a collection that have a specific compliance state for a software update.";
	DrillThroughColumns = {};


	GraphCaption = "";
	GraphXCol = 1;
	GraphYCol = 2;
	MachineDetail = FALSE;
	MachineSource = FALSE;
	Name = "Computers in a specific compliance state for an update";
	NumPrompts = 3;
	RefreshInterval = 0;
	ReportGUID = "{C5616815-4C02-430A-B132-F69580667844}";
	
	ReportParams = {
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Update ID (Required)";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select distinct CI_UniqueID as UniqueUpdateID, ArticleID, BulletinID, Title from v_UpdateInfo
\n  where title not like '%server%'
\n  order by BulletinID desc, ArticleID desc
\n else
\n  select distinct CI_UniqueID as UniqueUpdateID, ArticleID, BulletinID, Title from v_UpdateInfo
\n  WHERE (CI_UniqueID like @__filterwildcard
\n OR Title like @__filterwildcard
\n OR BulletinID like @__filterwildcard
\n OR ArticleID like @__filterwildcard)
\nand title not like '%server%'
\n  order by BulletinID desc, ArticleID desc
\nend";
	VariableName = "UpdateID";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "State Name (Required)";
	SampleValueSQL = "begin
\nif (@__filterwildcard = '')
\nselect StateName as State, StateID as StateID
\n\tfrom v_StateNames where TopicType=500
\nelse 
\nselect StateName as State, StateID as StateID
\n\tfrom v_StateNames where TopicType=500
\n\tand ((StateName like @__filterwildcard)
\n\tor (StateID like @__filterwildcard))
\nend";
	VariableName = "Status";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "CollectionID (required)";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  where (b.parentcollectionID = '[Enter the parent collection ID of your business unit collections.  Example:  Create an empty collection called "Business Units".  Then create multiple sub collections that represent your various business units]') and a.name not like '%No Client%'
\n  order by a.Name
\n else
\n  select a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  WHERE a.CollectionID like @__filterwildcard
\n  order by a.Name
\nend";
	VariableName = "CollectionID";
}};
	SecurityKey = "";
	SQLQuery = "declare @Unknown as varchar (256)
\ndeclare @NotRequired as varchar (256)
\ndeclare @Missing as varchar (256)
\ndeclare @Installed as varchar (256)
\n
\nselect @Unknown = StateName from v_StateNames where TopicType=500 and StateID=0
\nselect @NotRequired = StateName from v_StateNames where TopicType=500 and StateID=1
\nselect @Missing = StateName from v_StateNames where TopicType=500 and StateID=2
\nselect @Installed = StateName from v_StateNames where TopicType=500 and StateID=3
\n
\ndeclare @CIID int; select @CIID=CI_ID from v_UpdateInfo where CI_UniqueID=@UpdateID
\nselect 
\nm.Name0 as ComputerName0,
\nm.AD_Site_Name0 as 'AD Site',
\ncol.Name as 'Business Unit',
\nui.ArticleID as ArticleID, 
\nui.BulletinID as BulletinID,
\nui.Title as Title,
\n@Status as 'Update Status',
\n            ws.LastHWScan as 'SCCM Last Hardware Inventory',
\n            Dateadd(hour,(datediff(hour,getutcdate(),getdate())),uss.lastscantime) as 'Last WSUS Scan'
\n            , scan.StateName 'WSUSscan_State'
\n\t\t\t, scan.ErrorStatusID
\n\t\t\t, scan.ErrorCode
\n\t\t\t, scan.HexErrorCode,
\nm.User_Name0 as LastLoggedOnUser,
\nasite.SMS_Assigned_Sites0 as AssignedSite, 
\nm.Client_Version0 as ClientVersion,
\nDATEADD(ss,@__timezoneoffset,LastStatusCheckTime) as LastStateReceived, 
\nDATEADD(ss,@__timezoneoffset,LastStatusChangeTime) as LastStateChange,
\n''
\nfrom v_Update_ComplianceStatusAll ucs 
\njoin v_UpdateInfo ui on ucs.CI_ID=ui.CI_ID
\njoin v_ClientCollectionMembers cm on cm.ResourceID=ucs.ResourceID
\njoin v_collection col on col.collectionID = cm.collectionID
\njoin v_R_System m on m.ResourceType=5 and m.ResourceID=ucs.ResourceID and isnull(m.Obsolete0,0)<>1
\nleft join v_RA_System_SMSAssignedSites asite on m.ResourceID=asite.ResourceID
\nleft join \tv_GS_WORKSTATION_STATUS ws on cm.ResourceID = ws.ResourceID
\nleft join \tv_UpdateScanStatus uss on cm.ResourceID = uss.ResourceID
\nleft join
\n(
\nselect uss.ResourceID, SN.StateName, uss.LastStatusMessageID&0x0000FFFF as ErrorStatusID,
\n\tisnull(uss.LastErrorCode,0) as ErrorCode,
\n\tdbo.fnConvertBinaryToHexString(convert(VARBINARY(8), isnull(uss.LastErrorCode,0))) as HexErrorCode
\n from v_UpdateScanStatus uss
\n\tjoin v_R_System rsys on rsys.ResourceID = uss.ResourceID and isnull(rsys.Obsolete0,0)<>1
\n\tjoin v_SoftwareUpdateSource sus on uss.UpdateSource_ID = sus.UpdateSource_ID 
\n\tjoin v_RA_System_SMSAssignedSites sass on uss.ResourceID = sass.ResourceID
\n\tjoin v_StateNames sn on sn.TopicType = 501 and 
\n\t\tsn.StateID = (case when (isnull(uss.LastScanState, 0)=0 and Left(isnull(rsys.Client_Version0, '4.0'), 1)<'4') then 7 else isnull(uss.LastScanState, 0) end)
\nwhere 1= 1
\n\t--and (sass.SMS_Assigned_Sites0= 'P01')
\n\t--and (sn.StateName='Scan failed')
\n\tand (sus.UpdateSource_UniqueID = '{BE893E98-BD57-4E80-93F6-5D38B61C6069}')
\n) scan
\non cm.ResourceID = scan.ResourceID 
\nwhere ucs.CI_ID=@CIID
\nand cm.CollectionID  = @CollectionID
\nand ((@Status='')
\n\tor (@Status = @Unknown and ucs.Status=0) 
\n\tor (@Status = @NotRequired and ucs.Status=1)
\n\tor (@Status = @Missing and ucs.Status=2)
\n\tor (@Status = @Installed and ucs.Status=3))
\norder by m.Name0";
	StatusMessageDetailSource = FALSE;
	UnicodeData = FALSE;
	XColLabel = "";
	YColLabel = "";
};
// ***** End *****

Compliance for a specific computer

This report shows the patch status for a specific computer

This report will list the following information:

MachineName
Vendor
Update Class – example: security update, service pack, etc
BulletinID – example MS12-006
ArticleID – example 2526086
Title
Date Posted
Date Revised
Approved – this means that the patch is in an Update List
Installed – an asterisk indicates that the patch is installed
Is Required – an asterisk indicates that the patch is required
PatchInstallDate
Last State Change – date
Deadline – This is the deadline you set in the Deployment Management
SCCM Last Hardware Inventory – Date
Last WSUS Scan – date
WSUSscan_State
Error Status ID – If the WSUSscan_State is not “scan completed” then this column will display the ErrorID for troubleshooting
Error Code – If the WSUSscan_State is not “scan completed” then this column will display the ErrorCode for troubleshooting
HexErrorCode – If the WSUSscan_State is not “scan completed” then this column will display the HexCode for troubleshooting
UpdateID
Information URL

This report has one prompt

Enter a MachineName

This report does not have any links to other reports

Here is the report. You will need to do the following:

Copy the contents of the report into Notepad
Chose File > Save As > Save as type “All Files”
Chose Unicode for the encoding type
Save the file with a .MOF extension

// *********************************************************************************
//
//		Created by SMS Export object wizard
//
//		Wednesday, January 18, 2012 created
//
//		File Name: Compliance for a specific computer.MOF
//
// Comments :
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
	Category = "Software Updates";
	Comment = "This report returns the software update compliance data for a specific computer.";
	GraphXCol = 1;
	GraphYCol = 2;
	MachineDetail = FALSE;
	MachineSource = FALSE;
	Name = "Compliance for a specific computer";
	NumPrompts = 1;
	RefreshInterval = 0;
	ReportGUID = "{DE76B7D3-29B5-493F-AE12-1DDC591163E2}";
	
	ReportParams = {
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Computer Name (Required)";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select distinct Name0 from v_R_System WHERE isnull(Obsolete0,0)<>1 order by Name0
\n else
\n  select distinct Name0 from v_R_System
\n  WHERE Name0 like @__filterwildcard
\n and isnull(Obsolete0,0)<>1 order by Name0
\nend";
	VariableName = "MachineName";
}};
	SecurityKey = "";
	SQLQuery = "SET NOCOUNT ON
\n
\nCreate table #ResourceID (ResourceID int, name0 varchar(64))
\nInsert into #ResourceID (ResourceID, name0)
\nselect resourceID, name0 from v_R_System where ((Name0 like @MachineName) and (Active0 = 1) and Operating_System_Name_and0 not like '%server%')
\n--select * from #ResourceID
\n--drop table #ResourceID
\n
\n--select 'Total number of machines', count(ResourceID) from #ResourceID
\n
\nselect 
\n\t\t\t#ResourceID.name0 as 'Machine Name',
\n\t\t\tcatinfo.CategoryInstanceName as Vendor,
\n\t\t\tcatinfo2.CategoryInstanceName as UpdateClassification,
\n            ui.BulletinID as BulletinID,
\n            ui.ArticleID as ArticleID,
\n            ui.Title as Title,   
\n            ui.dateposted,
\n            ui.daterevised,             
\n            Targeted=(case when ctm.ResourceID is not null then '*' else '' end),
\n            Installed=(case when css.Status=3 then '*' else '' end),
\n            IsRequired=(case when css.Status=2 then '*' else '' end),
\n\t\t\tcss.LastStatusCheckTime as 'PatchInstallDate',
\n\t\t\tcss.LastStatusChangeTime as 'LastStateChange',
\n            Deadline=cdl.Deadline,
\n            ws.LastHWScan as 'SCCM Last Hardware Inventory',
\n            Dateadd(hour,(datediff(hour,getutcdate(),getdate())),uss.lastscantime) as 'Last WSUS Scan'
\n            , scan.StateName 'WSUSscan_State'
\n\t\t\t, scan.ErrorStatusID
\n\t\t\t, scan.ErrorCode
\n\t\t\t, scan.HexErrorCode,
\n            ui.CI_UniqueID as UniqueUpdateID,
\n\t\t\tui.InfoURL as InformationURL
\nfrom v_Update_ComplianceStatusall css
\njoin #ResourceID on css.ResourceID = #ResourceID.ResourceID
\njoin v_UpdateInfo ui on ui.CI_ID=css.CI_ID
\njoin v_CIRelation cir on cir.ToCIID = ui.CI_ID
\njoin v_CICategories_All catall on catall.CI_ID=ui.CI_ID 
\njoin v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company' 
\njoin v_CICategories_All catall2 on catall2.CI_ID=ui.CI_ID 
\njoin v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification' 
\n--left join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = @RscID
\nleft join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = #ResourceID.ResourceID
\nleft join (
\n                        select atc.CI_ID, Deadline=min(a.EnforcementDeadline) from v_CIAssignment a
\n                        join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID
\n                        group by atc.CI_ID) cdl   on cdl.CI_ID=css.CI_ID
\n--where  css.ResourceID = @RscID 
\nleft join \tv_GS_WORKSTATION_STATUS ws on css.ResourceID = ws.ResourceID
\nleft join \tv_UpdateScanStatus uss on css.ResourceID = uss.ResourceID
\nleft join
\n(
\nselect uss.ResourceID, SN.StateName, uss.LastStatusMessageID&0x0000FFFF as ErrorStatusID,
\n\tisnull(uss.LastErrorCode,0) as ErrorCode,
\n\tdbo.fnConvertBinaryToHexString(convert(VARBINARY(8), isnull(uss.LastErrorCode,0))) as HexErrorCode
\n from v_UpdateScanStatus uss
\n\tjoin v_R_System rsys on rsys.ResourceID = uss.ResourceID and isnull(rsys.Obsolete0,0)<>1
\n\tjoin v_SoftwareUpdateSource sus on uss.UpdateSource_ID = sus.UpdateSource_ID 
\n\tjoin v_RA_System_SMSAssignedSites sass on uss.ResourceID = sass.ResourceID
\n\tjoin v_StateNames sn on sn.TopicType = 501 and 
\n\t\tsn.StateID = (case when (isnull(uss.LastScanState, 0)=0 and Left(isnull(rsys.Client_Version0, '4.0'), 1)<'4') then 7 else isnull(uss.LastScanState, 0) end)
\nwhere 1= 1
\n\t--and (sass.SMS_Assigned_Sites0= 'P01')
\n\t--and (sn.StateName='Scan failed')
\n\tand (sus.UpdateSource_UniqueID = '{BE893E98-BD57-4E80-93F6-5D38B61C6069}')
\n) scan
\non css.ResourceID = scan.ResourceID 
\nwhere  css.ResourceID = #ResourceID.ResourceID
\nand ((css.Status=2) or (css.Status=3))
\n--and (@Vendor = '' or catinfo.CategoryInstanceName = @Vendor)
\n--and (@UpdateClass = '' or catinfo2.CategoryInstanceName = @UpdateClass)
\nand cir.FromCIID in 
\n(
\nselect CI_ID
\nfrom v_AuthListInfo where Title like '[Enter the portion of the title of the update lists you want displayed. Example:  if you had update lists titled "Servers - 2010", "Servers 2011", etc you could just enter %Servers%]'
\n)
\nand cir.RelationType=1
\n--order by  ui.BulletinID desc
\norder by #ResourceID.name0, ui.BulletinID desc";
	StatusMessageDetailSource = FALSE;
	UnicodeData = FALSE;
};
// ***** End *****

Specific software update status

This report list the compliance percentage of a particular patch

This report will list the following information:

Vendor
ArticleID – example: 2585542
BuletinID – example: MS12-006
Title
Installed – quantity of machines with the patch installed
Required – quantity of machines that require the patch
Not Required – quantity of machines that do no require the patch
Unknown – quantity of machines that have an status of “unknown” for the patch
Total – total quantity of the machines
%Compliant
%Not Compliant
%Unknown
CollectionID
Update ID

This report will have one prompt

Update Title, Bulletin ID or Article ID

This report will link to the Specific Software Updates States report

Here is the report. You will need to do the following:

Copy the contents of the report into Notepad
Chose File > Save As > Save as type “All Files”
Chose Unicode for the encoding type
Save the file with a .MOF extension

// *********************************************************************************
//
//		Created by SMS Export object wizard
//
//		Wednesday, January 18, 2012 created
//
//		File Name: Specific software update status.MOF
//
// Comments :
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
	Category = "Software Updates";
	Comment = "This report returns the patch percentage of machines for a particular update.";
	DrillThroughColumns = {};


	GraphCaption = "";
	GraphXCol = 1;
	GraphYCol = 2;
	MachineDetail = FALSE;
	MachineSource = FALSE;
	Name = "Specific software update status";
	NumPrompts = 1;
	RefreshInterval = 0;
	ReportGUID = "{0D228B49-56B8-4DA9-A0E2-27D64642B0E3}";
	
	ReportParams = {
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Update Title, Bulletin ID or Article ID (Required)";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select distinct ArticleID, BulletinID, Title from v_UpdateInfo
\n  where title not like '%server%'
\n  order by BulletinID desc, ArticleID desc
\n else
\n  select distinct ArticleID, BulletinID, Title from v_UpdateInfo
\n  WHERE ((ArticleID like @__filterwildcard) or 
\n         (BulletinID like @__filterwildcard) or 
\n         (Title like @__filterwildcard))
\n\tand title not like '%server%'
\n  order by BulletinID desc, ArticleID desc
\nend";
	VariableName = "Update";
}};
	SecurityKey = "";
	SQLQuery = "set nocount on
\ndeclare @UpdInfo table (CI_ID int primary key, BulletinID varchar(64), ArticleID varchar(64), Title varchar(512), CI_UniqueID varchar(512))
\ninsert into @UpdInfo (CI_ID, BulletinID, ArticleID, Title, CI_UniqueID) 
\n    select CI_ID, BulletinID, ArticleID, Title, CI_UniqueID
\n    from v_UpdateInfo ui
\n    where (ui.ArticleID=@Update) 
\n    or (ui.BulletinID=@Update) 
\n    or (ui.Title=@Update) 
\n    or (ui.CI_UniqueID=@Update)
\n    and ui.title not like '%server%'
\n
\nselect  catinfo.CategoryInstanceName as Vendor0,
\n        ui.ArticleID as ArticleID, 
\n        ui.BulletinID as BulletinID,
\n        ui.Title as Title,
\n        NumPresent as Present,
\n        NumMissing as Missing,
\n        NumNotApplicable as NotApplicable,
\n        NumUnknown as Unknown,
\n        NumTotal as Total,
\n        PCompliant=convert(numeric(5,2), (isnull(NumPresent, 0)+isnull(NumNotApplicable, 0))*100.0 / isnull(nullif(NumTotal, 0), 1)),
\n        PNotCompliant=convert(numeric(5,2), (isnull(NumMissing, 0))*100.0 / isnull(nullif(NumTotal, 0), 1)),
\n        PUnknown=convert(numeric(5,2), (isnull(NumTotal - (isnull(NumPresent, 0)+isnull(NumMissing, 0)+isnull(NumNotApplicable, 0)), 0))*100.0 / isnull(nullif(NumTotal, 0), 1)),
\n        CollectionID as CollectionID,
\n        ui.CI_UniqueID as UniqueUpdateID 
\nfrom @UpdInfo ui
\njoin v_UpdateSummaryPerCollection uspc on ui.CI_ID = uspc.CI_ID and uspc.CollectionID in ('[Enter the collection ID of the collection that you target Software Updates to]')
\njoin v_CICategoryInfo_All catinfo on ui.CI_ID = catinfo.CI_ID and catinfo.CategoryTypeName = 'Company'
\norder by ui.ArticleID";
	StatusMessageDetailSource = FALSE;
	UnicodeData = FALSE;
	XColLabel = "";
	YColLabel = "";
};
// ***** End *****

Machines that are missing a patch over 30 days old

This report lists all machines that require a patch that has been created or modified over 30 days ago

This report will list the following information

MachineName
Vendor
Update Class
ArticleID – example: 2585542
BuletinID – example: MS12-006
Title
Date Posted
Date Revised
Expired – Yes or no
Superseded – yes or no
IsDeployed – yes or no
Is Required – an asterisk indicates that the patch is required
Deadline – this is the deadline for the Deployment Management
SCCM Last Hardwar Inventory – date
Last WSUS Scan – date
WSUSscan_State
Error Status ID – If the WSUSscan_State is not “scan completed” then this column will display the ErrorID for troubleshooting
Error Code – If the WSUSscan_State is not “scan completed” then this column will display the ErrorCode for troubleshooting
HexErrorCode – If the WSUSscan_State is not “scan completed” then this column will display the HexCode for troubleshooting
Update ID
Information URL

This report will have two prompts

Select a Business Unit by clicking on the values button or enter % for ALL
Enter YES for patches that are being pushed, NO for ALL

This report will link to the “Compliance for a specific computer” report

Machinename – column 1

Here is the report. You will need to do the following:

Copy the contents of the report into Notepad
Chose File > Save As > Save as type “All Files”
Chose Unicode for the encoding type
Save the file with a .MOF extension

// *********************************************************************************
//
//		Created by SMS Export object wizard
//
//		Wednesday, January 18, 2012 created
//
//		File Name: Machines that are missing a patch over 30 days old.MOF
//
// Comments :
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
	Category = "Software Updates";
	Comment = "This report lists all machines that require a patch that has been created or modified over 30 days ago.";
	DrillThroughColumns = {};


	GraphCaption = "";
	GraphXCol = 1;
	GraphYCol = 2;
	MachineDetail = FALSE;
	MachineSource = FALSE;
	Name = "Machines that are missing a patch over 30 days old";
	NumPrompts = 2;
	RefreshInterval = 0;
	ReportGUID = "{7AE2D12B-C0DE-4728-BFA7-58CDE4ADFE84}";
	
	ReportParams = {
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Select a business unit or % for all";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  where b.parentcollectionID = '[Enter the parent collection ID of your business unit collections.  Example:  Create an empty collection called "Business Units".  Then create multiple sub collections that represent your various business units]' and a.name not like '%No Client%'
\n  order by a.Name
\n else
\n  select a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  WHERE a.CollectionID like @__filterwildcard
\n  order by a.Name
\nend";
	VariableName = "CollectionID";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Enter YES for pushed patches or NO for all patches";
	SampleValueSQL = "";
	VariableName = "IsPushed";
}};
	SecurityKey = "";
	SQLQuery = "if @ISpushed = 'yes'
\nselect distinct
\n\t\t\tRsys.name0 as 'Machine Name',
\n\t\t\tcatinfo.CategoryInstanceName as Vendor,
\n\t\t\tcatinfo2.CategoryInstanceName as UpdateClassification,
\n            ui.BulletinID as BulletinID,
\n            ui.ArticleID as ArticleID,
\n            ui.Title as Title,    
\n            ui.DatePosted,
\n            ui.DateRevised,        
\n            --Targeted=(case when ctm.ResourceID is not null then '*' else '' end),
\n            --Installed=(case when css.Status=3 then '*' else '' end),
\n            Expired = case when ui.Isexpired = 0 then 'NO' else 'YES' end,
\n\t\t\tSuperseded = case when ui.isSuperseded = 0 then 'NO' else 'YES' end,
\n\t\t\tIsDeployed = case when ui.IsDeployed = 0 then 'NO' else 'YES' end,
\n            IsRequired=(case when css.Status=2 then '*' else '' end),
\n\t\t\tcss.LastStatusCheckTime as 'PatchInstallDate',
\n\t\t\tcss.LastStatusChangeTime as 'LastStateChange',
\n            Deadline=cdl.Deadline,
\n            ws.LastHWScan as 'SCCM Last Hardware Inventory',
\n            Dateadd(hour,(datediff(hour,getutcdate(),getdate())),uss.lastscantime) as 'Last WSUS Scan'
\n            , scan.StateName 'WSUSscan_State'
\n\t\t\t, scan.ErrorStatusID
\n\t\t\t, scan.ErrorCode
\n\t\t\t, scan.HexErrorCode,
\n--\t\t\tmaint.MaintenanceWindowName as 'CEPSWName', maint.Description, maint.StartTime, maint.DurationMinutes as 'DurationMinutes', maint.Enabled as 'CEPSWEnabled',
\n            ui.CI_UniqueID as UniqueUpdateID,
\n\t\t\tui.InfoURL as InformationURL
\nfrom v_Update_ComplianceStatusall css
\njoin v_R_System Rsys on css.ResourceID = Rsys.ResourceID
\njoin v_UpdateInfo ui on ui.CI_ID=css.CI_ID
\njoin v_CIRelation cir on cir.ToCIID = ui.CI_ID
\njoin v_CICategories_All catall on catall.CI_ID=ui.CI_ID 
\njoin v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company' 
\njoin v_CICategories_All catall2 on catall2.CI_ID=ui.CI_ID 
\njoin v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification' 
\n--left join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = @RscID
\nleft join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = rsys.ResourceID
\nleft join (
\n                        select atc.CI_ID, Deadline=min(a.EnforcementDeadline) from v_CIAssignment a
\n                        join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID
\n                        group by atc.CI_ID) cdl   on cdl.CI_ID=css.CI_ID
\n--where  css.ResourceID = @RscID 
\n/*
\nleft join (
\n\t\t\tselect fcm.resourceid, sw.Name as 'MaintenanceWindowName', sw.Description, sw.StartTime, sw.Duration as 'DurationMinutes', sw.IsEnabled as 'Enabled',case when sw.RecurrenceType = 1 then 'No Recurrence'
\n\t\t\twhen sw.RecurrenceType = 2 then 'Daily'
\n\t\t\twhen sw.RecurrenceType = 3 then 'Weekly'
\n\t\t\twhen sw.RecurrenceType = 4 then 'Monthly'
\n\t\t\telse 'Unkwnon' end as RecurrenceType, case when sw.ServiceWindowType=5 then '*' else ' ' end as 'OSDServiceWindow'
\n\t\t\t, col.name as 'CollectionName'
\n\t\t\t, col.CollectionID
\n\t\t\tfrom v_ServiceWindow sw
\n\t\t\tjoin v_FullCollectionMembership fcm on sw.CollectionID = fcm.CollectionID
\n\t\t\tjoin v_Collection col
\n\t\t\ton sw.collectionid = col.collectionid
\n\t\t\twhere sw.Name <> 'NO RUN'
\n\t\t\t) Maint on maint.ResourceID = Rsys.ResourceID
\n*/
\nleft join \tv_GS_WORKSTATION_STATUS ws on Rsys.ResourceID = ws.ResourceID
\nleft join \tv_UpdateScanStatus uss on Rsys.ResourceID = uss.ResourceID
\nleft join
\n(
\nselect uss.ResourceID, SN.StateName, uss.LastStatusMessageID&0x0000FFFF as ErrorStatusID,
\n\tisnull(uss.LastErrorCode,0) as ErrorCode,
\n\tdbo.fnConvertBinaryToHexString(convert(VARBINARY(8), isnull(uss.LastErrorCode,0))) as HexErrorCode
\n from v_UpdateScanStatus uss
\n\tjoin v_R_System rsys on rsys.ResourceID = uss.ResourceID and isnull(rsys.Obsolete0,0)<>1
\n\tjoin v_SoftwareUpdateSource sus on uss.UpdateSource_ID = sus.UpdateSource_ID 
\n\tjoin v_RA_System_SMSAssignedSites sass on uss.ResourceID = sass.ResourceID
\n\tjoin v_StateNames sn on sn.TopicType = 501 and 
\n\t\tsn.StateID = (case when (isnull(uss.LastScanState, 0)=0 and Left(isnull(rsys.Client_Version0, '4.0'), 1)<'4') then 7 else isnull(uss.LastScanState, 0) end)
\nwhere 1= 1
\n\t--and (sass.SMS_Assigned_Sites0= 'P01')
\n\t--and (sn.StateName='Scan failed')
\n\tand (sus.UpdateSource_UniqueID = '{BE893E98-BD57-4E80-93F6-5D38B61C6069}')
\n) scan
\non Rsys.ResourceID = scan.ResourceID 
\nwhere  css.ResourceID = rsys.ResourceID
\nand ((css.Status=2))
\nand Rsys.Operating_System_Name_and0 not like '%server%'
\n--and (@Vendor = '' or catinfo.CategoryInstanceName = @Vendor)
\n--and (@UpdateClass = '' or catinfo2.CategoryInstanceName = @UpdateClass)
\nand cir.FromCIID in 
\n(
\nselect CI_ID
\nfrom v_AuthListInfo where Title like '%[Enter the portion of the title of the update lists you want displayed. Example:  if you had update lists titled "Servers - 2010", "Servers 2011", etc you could just enter %Servers%]%' and (Title not like '%test%' or title not like '%sandbox%')
\n)
\nand cir.RelationType=1
\nand (ui.DatePosted <= (GETDATE()-30) or ui.DateRevised <= (GETDATE()-30))
\n--and rsys.Name0 like 'SWMN00XB03231'
\n--order by  ui.BulletinID desc
\nand IsDeployed = 1
\nand rsys.Name0 in
\n\t(
\n\tselect Name
\n\tfrom dbo.v_FullCollectionMembership
\n\twhere CollectionID = @CollectionID
\n\t)
\norder by rsys.name0, ui.BulletinID desc
\nelse
\nselect distinct
\n\t\t\tRsys.name0 as 'Machine Name',
\n\t\t\tcatinfo.CategoryInstanceName as Vendor,
\n\t\t\tcatinfo2.CategoryInstanceName as UpdateClassification,
\n            ui.BulletinID as BulletinID,
\n            ui.ArticleID as ArticleID,
\n            ui.Title as Title,    
\n            ui.DatePosted,
\n            ui.DateRevised,        
\n            --Targeted=(case when ctm.ResourceID is not null then '*' else '' end),
\n            --Installed=(case when css.Status=3 then '*' else '' end),
\n            Expired = case when ui.Isexpired = 0 then 'NO' else 'YES' end,
\n\t\t\tSuperseded = case when ui.isSuperseded = 0 then 'NO' else 'YES' end,
\n\t\t\tIsDeployed = case when ui.IsDeployed = 0 then 'NO' else 'YES' end,
\n            IsRequired=(case when css.Status=2 then '*' else '' end),
\n\t\t\tcss.LastStatusCheckTime as 'PatchInstallDate',
\n\t\t\tcss.LastStatusChangeTime as 'LastStateChange',
\n            Deadline=cdl.Deadline,
\n            ws.LastHWScan as 'SCCM Last Hardware Inventory',
\n            Dateadd(hour,(datediff(hour,getutcdate(),getdate())),uss.lastscantime) as 'Last WSUS Scan'
\n            , scan.StateName 'WSUSscan_State'
\n\t\t\t, scan.ErrorStatusID
\n\t\t\t, scan.ErrorCode
\n\t\t\t, scan.HexErrorCode,
\n--\t\t\tmaint.MaintenanceWindowName as 'CEPSWName', maint.Description, maint.StartTime, maint.DurationMinutes as 'DurationMinutes', maint.Enabled as 'CEPSWEnabled',
\n            ui.CI_UniqueID as UniqueUpdateID,
\n\t\t\tui.InfoURL as InformationURL
\nfrom v_Update_ComplianceStatusall css
\njoin v_R_System Rsys on css.ResourceID = Rsys.ResourceID
\njoin v_UpdateInfo ui on ui.CI_ID=css.CI_ID
\njoin v_CIRelation cir on cir.ToCIID = ui.CI_ID
\njoin v_CICategories_All catall on catall.CI_ID=ui.CI_ID 
\njoin v_CategoryInfo catinfo on catall.CategoryInstance_UniqueID = catinfo.CategoryInstance_UniqueID and catinfo.CategoryTypeName='Company' 
\njoin v_CICategories_All catall2 on catall2.CI_ID=ui.CI_ID 
\njoin v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName='UpdateClassification' 
\n--left join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = @RscID
\nleft join v_CITargetedMachines ctm on ctm.CI_ID=css.CI_ID and ctm.ResourceID = rsys.ResourceID
\nleft join (
\n                        select atc.CI_ID, Deadline=min(a.EnforcementDeadline) from v_CIAssignment a
\n                        join v_CIAssignmentToCI atc on atc.AssignmentID=a.AssignmentID
\n                        group by atc.CI_ID) cdl   on cdl.CI_ID=css.CI_ID
\n--where  css.ResourceID = @RscID 
\n/*
\nleft join (
\n\t\t\tselect fcm.resourceid, sw.Name as 'MaintenanceWindowName', sw.Description, sw.StartTime, sw.Duration as 'DurationMinutes', sw.IsEnabled as 'Enabled',case when sw.RecurrenceType = 1 then 'No Recurrence'
\n\t\t\twhen sw.RecurrenceType = 2 then 'Daily'
\n\t\t\twhen sw.RecurrenceType = 3 then 'Weekly'
\n\t\t\twhen sw.RecurrenceType = 4 then 'Monthly'
\n\t\t\telse 'Unkwnon' end as RecurrenceType, case when sw.ServiceWindowType=5 then '*' else ' ' end as 'OSDServiceWindow'
\n\t\t\t, col.name as 'CollectionName'
\n\t\t\t, col.CollectionID
\n\t\t\tfrom v_ServiceWindow sw
\n\t\t\tjoin v_FullCollectionMembership fcm on sw.CollectionID = fcm.CollectionID
\n\t\t\tjoin v_Collection col
\n\t\t\ton sw.collectionid = col.collectionid
\n\t\t\twhere sw.Name <> 'NO RUN'
\n\t\t\t) Maint on maint.ResourceID = Rsys.ResourceID
\n*/
\nleft join \tv_GS_WORKSTATION_STATUS ws on Rsys.ResourceID = ws.ResourceID
\nleft join \tv_UpdateScanStatus uss on Rsys.ResourceID = uss.ResourceID
\nleft join
\n(
\nselect uss.ResourceID, SN.StateName, uss.LastStatusMessageID&0x0000FFFF as ErrorStatusID,
\n\tisnull(uss.LastErrorCode,0) as ErrorCode,
\n\tdbo.fnConvertBinaryToHexString(convert(VARBINARY(8), isnull(uss.LastErrorCode,0))) as HexErrorCode
\n from v_UpdateScanStatus uss
\n\tjoin v_R_System rsys on rsys.ResourceID = uss.ResourceID and isnull(rsys.Obsolete0,0)<>1
\n\tjoin v_SoftwareUpdateSource sus on uss.UpdateSource_ID = sus.UpdateSource_ID 
\n\tjoin v_RA_System_SMSAssignedSites sass on uss.ResourceID = sass.ResourceID
\n\tjoin v_StateNames sn on sn.TopicType = 501 and 
\n\t\tsn.StateID = (case when (isnull(uss.LastScanState, 0)=0 and Left(isnull(rsys.Client_Version0, '4.0'), 1)<'4') then 7 else isnull(uss.LastScanState, 0) end)
\nwhere 1= 1
\n\t--and (sass.SMS_Assigned_Sites0= 'P01')
\n\t--and (sn.StateName='Scan failed')
\n\tand (sus.UpdateSource_UniqueID = '{BE893E98-BD57-4E80-93F6-5D38B61C6069}')
\n) scan
\non Rsys.ResourceID = scan.ResourceID 
\nwhere  css.ResourceID = rsys.ResourceID
\nand ((css.Status=2))
\nand Rsys.Operating_System_Name_and0 not like '%server%'
\n--and (@Vendor = '' or catinfo.CategoryInstanceName = @Vendor)
\n--and (@UpdateClass = '' or catinfo2.CategoryInstanceName = @UpdateClass)
\n--and cir.FromCIID in 
\n--(
\n--select CI_ID
\n--from v_AuthListInfo where Title like '%Wintel%' and (Title not like '%test%' or title not like '%sandbox%')
\n--)
\n--and cir.RelationType=1
\nand (ui.DatePosted <= (GETDATE()-30) or ui.DateRevised <= (GETDATE()-30))
\n--and rsys.Name0 like 'SWMN00XB03231'
\n--order by  ui.BulletinID desc
\nand rsys.Name0 in
\n\t(
\n\tselect Name
\n\tfrom dbo.v_FullCollectionMembership
\n\twhere CollectionID = @CollectionID
\n\t)
\norder by rsys.name0, ui.BulletinID desc";
	StatusMessageDetailSource = FALSE;
	UnicodeData = FALSE;
	XColLabel = "";
	YColLabel = "";
};
// ***** End *****

States for a deployment

This report returns the states for all software updates in the specified deployment for a specified collection

NOTE: This is a very process intensive query. DO NOT run this report against a large collection of machines.

This report will list the following information:

ArticleID – example: 2393802
Bulletin ID – example MS11-011
Title
State Name – example “Update is installed” or “Update is downloaded”
Error Status ID
Update ID
Name – MachineName
Last Bootup Time – Datetime
SCCM Last Hardware Inventory – Datetime

The report will have two prompts

Deployment ID – this is the ID of the Update List you want to interrogate
Enter a business unit or CollectionID – This is the collection of machines you are interested in

This report does not link to any other reports

Here is the report. You will need to do the following:

Copy the contents of the report into Notepad
Chose File > Save As > Save as type “All Files”
Chose Unicode for the encoding type
Save the file with a .MOF extension

// *********************************************************************************
//
//		Created by SMS Export object wizard
//
//		Wednesday, January 18, 2012 created
//
//		File Name: States for a deployment.MOF
//
// Comments :
//
//
// *********************************************************************************


// ***** Class : SMS_Report *****
[SecurityVerbs(140551)]
instance of SMS_Report
{
	Category = "Software Updates";
	Comment = "This report returns the states for all software updates in the specified deployment for a specified collection";
	DrillThroughColumns = {};


	GraphCaption = "";
	GraphXCol = 1;
	GraphYCol = 2;
	MachineDetail = FALSE;
	MachineSource = FALSE;
	Name = "States for a deployment";
	NumPrompts = 2;
	RefreshInterval = 0;
	ReportGUID = "{CA6D9DC9-B7F4-4AA4-AE42-1B2892301B79}";
	
	ReportParams = {
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Deployment ID (Required)";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '') 
\nselect 
\ncia.Assignment_UniqueID as DeploymentID , cia.AssignmentName as DeploymentName
\nfrom v_CIAssignment cia
\nwhere cia.UpdateAssignment=1 and AssignmentName like '%[Enter the portion of the title of the update lists you want displayed. Example:  if you had update lists titled "Servers - 2010", "Servers 2011", etc you could just enter %Servers%]%'
\norder by AssignmentName
\nelse
\nselect 
\ncia.Assignment_UniqueID as DeploymentID , cia.AssignmentName as DeploymentName
\nfrom v_CIAssignment cia
\nwhere cia.UpdateAssignment=1
\nand Assignment_UniqueID like @__filterwildcard or AssignmentName like @__filterwildcard
\norder by AssignmentName
\nend";
	VariableName = "DEPLOYMENTID";
}, 
instance of SMS_ReportParameter
{
	AllowEmpty = FALSE;
	DefaultValue = "";
	PromptText = "Enter a business unit or Collection ID";
	SampleValueSQL = "begin
\n if (@__filterwildcard = '')
\n  select distinct a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  where (b.parentcollectionID = '[Enter the parent collection ID of your business unit collections.  Example:  Create an empty collection called "Business Units".  Then create multiple sub collections that represent your various business units]' or b.parentcollectionID = '[Enter the parent collection ID of your business unit collections.  Example:  Create an empty collection called "Business Units".  Then create multiple sub collections that represent your various business units]') and a.name not like '%No Client%' 
\nor a.collectionID in (select collectionID from v_SCCM_WintelPatchWindowCollections)
\n  order by a.Name
\n else
\n  select a.collectionID, a.name
\n  from v_collection a
\n  join v_CollectToSubCollect b
\n  on a.collectionID = b.subcollectionid
\n  WHERE a.CollectionID like @__filterwildcard
\n  order by a.Name
\nend";
	VariableName = "CollectionID";
}};
	SecurityKey = "";
	SQLQuery = "select 
\nui.ArticleID as ArticleID,
\nui.BulletinID as BulletinID,
\nui.Title as Title,
\nsn.StateName as Status,
\nucs.LastEnforcementStatusMsgID&0x0000FFFF as ErrorStatusID,
\nui.CI_UniqueID as UniqueUpdateID,
\ncol.name,
\nos.LastBootUpTime0 as 'LastBootUpTime',
\nws.LastHWScan as 'SCCM Last Hardware Inventory'
\nfrom v_CIAssignment a
\njoin v_CIAssignmentToCI aci
\n   on aci.AssignmentID=a.AssignmentID
\njoin v_CIAssignmentTargetedMachines atm
\n   on atm.AssignmentID=a.AssignmentID
\njoin v_UpdateState_Combined ucs
\n   on ucs.CI_ID=aci.CI_ID and ucs.ResourceID=atm.ResourceID
\njoin v_StateNames sn
\n   on sn.StateID=ucs.StateID and sn.TopicType=ucs.StateType
\njoin v_UpdateInfo ui
\n   on ui.CI_ID=aci.CI_ID
\nleft join   v_GS_Operating_System os on atm.ResourceID = os.resourceid
\nleft join \tv_GS_WORKSTATION_STATUS ws on atm.ResourceID = ws.ResourceID
\nleft join v_FullCollectionMembership Col
\non atm.ResourceID = col.ResourceID
\nwhere (a.Assignment_UniqueID = @DEPLOYMENTID)  and col. CollectionID = @CollectionID
\norder by ui.ArticleID";
	StatusMessageDetailSource = FALSE;
	UnicodeData = FALSE;
	XColLabel = "";
	YColLabel = "";
};
// ***** End *****

August 30, 2011

Using PowerShell to find all groups with members in a different domain

Yesterday I was asked by the Security Team to help them out with a project they were working on. There is an OU with over 2,000 groups in it and they needed to find the names of all the groups whose members where in a different domain.

#This script requires the Microsoft 'ActiveDirectory' module to be loaded.  
#The module is part of the Win7 RSAT and comes standard on Server 2008 R2
$Erroractionpreference = "SilentlyContinue" # For whatever reason, there are groups that can't be bound to.  
											# I will guess these are orphaned. In either case this line prevents errors from being displayed
foreach ($group in Get-ADGroup -SearchBase 'OU=SOMETHING,OU=GROUPS,OU=SOMETHING,OU=BUSINESSUNIT,DC=EmptyGarden,DC=info' -Filter *)
	{
	foreach ($user in Get-ADGroupMember -Identity $Group.Name)
		{
		if ($user.distinguishedName -notlike '*AnotherDomain,*') 
			{
			$group.name
			break # We only care if one user is a member of a different domain.  Thus, once we find one, we get out of the IF loop and grab another group.
			}
		}
	}

August 2, 2011

Powershell–Getting information from AD into the SCCM database

Recently I was asked to deliver a report on the count of machines in AD and their location. In our environment we use a computer name identifier to designate location. But, you could use OU information or eve AD Site information. So, I wrote a PowerShell script that grabs all three and puts the information into SQL. You can then merge the tables together and create a view. You may be asking, “doesn’t SCCM System Discovery provide this information?” Well, yes and no. My PowerShell script returns more computers than SCCM is discovering. Plus, the PowerShell script also returns information from AD Sites and Services as well as OU Description that can’t be obtained using SCCM discovery.

Get-ADComptuerInformation

Import-Module[DIRECTORY WHERE THE SCCM MODULE IS]\SCCM-Commands.psm1'
add-pssnapin Quest.ActiveRoles.ADManagement
Set-QADPSSnapinSettings -DefaultSizeLimit 100000
$sccm = Connect-SCCMServer
# Get all of the domain names
# Connect to RootDSE
$rootDSE = [ADSI]"LDAP://RootDSE"
# Connect to the Configuration Naming Context
$configSearchRoot = [ADSI]("LDAP://" + `
$rootDSE.Get("configurationNamingContext"))
# Configure the filter
$filter = "(NETBIOSName=*)"
# Search for all partitions where the NetBIOSName is set
$configSearch = New-Object DirectoryServices.DirectorySearcher($configSearchRoot, $filter)
# Configure search to return dnsroot and ncname attributes
$retVal = $configSearch.PropertiesToLoad.Add("dnsroot")
$retVal = $configSearch.PropertiesToLoad.Add("ncname")
#$configSearch.FindAll() | Select-Object @{n="dnsroot";e={$_.Properties.dnsroot}},  @{n="ncname";e={$_.Properties.ncname}}
#$DomainNames = foreach ($i in $configSearch.FindAll()) {$i.Properties.ncname}
$DomainNames = foreach ($i in $configSearch.FindAll()) {@{[string]$i.Properties.ncname = [string]$i.Properties.dnsroot}}
# Get computer information from AD into SQL
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=[SQL SERVER NAME]; Initial Catalog=[SCCM DATABASE NAME]; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "delete from ADComputerInformation"
$cmd.ExecuteNonQuery()

foreach ($domain in $DomainNames) {
	#$LDAPDomainName = $domain.Keys
	#$LDAPDomainName = "`'$LDAPDomainName'"
	#[string]$DNSDomainName = $domain.Values
	$ADService = Connect-QADService -Service $domain.Values
	$ComputersInDomain = Get-QADComputer -SearchRoot $ADService.DefaultNamingContextDN -service $ADService.Domain.Name -SearchScope Subtree -Inactive:$false |
	Select OperatingSystem, ParentContainerDN, Name, domain
	# Write to SQL

	foreach ($i in $ComputersInDomain) {
		$cmd = $conn.CreateCommand()
		$OperatingSystem = $i.OperatingSystem.ToString()
		$ParentContainerDN = $i.ParentContainerDN.ToString()
		$Name = $i.Name.ToString()
		$domain = $i.domain.ToString()
		$cmd.CommandText ="INSERT ADComputerInformation VALUES ('$OperatingSystem','$ParentContainerDN', '$Name', '$domain')"
		$cmd.ExecuteNonQuery()
		}
	Clear-Variable computersindomain
	}
$conn.Close()


# Old working area
#$var = Get-QADComputer -SearchRoot['LDAP DOMAIN] -SearchScope Subtree -SizeLimit 1 -Inactive:$false |
#	group operatingsystem | sort count -Descending 
#
#$var = $var.Group

# SQL
#-- USE [SCCM DATABASE]
#GO
#
#/****** Object:  Table [dbo].[ADComputerInformation]    Script Date: 07/29/2011 09:09:15 ******/
#SET ANSI_NULLS ON
#GO
#
#SET QUOTED_IDENTIFIER ON
#GO
#
#CREATE TABLE [dbo].[ADComputerInformation](
#	[OperatingSystem] [nvarchar](256) NULL,
#	[ParentContainerDN] [nvarchar](max) NULL,
#	[Name] [nvarchar](256) NULL,
#	[Domain] [nvarchar](256) NULL
#) ON [PRIMARY]
#
#GO

Get-ADOUInfo

Import-Module [DIRECTORY WHERE THE SCCM MODULE IS]\SCCM-Commands.psm1'
add-pssnapin Quest.ActiveRoles.ADManagement
Set-QADPSSnapinSettings -DefaultSizeLimit 100000
$sccm = Connect-SCCMServer
$var = Get-QADObject -Type 'organizationalUnit' | select DN, Description, ParentContainerDN
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=[SQL Servers]; Initial Catalog=[SCCM Database]; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "delete from ADOUInformation"
$cmd.ExecuteNonQuery()
foreach ($i in $var) {
	if ($i.Description -eq $Null) {$description = $Null}
	else {$description = $i.Description.ToString()}
	if ($i.DN -eq $null) {$DN = $null}
	else {$DN = $i.DN.ToString()}
	if ($i.ParentcontainerDN -eq $null) {$ParentContainerDN = $Null}
	else {$ParentContainerDN = $i.ParentContainerDN.tostring()}
$cmd.CommandText ="INSERT ADOUInformation VALUES ('$DN','$description', '$ParentContainerDN' )"
$cmd.ExecuteNonQuery()
}
$conn.Close()

#SQL
#
#-- USE [SCCM DATABASE]
#GO
#
#/****** Object:  Table [dbo].[ADOUInformation]    Script Date: 07/29/2011 10:44:46 ******/
#SET ANSI_NULLS ON
#GO
#
#SET QUOTED_IDENTIFIER ON
#GO
#
#CREATE TABLE [dbo].[ADOUInformation](
#	[DN] [nvarchar](max) NULL,
#	[Description] [nvarchar](256) NULL,
#	[ParentContainerDN] [nvarchar] (max) NULL
#) ON [PRIMARY]
#
#GO


Get-ADSiteInfo

Import-Module [DIRECTORY WHERE THE SCCM MODULE IS]\SCCM-Commands.psm1'
add-pssnapin Quest.ActiveRoles.ADManagement
Set-QADPSSnapinSettings -DefaultSizeLimit 100000
$sccm = Connect-SCCMServer
$var = Get-QADObject -Searchroot "CN=Subnets,CN=Sites,CN=Configuration,DC=[FOREST ROOT],DC=[FOREST ROOT]" -includedproperties "Description", "Location" | select description, location
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=[SCCM SERVER]; Initial Catalog=[SCCM Database]; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "delete from ADSiteInformation"
$cmd.ExecuteNonQuery()
foreach ($i in $var) {
	if ($i.Description -eq $null) {$description = $null}
	else {$description = $i.Description.ToString()}
	if ($i.location -eq $null) {$location = $null}
	else {$location = $i.location.ToString()}
$cmd = $conn.CreateCommand()
$cmd.CommandText ="INSERT ADSiteInformation VALUES ('$location','$description' )"
$cmd.ExecuteNonQuery()
}
$conn.Close()

#SQL Code
#
#-- USE [SCCM DATABASE]
#GO
#
#/****** Object:  Table [dbo].[ADSiteInformation]    Script Date: 07/28/2011 13:18:14 ******/
#SET ANSI_NULLS ON
#GO
#
#SET QUOTED_IDENTIFIER ON
#GO
#
#CREATE TABLE [dbo].[ADSiteInformation](
#	[LocationCode] [nchar](10) NULL,
#	[LocationDescription] [nvarchar](max) NULL
#) ON [PRIMARY]
#
#GO

August 1, 2011

Delete-SCCMCollectionRule : Addition to the SCCM-Commands.psm1

Did you know that there is SCCM-Commands powershell module? You can get them here: http://www.snowland.se/sccm-posh/

Today I had a need to delete a collection member. But there isn’t a function in the SCCM-Commands powershell module that can do that.

Well, there is now!! Just copy this function into the SCCM-Commands.psm1 file

Function Delete-SCCMCollectionRule { 
    [CmdletBinding()] 
    PARAM ( 
        [Parameter(Mandatory=$true,  HelpMessage="SCCM Server")][Alias("Server","SmsServer")][System.Object] $SccmServer, 
        [Parameter(Mandatory=$true,  HelpMessage="CollectionID", ValueFromPipelineByPropertyName=$true)] $collectionID, 
        [Parameter(Mandatory=$true,  HelpMessage="Rule Name", ValueFromPipeline=$true)] [String] $queryRuleName
                ) 
  
    PROCESS { 
                #$CollectionID = CEN00247
                #$SCCMServer = $SCCM
                #$QueryRuleName = LWMN00x2
        $coll = [wmi]"$($SccmServer.SccmProvider.NamespacePath):SMS_Collection.CollectionID='$collectionID'"
             # Find each computer 
                        foreach ($i in $queryRuleName) 
                            {$computer = Get-SCCMComputer -sccmServer $SccmServer -NetbiosName $i
                   # See if the computer is already a member 
                $found = $false
                if ($coll.CollectionRules -ne $null) { 
                foreach ($member in $coll.CollectionRules)
                                    { 
                  if ($member.ResourceID -eq $computer.ResourceID) {$found = $true} 
                    } 
                } 
            if ($found) { 
                                $ResourceToDelete = $coll.CollectionRules | where {$_.rulename -eq $i}
                                $coll.DeleteMembershipRule($ResourceToDelete)
                           } else { 
                Write-Verbose "Computer $queryRuleName is not in the collection"
                    }     
                    } 
    } 
        }