Application and server owners want to know which SCCM maintenance window(s) their servers are in. I created an SSRS report that lists all future maintenance windows with their memberships.
Caveats that may or may not be important to you:
- We don't use recurring maintenance windows so this has not been tested with them. I don't know what it will do... could be an infinite loop.
- We don't use maintenance windows for our workstations, just for servers. If you use maintenance windows for workstations this report might get pretty bulky.
That said, here's the report. As always please open it up in an editor (its an XML file) before using it.
Never blindly trust things you find on the Internet
Link: Upcoming Maintenance Windows with Membership.rdl
<?xml version="1.0" encoding="utf-8"?><Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition"><Description>Shows all future maintenance memberships with their collection names and full membership. Created by Mark Randol - http://randoltech.blogspot.com</Description><AutoRefresh>0</AutoRefresh><DataSources><DataSource Name="ID5C6358F24BB64a1bA16E8D96795D8602"><DataSourceReference>/ConfigMgr_DNV/{5C6358F2-4BB6-4a1b-A16E-8D96795D8602}</DataSourceReference><rd:SecurityType>None</rd:SecurityType><rd:DataSourceID>908a65b1-0e01-4914-bd30-162c7a2308a2</rd:DataSourceID></DataSource></DataSources><DataSets><DataSet Name="DataSet1"><Query><DataSourceName>ID5C6358F24BB64a1bA16E8D96795D8602</DataSourceName><CommandText>SELECTv_FullCollectionMembership.Name AS [v_FullCollectionMembership Name],v_Collection.Name AS [v_Collection Name],v_ServiceWindow.Name AS [v_ServiceWindow Name],v_ServiceWindow.StartTime,v_ServiceWindow.Duration,v_ServiceWindow.IsGMT,v_ServiceWindow.RecurrenceType,v_ServiceWindow.IsEnabled,v_ServiceWindow.ServiceWindowTypeFROMv_FullCollectionMembershipINNER JOIN v_ServiceWindowON v_FullCollectionMembership.CollectionID = v_ServiceWindow.CollectionIDINNER JOIN v_CollectionON v_Collection.CollectionID = v_FullCollectionMembership.CollectionIDWHEREv_ServiceWindow.StartTime > GETDATE()ORDER BYv_ServiceWindow.StartTime</CommandText><rd:DesignerState><QueryDefinition xmlns="http://schemas.microsoft.com/ReportingServices/QueryDefinition/Relational"><AutoJoin>False</AutoJoin><SelectedColumns><ColumnExpression ColumnOwner="v_FullCollectionMembership" ColumnName="Name" /><ColumnExpression ColumnOwner="v_Collection" ColumnName="Name" /><ColumnExpression ColumnOwner="v_ServiceWindow" ColumnName="Name" /><ColumnExpression ColumnOwner="v_ServiceWindow" ColumnName="StartTime" /><ColumnExpression ColumnOwner="v_ServiceWindow" ColumnName="Duration" /><ColumnExpression ColumnOwner="v_ServiceWindow" ColumnName="IsGMT" /><ColumnExpression ColumnOwner="v_ServiceWindow" ColumnName="RecurrenceType" /><ColumnExpression ColumnOwner="v_ServiceWindow" ColumnName="IsEnabled" /><ColumnExpression ColumnOwner="v_ServiceWindow" ColumnName="ServiceWindowType" /></SelectedColumns><Joins><Join Type="CROSS JOIN" ColumnOwner="v_FullCollectionMembership" /><Join Type="INNER JOIN" ColumnOwner="v_ServiceWindow"><On><From ColumnOwner="v_FullCollectionMembership" ColumnName="CollectionID" /><To ColumnOwner="v_ServiceWindow" ColumnName="CollectionID" /></On></Join><Join Type="INNER JOIN" ColumnOwner="v_Collection"><On><From ColumnOwner="v_Collection" ColumnName="CollectionID" /><To ColumnOwner="v_FullCollectionMembership" ColumnName="CollectionID" /></On></Join></Joins><Filters><Filter Operator=">"><ColumnExpression ColumnOwner="v_ServiceWindow" ColumnName="StartTime" /><FilterValue>01/02/2015 03:00:00</FilterValue></Filter></Filters></QueryDefinition></rd:DesignerState></Query><Fields><Field Name="v_FullCollectionMembership_Name"><DataField>v_FullCollectionMembership Name</DataField><rd:TypeName>System.String</rd:TypeName></Field><Field Name="v_Collection_Name"><DataField>v_Collection Name</DataField><rd:TypeName>System.String</rd:TypeName></Field><Field Name="v_ServiceWindow_Name"><DataField>v_ServiceWindow Name</DataField><rd:TypeName>System.String</rd:TypeName></Field><Field Name="StartTime"><DataField>StartTime</DataField><rd:TypeName>System.DateTime</rd:TypeName></Field><Field Name="Duration"><DataField>Duration</DataField><rd:TypeName>System.Int32</rd:TypeName></Field><Field Name="IsGMT"><DataField>IsGMT</DataField><rd:TypeName>System.Boolean</rd:TypeName></Field><Field Name="RecurrenceType"><DataField>RecurrenceType</DataField><rd:TypeName>System.Int32</rd:TypeName></Field><Field Name="IsEnabled"><DataField>IsEnabled</DataField><rd:TypeName>System.Boolean</rd:TypeName></Field><Field Name="ServiceWindowType"><DataField>ServiceWindowType</DataField><rd:TypeName>System.Int32</rd:TypeName></Field></Fields></DataSet></DataSets><ReportSections><ReportSection><Body><ReportItems><Tablix Name="Tablix1"><TablixBody><TablixColumns><TablixColumn><Width>1.60417in</Width></TablixColumn><TablixColumn><Width>2.125in</Width></TablixColumn><TablixColumn><Width>1.47917in</Width></TablixColumn><TablixColumn><Width>1.54166in</Width></TablixColumn><TablixColumn><Width>1.23958in</Width></TablixColumn><TablixColumn><Width>1in</Width></TablixColumn><TablixColumn><Width>1in</Width></TablixColumn></TablixColumns><TablixRows><TablixRow><Height>0.25in</Height><TablixCells><TablixCell><CellContents><Textbox Name="Textbox3"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>Collection Name</Value><Style><FontFamily>Tahoma</FontFamily><FontSize>11pt</FontSize><FontWeight>Bold</FontWeight><Color>White</Color></Style></TextRun></TextRuns><Style /></Paragraph></Paragraphs><rd:DefaultName>Textbox3</rd:DefaultName><Style><Border><Color>#7292cc</Color><Style>Solid</Style></Border><BackgroundColor>#4c68a2</BackgroundColor><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell><TablixCell><CellContents><Textbox Name="Textbox5"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>Member Name</Value><Style><FontFamily>Tahoma</FontFamily><FontSize>11pt</FontSize><FontWeight>Bold</FontWeight><Color>White</Color></Style></TextRun></TextRuns><Style /></Paragraph></Paragraphs><rd:DefaultName>Textbox5</rd:DefaultName><Style><Border><Color>#7292cc</Color><Style>Solid</Style></Border><BackgroundColor>#4c68a2</BackgroundColor><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell><TablixCell><CellContents><Textbox Name="Textbox7"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>Start Time (UTC)</Value><Style><FontFamily>Tahoma</FontFamily><FontSize>11pt</FontSize><FontWeight>Bold</FontWeight><Color>White</Color></Style></TextRun></TextRuns><Style /></Paragraph></Paragraphs><rd:DefaultName>Textbox7</rd:DefaultName><Style><Border><Color>#7292cc</Color><Style>Solid</Style></Border><BackgroundColor>#4c68a2</BackgroundColor><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell><TablixCell><CellContents><Textbox Name="Textbox1"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>Start Time (MDT)</Value><Style><FontFamily>Tahoma</FontFamily><FontSize>11pt</FontSize><FontWeight>Bold</FontWeight><Color>White</Color></Style></TextRun></TextRuns><Style /></Paragraph></Paragraphs><rd:DefaultName>Textbox1</rd:DefaultName><Style><Border><Color>#7292cc</Color><Style>Solid</Style></Border><BackgroundColor>#4c68a2</BackgroundColor><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell><TablixCell><CellContents><Textbox Name="Textbox9"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>Duration (min)</Value><Style><FontFamily>Tahoma</FontFamily><FontSize>11pt</FontSize><FontWeight>Bold</FontWeight><Color>White</Color></Style></TextRun></TextRuns><Style /></Paragraph></Paragraphs><rd:DefaultName>Textbox9</rd:DefaultName><Style><Border><Color>#7292cc</Color><Style>Solid</Style></Border><BackgroundColor>#4c68a2</BackgroundColor><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell><TablixCell><CellContents><Textbox Name="Textbox13"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>GMT</Value><Style><FontFamily>Tahoma</FontFamily><FontSize>11pt</FontSize><FontWeight>Bold</FontWeight><Color>White</Color></Style></TextRun></TextRuns><Style /></Paragraph></Paragraphs><rd:DefaultName>Textbox13</rd:DefaultName><Style><Border><Color>#7292cc</Color><Style>Solid</Style></Border><BackgroundColor>#4c68a2</BackgroundColor><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell><TablixCell><CellContents><Textbox Name="Textbox15"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>Enabled</Value><Style><FontFamily>Tahoma</FontFamily><FontSize>11pt</FontSize><FontWeight>Bold</FontWeight><Color>White</Color></Style></TextRun></TextRuns><Style /></Paragraph></Paragraphs><rd:DefaultName>Textbox15</rd:DefaultName><Style><Border><Color>#7292cc</Color><Style>Solid</Style></Border><BackgroundColor>#4c68a2</BackgroundColor><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell></TablixCells></TablixRow><TablixRow><Height>0.25in</Height><TablixCells><TablixCell><CellContents><Textbox Name="v_Collection_Name"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Label>Collection Name</Label><Value>=Fields!v_Collection_Name.Value</Value><Style><FontFamily>Tahoma</FontFamily><Color>#4d4d4d</Color></Style></TextRun></TextRuns><Style /></Paragraph></Paragraphs><rd:DefaultName>v_Collection_Name</rd:DefaultName><Style><Border><Color>#e5e5e5</Color><Style>Solid</Style></Border><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell><TablixCell><CellContents><Textbox Name="v_FullCollectionMembership_Name"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Label>Member Name</Label><Value>=Fields!v_FullCollectionMembership_Name.Value</Value><Style><FontFamily>Tahoma</FontFamily><Color>#4d4d4d</Color></Style></TextRun></TextRuns><Style /></Paragraph></Paragraphs><rd:DefaultName>v_FullCollectionMembership_Name</rd:DefaultName><Style><Border><Color>#e5e5e5</Color><Style>Solid</Style></Border><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell><TablixCell><CellContents><Textbox Name="StartTime"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Label>Start Time</Label><Value>=DateAdd("h",1,Fields!StartTime.Value)</Value><Style><FontFamily>Tahoma</FontFamily><Format>dddd, MMMM dd, yyyy h:mm:ss tt</Format><Color>#4d4d4d</Color></Style></TextRun></TextRuns><Style /></Paragraph></Paragraphs><rd:DefaultName>StartTime</rd:DefaultName><Style><Border><Color>#e5e5e5</Color><Style>Solid</Style></Border><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell><TablixCell><CellContents><Textbox Name="StartTime1"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>=DateAdd("h",-5,Fields!StartTime.Value)</Value><Style><FontFamily>Tahoma</FontFamily><Format>dddd, MMMM dd, yyyy h:mm:ss tt</Format><Color>#4d4d4d</Color></Style></TextRun></TextRuns><Style /></Paragraph></Paragraphs><rd:DefaultName>StartTime1</rd:DefaultName><Style><Border><Color>#e5e5e5</Color><Style>Solid</Style></Border><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell><TablixCell><CellContents><Textbox Name="Duration"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Label>Duration</Label><Value>=Fields!Duration.Value</Value><Style><FontFamily>Tahoma</FontFamily><Color>#4d4d4d</Color></Style></TextRun></TextRuns><Style /></Paragraph></Paragraphs><rd:DefaultName>Duration</rd:DefaultName><Style><Border><Color>#e5e5e5</Color><Style>Solid</Style></Border><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell><TablixCell><CellContents><Textbox Name="IsGMT"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Label>GMT</Label><Value>=Fields!IsGMT.Value</Value><Style><FontFamily>Tahoma</FontFamily><Color>#4d4d4d</Color></Style></TextRun></TextRuns><Style /></Paragraph></Paragraphs><rd:DefaultName>IsGMT</rd:DefaultName><Style><Border><Color>#e5e5e5</Color><Style>Solid</Style></Border><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell><TablixCell><CellContents><Textbox Name="IsEnabled"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Label>Enabled</Label><Value>=Fields!IsEnabled.Value</Value><Style><FontFamily>Tahoma</FontFamily><Color>#4d4d4d</Color></Style></TextRun></TextRuns><Style /></Paragraph></Paragraphs><rd:DefaultName>IsEnabled</rd:DefaultName><Style><Border><Color>#e5e5e5</Color><Style>Solid</Style></Border><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixCell></TablixCells></TablixRow></TablixRows></TablixBody><TablixColumnHierarchy><TablixMembers><TablixMember /><TablixMember /><TablixMember /><TablixMember /><TablixMember /><TablixMember /><TablixMember /></TablixMembers></TablixColumnHierarchy><TablixRowHierarchy><TablixMembers><TablixMember><TablixHeader><Size>2in</Size><CellContents><Textbox Name="Textbox17"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>Service Window Name</Value><Style><FontFamily>Tahoma</FontFamily><FontSize>11pt</FontSize><FontWeight>Bold</FontWeight><Color>White</Color></Style></TextRun></TextRuns><Style /></Paragraph></Paragraphs><rd:DefaultName>Textbox17</rd:DefaultName><Style><Border><Color>#7292cc</Color><Style>Solid</Style></Border><BackgroundColor>#4c68a2</BackgroundColor><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixHeader><TablixMembers><TablixMember /></TablixMembers><KeepWithGroup>After</KeepWithGroup></TablixMember><TablixMember><Group Name="v_ServiceWindow_Name"><GroupExpressions><GroupExpression>=Fields!v_ServiceWindow_Name.Value</GroupExpression></GroupExpressions></Group><SortExpressions><SortExpression><Value>=Fields!StartTime.Value</Value></SortExpression></SortExpressions><TablixHeader><Size>2in</Size><CellContents><Textbox Name="v_ServiceWindow_Name1"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Label>Service Window Name</Label><Value>=Fields!v_ServiceWindow_Name.Value</Value><Style><FontFamily>Tahoma</FontFamily><FontWeight>Bold</FontWeight><Color>#465678</Color></Style></TextRun></TextRuns><Style /></Paragraph></Paragraphs><rd:DefaultName>v_ServiceWindow_Name1</rd:DefaultName><Style><Border><Color>#c6daf8</Color><Style>Solid</Style></Border><BackgroundColor>#9eb6e4</BackgroundColor><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></CellContents></TablixHeader><TablixMembers><TablixMember><Group Name="Details" /><Visibility><ToggleItem>v_ServiceWindow_Name1</ToggleItem></Visibility></TablixMember></TablixMembers></TablixMember></TablixMembers></TablixRowHierarchy><KeepTogether>true</KeepTogether><DataSetName>DataSet1</DataSetName><SortExpressions><SortExpression><Value>=Fields!StartTime.Value</Value></SortExpression><SortExpression><Value>=Fields!v_FullCollectionMembership_Name.Value</Value></SortExpression></SortExpressions><Top>0.45556in</Top><Left>0.1875in</Left><Height>0.5in</Height><Width>11.98958in</Width><Style><Border><Style>None</Style></Border></Style></Tablix><Textbox Name="ReportTitle"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>Upcoming Maintenance Windows with Membership</Value><Style><FontFamily>Verdana</FontFamily><FontSize>20pt</FontSize></Style></TextRun></TextRuns><Style /></Paragraph></Paragraphs><rd:WatermarkTextbox>Title</rd:WatermarkTextbox><rd:DefaultName>ReportTitle</rd:DefaultName><Height>0.4in</Height><Width>7.27084in</Width><ZIndex>1</ZIndex><Style><Border><Style>None</Style></Border><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></ReportItems><Height>2.30556in</Height><Style><Border><Style>None</Style></Border></Style></Body><Width>13.03125in</Width><Page><PageFooter><Height>0.45in</Height><PrintOnFirstPage>true</PrintOnFirstPage><PrintOnLastPage>true</PrintOnLastPage><ReportItems><Textbox Name="ExecutionTime"><CanGrow>true</CanGrow><KeepTogether>true</KeepTogether><Paragraphs><Paragraph><TextRuns><TextRun><Value>=Globals!ExecutionTime</Value><Style /></TextRun></TextRuns><Style><TextAlign>Right</TextAlign></Style></Paragraph></Paragraphs><rd:DefaultName>ExecutionTime</rd:DefaultName><Top>0.2in</Top><Left>4.1875in</Left><Height>0.25in</Height><Width>2in</Width><Style><Border><Style>None</Style></Border><PaddingLeft>2pt</PaddingLeft><PaddingRight>2pt</PaddingRight><PaddingTop>2pt</PaddingTop><PaddingBottom>2pt</PaddingBottom></Style></Textbox></ReportItems><Style><Border><Style>None</Style></Border></Style></PageFooter><LeftMargin>1in</LeftMargin><RightMargin>1in</RightMargin><TopMargin>1in</TopMargin><BottomMargin>1in</BottomMargin><Style /></Page></ReportSection></ReportSections><rd:ReportUnitType>Inch</rd:ReportUnitType><rd:ReportServerUrl>http://yourserver/ReportServer</rd:ReportServerUrl><rd:ReportID>9be14d20-40dd-4d05-a4f6-ac4ed5959361</rd:ReportID></Report>
No comments:
Post a Comment