Finding a DNS Zone Creation Date

Not SCOM related, but this is pretty much the only place I dump things I need to remember, and maybe you'll find it useful.

Was in my Windows Server DNS console this morning and I noticed some odd domains listed. I don't remember seeing them before and wanted to see when they were created, to make sure folks weren't randomly adding new zones.

• Fire up adsiedit.msc on a domain controller.
• Choose Connect to
• Under Connection Point, choose Select or type a Distinguished Name or Naming Context
• Enter DC=DomainDnsZones,DC=<second level DNS>,DC=<top level DNS>
• E.G. dc=DomainDnsZones,DC=contoso,DC=corp
• Next, select CN=MicrosoftDNS
• In the right hand pane, look for the zone in question, reverse or forward
• Right-click on the folder and select Properties
• Browse to the whenCreated properties to find out when the zone was added to the system

SCOM - Check for and alert on low space on mount points - Part 1

I have a number of sites still running Exchange 2010 and because of the buggy management pack there is often not a way to alert on the drive space of an exchange server mount point because sites have removed the management pack.

In searching for a solution, I came across a nice article that helped get me started here:

http://www.powershellneedfulthings.com/?p=36

I took that script and added some additional information so that I could generate both an error condition on low drive space as well as a recovery alert so SCOM knew when to close the alert itself.

In order for this script to run cleanly, you first need to run the following powershell command on any server you want to run mout-point checks on:

new-eventlog -LogName System -Source OpsHealthScript

Next, schedule the following script to run periodically on your server via task scheduler or whatever program you might use to run scripts on a recurring basis.

$TotalGB = @{Name="Capacity(GB)";expression={[math]::round(($_.Capacity/ 1073741824),2)}}
$FreeGB = @{Name="FreeSpace(GB)";expression={[math]::round(($_.FreeSpace / 1073741824),2)}}
$FreePerc =    @{Name="Free(%)";expression={[math]::round(((($_.FreeSpace / 1073741824)/($_.Capacity / 1073741824)) * 100),0)}}
$Check = @{Name="Failed";expression={[math]::round(((($_.FreeSpace / 1073741824)/($_.Capacity / 1073741824)) * 100),0) -lt 10}}

 function get-mountpoints {
 $global:volumes = Get-WmiObject -computer $server win32_volume | Where-object {$_.DriveLetter -eq $null}
 $global:volumes | Select SystemName, Label, Capacity, FreeSpace, $Check | Format-Table -AutoSize
        }

$servers = [system.environment]::MachineName
foreach ($server in $servers){
get-mountpoints
}
$Flag = $global:volumes | Select $Check
If ($Flag -match "True") {
Write-EventLog -logname System -source OpsHealthScript -eventID 500 -Entrytype Error -message 'One or more Exchange mount points are below 10% free space.'
}
Else {
Write-EventLog -logname System -source OpsHealthScript -eventID 800 -Entrytype Information -message 'Exchange mount points are healthy.'
}

You can access the file here: mount-point-space.ps1

Once this is scheduled, you can setup alerting in a couple of ways, one is a simple alert detection within SCOM to detect Error 500 in the system event log, the other is a correlated alert detection so that each alert generated by the script does not create a new alert within SCOM. I'll go over that in additional detail.

Exchange Inventory Powershell Script

Not SCOM related, but I cobbled together a powershell script that gives useful information for your exchange inventory. In particular, I was looking to re-balance my exchange databases and wanted to get the particulars of all my user mailboxes. I find that using using the alias when running some of the move commands is easier. The script will provide you with the following information:

• Users's display name
• Total size of the user's mailbox
• The primary SMTP address for the user
• The user's alias
• The database on which the mailbox is located

By tweaking fields in the PSObject section, you can display or remove additional information, so long as that information is part of one of the other calls, like get-recipient or get-mailboxstatistics. As an example, the original script I found did not pull the database information, so I added that field in.

