SCOM 2012 database grooming

Approximately three months back we migrated to SCOM 2012 and have been slowly rebuilding our configuration. In defining the configuration we forgot one key part, database grooming customization. By default some data is kept for a couple of days but a lot of day is kept for either 180 days or 400 days. While in a lab environment this may be ok since you are only monitoring a few systems in production this will cause some unexpected database growth issues. Below you can see the defaults configured.

 
Dataset name                   Aggregation name     Max Age     Current Size, Kb
—————————— ——————– ——- ——————–
Alert data set                 Raw data                 400         8,440 (  0%)
Client Monitoring data set     Raw data                  30             0 (  0%)
Client Monitoring data set     Daily aggregations       400            16 (  0%)
Configuration dataset          Raw data                 400       133,616 (  0%)
DPM event dataset              Raw data                 400             0 (  0%)
Event data set                 Raw data                 100       594,592 (  2%)
Microsoft.Exchange.2010.Dataset.AlertImpact Raw data                   7             0 (  0%)
Microsoft.Exchange.2010.Dataset.AlertImpact Hourly aggregations        3             0 (  0%)
Microsoft.Exchange.2010.Dataset.AlertImpact Daily aggregations       182             0 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.Availability Raw data                 400            16 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.Availability Daily aggregations       400             0 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.TenantMapping Raw data                   7             0 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.TenantMapping Daily aggregations       400             0 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ActiveUserMailflowStatistics.Data Raw data                   3        17,424 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ActiveUserMailflowStatistics.Data Hourly aggregations        7       225,104 (  1%)
Microsoft.Exchange.2010.Reports.Transport.ActiveUserMailflowStatistics.Data Daily aggregations       182       104,592 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ServerMailflowStatistics.Data Raw data                   7         1,616 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ServerMailflowStatistics.Data Hourly aggregations       31         6,480 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ServerMailflowStatistics.Data Daily aggregations       182           688 (  0%)
Performance data set           Raw data                  10     4,984,944 ( 13%)
Performance data set           Hourly aggregations      400    26,558,360 ( 69%)
Performance data set           Daily aggregations       400     3,047,320 (  8%)
State data set                 Raw data                 180        37,280 (  0%)
State data set                 Hourly aggregations      400     2,481,936 (  6%)
State data set                 Daily aggregations       400       117,280 (  0%)
 

To prevent the database from growing to hundreds of GB we need to adjust the retention policies. In order to accomplish this we need to download the dwdataarp.exe utility from Microsoft at: http://blogs.technet.com/b/momteam/archive/2008/05/14/data-warehouse-data-retention-policy-dwdatarp-exe.aspx

With this installed open up an administrative command line on the SCOM server we can begin.

First run: dwdatarp.exe -s localhost -d “OperationsManagerDW”
This will show you your current configuration, now we need to tweak some of the retentions. The below example is a mix of retention periods in an environment with Exchange 2010 and DPM 2012 installed.

dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Alert data set" -a "Raw data" -m "30"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Event data set" -a "Raw data" -m "30"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Client Monitoring data set" -a "Daily aggregations" -m "60"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Configuration dataset" -a "Raw data" -m "30"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "DPM event dataset" -a "Raw data" -m "30"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Microsoft.Exchange.2010.Reports.Dataset.Availability" -a "Raw data" -m "30"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Microsoft.Exchange.2010.Reports.Dataset.Availability" -a "Daily aggregations" -m "90"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Microsoft.Exchange.2010.Reports.Dataset.TenantMapping" -a "Daily aggregations" -m "90"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Microsoft.Exchange.2010.Reports.Transport.ActiveUserMailflowStatistics.Data" -a "Daily aggregations" -m "90"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Microsoft.Exchange.2010.Reports.Transport.ServerMailflowStatistics.Data" -a "Daily aggregations" -m "90"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Performancedata set" -a "Raw data" -m "7"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Performance data set" -a "Hourly aggregations" -m "14"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "Performance data set" -a "Daily aggregations" -m "90"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "State data set" -a "Raw data" -m "7"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "State data set" -a "Hourly aggregations" -m "14"
dwdatarp.exe -s localhost -d "OperationsManagerDW" -ds "State data set" -a "Daily aggregations" -m "90"

 

 

After making the change and waiting for the automated grooming to complete I ended up dropping the database size from 42GB and growing to 21GB.

 

