Monthly Archives: April 2012

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.

Changing Display Language on Windows 7 Home and Professional Editions

Written by Tao Yang

I bought a laptop for other family members yesterday, it comes with Windows 7 Home Premium. I needed to change the display language from English to Chinese because the main user of this laptop does not speak English.

I thought it was a no brainer as I’ve done it before, all I had to do was to load another language pack in “Regional and Language” in Control Panel. However, I was wrong. apparently this function is available in Windows 7 Ultimate and Enterprise editions.

I didn’t really want to use Windows Anytime Upgrade to upgrade it to Ultimate just so I can change the language. Lucky I found this post: http://mark.ossdl.de/2009/08/change-mui-language-pack-in-windows-7-home-and-professional/

So below is what I’ve done:

  1. Download Windows 7 Service Pack 1 language pack (Because the laptop comes with Windows 7 SP1, I had RTM version of the language pack but it didn’t work.) – I downloaded the entire ISO from my TechNet subscription, but there are many blog posts around with the direct link to Windows Update for each individual language (such as this one: http://www.technize.net/windows-7-sp1-language-packs-direct-download-links-kb2483139/)
  2. Extracted the downloaded ISO (from TechNet subscription) to C:\Apps\langpacks
  3. in Command prompt:
    1. dism /online /add-package /packagepath:C:\Apps\langpacks\zh-cn\lp.cab
    2. bcdedit /set {current} locale zh-cn
    3. bcdboot %WinDir% /l zh-cn
  4. Backed up and deleted HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\MUI\UILanguages\en-US
  5. Reboot

Note: if there were any windows updates that were pending to be installed, the install may fail after the language was changed. I had to run wuauclt /detectnow so Windows Update agent detects the updates for different language.

SCCM 2012 Log Parser: cmtrace.exe

Written by Tao Yang

In my opinion, THE most used utility (other than SCCM console) for any SCCM administrators / engineers would have to be trace32.exe. Back in SMS and SCCM 2007 days, trace32.exe comes with the SCCM Toolkit, which contains a bunch of other tools.

Speaking of my own experience, out of all the tools provided by the toolkit, trace32.exe is the one I used the most.

Now with SCCM 2012, trace32.exe has been replaced by a new tool called cmtrace.exe.

Unlike trace32.exe, cmtrace.exe is actually built-in in SCCM, there is no need to download separate toolkits for it. cmtrace.32 can be found on the SCCM site server, under “<SCCM Install Dir>\tools\” folder. Same as it’s predecessor trace32.exe, cmtrace.exe can be copied / redistributed to other locations / computers alone and use as a log parser.

I have also found that trace32.exe actually does not correct parse SCCM 2012 logs. For example, I’m using both trace32.exe and cmtrace.exe to open execmgr.log from a SCCM 2012 client:

trace32.exe:

image

cmtrace.exe:

image

So, if you are working with SCCM 2012, make sure you use cmtrace.exe rather than the good old trace32.exe. And maybe like me, copy cmtrace32.exe to your local machine and use it from there rather than using it on the server.

Installing SCCM 2012 RTM Secondary Site using A Pre-Installed SQL Express 2008 R2 Instance

Written by Tao Yang

Since System Center 2012 was RTM’d few days ago, I have started updating / migrating my home environment. After I migrated my 2 Hyper-V servers from VMM 2008 R2 to VMM 2012, I have started building a brand new SCCM 2012 environment so I can migrate SCCM 2007 to it. My plan is to install a Central Admin site, a child primary site and a Secondary site so I have a simple 3-tier hierarchy like my existing 2007 and 2012 Beta 2 environments.

The Central Admin site and the child primary site installation all went pretty smoothly. But I had some issues when installing the secondary site.

When installing Secondary Site from it’s parent primary, There are two options available for the database:

  1. Install and Configure a local copy of SQL Server Express on the secondary site computer
  2. Use an existing SQL Server instance.

I wanted to install SQL Express myself so I can control where it’s installed to and locations for data, log and backup files. – This is pretty common and most of SQL DBAs would configure to install SQL on a volume other than C:\ and place data / logs / backups on dedicated and separate disks. By using SCCM to install SQL express for you, you don’t get to choose any of this, which can be pretty annoying.

According to Supported Configurations for Configuration Manager, secondary sites supports SQL Server Express 2008 R2 with SP1 and Cumulative Update 4. So I downloaded SQL Server 2008 R2 Express With SP1 with Tools (SQLEXPRWT_x64_ENU.exe) and SQL 2008 R2 Service Pack 1 Cumulative Update 4 and installed them in order on my secondary site site server.

Below is what I have customised during the SQL express install:

  • I configured the location for SQL, SQL instance, data files, log files and backup files the way I wanted it.
  • I selected the SQL instance to use the collation “SQL_Latin1_General_CP1_CI_AS because it is the only collation that SCCM supports.
  • I kept the default secondary site SQL instance name “CONFIGMGRSEC” (this name is what’s used if you choose SCCM to install SQL Express for you).
  • I have given a pre-configured AD group called “ConfigMgr2012 Servers” which contains all SCCM 2012 site servers sysadmin rights in SQL Express.

After the install, I applied CU4 and all went pretty smoothly.

Now, I tried to push Secondary Site install from the primary site. Under SQL Server setting step, I selected “Use an existing SQL Server instance” option and enter the secondary site server’s FQDN under “SQL server fully qualified domain name” and “CONFIGMGRSEC” under “SQL server instance name, if applicable”. After finishing the wizard, the secondary site install failed during prerequisite checks. I got few errors in regards to the SQL collation is not set to SQL_Latin1_General_CP1_CI-AS:

image

This is very strange because all my SQL instances in this hierarchy are set to this collation, and because of this, the setup did not even get kicked off.

Additionally, I also found the following:

  • On the primary site server, in the ConfigMgrSetup.log under System root, I get the following errors:
    • CSql Error: Cannot find type data, cannot get a connection.
    • *** [08001][17][Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
    • I could use the SQL management studio from Secondary site server to connect to the SQL express instance, but I couldn’t use the SQL management studio from a remote machine to connect to it:

image

After spending some time troubleshooting, I got it going. Below is what I have done on the SQL Express instance:

1. I’ve assign “ConfigMgr2012 Servers” group (which I created myself and it contains the primary site server’s computer account) “dbcreator” role on top of sysadmin role it already had.

image

2. I realised by default, after I installed SQL express, TCP/IP protocol is disabled. So I went to SQL Server Configuration Manager, under SQL Server Network Connection —> Protocols for CONFIGMGRSEC—>TCP/IP, enabled it. I also had to configure the ports for this connection:

I removed 0 from “TCP Dynamic Ports” for each IP and added static port 1433 under “TCP Port”

image

After you enabled TCP/IP and changed the port, you will be prompted that you have to restart SQL server service for the change to take effect, so I restarted the SQL service.

After these steps, the prerequisite checks were passed and the Secondary site installation finished successfully.

In summary below are the steps I took to pre-configure a SQL Express instance for SCCM 2012 secondary site:

  1. Install SQL Express 2008 R2 with SP1 with Tools
  2. Configure SQL express install directory as per my standard (not on C:\ drive)
  3. Configure SQL Express instance name as “CONFIGMGRSEC” as it is default to SCCM secondary site and there’s no reason to change it.
  4. Select “SQL_Latin1_General_CP1_CI_AS” as SQL server collation.
  5. Configure data/logs/backups directory
  6. add primary site server’s computer account (or a group containing primary site server’s computer account) as administrator during install
  7. Apply SQL Server 2008 R2 Service Pack 1 Cumulative Update 4 after SQL Express install
  8. Set a limit for amount of memory SQL express can use.
  9. Reboot secondary site server (just to be safe)
  10. give the parent primary site server’s computer account dbcreator access in SQL Express instance.
  11. Enable TCP/IP for the SQL express instance.
  12. Configure TCP/IP connection port settings.
  13. Restart SQL service.
  14. Initiate Secondary Site install from Primary site (via SCCM console). – Unlike SCCM 2007, secondary site install can no longer be performed by running SCCM setup from secondary site servers.
  15. During setup wizard, choose “Use an existing SQL Server instance”, enter secondary site server’s FQDN and SQL instance name (“CONFIGMGRSEC”). leave site database name and SQL broker port as default.
  16. monitor install status using the SCCM console:

image

image

You can also check:

  • C:\ConfigMgrSetup.log on Primary Site server (contains details for Secondary Site install’s prerequisite checks).
  • C:\ConfigMgrSetup.log on Secondary Site server (contains details for the actual setup).

Now, instead of having SQL Express installed and configured by SCCM, I have more control of it so I can align the configuration with my organisation’s standard (if it’s in a real production environment Smile).

In this case, I have my SQL data file located under F:\SQL_Data\Microsoft SQL Server\MSSQL10_50.CONFIGMGRSEC\MSSQL\DATA:

image

And log files under G:\SQL_Logs\Microsoft SQL Server\MSSQL10_50.CONFIGMGRSEC\MSSQL\Data:

image