Notes from the Minnesota System Center Users Group

 

Last Friday at the Minnesota System Center Users Group I had the pleasure of seeing Wally Mead speak! His session lasted about three hours and he covered Config Manger 2012 SP1.

  • Configuration Manger 2012 – SP1 – probably won’t be released until Windows 8 is released.
  • Windows 8
      • No more WAIK.
      • WAIK is replaced by the ADK (Assessment and Deployment Kit) – ADK is a separate download
  • Adding support for UEFI-32 and 64 bit
      • This is the thing that happens between BIOS and CTRL-ALT-DEL or something like that!
  • Always on always connected (AOAC)
      • Newer slate devices have this
      • Helps preserve battery life
  • Configuration Mgr can no longer keep the OS from sleeping
  • Configuration Mgr client behavior was adapted to be a good citizen to act differently in these new states
      • On battery only
      • Configuration manager will not perform non-end user initiated tasks, unless those tasks were missed/overdue
      • On AC – normal Configuration Manger behavior
      • Network connected
      • No network – Configuration manger won’t attempt tasks that require network
      • Windows maintenance hour
        • Configuration Manger can take advantage of this new feature in Windows 8
  • New deployment type for METRO Apps.
  • Deep Links
      • End user goes to the Configuration Mgr 2012 Application Catalog. An application can be configured to download from the Windows Store
      • Thus, the SCCM admin does not need to repackage an application.
      • The downside is that instead of using a local DP for the content, the content will be hosted online.
  • Configuration Mgr SP1 can manage
      • Windows 8 Client Side Caching, Roaming User Profiles, Folder Redirection
  • Network Cost Support (only Windows 8 clients)
      • Configuration Manger can block network traffic if the Windows 8 machine is on a 3G or 4G network.
  • Windows To Go
      • Configuration Mgr can create and provision a Windows-To-Go device
  • New Platform Support
      • Supports more Nokia devices (E6, Nokia 701, Nokia C7, N8, Nokia 603)
      • Apple Mac OS
        • Supports 10.6 (snow leopard) and 10.7 (lion)
        • Hardware Inventory, Software Dist, Software updates, settings management
  • Endpoint protection
    • Linux client support
    • Linux server support
  • Feature Improvements
      • Can add a Central Administration Site (CAS) later
      • Configuration Manger 2012 to Configuration Mgr 2012 migration
        • Scenario: you have two primary 2012 sites. You can only attach ONE of them to the new CAS. You would need to build another 2012 site and attach it to the CAS. Then you could migrate the standalone 2012 site to the newly attached one.
        • Scenario: lab to production
      • Granular control over SQL Server Replication
  • Server Infrastructure Support
      • Exchange connector support
        • E14 SP2
        • E15
  • SQL Server 2012 support (only for Configuration Mgr SP1)
  • End User Client UI improvements
      • Software Center supports multi-select
      • No more ActiveX control – it now depends on Silverlight 5
  • OS Deployment Improvements
      • Prestage media now supports additional content types
        • Before: WIM only
        • Now: WIM, applications, drives, package / programs
      • Task Sequences can now be applicable to WinPE only
  • Endpoint Protection
      • Automatically deploy definition updates 3 times per day
      • Real time actions
      • Run Definition updates
      • Run Quick Scan
      • Run Full Scan
      • Allow threats
      • Exclude paths and / or files
  • App-V
      • App-V 4.6 SP2 support (this is needed for Windows 8 support)
      • App-V 5.0 support
  • PowerShell support
      • PowerShell provider
  • Alerts
      • All alerts support email notification
      • Admin can choose to ignore specific types of alerts
  • Software updates
      • If the client is Internet based and can’t get their updates from the SUP they can be configured to go to Windows Update
  • Configuration Manger 2012 SDK comes out at the end of June 2012
Posted in Uncategorized | Leave a comment

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!

Posted in Uncategorized | Leave a comment

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; }

Posted in Uncategorized | Leave a comment

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/

Posted in Uncategorized | Leave a comment

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
Posted in Uncategorized | Leave a comment

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.

Posted in Uncategorized | Leave a comment

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

.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; }

Posted in Uncategorized | 1 Comment