Dataset name                   Aggregation name     Max Age     Current Size, Kb
—————————— ——————– ——- ——————–
Alert data set                 Raw data                  30         4,656 (  0%)
Client Monitoring data set     Raw data                  30             0 (  0%)
Client Monitoring data set     Daily aggregations        60            16 (  0%)
Configuration dataset          Raw data                  30       133,552 (  1%)
DPM event dataset              Raw data                  30             0 (  0%)
Event data set                 Raw data                  30       352,040 (  2%)
Microsoft.Exchange.2010.Dataset.AlertImpact Raw data                   7             0 (  0%)
Microsoft.Exchange.2010.Dataset.AlertImpact Hourly aggregations        3             0 (  0%)
Microsoft.Exchange.2010.Dataset.AlertImpact Daily aggregations       182             0 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.Availability Raw data                  30            16 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.Availability Daily aggregations        90             0 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.TenantMapping Raw data                   7             0 (  0%)
Microsoft.Exchange.2010.Reports.Dataset.TenantMapping Daily aggregations        90             0 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ActiveUserMailflowStatistics.Data Raw data                   3        17,680 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ActiveUserMailflowStatistics.Data Hourly aggregations        7       226,384 (  1%)
Microsoft.Exchange.2010.Reports.Transport.ActiveUserMailflowStatistics.Data Daily aggregations        90       104,144 (  1%)
Microsoft.Exchange.2010.Reports.Transport.ServerMailflowStatistics.Data Raw data                   7         1,616 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ServerMailflowStatistics.Data Hourly aggregations       31         6,416 (  0%)
Microsoft.Exchange.2010.Reports.Transport.ServerMailflowStatistics.Data Daily aggregations        90           688 (  0%)
Performance data set           Raw data                  10     5,047,512 ( 30%)
Performance data set           Hourly aggregations       14     6,600,016 ( 39%)
Performance data set           Daily aggregations        90     3,047,104 ( 18%)
State data set                 Raw data                   7        23,840 (  0%)
State data set                 Hourly aggregations       14     1,064,864 (  6%)
State data set                 Daily aggregations        90       117,088 (  1%)

 

Also, if you want to speedup the time it takes for the cleanup to occur from within SQL you can run the following command to reduce the time period between cleanup.

update StandardDatasetAggregation set GroomingIntervalMinutes = '11' where GroomingIntervalMinutes = '240'

After cleanup has finished, run the below to change the configuration back to what it was

update StandardDatasetAggregation set GroomingIntervalMinutes = '240' where GroomingIntervalMinutes = '11'

Counter Intelligence via Palo Alto Networks

As an aside from most posts found here, I found a fair amount of unusual traffic recently.  Last week I ended up looking up some models of Palo Alto firewalls and even downloaded a smartphone application of theirs. Nothing really out of the ordinary until  this past Wednesday when I see a large influx of traffic to the site.

Site Hits

In the above graphic you can large influx of probing hits coming from Palo Alto Networks. I can only assuming per other farming tactics I’ve seen in the past the data flows as follows.

  1. You visit Vendor XYZ’s site
  2. Vendor XYZ parses their website logs looking for those users who are making multiple queries against the products portion of the company site
  3. Vendor XYZ performs a bot query hitting up the site client’s IP to see if there is a website and if contact information can be captured
  4. A followup script takes the results and if enough information is capture then is forwarded to the pre-sales department
  5. My phone rings

Really? Do you think I’ll buy a PA-5050 or PA-5060 because you cold-call me mysteriously after I visit your website?

 

NAS4Free under ESXi

 

One surprising thing I noticed when testing out NAS4Free is the lack of documentation with regards to installation on VMware. I can understand a viewpoint that a NAS is a NAS and not to be anything else, but what about working loads where that kind of raw performance is not required (granted the virtualization overhead these days should be within 5-10% of physical). In any case the below instructions are written with running ESXi 5.1 with NAS4Free 9.1.0.1, for ease of reading the directions are broken down into three sections.

 

 

Initial download and VM configuration

 

1)      Download the latest x64 release at http://www.nas4free.org/downloads.html

2)      Create a new custom Virtual Machine (Assume defaults unless otherwise specified)

a)      Guest Operating System – Other – FreeBSD 64-Bit

b)      Virtual sockets – 3  (you can use less but I was seeing a significant performance hit with less than 3)

c)      Memory: 4GB (not a hard requirement but in general the more the better)

d)     Network

i)        Number of NICs: 2

ii)      NIC 1 Adapter: e1000
(The e1000 will be used for management only as the default NAS4Free install does not correctly load the VMXNet3 driver)

iii)    NIC 2 Adapter: VMXNet3
(The VMXNet3 adapter will be used for Samba/NFS/iSCSI traffic)

e)      SCSI Controller: LSI Logic Parallel

f)       Disk: 4GB, can be thin provisioned

3)      Finish creating the VM

4)      Edit the VM

5)      Add  your additional hard disks and assign them starting at 1:0, 1:1, 1:2 (virtual RDM’s are an option as well). One VMDK per disk unless your really just feature evaluating the setup.

a)      Optionally if supported you could use Direct-Path to pass through your favorite SCSI controller

