Want to ‘Design a new report’ in SCOM 2007?

Most have you will have clicked on the ‘Design a new report’ button and found that there were ‘No Data Sources available’.

Here is how you can setup this feature for SCOM 2007.

CREATE A REPORT MODEL
1. Open SQL Server Business Intelligence Development Studio
2. Click File -> New -> Project
3. Select Report Model Project
4. Fill in a Name and Location
5. Click OK

CREATE A DATA SOURCE
1. In Solution Explorer, right-click Data Sources, Click Add New Data Source

2. Click Next on the Welcome Screen
3. Click New
4. Fill in the connection to the SQL Server and Instance (ie: SERVER\INSTANCE) to your SCOM Database
5. Select the Database you want to interact with. (ie: OperationsManagerDW)

6. Click OK
7. Click Finish
You will now see the Data Source under Solution Explorer
CREATE A DATA SOURCE VIEW
1. In Solution Explorer, right-click Data Sources, Click Add New Data Source View
2. Click Next
3. Select the data source from the Relational data souces: panel
4. Click Next
5. This is the part where you need to think, You will need to select the tables and views you want to include in the model. If you not sure and just want to play around you can however I would not suggest it… but you can sellect all the tables, however leave the views out as they can cause issues.
6. Click Next
7. Click Finish
You will now see the Data Source View under Solution Explorer

CREATE A REPORT MODEL
1. In Solution Explorer, right-click Data Sources, Click Add New Report
2. Click Next
3. Select the Data Source and Click Next
4. Select the Model Rules and Language (If unsure, keep the defaults) Click Next
5. Select Update model statistics before generating and Click Next
6. Type in a name Name and Click Run
Note: This may take some time depending on the number of Tables and Views selected when creating the Data Source View.
7. Click Finish
8. Under Solution Explorer Right-Click the new Report Model and selct Deploy

9. Exit SQL Server Business Intelligence Development Studio and Save.
RUN DESIGN A NEW REPORT
1. Open System Center Operations Manager Console
2. In Report Click Design new report
3. Report Builder will launch and you will see a Data Source
4. Highlight it and Select a Report Layout Click OK

HAVE FUN!!!

Obtaining Certificates for Non-Domain Joined Agents Made Easy With Certificate Generation Wizard

We have created a new UI tool to make obtaining mass certificates easy.

Here at OpsMgr, we understand the pain that you have to go through to configure certificate authentication to deploy non-domain joined agents.  There are many things we've provided for you to make obtaining certificates easier.  However, we know we're far from getting to that seamless solution, and are continually providing new tools to help facilitate this process. 

Here's a quick lowdown: To mutually authenticate the non-domain joined agent, both the non-domain joined agent and the server both require a personal computer certificate and a root CA certificate.  This can be accomplished through two basic steps:

1.  Request and acquire the certs from a Certification Authority (CA). 

Your company may already have an Enterprise CA set up if using PKI, but if not, you can install a CA (just add it as a role, like you do any other role in Win2K3 and up) and request certificates from there. 

2.  Install the certificates onto the local machine certificate store of the agent and server computer.  Run MOMCertImport.exe tool.

This step is required to, in a sense, "register" your certificates to your computer.  The MOMCertImport tool will alert OpsMgr of which certificates you would like to use.

To make it easy, we have developed a tool (attached below as CertGenBinaries.zip) to help simplify the process.

