SCSM 2012: Asset Management Part 5 – Reports

This is the fifth part of the blog series about my Asset Management solution for SCSM 2012 R2.
Part 1: General overview
Part 2: Authoring – Classes and Relationships
Part 3: Folders and Views
Part 4: Authoring – Forms
Part 6: Runbook/Automation details

This part covers SSRS reports to export the data out of the SCSM database.

The reports are connecting to the ServiceManager database and not the DataWarehouse, so that also environments without a DW can use this.

You can import my published reports or you create your own based on this example.

Create a new report and use the table wizard or create a blank report. You need to define a DataSet to retrieve the data.

The shown details of the DataSet belong to the All Computer Assets report.

The DataSet has a lot of joins because of the class structure and that some properties are not in the base class but in the sub classes. That is also the reason why the report takes a while to run.

with tab1 as
(
select
AssetID_DFC39F85_F244_B890_ABE1_434092CE4CCF as AssitID,
DS2.DisplayName as Region,
DS.DisplayName as Country, DS1.DisplayName as Site,
DS3.DisplayName as Manufacturer,
A.WarrantySLA_2436F00C_8311_69F9_51BC_4A117865E691 as WarrantySLA,
A.WarrantyExpirationDate_9F77D31A_5E17_203A_9A1C_3053CA0CB5BD as WarrantyExpire,
DS4.DisplayName as AssetStatus,
A.AssetTag_323C1952_001D_7A22_769C_75E87571AC00 as AssetTag,
A.SerialNumber_B6972540_76A0_783B_4D52_50550ACBB70D as SerialNumber,
DS5.DisplayName as Model,
DS6.DisplayName as Type,
DS7.DisplayName as Category,
usr.DisplayName as Custodian,
from
MT_AssetManagementBaseClass as A with (NOLOCK)
inner join MT_ComputerAsset as C on A.BaseManagedEntityId = C.BaseManagedEntityId
left join dbo.DisplayStringView as DS2 with (NOLOCK) on A.Region_CC2529A0_A250_14EA_545F_64690C2641AD = DS2.MPElementId
left join dbo.DisplayStringView as DS with (NOLOCK) on A.Country_E7A6DA1E_DAA6_CEFF_0359_71D21E300887 = DS.MPElementID
left join dbo.DisplayStringView as DS1 with (NOLOCK) on A.Site_EF2272FC_B5F0_4FF4_19DB_873308FD9E21 = DS1.MPElementID
left join dbo.DisplayStringView as DS3 with (NOLOCK) on A.AssetManufacturer_C56091BA_FDCE_AA2A_F1BD_C6DE0483F1E5 = DS3. MPElementId
left join dbo.DisplayStringView as DS4 with (NOLOCK) on A.AssetStatus_B6E7674B_684A_040D_30B8_D1B42CCB3BC6 = DS4.MPElementId
left join dbo.DisplayStringView as DS5 with (NOLOCK) on C.AssetModel_9044D038_1D8D_F1C3_62D0_689A4FB335B6 = DS5.MPElementId
left join dbo.DisplayStringView as DS6 with (NOLOCK) on C.Type_91F17DA4_E885_2295_6A8F_99454001961A = DS6.MPElementId
left join dbo.DisplayStringView as DS7 with (NOLOCK) on C.Category_7A7B2210_04E4_DCB3_D669_401D6E5652FD = DS7.MPElementId
left outer join relationship rel with (nolock) on rel.SourceEntityId = A.BaseManagedEntityId and rel.RelationshipTypeId=’27C08AC8-422E-B3DB-DE9E-1AC2C3D351D7′ — AssetToCustodian Relationship
and rel.IsDeleted=0
left outer join MTV_System$Domain$User usr with (nolock) on rel.TargetEntityId = usr.BaseManagedEntityId
where DS4.LanguageCode = ‘ENU’
)
select distinct * from tabsq

The result looks like this: (All Computer Assets)

The solution has a report for all four asset types like (Computer Asset, Peripheral, Server Infrastructure Asset and Network Infrastructure Asset). The DataSet differs mainly in the selected table, in this example it is the MT_ComputerAsset.

There is also one additional report to list all Computer Assets owned by a Custodian:

You can find all reports on github.

SCOM: Sample Maintenance Mode MP works on SCOM 2016

With all the great changes related to Maintenance Mode in SCOM 2016 you probably only miss the possibility to easily set Maintenance Mode on the agent without the need of knowing the PowerShell script details.

My old Sample Maintenance Mode management pack can help you with this also on SCOM 2016. I have imported it into my SCOM 2016 test environment and set a server into maintenance mode through it without any problem.

It was required in the past, that you deploy the files separately to the agents to have the Splash Screen available. Now I have added the files to the Visual Studio solution and deploy them to c:\it\mom\mm. The solution also adds a shortcut to the default user desktop and to the public startup folder on Windows Server 2012 and above (also applies to the corresponding client versions). I have used the examples from David Allen’s blog post.

I have posted the sample sealed mpb file on github including also the whole solution.

To adjust the solution to your needs I recommend to change the text in the file OpsMgrMM.ps1 which runs the Splash Screen.
Also you can change the target directory in the DeployableFile.ps1:

$TargetDirectory = “C:\IT\MOM\MM” You will find both files in the Resources folder. When you have done your adjustments, then build the solution (seal the mps) and import the mpb file into your SCOM environment. If you had a previous version installed, then you will need to remove that first. Have fun with it! SCSM 2012: Asset Management Part 4 – Authoring Forms This is the fourth part of the blog series about my Asset Management solution for SCSM 2012 R2. Part 1: General overview Part 2: Authoring – Classes and Relationships Part 3: Folders and Views Part 5: Reports Part 6: Runbook/Automation details This part of the series covers the forms created with Visual Studio 2015 Community Edition (incl. VSAE). Each object which has a view in the solution also needs a form to enter values and a preview form (OneWay). The forms are based on the WPF User Control library (requires .Net 3.5). All forms and resources required for the forms are defined in the AssetManagementForms Assembly, which is referenced in the AssetManagement management pack in the References area and in the Forms.mpx. Forms.mpx: Each form needs to be listed here (standard and preview). The targeted TypeProjection defines which classes will be referenced in the form. <Form ID=”AssetManagementForms.Computer” Assembly=”AssetManagementForms.Assembly” Accessibility=”Public” Target=”ComputerAsset.Form.TypeProjection” TypeName=”AssetManagementForms.Computer”> <Category>Form</Category> </Form> <Form ID=”AssetManagementForms.Computer.Preview” Assembly=”AssetManagementForms.Assembly” Accessibility=”Public” Target=”ComputerAsset.Form.TypeProjection” TypeName=”AssetManagementForms.ComputerPreview”> <Category>Form</Category> </Form> A ResourceDictionary is used to give all EnumClass Guids a meaningful name (Resources.xaml): This dictionary is referenced in each form before the Grid definition: <UserControl.Resources> <ResourceDictionary> <ResourceDictionary.MergedDictionaries> <ResourceDictionary Source=”Resources.xaml“/> </ResourceDictionary.MergedDictionaries> </ResourceDictionary> </UserControl.Resources> All required libraries need to be referenced at the beginning of the forms: ComputerAsset Form: Order Form: The forms contain a Grid with two columns and a lot of rows. Each Panel contains a label and a field – of different types: TextBox, ListPicker, DatePicker or UserPicker. You can also only reference an image. Here are examples of the different types: <Label Name=”AssetID” Content=”{Binding AssetID, Mode=OneWay}” Height=”31″ FontWeight=”Bold” FontSize=”16″/> <TextBox x:Name=”AssetTag” Text=”{Binding AssetTag, Mode=TwoWay}” /> <Image x:Name=”Logo” Height=”40″ Source=”/AssetManagementForms;component/Logo_NoTag_150px.png”/> <scwpf:ListPicker x:Name=”AssetLifecycleStatus” HorizontalAlignment=”Stretch” Width=”Auto” ParentCategoryId=”{Binding Mode=OneWay, Source={x:Static local:Resources.guidAssetStatusTypeEnumRoot}}” SelectedItem=”{Binding AssetStatus, Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}”/> The ListPicker is used everywhere where we want to show the predefined values of the selected EnumType. <wpfToolKit:DatePicker Text=”{Binding GoodsReceivedDate, Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}” SelectedDateFormat=”FullDateTime” /> <scwpf:UserPicker x:Name=”AssetCustodian” User=”{Binding Path=ComponentAlias_d5fb8379_ba30_4351_9e9a_ec9fbd9a3b65, Mode=TwoWay}” /> If we also want to show the Related Items and History information, then we need to add the required tabs: <TabItem Header=”Related Items” x:Name=”tabItemRelItems”/> <TabItem Header=”History” x:Name=”tabItemHistory”> <scwpf:HistoryTab/> </TabItem> All Preview forms only have TextBoxes no Pickers and also no Related Items or History tab. ComputerAsset PreviewForm: Here are some links which helped me to create these forms: SCSM 2012: Asset Management Part 3 – Folders and Views This is the third part of the blog series about my Asset Management solution for SCSM 2012 R2. Part 1: General overview Part 2: Authoring – Classes and Relationships Part 4: Authoring – Forms Part 5: Reports Part 6: Runbook/Automation details This part of the series covers the folders and views created with Visual Studio 2015 Community Edition (incl. VSAE). The following details can be found in the AssetManagementViews.mp. Folders: (Folders.mpx) This entry creates the AssetManagement Workspace: <Folder ID=”Folder.AssetManagementWorkspace” Accessibility=”Public” ParentFolder=”EnterpriseManagement!ServiceManager.Console.RootFolder” /> Then we have the AssetManagement Root Folder: <FolderItem ElementID=”EnterpriseManagement!Microsoft.EnterpriseManagement.ServiceManager.UI.Console.Task.CreateFolder” ID=”FolderItem.AssetManagementRootFolder.CreateFolder” Folder=”Folder.AssetManagementWorkspace.RootFolder” /> This entry displays the view in the folder: <FolderItem ElementID=”EnterpriseManagement!Microsoft.EnterpriseManagement.ServiceManager.UI.Console.Task.CreateGridView” ID=”FolderItem.AssetManagementRootFolder.CreateView” Folder=”Folder.AssetManagementWorkspace.RootFolder” /> And below the root folder are all class related folders: Each folder has an icon defined through the ImageReference: Views: (Views.mpx) Each class has its own view, and I also created views for WindowsComputer objects, Users and Groups. Here are two examples how the views are assigned to the folders: <FolderItem ElementID=”View.ComputerAssets” ID=”FolderItem.AllComputerAssets.View” Folder=”Folder.ComputerAssets” /> <FolderItem ElementID=”View.AllWindowsComputers” ID=”FolderItem.AllWindowsComputers.View” Folder=”Folder.AssetManagementWorkspace.RootFolder” /> The views use all the same icon as the folders. Here is an example of the default config item image, which is used for the groups: <ImageReference ElementID=”View.AllUsersandGroups” ImageID=”ConfigurationManagement!ConfigItemImage16x16″ /> For each view we also need to create two categories: 1. To display the create class object task: (Here “Create Computer Asset”) <Category ID=”View.ComputerAssets.CreateAsset.Category” Target=”View.ComputerAssets” Value=”MESUA!Microsoft.EnterpriseManagement.ServiceManager.UI.Authoring.CreateTypeCategory”/> 2. To display tasks in general: <Category ID=”View.ComputerAssets.ViewTasks” Target=”View.ComputerAssets” Value=”EnterpriseManagement!Microsoft.EnterpriseManagement.ServiceManager.UI.Console.ViewTasks” /> Each view needs to have a TypeProjection defined to display fields from other classes in the same view. The TypeProjection represents the reference between the two classes. TypeProjections only work, if corresponding relationships are defined. <TypeProjection ID=”ComputerAsset.TypeProjection” Accessibility=”Public” Type=”ComputerAsset”> <Component Path=”$Context/Path[Relationship=’AssetToCustodianRelationShip’]$” Alias=”ComponentAlias_d5fb8379_ba30_4351_9e9a_ec9fbd9a3b65” /> </TypeProjection> In the views you use the Alias of the TypeProjection to guide Service Manager (through the relationship) to the other class fields. The second sample TypeProjection shows the GeneralAssets view, which is the longest one, because it references all classes. View details: Each view needs targets a class whose objects get displayed. For the GeneralAssets view it is the AssetManagementBaseClass. <View ID=”View.GeneralAssets” Accessibility=”Public” Enabled=”true” Target=”AssetManagementMP!AssetManagementBaseClass” TypeID=”EnterpriseManagement!GridViewType” … Then the TypeProjection needs to be referenced: …<ItemsSource>…<AdvancedListSupportClass.Parameters> <QueryParameter Parameter=”TypeProjectionId” Value=”$MPElement[Name=’AssetManagementMP!GeneralAsset_TypeProjection’]$” /> </AdvancedListSupportClass.Parameters>… </ItemsSource>… The <Criteria> area defines which objects will be displayed. We do not want to see objects with the status inactive. …<Criteria>… <SimpleExpression> <ValueExpressionLeft> <Property>$Context/Property[Type=’System!System.ConfigItem’]/ObjectStatus$</Property> </ValueExpressionLeft> <Operator>NotEqual</Operator> <ValueExpressionRight> <Value>{47101e64-237f-12c8-e3f5-ec5a665412fb}</Value> </ValueExpressionRight> </SimpleExpression> …</Criteria>… The next section in the view handles the <Presentation> of the data, i.e. which columns are displayed. If there are assemblies which need to be referenced to display data, then they are listed in front of the columns. In our case we reference the AssetManagementForms assembly: xmlns:local=”clr-namespace:AssetManagementForms;assembly=AssetManagementForms”>… Each column which should be displayed needs an entry with a binding to the class parameter. All are only for viewing therefore the mode is OneWay. Some string examples: <mux:Column Name=”SerialNumber” DisplayMemberBinding=”{Binding Path=SerialNumber, Mode=OneWay}” Width=”100″ DisplayName=”SerialNumber.View.GeneralAssets” Property=”SerialNumber” DataType=”s:String” /> Binding to the DisplayName of the Enum value: <mux:Column Name=”Country.DisplayName” DisplayMemberBinding=”{Binding Path=Country.DisplayName, Mode=OneWay}” Width=”100″ DisplayName=”Country.View.GeneralAssets” Property=”Country.DisplayName” DataType=”s:String” /> And if the data comes from another class than the targeted one then you need to use the alias of the reference in the TypeProjection: <mux:Column Name=”AssetCustodian” DisplayMemberBinding=”{Binding Path=ComponentAlias_b1f46e16_e9a0_49af_b9f5_1c9d4bb532d1.DisplayName, Mode=OneWay}” Width=”100″ DisplayName=”AssetCustodian.View.GeneralAssets” Property=”ComponentAlias_b527cd23_9043_48a2_a3c8_111ab26f0a95.DisplayName” DataType=”s:String” /> Date example: <mux:Column Name=”GoodsReceivedDate” DisplayMemberBinding=”{datebinding:DateBinding Path=GoodsReceivedDate, Mode=OneWay}” Width=”100″ DisplayName=”GoodsReceivedDate.View.GeneralAssets” Property=”GoodsReceivedDate” DataType=”s:DateTime” /> There are also some internal fields which could be useful to be displayed:$TimeAdded$,$LastModified$. The last part of the presentation area is <ViewStrings>. Each DisplayName of a column needs to have an entry there also: <ViewString ID=”AssetCustodian.View.GeneralAssets“>$MPElement[Name=”AssetCustodian.View.GeneralAssets”]$</ViewString> So, all views are build relatively the same as they are all grid views targeted to one class. The main difference are the TypeProjections and columns which are displayed. Here are some additional links which help to understand the Presentation area: I will publish the whole Visual Studio Project at the end of my series. SCOR 2012: Monitor expired CA certificates A lot people might think, why should I monitor expiring certificates with System Center Orchestrator 2012. There are already solutions to monitor certificates in the local store on Windows computers – for example the wonderful solution from Raphael Burri (PKI Certificate Verification MP). But we had an issue with an expired certificate on non-windows systems. With that problem the idea came up to directly query expired certificates from the certificate authority (CA) – our internal cert store. We also wanted to notify the requesters directly, when a certificate expires within the next 30 days, send him/her reminders and last but not least inform the management a few days before the certificate expires, if no one has taken an action. With that idea we through about an automation solution – System Center Orchestrator. Here is the description of the solution. Prerequisites: • System Center Orchestrator 2012 R2 • Exchange Mailbox to send and receive emails and a user which has permissions to it • Exchange Users Integration Pack • PS PKI PowerShell module installed on all Runbook servers • Microsoft Certification Authority • SQL Temp database (Orchestrator Temp) Temp Database config: Mailbox folders: Exchange Users Integration Pack Config: GetEmail: Move Email: For both configurations you need to know the path of your Exchange Server EWS service: https://autodiscover.fqdn/EWS/Exchange.asmx and the user with domain and password, which owns the mailbox. After importing the runbooks, you will also need to configure the SQL activites (enter DB instance and Database) and the Send email activities (SMTP server and addresses). There will be some more things to change, but I will mention them in the runbook descriptions. I suggest to run the runbooks through a scheduled task on a daily basis. The first runbook reads the certificates and writes them to the database: Certificates.WriteToDB Update the template list, you want to check, in the GetCerts Activity:$Templist=@(
“WebServer”, #Web Server
“1.3.6.1.4.1.311.21.8.15817755.12287325.10963384.6580300.14252506.110.2574398.13148790” #Web Servers
)

