Howard Hoy

System Center 2012 Configuration Manager and other thoughts.

PowerShell – Maintenance Windows and ConfigMgr Console Extensions

PowerShell – Maintenance Windows and ConfigMgr Console Extensions

  • Comments 5
  • Likes

It’s been awhile and I’ve been having way to much fun with ConfigMgr 2012. I’ve been working for the last year on large deployment and would like to share some of the items that have made my day just a bit easier.

This article assumes you are all familiar with the PowerShell cmdLets introduced in SP1 and forward. If not go here and welcome to the fun.

My current environment we have a lot of collections based upon dynamic AD group memberships. I do not  have control of when devices are add, moved or deleted from AD. Some of these collections have very tight Maintenance Windows (MW) applied. To complicate matters worse we also have very strict patching deadlines for compliance that follow a schedule. After discovering that some of my devices were added to a collection due to an updated AD Group membership that had MW applied post compliance reporting day. Needless to say they were not compliant.

I live for scripting especially if ConfigMgr is involved. I also enjoy PowerShell and Console Extensions. What an opportunity.

Disclaimer – there are always more than one way to get information.  This is my method.

A MW can be viewed in the Console in  the individual collection properties.

image

Well that sucks for me as I have many Collections and some with MW windows. I would much prefer to click on a device in the Console and say “show me your MW windows”. So now goal has been defined. Let’s jump into GUID hell

image

Yup – it’s good.

image

I created a new .xml for my right click console action:

<ActionDescription Class="Group" DisplayName="Show Me My Maintenance Windows" MnemonicDisplayName="Show Me My Maintenance Windows" Description="Show Me My Maintenance Windows"><ShowOn><string>DefaultHomeTab</string><string>ContextMenu</string>  </ShowOn><ActionGroups> <ActionDescription Class="Executable" DisplayName="Show Me My Maintenance Windows" MnemonicDisplayName="Show Me My Maintenance Windows" Description = "Show Me My Maintenance Windows" RibbonDisplayType="TextAndSmallImage"><ShowOn><string>ContextMenu</string><string>DefaultHomeTab</string></ShowOn>
<Executable><FilePath>showmw.bat</FilePath><Parameters>"##SUB:Name##"</Parameters></Executable></ActionDescription>
</ActionGroups></ActionDescription>

This provides:

image

I use a batch file so I can make quick changes to avoid closing the console and refreshing the right click xml.

image

And yup – this one does have a MW.

image

The Powershell script.

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
052
053
054
055
056
057
058
059
060
061
062
063
064
065
066
067
068
069
070
071
072
073
074
075
076
077
078
079
080
081
082
083
084
085
# Environment Settings #
$CMSQLServer = 'your server FQDN'
$CMDatabase = 'CM_yoursitecode'
$ConnectionTimeout = 30
$QueryTimeout = 120


function GetServiceWindow() {
    [CmdletBinding()]
    param(
       [String]$vName = ''
    )

    [Array]$SWDS = @()
    [Array]$ServiceWindows = @()
    $strSql = "
 
    ;WITH AllServiceWindows AS
(SELECT fcm.ResourceID
,c.Name AS Collection
,nsw.Duration
,nsw.NextServiceWindow
,ROW_NUMBER() OVER (PARTITION BY ResourceID ORDER BY nsw.NextServiceWindow) AS RowNumber
FROM vSMS_ServiceWindow AS sw
JOIN v_FullCollectionMembership AS fcm
ON sw.SiteID = fcm.CollectionID
JOIN v_Collection AS c
ON sw.SiteID = c.CollectionID
CROSS
APPLY dbo.SCCM_GetNextServiceWindow(sw.Schedules, sw.RecurrenceType) AS nsw
)
SELECT sys.Name0 AS [Computer]
, sys.Distinguished_Name0 AS [AD DN]
, asw.Collection AS [Collection]
, CONVERT(CHAR(5), DATEADD(MINUTE, asw.Duration, 0), 108) AS [Duration]
, CONVERT(CHAR(16), asw.NextServiceWindow, 120) AS [Next_Maintenance_Window]
, DATENAME(WEEKDAY, asw.NextServiceWindow) AS [Day_of_Week]
FROM v_R_System as sys
LEFT
JOIN AllServiceWindows AS asw
ON sys.ResourceID = asw.ResourceID
WHERE sys.Client0 = 1
AND asw.RowNumber = 1
And sys.Name0 = '$vname'
ORDER
BY asw.NextServiceWindow
, asw.Collection
, sys.Name0
 
    
 
    "


    $Connection = New-Object System.Data.SqlClient.SqlConnection
    $ConnectionString = "Server=$($CMSQLServer);Database=$($CMDatabase);Integrated Security=True;Connect Timeout=$($ConnectionTimeout)"
    $Connection.ConnectionString = $ConnectionString
    $Connection.Open()
    $cmd=New-Object System.Data.SqlClient.SqlCommand($strSQL,$Connection)
    $cmd.CommandTimeout=$QueryTimeout
    $DataSet=New-Object System.Data.DataSet
    $DataAdapter=New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
    $Dataset = new-object system.data.datatable
    $DataAdapter.fill($DataSet) |out-null
    $Connection.Close()
    $ServiceWindows = @($DataSet)

    foreach ($ServiceWindow in $ServiceWindows) {
        $Object = New-Object PSObject -Property @{           
            Name = $ServiceWindow.Name
            Collection = $ServiceWindow.collection
            Duration = $ServiceWindow.duration 
            NMW = $ServiceWindow.next_maintenance_window
            TimeOfDay = $ServiceWindow.day_of_week              
        } 
        $SWDS += $Object
    }


    return $SWDS
}

$ServiceWindows = GetServiceWindow($args[0]) | Out-GridView

You can modify the script to remove the NetBIOS_name0 clause to find all devices if desired. This is just the start framework and the possibilities are endless to instant information with PowerShell and ConfigMgr.

Until next time.

Comments
  • Can you extend the script to convert the NextServiceWindow time which is in UTC to client local time?

  • Hi Howard,

    When I run this script I get this error:

    Exception calling "Fill" with "1" argument(s): "Invalid object name 'dbo.SCCM_GetNextServiceWindow'."
    At C:\Users\username\Desktop\New Text Document.ps1:63 char:5
    + $DataAdapter.fill($DataSet) |out-null
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

    Any ideas?

  • Great article, thanks for sharing.

  • Yes, this comes very handy especially when we have too many clients in different locations. Client local time will be very helpful.


  • You should be able to resolve the issue by replacing the lines between 064 to 068 with
    $DataAdapter=New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
    $DataTable = new-object system.data.datatable
    $DataAdapter.fill($DataSet) |out-null
    $Connection.Close()
    $DataTable = $DataSet.Tables[0]
    $ServiceWindows = @($DataTable)

    Hope this helps!!







Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment