The Empty Garden

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 ***

.csharpcode, .csharpcode pre
{
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;
%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

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";
	Var