CertGenWizard.exe is a wizard tool which will take your CA information as input (it isn't required if you are running the wizard on the box with the CA), take in the computer names (has to be FQDNs), and send out a request for the certificates you need.  Now, you no longer have to fill out the Certificate Request form or enter parameters or connect to the web enrollment service.  Once the certificates are approved, there is a Retrieve button in the CertGenWizard which will allow you to retrieve the certificates that you have requested.  On top of the personal certificates, the wizard will retrieve the root CA certificate.

The biggest benefit to this tool is the added ability to request multiple certificates at once.  If you have 100 non-domain joined agents that you need to set up cert auth for, you can simply request all 100 machine certificates at once, retrieve them all, and manually bring them over to your other machines. 

Once you have brought them to your other machines, CertInstaller.exe is a second tool that will install the certificates into the local machine store of your computer and run MOMCertImport.exe for you.  Note: Install OpsMgr Agent FIRST and then run the tool!

Below are the steps to using the tool:

Pre-requisites:

-.NET Framework 3.0

-A Certification Authority (Win2K3/Win2K8 Enterprise/Stand-alone CA)

-If it is an Enterprise CA (an OpsMgr certificate template must be created)

-make sure createReqFile.bat is in the same directory as the CertGenWizard.exe

-MOMCertImport.exe must be in the same directory as CertInstaller.exe.

Using CertGenWizard.exe:

Installing the wizard:

1. Download the .zip file and unzip it on to a computer with a CA or that has access to a CA.
2. Run CertGenWizard.exe.

Requesting certificates:

1. 
   
   Discover your CA page - Supply your CA information to find a particular CA to use.  If you don't have a CA installed, you'll have to install one yourself.  Note: The wizard won't continue if it doesn't detect a CA.
2. 
   
   Certificate Request page - Enter the FQDNs of the computers you need certificates for (all the agents and servers), a save directory.  Note: If you have an Enterprise CA, a drop down box will appear and you must select a certificate template.  This must be created beforehand by your CA admin.  The instructions to create an OpsMgr cert template are included in the OpsMgr Security Guide.
3. 
   
   Hit Create.

Notes: 

A processing page will pop up showing the status of each certificate request.

The root CA certificate will also be downloaded at this level and saved as RootCertificate.cer. 

Retrieving certificates:

1. 
   
   If auto-approve is on, your certificates will be retrieved automatically.  You're done.
2. 
   
   Otherwise, the pending certificates will be displayed in the next screen.
3. 
   
   Ask your CA admin to approve the requests.  At this point you can close the wizard and come back to it.  If you are the CA Admin, log on to your CA machine, run cmd --> certsrv.msc to open your CA console.  Go to Pending Requests, and find the request ID of the certificates you have requested and issue them.  Close the console once you're done.
4. 
   
   Open your wizard if it's closed, view your pending Certificate Requests and hit Retrieve.

Status:

The final page will alert you of your status.  It will alert you to say which certificates have been denied, which have been approved, and which still are pending.

Using the Certificate Installer:

Note: Install the OpsMgr agents BEFORE running the Installer.

What you need on the agent machine:

• 
  
  CertInstaller.exe
• 
  
  The generated machine certificate (ex. server1.contoso.com.cer)
• 
  
  Root certificate (RootCertificate.cer)
• 
  
  MOMCertImport.exe.

1. 
   
   Load the machine certificate.
2. 
   
   Load the root certificate.
3. 
   
   Click install.

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included utilities are subject to the terms specified athttp://www.microsoft.com/info/cpyright.htm

-Adam Kiu

System Center Operations Manager

Attachment(s): CertGenBinaries.zip

How to create a Recovery task in SCOM on a windows service.

When you have the requirement to monitor a windows service through Microsoft’s System Center Operations Manger and have it restarted automatically you can not use the management pack templates. The reason for this is that the templates are stored in locked MP’s that you do not have access too.
Follow the following steps to monitor a service and have it restarted automatically by SCOM if it fails.


Steps

Create a management pack to store the monitor in

Create a group that the monitor will be applied against

Create a monitor that watches the service

Create overrides (apply to group)

Create the recovery task

Step 1: Create the Management Pack

In the managment console under Administration right click on Management Packs and select “Create Management Pack”. Give this new MP a meaningfull name and continue.



Step 2: Create the Group

• In the Authoring Pane right click on “create new group” Give this new group a meaningful name. A description. And select the management pack that you created.

Important: Do not use the default management pack. You can do it. But this will create problems when trying to remove MP in the future. Always use Custom MP’s.

• Say next. Click on Add/remove objects on the Explicit Group Members. Enter a server name and search. Select your computer object and say ok. You can now add dynamic and exclusions. I am not going into that on this post so except all defaults.

Step 3: Create the Monitor

• In the Authoring Pane right click on monitors and select Create a monitor, then Unit Monitor.

• Expand Windows Services, and select “Basic Service Monitor”. Select the custom management pack.

• Provide a name for the monitor. I am going to create this monitor to watch Windows Update services. Select the monitor target as windows Server Operating System. Select the Parent Monitor as Availability.

• Next Browse to the server, or any server with the service running. and select the service.

• The next window shows how the monitor sees the health state. Accept the default.

• The last window is how the alerts are configured.

• uncheck the “Generate alerts for this monitor:” for now. This will prevent any unwanted alerts until we are ready.

Step 4: Apply to group
Lets apply this MP against the group that we created earlier and disable it for all other servers.
Fist disable the rule for all servers

• Find the new rule under Authoring\monitors. The easiest way to do this is to change your scope. Click on the Change scope in the top right corner
• Look for windows server operating System and select it and say ok.

• Expand the monitor, right click on it and select properties.

• Select Disable and “For all objects of type: windows Server Operating System”

• Next Right click on the same monitor and select override, Override the Monitor, for a group

• Start to type the name of the group you created and select it.
  
• Place a check beside “Parameter name”. Change the override setting to True. This will enable the rule for the group.

• Say OK to save.

STEP 5: Create Recovery Task

• In the Authoring Pane right click on monitors and select the monitor you created by right clicking on it and select properties.
• Click on the Diagnostic and Recovery Tab
• Under the configure recovery Tasks select Add… and choose “Recovery for Critical Health State”
  
• Select Run command
• Provide a meaningful name and description
• Select the recovery target as Windows server operating system
• Check run recovery automatically and recalculate monitor state.

• Next, for the full path to the file type C:\WINDOWS\system32\net.exe This will spawn the net command
• for the parameters type start service name. We are starting windows update service. Here is the best way to find the service name. Open services by opening the run box and type services.msc

• now that you have the actual service name lets enter it in the parameters field. so you would type “Start wuauserv”

• Once you have clicked on create you are done.

How to create a SCOM Windows Events Monitor and alert on the Description field

When creating a monitor that alerts on event logs you may want to be able to monitor based on key words in the description field. This is not a default parmater and needs a few extra steps. But is still very easy to accomplish once you now the steps.
here are the two variables you will be adding to the monitor
parameter name: EventDescription
Alert description: $Data/EventDescription$


1. When you are creating the Event Expression click on insert, then click on button “…: under parameter name
2. Select Use Parameter Name not specified above and enter EventDescription

$Data/EventDescription$

Select an Event Property-EventDescription

3. change your operator to Contains
4. under the Value, enter the words you want to find in the desction field.

Build Event Expresion - operator and value

THIS IS NOT DONE!!!!

5. Continue to build your rule until you arrive at the Configure Alerts page. Enter the value $Data/EventDescription$ in the Alert description window. If you do not you will receive errors.

6. Create the rule, and refresh how ever you like. When i am in a hurry i will restart the health service on the server that I am monitoring.
7. To test your rule the OpsMgr Event Creator tool is not going to work. It does not allow you to create custom descriptions. Log onto the server that you want to monitor and open a command window. Using the eventcreate command type the following

eventcreate /t error /ID 1000/d “fieldxu.exe THIS IS JUST A TEST BY Brad Hearn”
/t sets as an error
/ID is the event id
/d is what will be placed into the description field. Remeber to place quotes around your text.

The alerts will look something like this.

Hope this helps out.

Out of date Hardware Inventory sms

Here is a query to create a collection of computers (last hardware scan > 60 days ) that haven't had there HW inventory updated in 60 days, you can change this to what ever time period you want. This will work on both SCCM or SMS

---------------------------------------------
select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,
SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,
SMS_R_System.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name not in (select SMS_R_System.Name from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_WORKSTATION_STATUS.LastHardwareScan >= DateAdd(dd,-60,GetDate()) )
-----------------------
You could also add a query to this collection to pull all computers with no SMS client then you have all your comptuers in one collection to work with. You could force HW scans on the collection, force Client installs or whatever maintenance you need.

Using certificates SCOM

CA Server – We have a single stand-alone CA server, which only purpose is to create certificates for the SCOM 2007 environment. The root CA is called JAMA00.
Requesting a certificate – From the target server (running the agent), we have no access to the CA server’s web interface. Therefore we must manually generate a certificate request and upload this request to a central file share.


Creating a certificate – We have automated the process of picking up the certificate requests (from the central file share) and generating a certificate file. We also generate an executable installer file, which will install the newly generated certificate on the target server and will configure SCOM to use this certificate for communication with our environment.
Importing a certificate – We support several ways for importing the certificate at the target server. This can be done before, during or after the SCOM agent has been manually installed (as long as our scripted version of the manual installation is used).

---

CA Server – Default a certificate issued by a CA is valid for one year. To prevent us from replacing all certificates after a year we have changed the configuration, so certificates are valid for a period of 100 years. See this Microsoft article on how to make these settings:

But since a certificate can not surpass the lifetime of the root CA, we have also set the expiration date of the root CA certificate to 100 years:

So effectively, all certificates are valid until the expiration date of the root CA certificate.

---

Requesting a certificate – To request a certificate, the certreq.exe application must be available on the system. This is default the case for all versions of Windows, with the exception of Windows 2000.
Remember that we do not have access to the web interface of the CA server, so we make a manual request from the command line.
The certreq.exe must be supplied with a file which describes the required certificate for which a request file must be generated. This file should contain this information:

[Version]
Signature= "$Windows NT$"
[NewRequest]
Subject = "CN="
KeySpec = 1
KeyLength = 1024
KeyUsage = 0xa0
ProviderName = "Microsoft RSA Schannel Cryptographic Provider"
ProviderType = 12
RequestType = PKCS10
Exportable = TRUE
MachineKeySet = TRUE
UseExistingKeySet = FALSE
[EnhancedKeyUsageExtension]
OID = 1.3.6.1.5.5.7.3.1
OID = 1.3.6.1.5.5.7.3.2

must be the FQDN of the server when it is a domain member or the computer name when it is a workgroup server.
It is possible to set the value for “Exportable” to “FALSE”, so the private key is no longer exportable. However Microsoft states that the private key must be exportable (I don’t know why).
When we name this file: certreq.inf, the command to generate a certificate request file will become:

certreq -new -q -f certreq.inf certificate.req

This will generate a request file called: “certificate.req”
We have scripted all of these steps and made sure that the actual name of the request file is set to the FQDN of the computer generating the request. This will prevent any duplicate name issues when copying the request file to the shared location. So in general terms we execute:

certreq –new –q –f certreq.inf .req

And here the part is the FQDN of the computer (or the computer name for workgroup computers). So we now have a certificate request file, which name is unique in the SCOM environment.
This certificate request file can now be placed on the central file share.

---

Creating a certificate – After the certificate request file has been placed on the central file share, the request file will be processed by a script to generate the required certificate file for the target server.
To generate a valid certificate, the following steps are performed:

 Submit the request

To submit the request, the following command is given on the CA server:

certreq -submit -q -config ""

Where equals the FQDN of the CA server and is the request file from the target server.
This command will pass back an Id, which we can use in the next step of processing the certificate request. The certificate is now in the pending state and must be issued before it can be used.

Issue the request

To issue the request, the following command is given on the CA server:

certutil -resubmit

Here is the Id that was passed back from the submit command in the previous step. When completed, the certificate is issued and ready for use. We can now retrieve the certificate and use it at the target server.

Retrieve the certificate

To retrieve the request, the following command is given on the CA server:

certreq -retrieve -q -config "”

Again the equals the FQDN of the CA server, the equals the Id that was passed back from the first step and is the name for the retrieved certificate.
So when all is finished, we have a certificate file called , which can be imported on the target server that generated the certificate request file.
To make life easier we gave the final the same name as the FQDN of the target server, followed with the .cer extension.
So if a server is called “server01.company.local”, the final certificate file that is generated is called “server01.company.local.cer”.

Importing the certificate

To import the certificate, the newly generated certificate must be “accepted” by the machine that generated the certificate request. The following command is given on the target server:

certreq.exe -accept –q “"

This command will make the certificate file available for use on the target machine. Again, is the certificate file generated in the previous step.

Configuring SCOM agent

The final step in this process is to configure the SCOM agent to use this certificate for communication with the SCOM management servers. To do this, this command is given on the target server:

MOMCertImport.exe /SubjectName

Here is the FQDN of the target server and this should be equal to the FQDN used in the certificate.
After a restart of the SCOM agent, the agent will start communicating with the management servers, using the certificate.

Certificate installer package

When the certificate request is generated on the CA server, we also package this certificate file into an installer executable, which contains the certificate and a script that will accept this certificate on the local server and instruct the SCOM agent to start communicating, using this certificate (combining the import and configuring steps mentioned above).

---

The root CA certificate – Now this all works great, as long as both the agent and the management servers use a certificate generated by the same CA and do trust this CA.
The management servers are given a correct certificate and the root CA certificate during installation of the management server.
The target servers (running the agents) must also have the correct root CA certificate in the “Trusted Root Certification Authorities” in the local computer store.

We have completely scripted the (manual) installation of the agent and part of this script is the import of the root CA certificate in the “Trusted Root Certification Authorities” of the local computer.
This is done by running this command on the target server

certutil.exe –addstore root

The must be replaced with the root CA certificate file (JAMA00.cer in our case), which holds the root CA certificate.