Tag Archives: MimboloveSQL

SMA Management Pack Could Not Connect To Database Alerts – My Troubleshooting Experience

Written by Tao Yang

I’ve setup 2 servers for the SMA environment in my lab a while back. Yesterday, I loaded the SMA MP (version 7.2.89.0) into my OpsMgr management group. Needless to say, I followed the MP guide and configured the Database RunAs profile. However, soon after the MP is loaded, I started getting these 2 alerts:

  • The Service Management Automation web service could not connect to the database.
  • The Service Management Automation worker server could not connect to the database.

SNAGHTMLc49f04a

To troubleshoot these alerts, I firstly unsealed the management pack Microsoft.SystemCenter.ServiceManagementAutomation.2012R2.mp, as this is where the monitors are coming from. The Data Source module of the monitor type uses System.OleDbProbe probe action module to make the connection to the database.

SNAGHTMLc5582d0

To simulate the problem, I used a small free utility called Database Browser Portable to test the DB connection. I launched Database Browser using the same service account as what I configured in the RunAs profile in OpsMgr, and selected OleDB as the connection type:

SNAGHTMLc61bf04

I populated the Connection String based on the parameters (monitoring object properties) passed into the data source module: Provider=SQLOLEDB;Server=SQLDB01.corp.tyang.org\;Database=SMA;Integrated Security=SSPI

image

Note the Database Instance property is empty. this is OK in my lab because I’m using the default SQL instance. I’ll explain this later.

The test connection result is positive:

SNAGHTMLc687aa6

However, after connected, when I clicked the connection, nothing happened, the list of tables did not get populated. I then tried using my own account (which has god rights on everything in the lab), and I got the same result.

Long story short, after trying different configuration changes on the SQL server, I finally found the issue:

On the SQL server, the Name Pipes protocol was disabled

SNAGHTMLc705360

After I enabled it, I was able to populate the tables in Database Browser:

SNAGHTMLc7245ca

And within few minutes, the alerts were auto closed.

While I was troubleshooting this issue, I came across a blog post from Stanislav Zhelyazkov. In the blog post, Stan mentioned adding the DB instance name in the registry (where the discoveries are looking for). However, when I added “MSSQLSERVER” in the registry and forced re-discovery, the monitors became critical again and I received several 11852 event in Operations Manager event log:

SNAGHTMLc7e3526

I email Stan and he got back to me and told me he’s using a named instance in his lab and these monitors are working fine in his lab after he added the SQL instance name in the registry. He also told me he didn’t recall specifying the SQL Instance name during the SMA setup but the setup went successful. My guess is that the SQL Browser service must be running on his SQL server, so the setup had no problem identifying the named instance.

Conclusion

Based on my experience and Stan’s experience, we’d like to make the following recommendations:

  • Enable the Name Pipes protocol
  • If using the default SQL instance, please do not manually populate the registry key
  • If using a named instance, please add the SQL instance name in the registry if it’s not populated after setup.

 

Thanks Stan for his input on this one!

Using SCOM To Count Logs and Produce Reports

Written by Tao Yang

Recently, I’ve been asked twice to produce daily reports involves counting some kind of logs:

Scenario 1:

The support team need to count number of Application event log entries of events with a specific event ID. A daily report is required to list the number for each computer.

Scenario 2:

An application produces a log file each day. The support team need to count the number of a specific phrase appeared in previous day’s log file. A daily report is required to list the count number for each computer.

The solution I produced for both scenarios are very similar. so I thought I’d blog this one.

Solution from High level View:

  1. Create a rule in the SCOM management pack to run once a day.
  2. Write a script within a rule in the SCOM management pack to count the log
  3. map the count number to performance data and save it in the SCOM operational and data warehouse DB.
  4. design a report for raw performance data in SQL SRS report builder
  5. save the report into the management pack
  6. schedule the report to run and to be emailed out once a day, AFTER the rule has run for the day.

In this blog post, I’m not going to go through the steps of creating the custom data source module and the performance collection rule. They are pretty straightforward and the sample management pack can be downloaded HERE.

I will however go through the steps to create the custom report for the data collected by this rule. I’m creating the report rather than using the built-in performance reports from the “Microsoft Generic Report Library” because none of the built-in performance reports support a table format. I don’t want any fancy charts with the report. All I want is a simple list of the raw perf counter values.

Now, let’s briefly go through the data source module and the performance collection rule.

Data Source Module: contains 2 members: System.Scheduler and Microsoft.Windows.PowerShellPropertyBagTriggerOnlyProbe:

image

The Microsoft.PowershellPropertyBagTriggerOnlyProbe contains a powershell script that counts event log entries and pass the count into a PropertyBag:

#===========================================================================================
# AUTHOR:  Tao Yang
# DATE:    30/01/2012
# Version: 1.0
# COMMENT: Count for a particular event in event log and pass the count to property bag
#===========================================================================================
Param ([int]$TimeFrameInHours, [string]$LogName, [int]$EventID, [string]$EventSource)

$StartTime = (Get-Date).AddHours(-$TimeFrameInHours)
$iEventCount = 0
Try {
$Events = Get-EventLog -LogName $LogName -After $StartTime -Source $EventSource | Where-Object {$_.EventID -eq $EventID}
Foreach ($Event in $Events)
{
If ($Event -ne $null) {$iEventCount++}
}
} Catch {
$iEventCount = 0
}
$ComputerName = (Get-WmiObject Win32_ComputerSystem).Caption
$oAPI = New-Object -ComObject "MOM.ScriptAPI"
$OAPI.LogScriptEvent("Event-Count.PS1",9999,0,"Start EventID $EventID Perf Collection Rule. Collecting $EventID events since $starttime...")
$oBag = $oAPI.CreatePropertyBag()
$oBag.AddValue('ComputerName', $ComputerName)
$oBag.AddValue('EventCount', $iEventCount)
$oBag.AddValue('TimeFrameInHours', $TimeFrameInHours)
$oBag.AddValue('LogName', $LogName)
$oBag.AddValue('EventID', $EventID)
$oBag.AddValue('EventSource', $EventSource)
$oBag

Performance Collection Rule: This rule contains:

Data Source: the data source module created previously

Condition Detection: map the event log count in PropertyBag to performance counter

Actions: Write performance data to Operational and DW databases.

image

Report:

Pre-requisites:

  • Install the Performance Report Model in SCOM reporting SSRS. Here’s a detailed instruction (even though it was written for SCOM 2007 SP1, it’s also applies to SCOM 2007 R2): http://www.systemcentercentral.com/BlogDetails/tabid/143/IndexID/20269/Default.aspx
  • Please Note that in above article, it uses Event model as example. The report I’m going to create uses Performance model. so please make sure Performance.smdl is uploaded into SCOM Reporting SSRS and configured to use the “Data Warehouse Main” data source.
  • Import the half finished management pack (with the data source module and the perf collection rule) into a SCOM management group (preferably your development environment).
  • Create an override or simply change the schedule of the rule to run ASAP so the perf data is collected. this is very useful when testing the report later on.

Steps of creating the report:

01.Browse to the SCOM Reporting SSRS reports http://<servername>/reports URL

02. Launch Report Builder and click “Run” if security warning pops up

image

03. In Report Builder, choose the following options in “Getting Started” pane to create a new report:

image

04. Enter the report title:

image

05. Drag “Performance Data Raw into the report

image

06. Under Performance Data Raw / Object, Drag the “Name” field to the reportimage

07. Rename the title of each row in the report table:

image

08. Right click the number under “Event Count”, select “Format…”, and change “Decimal places” to 0

image

09. Click the Filter button to create filters:

image

10. Under Performance Data Raw \ Performance Rule Instance \ Performance Rule, drag the “Rule System Name” Field to the right and choose the rule I created in the management pack from the list. (Note: the rule name appears on the list because the management pack is already imported into SCOM and this rule has already collected some performance data.)

image

11. Click on Performance Data Raw and drag “Date Time” field to the right

image

12. Click on “equals” next to “Date Time” and change it to “After”:

image

13. Choose “(n) days ago”

image

14. Change “(n)” to “2”

image

15. Click OK to exit the Filter Data window

16. Now, it’s time to test run the report. To do so, use the Run Report button on the top. Here’s the result from my test environment (Note: the date time is in UTC, NOT local time):

image

17. If you want to make the report prettier (i.e. changing the font colour to pink Smile with tongue out) or adjust the column width, or adding a company logo, you can click on “Design Report” button and modify the report.

18. Once you are happy with the report, save it to a RDL (report definition) file:

image

19. Open up the half finished management pack (unsealed) in Authoring Console, go to Reporting workspace and create a new report:

image

20. Give the report an ID:

image

21. In the “General” tab, give the report a name and target it to “Microsoft.Windows.Computer” class

image

22. Go to “Definition” tab, click “Load content from file” and select the RDL file you’ve just created.

image

23. Once the RDL file is loaded, remove the first line, which is the XML header <?xml version=”1.0″ encoding=”utf-8″?>

image

24. Once the first line is removed, go to “Options” tab

25. Make sure “Visible” is set to “true” and “Accessibility” is set to “public”

image

26. click apply and OK to exit the window

27. Now that the report is successfully created and tested, if you have changed the schedule of the perf collection rule (either edited the rule directly or created an override), it’s time to change the schedule back.

28. Now, if you want to keep the management pack unsealed, just export the updated management pack with the report into SCOM management group from authoring console. If you want to seal it, do so, and delete the previous unsealed version from the management group first, then import the sealed version into the management group.

