I want to alert on DWDataRP output! While everyone’s familiar with Holman’s SCOM SQL queries blog, read below to configure a new way to maintain data warehouse integrity and retention.
Data warehouse audits are included in monitoring platform checks. For those new to monitoring, basically DWDataRP analyzes SCOM Data Warehouse issues for alert/event/performance/state retention. One administration option is to utilize the SCOM Core Monitoring Addendum pack to run DWDataRP. Another option is to run DWDataRP via Holman’s blog, or recently with Blake Drumm’s GUI tool.
Using the SCOM Core Monitoring addendum pack pre-configures a number of overrides, as well as adding DWDataRP monitor/rule options. Consequently, the SCOM action account needs to have additional permissions on SCOM SQL servers where the OperationsManagerDW
resides.
Configure SCOM management server action account to alert on DWDataRP output
Example uses lab environment SVC.SCOM.PBIreader Substitute the SCOM action account above for the SCOM data warehouse (OperationsManagerDW) databases on their respective SCOM management group(s). Give SCOM Action account necessary rights Update SVC account rights Set and verify SVC account has Server role public Click on User Mapping > select OperationsManagerDW database Verify Default Schema shows DBO Under Database Role Membership Select db_datareader AND db_owner Click OK Verification Reach out to SCOM team to verify execution From SCOM, RDP to one of the management servers Click on Start > Right click on Windows PowerShell Click on More > click on Select PowerShell Click on More > Click on 'Run as a different user'
On the Windows Security pop-up > Click on 'Use a different account' Type the action account username and password Click OK
Paste in the following commands, and verify output
cd "##YourPathtoDWDATARP.EXE##"
# cd D:\MonAdmin\TOOLS\DWDataRP"
# Check events
$Command = '.\dwdatarp.exe -s 16DB02 -d OperationsManagerDW -ds "Event Data
Set"'
$EventDataSet = Invoke-Expression $Command
$EventDataSet
$EventDataSet[2]
$LLineSplit = $EventDataSet[2].Split("(")
$EventDBPercent = $LLineSplit[1].Split("%")
$EventDBPercent[0]
Example PowerShell output when SVC Account cannot execute DWDataRP PS C:\monadmin\tools\dwdatarp> whoami testlab\svc.scom.pbireader PS C:\monadmin\tools\dwdatarp> .\dwdatarp.exe -s 16db02 -d OperationsManagerDW -ds Event Dataset name Aggregation name Max Age Current Size, Kb ----------------------------------------------------------------------------
