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
----------------------------------------------------------------------------