Enter the names of the CAs which should be checked:
$canames=@(‘CA1FQDN‘,’CA2FQDN‘) Certificates.GetRequesterEmail Update the domain names and FQDNs which should be checked in the Get Requester Email activity. function get-domain($dn)
{
if($dn -like “*,dc=domainshortname1*”){$domain = “domain1fqdn“}
if($dn -like “*,dc=domainshortname2*”){$domain = “domain2fqdn“}
return \$domain
}

Now that we have all expiring certificates in the database, we need a runbook, which sends out the notifications.

The first email will be send out when 30 days are left and it should be send to the service desk to create a ticket. The requester will be on CC. The second email is send to the requester from day 29 to day 4. And then the last emails will be send to the management and the requester as CC.

We also created two additional runbooks to update the requester, if this information is not current anymore or to stop the notifications, if the certificate is already extended.
The requester has the option in his emails to click on a link which creates a new pre-filled email for both situations. If the certificate PPR changed, then he/she should enter the email address of the new PPR into the CC field and send the email. If the certificate was extended, then he/she only needs to send the email.

The emails will be sent to the mailbox for which we configured the Exchange User IP before.

Information:Certificate extended email

Information:Change certificate PPR email

Certificates.UpdateRequesterEmail

This runbook takes the email address of the CC field and writes it to the database, then it moves the email to the Certificates.NewRequester folder in the mailbox.