6)      Change your newly created SCSI Controller to: LSI Logic SAS
(Paravirtual does not function with the version of Vmware tools pre-bundled with the NAS distro)

7)      Select “OK” to complete the modifications

 

 

NAS4Free base install

 

1)      Boot the VM and start it off of the recently downloaded ISO

2)      Walk-through the normal installer screens selecting” Install ‘embedded’ OS on HDD/FLASH/USB”
(Full is extremely buggy at this point and only really used for NAS4Free developers)

3)  Install onto the 4GB volume

4)  After the install completes, reboot and disconnect the ISO volume

5)  Configure your mangement IP

 

 

Configuring your new VM

 

1)      Login to the web administration to the new VM

2)      Select System –> Advanced

3)      Select rc.conf

4)      We need to add some custom tuning for the VM

a)      Add – Name: vmguestd_enable – Value: Yes

b)      Add – Name: vmsetup_enable – Value: Yes

c)      Optionally (useful for debugging sometimes)

i)        Add – Name: dmesg_enable – Value: Yes

5)      Apply changes

6)      System –> Reboot

 

At this point the VM should be fully useable. If running into performance problems TOP within the VM and the vSphere performance graphs should be where to start looking. VM CPU usage and disk latency are generally the first points of issue.

Enjoy.

 

Manual SCOM client migration

Today I had the task of migrating a number of systems over to the SCOM 2012 server with the existing server being offline. Running the normal discovery fails to redirect the nodes over to the system because of a pair of registry entries still pointing to the old system. The below batch script can be run one at a time or via some automation method (Config Manager in my case) to mass-migrate nodes over to the new system.

@REG ADD "HKLM\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Agent Management Groups\LiquidObject\Parent Health Services\0" /v "AuthenticationName" /d "SCOM-1.liquidobject.com" /f
@REG ADD "HKLM\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Agent Management Groups\LiquidObject\Parent Health Services\0" /v "NetworkName" /d "SCOM-1.liquidobject.com" /f

@NET STOP HEALTHSERVICE
@NET START HEALTHSERVICE

Exchange 2010 Holiday Calendar Loading

Within an organization I had need to bulk load behind the scenes a number of calendar items on all employee’s accounts. This script had multiple sources with the original idea coming from http://www.mikepfeiffer.net/2011/01/creating-calendar-items-with-powershell-and-the-ews-managed-api/

To get this working requires a few things.
1) Exchange 2010 with SP1 (currently running under SP2)
2) The Exchange EMS shell installed
3) The Exchange Web Services API to be installed (http://www.microsoft.com/en-us/download/details.aspx?id=28952)
4) Elevated permissions within the Exchange environment.

Beyond your normal administration rights is the requirement for the ability to Impersonate all users within the organization. Because of the level of access required to make these changes please verify with the organization that there are no legal issues with attempting this.

#Path to your holiday file
$inputfile = "D:\Data\HolidayCalendar2012-2013.csv"
#Path to the EWS DLL file
$Webservices = "C:\Program Files\Microsoft\Exchange\Web Services\1.2\Microsoft.Exchange.WebServices.dll"
#Where are the users
$baseOU = "OU=My Employees,DC=liquidobject,DC=com"

#Need to update each year
$myyears = "2012-2013"
$myuniquebody = "My Unique Calendar Automation Message" + $myyears
#The above body entry was the easiest way for users to see that the items were loaded by the IT department and also provides the one place where we can go back and modify/delete calendar entries later.

#If we need to run the once per year everyone run, set this to true
$YearlyRun = $false

#Testing mode options
$testmode = $false
$testuser = "Testuser99"

####################### End Options ########################

if(!(Get-Module | where {$_.Name -eq "ActiveDirectory"})){Import-Module ActiveDirectory}
if(!(Get-PSSnapin -Name "Microsoft.Exchange.Management.PowerShell.E2010" -ErrorAction SilentlyContinue)){Add-pssnapin Microsoft.Exchange.Management.PowerShell.E2010}
if(!(Get-PSSnapin -Name "Microsoft.Exchange.Management.PowerShell.support" -ErrorAction SilentlyContinue)){Add-pssnapin Microsoft.Exchange.Management.PowerShell.support}

if(!(Test-Path $Webservices))
{
    Write-Host "`n`nExchange Web Services API is required and is not installed`nhttp://www.microsoft.com/en-us/download/details.aspx?id=28952`n"
    Exit
}
if(!(Test-Path $inputfile))
{
    Write-Host "`n`nInput file of: $inputfile is missing, cannot proceed without this file.`n`n"
    Exit
}
Write-Host "`nEWS Web Services - Loading"
Add-Type -Path $Webservices
Write-Host "EWS Web Services API - Loaded`n"