$(Foreach ($mailbox in Get-recipient -ResultSize Unlimited -RecipientType UserMailbox){
$Stat = $mailbox | Get-MailboxStatistics | Select TotalItemSize,ItemCount
                New-Object PSObject -Property @{
                DisplayName = $mailbox.DisplayName
                TotalItemSize = $Stat.TotalItemSize
                PrimarySmtpAddress = $mailbox.PrimarySmtpAddress
                Alias = $mailbox.Alias
                Database = $mailbox.Database}
}) | Select DisplayName,TotalItemSize,PrimarySmtpAddress,Alias,Database

System Center Custom Application Monitoring

Sometimes, a problem can seem really hard, but turn out to be rather simple if approached from a different perspective. The application team I support has a poorly written application running on a server. This should sound familiar to most system admins out there.

The application crashed the other day, but none of the windows services actually stopped, nor were there any event log errors to really go off of either. How do we monitor that service then? One option might be the TCP port but nobody seemed to know what that was. Digging into the application, it had a small scripting engine, which allowed us to run some basic scripts.

The first thought the application team had was, we'll write an event log saying everything is ok, and when that doesn't appear, we want an alert. Well, we can monitor for missing alerts in SCOM, but that seemed like it would be destined for error.

What we settled on instead was to have the program simply drop a file in the temp directory. It would put the file there every 30 minutes, with the same name. So now what? I created a small and simple batch file that would check for the file, then delete it if it was there. Otherwise, report the file missing and the service stopped.

IF EXIST C:\TEMP\running.log GOTO Good
EVENTCREATE /T ERROR /ID 333 /L application /d "Custom Application Failed"
:Good
DEL C:\TEMP\running.log /q

I then set a schedule task to run every 30 minutes to run this batch file. When the file went missing, it would write the error to the event log. From there, just setup an event monitor in System Center to catch and alert on the event.

Operations Manager Performance Trending with Excel - No SQL Required

A powerful tool for administrators is to trend data to troubleshoot performance problems and forecast future resource needs. In the past, I've run SQL queries but needed a way to instruct support staff on a basic means to accomplish the same tasks right from the console. Thankfully, Operations Manager and Excel allow just that.

Let's get started.

Fire up the Operations Manager Console to the Monitoring section, then open the Windows Computers view (or any section where you access the computer health view, such as SQL)

Find a server you're interested in or simply select one from the list.

 

After selecting the system, select the Performance View under the Navigation pane of the task panel on the right side of the management console.

 

 

Once the Performance View window comes up, in the Performance Actions pane on the right side of the console, change your time frame via Select Time Range, selecting a meaningful period, such as two weeks or longer.

 

 

 

 

Now at the bottom of the performance monitor screen, select a counter you're interested. I'll use Percent Memory Used for this example.

When selected, a graph should display such as the following:

 

  

Going back to the Performance Actions pane, select Copy Data to Clipboard

Open up notepad and past the contents, which should look similar to the following:

Save the file with an xml extension

 

 

  

Now open Excel, select the Data tab and select From Other Sources -> From XML Data Import

 

Select the XML file created earlier; accept the import defaults when prompted

 

This should populate the Excel spreadsheet with an X and Y column. The first column is the date/time stamp and the Y column is the performance data.

 

 

Select all of the Y data and with it highlighted, select the Insert tab -> Line -> 2-D Line to generate a graph.

 

This should yield a graph in Excel such as the following:

 

Right-click on the graph line and select Add Trendline

 

Generally, accepting the default will paint a trendline  that is helpful for finding issues such as a memory leak or consistent data usage on a hard drive. However, you can play around with the trend to perform longer-term forecasts. I added 50 periods to the end of my trend line to see how memory might look in the future after my data set.

 

 

Graph results with the trendline:

 

 

The line extends a bit beyond the graph data and shows an overall flat trend on memory utilization. If the server had a memory leak, as an example, the graph might trend steadily upwards like this:

 

There you have it, a simple but powerful tool for analyzing data recorded in SCOM without a lot of effort.

AD Site Availability Degraded / AD Site Performance Health Degraded

After deploying the Active Directory Management Packs, we had a domain controller start alert spewing. I had not come across anything out there that really dealt with the alert; the warning from this type of event was not in eventid.net either. But it's all figured out now and here is the solution to the perplexing problem I encountered.

