After a while my demo environment with OpsMgr data warehouse (and loads of other stuff) needed some more space.

I haven’t looked in to grooming of the DW before. So as usual Google is a nice friend.

Stefan Stranger has a nice post with loads of grooming information.

So what I did was to download the dwdatarp tool.

First I ran it to see the current status

dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW

Looks like most of the datasets are stored 400 or 180 days… That is somewhat to much data for a demo environment.
I took the values and divided them by 4. Then ran the following to free up 75% of the database.

dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds Alert -a Raw -m 100
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds "Client Monitoring" -a Raw -m 8
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds "Client Monitoring" -a Daily -m 100
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds Configuration -a Raw -m 100
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds Event -a Raw -m 25
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds Performance -a Raw -m 45
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds Performance -a Hourly -m 100
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds Performance -a Daily -m 100
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds State -a Raw -m 45
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds State -a Hourly -m 100
dwdatarp.exe -s SNWSQL01 -d OperationsManagerDW -ds State -a Daily -m 100

OK… when you have a database full of free space you need to truncate it, but that’s another story…

Made a small combination of a couple of queries, so this will show “Clients That Ran, Received, Rejected or Started a Specific Advertised Program”

SELECT stat.*, ins.*, att1.*, att1.AttributeTime
FROM SMS_StatusMessage AS stat
LEFT JOIN SMS_StatMsgInsStrings AS ins ON stat.RecordID = ins.RecordID
LEFT JOIN SMS_StatMsgAttributes AS att1 ON stat.RecordID = att1.RecordID
INNER JOIN SMS_StatMsgAttributes AS att2 ON stat.RecordID = att2.RecordID

WHERE
        stat.ModuleName = "SMS Client" AND
        (
                (stat.MessageID = 10005 AND att2.AttributeID = 401)  OR
                (stat.MessageID >= 10018 AND stat.MessageID <=10019 and att2.AttributeID = 401) OR
                (stat.MessageID = 10002 AND att2.AttributeID = 401) OR
                (stat.MessageID >= 10008 AND stat.MessageID <= 10009)
        )
        AND att2.AttributeValue = ##PRM:SMS_StatMsgAttributes.AttributeValue##
        AND att2.AttributeTime >= ##PRM:SMS_StatMsgAttributes.AttributeTime##
ORDER BY att1.AttributeTime DESC

Had some problems with loads of collection refresh taking all of the CPU on the SCCM-server.

So first, to get the SCCM-server to calm down I wrote a small (and somewhat ugly, since it uses SQL) hack:

UPDATE Collections
Set Flags = 17
Where CollectionName LIKE '%Something In The Collection Name%'
AND Flags=18

This script uncheck the box “Update his collection on a schedule” for the collections.

Then, when the SCCM server did go back to a normal CPU-utilization we used this script to set another refresh-rate on the collections.

Const cSccmProvider = "."
Const cWmiUsername = ""
Const cWmiPassword = ""

Const cCollectionNamePattern = "%Something In The Collection Name%"
Const cDoUpdate = True          ' Set to false to test
Const cRefreshDays = 0          ' 0 - 31
Const cRefreshHours = 12        ' 0 - 23
Const cRefreshMinutes = 0       ' 0-59

Set oLocator = CreateObject("WbemScripting.SWbemLocator")

' --- Get SCCM Site Code
WScript.Echo "Connecting to: " & cSccmProvider
Set oSccmWmi = oLocator.ConnectServer(cSccmProvider, "root\sms", cWmiUsername, cWmiPassword)
Set oWmiQuery = oSccmWmi.ExecQuery("SELECT SiteCode FROM SMS_ProviderLocation WHERE ProviderForLocalSite=true")
For each currentSite in oWmiQuery
        sSccmSiteCode = currentSite.SiteCode
        Exit For
Next

' --- Connect to site
WScript.Echo "Connecting to: " & cSccmProvider &  " - root\sms\site_" & sSccmSiteCode
Set oSccmWmi = oLocator.ConnectServer(cSccmProvider, "root\sms\site_" & sSccmSiteCode, cWmiUsername, cWmiPassword)

' --- Create interval
WScript.Echo "Creating Interval: " & cRefreshDays & " days, " & cRefreshHours & " hours, " & cRefreshMinutes & " minutes."
Set oInterval = oSccmWmi.Get("SMS_ST_RecurInterval")
oInterval.DaySpan = cRefreshDays
oInterval.HourSpan = cRefreshHours
oInterval.MinuteSpan = cRefreshMinutes
oInterval.isGmt = False
oInterval.StartTime = "20090101000000.000000+***"

' --- List all collection
set oCollections = oSccmWmi.ExecQuery("SELECT * FROM SMS_Collection WHERE Name LIKE '" & cCollectionNamePattern & "'")
For Each oCollection In oCollections
        ' --- Update interval on Collection
        If cDoUpdate Then
                WScript.Echo "Updating: " & oCollection.CollectionID & " - " & oCollection.Name
                Set oCollectionToChange = oSccmWmi.Get("SMS_Collection.CollectionID='"  & oCollection.CollectionID & "'")
                oCollectionToChange .RefreshSchedule = Array(oInterval)
                oCollectionToChange .RefreshType = 2  '1 = Manual, 2 = Periodic refresh
                oCollectionToChange .Put_
        Else
                WScript.Echo "Testing: " & oCollection.CollectionID & " - " & oCollection.Name
        End if
Next

The script is attached here: changeCollectionRefresh.vbs