function New-CalendarItem {
    [CmdletBinding()]
    param(
        [Parameter(Position=1, Mandatory=$true)]$CalendarUser,
        [Parameter(Position=2, Mandatory=$true)]$Subject,
        [Parameter(Position=3, Mandatory=$true)]$Date
        )
    
    $sid = (Get-ADUser -Identity $CalendarUser).SID
    $user = [ADSI]"LDAP://<SID=$sid>"
    $service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService -ArgumentList ([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010_SP1)
    $service.AutodiscoverUrl($user.Properties.mail)
    
    $Impersonate = (Get-Mailbox -Identity $calendaruser).PrimarySMTPAddress
    $ImpersonatedUserId = New-Object Microsoft.Exchange.WebServices.Data.ImpersonatedUserId -ArgumentList ([Microsoft.Exchange.WebServices.Data.ConnectingIdType]::SmtpAddress),$Impersonate
    $service.ImpersonatedUserId = $ImpersonatedUserId
    
    $appointment = New-Object Microsoft.Exchange.WebServices.Data.Appointment -ArgumentList $service
    $appointment.Subject = $Subject
    $startdate = Get-Date($Date)
    $appointment.Start = $startdate
    $enddate = (Get-Date($Date)).adddays(1)
    $appointment.End = $enddate
    $appointment.body = $myuniquebody 
    $appointment.LegacyFreeBusyStatus = "Free"
    $appointment.IsAllDayEvent = $true
    $appointment.IsReminderSet = $False
    $appointment.Save([Microsoft.Exchange.WebServices.Data.SendInvitationsMode]::SendToNone)
#    Write-Host "New Entry: " $CalendarUser " - " $Subject " - " $Date
    $appointment = $null
    sleep 0.3
}

$mydata = Import-Csv $inputfile
Write-Host "Preparing to process" $mydata.count "calendar entries`n"

#Everyone, we need this once per year
if($YearlyRun)
{
    $myusers = Get-ADuser -Filter {(mail -like "*") -and (ObjectClass -eq "user") -and (Enabled -eq $true)} -SearchBase $baseOU -Properties created | Select-Object samaccountname, created
}
else
{
    #Normally grab just the last 7 days of users for a weekly run.
    $mydays = (Get-Date).Adddays(-7)
    $myusers = Get-ADuser -Filter {(mail -like "*") -and (ObjectClass -eq "user") -and (Created -gt $mydays) -and (Enabled -eq $true)} -SearchBase $baseOU -Properties created | Select-Object samaccountname, created
}

#sorting for easy troubleshooting
$myusers = $myusers | Sort-Object samaccountname
#For testing lock to a single user
if($testmode)
{
    $myusers2 = @{"samAccountName"=$testuser}
}

$today = Get-Date
$mycount = $myusers.count
$currentpos = 1
foreach($i in $myusers)
{
    Write-Host "Loading $currentpos of $mycount for:" $i.samaccountname
    foreach($entry in $mydata)
    {
        if($today -lt $entry.date)
        {
            #Write-Host $entry.date " - " $entry.Subject
            New-CalendarItem -CalendarUser $i.samaccountname -Subject $entry.Subject -date $entry.date
            sleep 0.2
        }
        else
        {
            if($YearlyRun) #only load past dates on the yearly run
            {
                New-CalendarItem -CalendarUser $i.samaccountname -Subject $entry.Subject -date $entry.date
                sleep 0.2
            }
        }
        
        sleep 0.1
    }
    $currentpos++
    sleep 1.5
}

All items created are all-day events with availability as as free with no reminders to annoy the staff. This is designed for an annual run and then adjustment of the “$YearlyRun” variable for weekly loads on all new employees.

The CSV file is in the following form:
Subject,Date
New Years Day, 2013/1/1
April Fools, 2013/4/1

Be careful on the characters used in the Subject entries as extra ” or ‘ characters can cause lots of headaches.

Current Exchange CAS user counts

Below you’ll find a slight modification to a post from Mike Pfeiffer on querying Exchange 2010 CAS servers to obtain the current count of OWA and RPC clients.

function Get-CASActiveUsers {
  [CmdletBinding()]
  param(
      [Parameter(Position=0, ValueFromPipelineByPropertyName=$true, Mandatory=$true)]
      [String[]]$Name
      )

  process {
    $Name | %{
      $RPC = Get-Counter "\MSExchange RpcClientAccess\User Count" -ComputerName $_
      $OWA = Get-Counter "\MSExchange OWA\Current Unique Users" -ComputerName $_
      New-Object PSObject -Property @{
        Server = $_
        "RPC Client Access" = $RPC.CounterSamples[0].CookedValue
        "Outlook Web App" = $OWA.CounterSamples[0].CookedValue
      }
    }
  }
}

Get-CASActiveUsers CAS-1,CAS-2,CAS-3