You could also title this, "How to Perform an Online/Offline Defragmentation of your Health Service Store in System Center".

Problem Description:

First, the SCOM console began to fill up with AD Site Availability Health Degraded and AD Site Performance Health Degraded critical alerts from the Active Directory Management Packs.

On the offending domain controller, I observed the following Application event log spewing:

 



 

The contents of the warning were as follows:

Troubleshooting:

Initially what I suspected was that I had an application or process going bonkers on the server, taking up memory and causing the SCOM agent to malfunction or be starved of resources. I loaded the Systernals Process Monitor utility to see what was happening when these events fired off, since typically it only took a few minutes in between each event. What was captured was a significant amount of file activity from the Health Service to

C:\Program Files\Microsoft Monitoring Agent\Agent\Health Service State\Health Service Store\HealthServiceStore.edb . Essentially, there was no other process at the time of these warnings or corresponding alerts in the System Center Management Console that could account for issues on the system.

 

 

 

With the smoking gun being the Health Service Database, I performed some quick online maintenance from within the console to start.

 

In the Operations Manager Console, I started by browsing to the Operations Manager folder, then Agent Details and selecting the Agents by Version view.

 

 

Selecting the offending computer brought up the Health Service Tasks I could perform, Start Online Store Maintenance, being the one I was looking for.

 

Final Solution:

Unfortunately, the online store maintenance was not adequate enough to remediate the errors and warnings I was encountering so I opted for an offline defragmentation of the Health Service Store database. Perform the following if local warnings persist on the client system.

 