Certificates.SetDeleted

This runbook only sets a field in the database to deleted for the mentioned certificate in the email. Then it moves the email to the Certificates.Finished folder in the mailbox.

This is the whole solution.

Most of the activities are PowerShell activities, so it should not be a big effort to also migrate the runbooks to Azure Automation or SMA.

OMS Management Packs

If you ever have to author a SCOM management pack, which references Microsoft OMS resources, then it would be good, if you have the management packs to reference.

I was recently searching for the Microsoft.IntelligencePacks.Performance.mp and Microsoft.IntelligencePacks.Types.mp, but could only find the xml here. Sealing this management pack did not help as I needed the original MP (with the signature from Microsoft) for my management pack.
But SystemCenterCore.com provided the information that the MP is part of the Microsoft.IntelligencePack.Core.mpb. And the last hint came from a MVP colleague (thanks Stefan!).

You can find all OMS (formerly Advisor) management pack bundles on the SCOM Management Server which is already connected to OMS. They all get downloaded to C:\Windows\Temp.

Don’t get confused by the names, you can use them as they are. So, for my reference, I selected the Microsoft.IntelligencePack.Core_635779185101086268.mpb in Visual Studio and all included Management Packs appeared – also the Microsoft.IntelligencePacks.Types.mp :-).

SCSM 2012: Asset Management Part 2 – Classes and Relationships