I always increase the version number so I can lookup Event ID 1201 in SCOM agent’s Operations Manager log and make sure the updated version of the MP is received:

image

29. After couple of minutes, if everything goes well, you should be able to see the report in both Operations Console Reporting workspace and also in SCOM Reporting SSRS site:

image

image

Note: In SSRS, you should also see a .mp file in the same folder. I’ve experienced issues where the report does not get updated with the updated MP, which was caused by incorrect .mp file in SSRS directory. Please refer to my previous post for details.

30. Schedule the report in SCOM reporting (so it can be emailed out according to a schedule) if you want to. make sure the report schedule is AFTER the rule schedule time (i.e. if the rule runs daily at 0:00am, the report schedule should be something like daily at 0:30am) otherwise newly collected data is not included in the report.

That concludes the steps to create the report. Few other things I’d also like to mention:

  1. In my case, for the second scenario I mentioned in the beginning (reading log files), the whole process and idea is the same. The only thing different is the script in the Data Source module.
  2. I could have moved the condition detection module (System.Performance.DataGenericMapper) from the rule to the data source module. I didn’t do it because then I can use the same data source module for other purposes later. For example, if later on, the support team comes to me and ask me to generate alerts once the count reaches a threshold, I can simply create a separate rule (or a custom monitor type and a monitor), using the same data source. If the input parameters of the data source is the same as the existing performance collection rule, the data source should only run once for multiple workflows because of the Cookdown feature.
  3. If the SCOM agent computer is in maintenance mode when the perf collection rule is scheduled to run, no perf data will be collected and the computer will be missing from the report.
  4. In my example, I’m using a PowerShell script. So PowerShell and it’s execution policy needs to be installed / enabled on the SCOM agent computers. if this doesn’t meet your requirement, just modify the module to use a VBscript instead. I’ve blogged previously on how to create trigger only probe action modules for VBScript.

Again, the sample MP and the Report Definition RDL file can be downloaded HERE.

Disabling Auto Discovery in SCDPM 2010

Written by Tao Yang

System Center Data Protection Manager is not something I normally play with. Recently, I’ve been dobbed in to troubleshoot an issue with remote sites network performance at work and the issue ended up was caused by Auto Discovery in DPM 2010.

So basically, DPM has this built-in function called “Auto Discovery” which queries the domain controller of its’ own home domain and stores every single domain member servers in its database. This job runs once a day, you can choose the time window of this job, but you can’t really disable it.

One of my colleagues has posted this issue in DPM TechNet forum: http://social.technet.microsoft.com/Forums/en-US/dpmsetup/thread/df3dc4ae-200a-4778-8a91-1d7e68d564f2/ and I also logged a premier support call with Microsoft. We got 2 very different solutions from TechNet forum and the Microsoft support engineer in China. After evaluating both solutions, I have decided to go with the solution from the TechNet forum since it’s more robust, but make some modifications.

I have made 3 modifications from the original SQL scripts from TechNet forum:

  1. The solution from TechNet forum involves creating a custom SQL agent job called ‘Cancel DPM Auto Discovery’ that runs once a day, prior to the DPM Auto Discovery job. I noticed if you manually change the Auto Discovery start time from DPM console, a new SQL agent job for Auto Discovery is created. So I can’t really guarantee that the original schedule for ‘Cancel DPM Auto Discovery’ job is still valid. Therefore, I changed the schedule from daily to hourly, and runs at the 55th minutes of each hour(i.e. 12:55am, 1:55am, 2:55am, etc.). Because the Auto Discovery job can only run at the full hour (1:00am, 2:00am, 3:00am), by changing the schedule, I can make sure no matter what time the Auto Discovery is scheduled to run, the SQL agent job that I have created will always disable it 5 minutes prior to it.
  2. As I mentioned in the forum thread, I had to change the SQL job category to something other than DPM otherwise DPM will delete my job.
  3. Since we have over 2000 DPM servers in the environment, manually running the SQL script on each DPM server is impossible. Therefore I created a PowerShell script to run the SQL scripts and use SCCM to push it out. During testing, I found the SQL script works if I manually run it from SQL management studio, but when running in PowerShell using either System.Data.SqlClient.SqlConnection object or COM ADO object, the script complained about not able to find @owner_sid at the step of creating the job. I fixed it by changing the job owner from “MICROSOFT$DPM$Acct” to “sa”.

Below is the SQL Script and the PowerShell script after my modifications.

SQL Script

PowerShell Script

Note: Both SQL script and Powershell script assume the DPM database is configured as default (which is located locally on the DPM server and the SQL instance name is left as default of ‘MSDPM2010’). If your DPM server is located elsewhere, please modify the SQL script and the SQL connection string in the Powershell script accordingly.