• Login to the offending client system via console or RDP
• Open an administrative command prompt
• Change directory to "C:\Program Files\Microsoft Monitoring Agent\Agent\Health Service State\Health Service Store"
• From the service console (services.msc) or from command prompt (net stop “Microsoft Monitoring Agent”), stop the Microsoft Monitoring Agent service
• Run esentutl /r edb (without this, you likely won't be able to perform a defragmentation)
• Next, run esentutl /d HealthServiceStore.edb

When this completed, my HealthServiceStore.edb file went from 174MB to 27Mb and both the warnings in the local Application event log and the critical health alerts in the System Center Operations Manager Console went away.

Problems with the 2012 R2 Web Consoles

This post is a little long, but I wanted to include as much pertinent error information as possible to help folks properly identify if they are encountering the same type of issue.

Recently upgraded our systems to SCOM 2012 R2 and encountered some issues with client connectivity to the web console. SQL is on a separate system from the management console. Web and Management Console is on the same system (for perspective on how our systems are distributed).

First, let's start with some of the errors I was seeing:

From a client, attempting to connect to the AppAdvisor console:

Error on the client:

Warning on the SCOM management server when connecting to the AppAdvisor console:

Event code: 3005 Event message: An unhandled exception has occurred. Event time: 8/5/2014 9:38:10 AM : Event time (UTC): 8/5/2014 4:38:10 PM : Event ID: 20964fc40f3c43348ccff13e467e259a : Event sequence: 7 : Event occurrence: 1 : Event detail code: 0 : : Application information: : Application domain: /LM/W3SVC/1/ROOT/AppAdvisor-1-130517302775480349 : Trust level: Full : Application Virtual Path: /AppAdvisor : Application Path: C:\Program Files\Microsoft System Center 2012 R2\Operations Manager\WebConsole\AppDiagnostics\AppAdvisor\Web\ : Machine name: SCOM-MS01 : : Process information: : Process ID: 4332 : Process name: w3wp.exe : Account name: NT AUTHORITY\NETWORK SERVICE : : Exception information: : : Exception type: WebException : Exception message: The request failed with HTTP status 401: Unauthorized.: : Request information: : Request URL: http://scom-ms01/AppAdvisor/Pages/ReportService/ReportServicePageImpl.aspx?_r=&_c=g&_pg=436ac5a4-3e70-41b9-9fe1-5a5c96724dc0&_s=2C369460 : Request path: /AppAdvisor/Pages/ReportService/ReportServicePageImpl.aspx : User host address: : User: : Is authenticated: True : Authentication Type: Forms : Thread account name: NT AUTHORITY\NETWORK SERVICE : : Thread information: : Thread ID: 17 : Thread account name: NT AUTHORITY\NETWORK SERVICE : Is impersonating: False :

Similarly, I received that error when connecting to the AppDiagnostics site as well:

Event code: 3005 Event message: An unhandled exception has occurred. Event time: 8/5/2014 9:32:02 AM Event time (UTC): 8/5/2014 4:32:02 PM Event ID: 67e2d2ba9c4842c3bc041c62bad932e3 Event sequence: 8 Event occurrence: 1 Event detail code: 0 Application information: Application domain: /LM/W3SVC/1/ROOT/AppDiagnostics-2-130517299136496487 Trust level: Full Application Virtual Path: /AppDiagnostics Application Path: C:\Program Files\Microsoft System Center 2012 R2\Operations Manager\WebConsole\AppDiagnostics\Web\ Machine name: SCOM-MS01 Process information: Process ID: 8048 Process name: w3wp.exe Account name: IIS APPPOOL\OperationsManagerAppMonitoring Exception information: Exception type: OleDbCommandException Exception message: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Command text: Select CONFIGID, CONFIGNAME, CONFIGVALUE From apm.CONFIG Connection: Provider=SQLOLEDB;Server=scom-sql;database=OperationsManager;Integrated Security=SSPI; Request information: Request URL: http://scom-ms01/AppDiagnostics/Pages/Authenticate.aspx?ReturnUrl=/appdiagnostics Request path: /AppDiagnostics/Pages/Authenticate.aspx User host address: User: Is authenticated: False Authentication Type: Thread account name: IIS APPPOOL\OperationsManagerAppMonitoring Thread information: Thread ID: 9 Thread account name: IIS APPPOOL\OperationsManagerAppMonitoring Is impersonating: False

And finally, on the primary /OperationsManager web console, I'd receive an authentication error. The client would be prompted multiple times for a username and password and eventually bomb out.

 

 

Solving the problem.

First step was a prerequisite for both the AppAdvisor and AppDiagnostic issues.

1. Open the IIS console on the web console server
2. Select "Application Pools"
3. Select "OperationsManagerAppMonitoring"
4. If you are receiving the errors and the application pool "Identity" is set to "ApplicationPoolIdentity", with the OperationsManagerAppMonitoring pool highlighted, select "Advanced Settings" option in the action pane.
5. Under "Process Model", change the Identity from ApplicationPoolIdentity to "NetworkService"
6. Run an IISReset at an administrator (elevated) command prompt

At this point, the AppDiagnostic website started working, but the AppAdvisor site did not. I had to perform additional steps for that site.

1. Open the IIS console on the web console server
2. Select and expand the site (Default Web Site on my server) where the Operations Manager web console is installed.
3. Select the virtual directory named "AppAdvisor"
4. Open the "Authentication" applet
5. If not already enabled, enable the "Anonymous" and "ASP .NET Impersonation" methods
6. Run an IISReset at an administrator (elevated) command prompt

Final piece to get into the Operations Manager web console was to adjust an IE setting, oddly enough. To fix this portion, I took the following steps:

1. Open "Internet Options" in Internet Explorer
2. Select the "Advanced" tab
3. Scroll almost all the way down and uncheck the box for "Enable Integrated Windows Authentication"

After these adjustments, all web consoles were available for remote clients.

SCOM 2012 Failed Accessing Windows Event Log with Veeam Management Pack

Noticed during a routine health check that our two Management Servers were showing a warning state. Error read as "Failed Access Windows Event Log" <management server 1> (Health Service).

Error details show the following:

The Windows Event Log Provider is still unable to open the Veeam Collector event log on computer 'management server 1'. The Provider has been unable to open the Veeam Collector event log for 720 seconds. Most recent error details: The specified channel could not be found. Check channel configuration. One or more workflows were affected by this. Workflow name: many Instance name: many Instance ID: many Management group:

We have the Veeam management pack for SCOM loaded and sure enough, this appears to be a documented issue on the Veeam knowledge base.

http://www.veeam.com/kb1496#/kb1496

Windows 2012 WMI Hotfix

Had a 2012 Server that was being monitoring by System Center lock up on us today. Suspect a WMI leak. Hotfix deployment, engage!

http://support.microsoft.com/kb/2790831/en-us

SCOM 2012 Powershell - Retrieving a List of Computers in a Group

Had to search for a batch file that is on one of the many SQL servers we have in the environment. First inclination was, let me pull the systems from SCOM since it has all our SQL servers.

Poked around the interwebs a while and noticed a lot of scripts had references to 2007 commands that hadn't been updated to 2012. Here's the basic steps taken to get my group of SQL servers. You could perform the same task on pretty much any group in the same manner.

• Open the Operations Manager Shell powershell console

• Type in : Get-SCOMGroup

• Search for the group you want to retrieve members from

• Now type in: $Group = Get-SCOMGroup |  where {$_.DisplayName -eq "SQL Computers"} (or insert the group your looking for instead of SQL Computers")

• Next, type in: $Members = $Group.GetRelatedMonitoringObjects()

 

• Now, you can simply type: $Members

 

• Or, pipe the command out to a file: $Members | Sort DisplayName | FT DisplayName | out-file C:\Scripts\Servers.txt

 

Automated Discovery and Troubleshooting of Gray State Systems in System Center 2012 (Part-1)

Recently come across a rash of clients and internal systems at the office where monitored devices, for whatever reason, have gone into a gray state. I needed a way to quickly discover these systems, and ideally, run a script that would take some basic actions to remediate or troubleshoot these agents. In this first post, I'll give the full code necessary to get the gray agent discovery running. In the second post, I'll give a powershell script that detects the grayed out agents, shuts down the HealthService, clears the agent health directory, and then turns the HealthService back on automatically.

I came across three lines of code in the following blog, which got me pointed in the right direction. However, the code did not work correctly as provided.

http://www.bictt.com/blogs/bictt.php/2011/05/27/scom-trick-14-troubleshoot-grey

$WCC = get-monitoringclass -name "Microsoft.SystemCenter.Agent"
$MO = Get-MonitoringObject -monitoringclass:$WCC | where {$_.IsAvailable -eq $false}
$MO | select DisplayName

With just that code, I would receive the following error screen:

If you update the code to include the following path and connection to your system center server, the code will function properly. Running this should spit out a list of computers with a gray state in the agent status. This code should all be included in your powershell script:


$RMSFQDN = "<your SCOM managment server FQDN>"
$Name = "Microsoft.EnterpriseManagement.OperationsManager.Client"
$ModuleLoaded = Get-Pssnapin $Name -ErrorAction SilentlyContinue
If (-not $ModuleLoaded)
{
add-pssnapin "Microsoft.EnterpriseManagement.OperationsManager.Client";
}
New-ManagementGroupConnection -ConnectionString $RMSFQDN
Set-Location "OperationsManagerMonitoring::";

$AgentClass = get-monitoringclass -name:Microsoft.SystemCenter.Agent
$MO = Get-MonitoringObject -monitoringclass:$AgentClass | where {$_.IsAvailable -eq $false}

$MO | select DisplayName

Also, review this link for a comprehensive list of WMI hotfixes for various platforms:

http://support.microsoft.com/kb/2591403

Updated 11-15-2013: Review this link for agent based system hotfixes: http://support.microsoft.com/kb/2843219

Scripting the Deployment of the Action Account to servers

If you have a large server list and you quickly want to add rights for your action account, check out this helpful method. Just go to active directory and create a query for your servers, then remove the columns (outside of the server names). I've enclosed the query definition. Drop this into an xml file and import into Active Directory, under "Saved Queries".

<QUERY><NAME>Servers</NAME><DESCRIPTION></DESCRIPTION><DN></DN><FILTERLASTLOGON>-1</FILTERLASTLOGON><LDAPQUERY>(&amp;(&amp;(sAMAccountType=805306369)(objectCategory=computer)(objectClass=computer)(operatingSystem=Windows\20Server*)))</LDAPQUERY><ONELEVEL>FALSE</ONELEVEL><COLUMNID>{5AAC0BFD-BFA4-44BB-95A9-EF6CCC1F64EF}</COLUMNID></QUERY>
Here is the link to the site:

http://www.bluemoonpcrepair.com/wp/?p=145

Where Did My LINUX RPMs Go?! @##%&&%

Some time ago, I created a video and wrote a blog on a quick and painless way to install the LINUX agents for SCOM. Well, came time to do that again and I noticed, even after downloading the management packs from the catalog, I could not find the rpm files!

After some exasperating searching and much gnashing of the teeth, I finally found the download for the updated management packs. Installing the ones from this pack will give you the rpms. In the past, the rpms could be found on the CD/DVD.

System Center 2012 Monitoring Pack for UNIX and Linux Operating Systems :
http://www.microsoft.com/en-us/download/details.aspx?id=29696

Upcoming topics on SCOM

Haven't had a chance to post anything recently, but it's not for a lack of trying. I have 41 pages of screen shots and instructions that walk folks through the SNMP troubleshooting, from creating the LINUX test system through to getting the alerts in SCOM, trying to walk through where your SNMP setup could be failing. Also going to post pulling raw metrics for analysis in Excel. SQL Reporting is nice, but it can be overly complicated when sometimes, you just want some quick data and some graphs. Finally, will be posting another method for organizing data outside of the datawarehouse. This will provide a safer method of writing your own reports and views without disturbing the installation.

System Center Installer - OMServer.msi returned error 1603

Having problems installing your secondary management server, maybe even your primary? Great post about pre-requisites. Setting up our secondary server, the management server installer was failing. I had made sure to install the report viewer modules, .NET 4.0 but forgot .NET 3.51. The installer won't tell you directly that the prerequisite was missing and will simply fail, leaving you to pour through a large install log to find the problem.

If you're seeing this in your install log, it may be the same case:

Always: :LaunchMSI: Setting rollback to true
[16:58:55]: Error: :LaunchMSI: MSI C:\SCOMSP1\Setup\AMD64\Server\OMServer.msi returned error 1603

Check Christopher Keyaert's article for additional information and other troubleshooting steps.

http://www.vnext.be/2013/01/24/scom-2012-sp1-omserver-msi-returned-error-1603/

Way to Mass Uninstall System Center Agents on Remote Computers

I needed to remove the agents on a number of computers and naturally, I wanted a quick way of doing so. I had a csv output from an active directory query but needed a way to use that to uninstall the agent. The following powershell script will allow you to do just so. It may or may not work on certain 2000 and 2003 installations. I am working on a script that works on all systems. In the mean time, here you go.

$Servers="computer1","computer2", "computer3"
FOREACH ($TargetServer in $servers){(Get-WmiObject -Class Win32_Product -Filter "Name='System Center Operations Manager 2012 Agent'" -ComputerName $TargetServer ).Uninstall()}

Errors with SCOM 2012 SP1 Upgrade or Installation

Upgraded to SCOM 2012 SP1 last week and exposed an few errors for those who may have gone a little to fast in the installation. Chances are, if you are seeing the following errors, you may have forgotten to change the "Data Access Service" option during the upgrade to a domain account. If you leave it a service account, you will get the following errors post upgrade. Now, the good thing is, if you've done this and can no longer log into the management console, go back to your server, hunt down the System Center Data Access Service and change the login from "Local Service" to your data access account that was created during the original installation of System Center.

Also, if you are starting from scratch with an SP1 installation, you will need to add trailing "\" back slashes on the SQL directories. If you do not, the Datawarehouse installation will likely fail and roll the entire installation back. So, when you perform the SQL server discovery during installation, the installer will find your data and log directories and populate them automatically as such:

"D:\SQL Data"
"L:\SQL Logs"

You need to change these to the following format (obviously your drives and directories may differ)

"D:\SQL Data\"
"L:\SQL Logs\"

And then your System Center Operations Manager 2012 SP1 installation should proceed.


Errors you may encounter if you do not have a domain login for the Data Access account:

Inner Exception.Type: Microsoft.EnterpriseManagement.Common.ServiceNotRunningException, Exception Error Code: 0x80131604, Exception.Message: The Data Access service is either not running or not yet initialized. Check the event log for more information.
Event 29120
OpsMgr Management Configuration Service failed to process configuration request (Xml configuration file or management pack request) due to the following exception
System.Runtime.Remoting.RemotingException: Unable to get ISdkService interface. Please make sure local Sdk Service is running.
   at Microsoft.EnterpriseManagement.ManagementConfiguration.Communication.CredentialDataProvider.CreateSdkConnector()

Event 29195
OpsMgr Management Configuration Service failed to communicate with System Center Data Access Service due to the following exception
System.Runtime.Remoting.RemotingException: Unable to get ISdkService interface. Please make sure local Sdk Service is running.

Event 26380
The System Center Data Access service failed due to an unhandled exception. 
The service will attempt to restart.
Exception:
Microsoft.EnterpriseManagement.Common.SdkServiceNotInitializedException: The Data Access service has not yet initialized. Please try again.

Event 26340
System Center Data Access Service and/or System Center Management is unresponsive because Authorization Manager is unable to recover from database errors. Please restart services System Center Data Access Service and System Center Management.

Setup a Disk Report in SCOM 2012 (Part-3)

Finally got around to getting all the screen shots for the updated post for creating a more visually useful report. Now I don't have to make a new years resolution to get it done.

Recall in Part-2 of Setting up a Logical Disk Report for SCOM 2012 that a new view was created to make life a little easier. It was named “vCustomHourlyLogicalDiskPerf”. We will continue to use this view in the design of our updated, color-coded report. I was going to switch over to Visual Studio for this update, but decided to stick with Report Builder 3.0, which is a tool that comes with SQL or can be downloaded for free.

I will be more to the point with this post since I have covered other topics in parts one and two. Please refer back to those if you get stuck here. I presume you have already created the SQL view, which this post will utilize and was created earlier in Part-2.

Open Microsoft Report Builder 3.0 and connect to your SQL server housing the Operations Manager data warehouse. Start with a blank report project.

In the blank report, go ahead and resize out to about 8 inches for width.

 

 

Let’s add a quick title. For this, we’ll call it “Less than 10% Free Logical Disk Report”

Now let’s add the datasource:

 
Select the “Use a connection embedded in my report” and enter the connection string to DataWarehouseMain

data source=<SQL DW Server>;initial catalog=OperationsManagerDW;Integrated Security=SSPI

For the time being, change the credentials to “Use Current Windows User”. This will be changed after the report is complete, but use this for now in order to connect to the database and finish the report.

Now let us add a dataset:

We are going to do a slightly different dataset here than the one used in Part-2 of the series. For the purposes of this report, I want it to give me the status of disk space as of the last day. Since we’re trying to keep this simple and hack the code, we can run into accuracy issues with the construction of this report if more than one day of data is used. Additionally, I use this report each Friday so I know the disks that are currently having space issues so I can remediate the problems prior to the weekend and hopefully, avoid an outtage or after-hours call.

Here is the select statement for this dataset. Notice, I am hard coding the dates here. No variables will have to be used when this report runs. This makes it fast to use and easy to schedule. Notice some of the exclusions in the last section of the query. I am specifically removing the “total” metric, which can add redundant data. I also remove volume links that may be reported by services such as clustering or exchange, but effectively have the same data as the logical disk link. I could also exclude servers here that I know I don’t care about but that might have monitoring data, such as a backup server, video server, etc.
DECLARE @Start_Date DATETIME
DECLARE @End_Date DATETIME
SET @Start_Date = DateAdd(d,-1,GETDATE())
SET @End_Date = GETDATE()

select
[Total Disk Space],
[Free Megabytes],
[% Free Space],
InstanceName,
Path,
DateTime

from
(select CounterName, AverageValue,InstanceName, Path,DateTime
 FROM vCustomHourlyLogicalDiskPerf) AS SourceTable
PIVOT
(
AVG (AverageValue) FOR CounterName IN ([Total Disk Space],[Free Megabytes],[% Free Space])
) AS PivotTable

WHERE
DateTime >= @Start_Date AND
DateTime <= @End_Date AND
NOT InstanceName = '_Total' AND
NOT InstanceName Like '\\?\Volume%%' AND
[% Free Space] <= 10.00

 

 

With the query complete, let’s get the report up and running. This report will include a status bar to visually show the disk space left as well as color coding to show really critical space issues in red and warnings in orange.

Start by inserting a matrix into the report fields. Just drag and drop the matrix onto the report itself and then reposition to the upper-left, just under the title

Grab the “Path” variable from the dataset and drag to the lower left corner of the matrix:

Next, add a child group to the “Row Groups”:

Add the “Instance Name to the row child group”

On the third, remaining column, select the top of the column and right-click to add another column within the group. This needs to be repeated four times.

 Should now have something that looks like this:

We’ll need to split the top header. Right click on the top header to bring up the context menu and select “Split Cells”

Now, in this order, drag and drop the total disk space, free disk space and % free metrics into the bottom of each column. Leave a space between “free disk space” Should look something like the following:

This isn’t very handy, since we don’t want the sum of these metrics. So we’ll want to adjust each field, starting with “Total Disk”, right click on the field and select the “expression” option.

Let’s change this to the actual value along with converting it into Gigabytes, from the default Megabytes.

Now do the same with free space.

For the next field, which is blank, we’ll call it used disk space Used (GB). Here, we’ll use a calculated field. We could have done this in the SQL query itself, but I chose to do this differently. The expression here should include the total disk space minus the free disk space.

=(Fields!Total_Disk_Space.Value-Fields!ID__Free_Space.Value)/1000

Then finally on to percent free space.

For this field, we are going to add a color coding mechanism.  Right click on the ID_Free_space and select the “Text Box Properties”

Navigate to Font, and select the expression applet next to color.

Add the following expression in the "Color" field

=IIF(Fields!ID__Free_Space.Value >= 10, "Green", IIF(Fields!ID__Free_Space.Value >= 5, "Orange", "Red"))

What this is saying is, any field greater than 10% free space will show up as green, otherwise, anything between 10 and 5 will turn orange and anything under 5 will turn red. If this report had not been limited already to items under 10%, it would have made the corresponding report much easier to figure out which items were critical.

 
Under the “Insert” menu item in the report builder, select “Gauge” and then select the lower-right, empty column, to insert the visual gauge for free space.

We’ll choose the “Bullet Graph” under linear gauges.

Double-click on the gauge and for the “LinearPointer1” values, select the “ID_Free_Space” field and then delete “LinearPointer2”. The gauge will also want to sum up the values. Go back to the expression and remove the SUM option.

For the gauge, we’ll want similar fill options to our numerical values. Select the small line in the gauge, then right-click and select the “Pointer Properties”.

Select the “Pointer Fill” option and adjust the expression for the “Color” and “Secondary Color” with the following expression.

=IIF(Fields!ID__Free_Space.Value >= 10, "Green", IIF(Fields!ID__Free_Space.Value >= 5, "Orange", "Red"))

Adjust the column widths and get things all squared up. Now your report should look something like this:

Now go back to the properties of DataWarehouseMain and change the credentials back to “Do not use Credentials”

Now save the report to the SCOM reporting server.

 
The report is almost ready to be completed and viewed. Login to the web interface of the report server.

Usually with the syntax ofhttp://<report server>/Reports.

Select the "Details View" and look for the DataWarehouseMain object and select it. Go into the object properties and copy the selection string. Now find the report you just created, and select the manage option in the drop down menu.

Go to the “Data Sources” tab for the report and delete the existing connection string and replace with the one copied from DataWarehouseMain. Also ensure that “Credentials are not required” is selected. Apply the changes.

You should now be able to run the report. Depending on the cleanup you did in the report format, you may need to go back and make some changes. In my example, I did not adjust the numerical formatting for each text field and ended up with too many decimals in the fields. So long as you are only changing text field properties, you should be able to save the report without altering any connection string settings.

To correct this, open a text box property, select the “Number” tab and change the format to “Number” with 2 decimal places.

Now the output looks like this, which is much better:

Good luck and happy report writing. There is a little bit of everything in this post and hopefully this helps get you started with additional techniques.