This is the second part of the blog series about my Asset Management solution for SCSM 2012 R2.

Part 1: General overview
Part 3: Authoring – Folders and Views
Part 4: Authoring – Forms
Part 5: Reports
Part 6: Runbook/Automation details

This part of the series covers the classes and relationships created with Visual Studio 2015 Community Edition (incl. VSAE).

Classes
The solution has two base classes

• AssetManagementBaseClass (DisplayName: Asset)
The AssetID is automatically filled and auto-increments.
Some fields reference lists (EnumType), so that the user can select the entries from predefined values.
• OrderBaseClass (DisplayName: Order)

Four sub-classes (based on AssetManagementBaseClass):

• ComputerAsset (DisplayName: Computer Asset)
• Peripheral (DisplayName: Peripheral)
• ServerInfrastructureAsset (DisplayName: Server Infrastructure Asset)
• NetworkInfrastructureAsset (DisplayName: Network Infrastructure Asset)

ServerInfrastructureAsset and NetworkInfrastructureAsset are similar to Peripheral, that is the reason why I did not add a picture. Because of the inheritance the sub-classes get all properties from the AssetManagementBaseClass.

And 8 Groups to be able to give permissions to these objects. They all look like this:

There are separated Groups for Assets, Computer Assets, Peripherals, NetworkInfrastructureAssets, ServerInfrastructureAssets, Orders, Windows Computers and AD Users.

Each group needs to be discovered. So the solution also has 8 discoveries like this:

EnumTypes
Each property which should use a list needs an enumtype defined. I also created three additional entries for the AssetStatus list

Categories
Categories need to be defined, to be able to see and edit the lists in the console.

The Category  Category.ManagementPackName is required to define that this a SCSM Management Pack:

Here is an example how the other categories look like:
The categories with Value=”System!VisibleToUser” enables the user to view the list in the list view.
lets the edit task appear in the list view.

Relationships
Relationships are required to be able to reference the different class objects with each other. All relationships are from the type System.Reference.

• Asset To Order Relationship:
• Asset To Custodian Relationship:

With that an AD User can be linked to the Asset as Custodian.
• Asset To Computer Asset Relationship:

The same type of relationship is also created for Asset To Peripheral, Asset To NetworkInfrastructureAsset and Asset To ServerInfrastructureAsset.

Remember these relationships as you need to create them when you want to view fields from different related classes in the same view/form. This means when an ComputerAsset object gets created then you also need to create relationship object for the AssetToComputerAsset relationship. Otherwise you cannot show field values from both classes in the same view/form.

You can also create new classes i.e. for mobile devices or software assets. Use these classes here as an example and create all required objects (EnumTypes, Groups, Discoveries, Relationships, Categories).