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…

snowland.osdx

Since I don’t like the popup on my phone, I’m not using reminders.

So to get rid of incoming reminders I wrote a small script.

Press ALT+F11 and paste this code:

Sub snwRemoveMeetingReminder(Item As Outlook.MeetingItem)
    If TypeOf Item Is Outlook.MeetingItem Then
        Item.ReminderSet = False
        Item.Save

        Set Appt = Item.GetAssociatedAppointment(True)
        If Not Appt Is Nothing Then
            Appt.ReminderSet = False
            Appt.Save
        End If
    End If
End Sub

Then add the the script to incoming meeting requests.

Now the script will remove reminders on every incoming meeting request… It’s a client side rule, so you need to have Outlook running to get it to work.

Why I did it…
1. I wanted to test if I still know how to use a HEX-editor
2. I don’t like the original text.

Do I need to mention: This is NOT supported by anyone and if you break any deployment-solution… don’t blame me.

First, make a backup (*doh*).

The files you are looking for are located on your PXE-point, X:\RemoteInstall\SMSBoot\x86 (and \x64)

I use XVI32 v2.51 www.chmaas.handshake.de/delphi/freeware/xvi32/xvi32.htm to make the changes in the binary files.

Open up PXEBOOT.COM in XVI and search for “Press F12″ (case sensitive).
Then replace the text. If you have a shorter text, fill it out with blank spaces. If you have a longer text… tough luck. :-\

The original text:

And the changed text:

Save and test to PXE-boot a machine.
(If you can’t see the new text, you probably need to copy the changed file to both the x86 and the x64 directory)

That wasn’t to hard, was it?
If it was… don’t bother to try the WDSNBP.COM-file.

The largest textblock that a user can see is the following text, this is what we want to change / pimp.

The easy way is just to change the text to something else, but why not use some ascii-art?

If you want to use ascii-art, the maximum width is 51 chars (the width of the third line) and 3 lines high since you need to replace and not add/delete anything.
Of course the first and second line can be a bit longer.

So… start up some texteditor with monospace font (notepad will do) and create some ascii-art.

Now that we have some ascii-art with loads of blank spaces we need to replace the right number of spaces with something that we can see.

The number of # should be exactly the same as the original text.

Start up XVI32 and load WDSNBP.COM, search for “The details below”… now comes the boring/tricky part. Replace the original text with your new ascii-art.

I did a small “translation”-file where I have the orignal row and the new row next to each other, like this for the second row.

Now you can see that the text “computer.” sould be replaced to ” |\ | | “. Yes, it will be hard to read and easy to do wrong.

There is a search/replace option in XVI32, if you use it remember: replace with the exact same number of characters.

Save, PXE-boot and enjoy…

The original:

The final result:

The MD5-hash of the WDSNBP.COM and PXEBOOT.COM-files in both x86 and x64 directories is the same so you only have to make the changes in one of them.

The scenario is:
- A active/active SQL Cluster running SQL 2005 SP3 (This is where we want to host the OpsMgr-database)
- A couple of Server 2008 as RMS and MS

When you install the database on you run the setup on one of the clusternodes… works fine.

Then… when it’s time to install the RMS the problems kicks in.
A few steps in to the setup you are prompted to name the SQL-Server, Databasename and what port… this works fine.
The next steps are for the accounts you want to use…
Then when at the end of the progress bar the text says something like “Executing SQL Strings” it fails.

If you look in to the log file you can see that it cant create a connection to the SQL Server, kinda strange since it did verify that the database existed in the step where you point out server and name.

After trying everything we started to create logs… massive amount of logs, traces and dumps.
We couldn’t see that the msiexec tried to connect to the SQL… so after googling, reading and googling some more we found the problem.

IP-Sec and the broker-service.

What happens is that the msiexec tries to connect to the SQL Server, what it does in a clustered environment is that it talks to the SQL Broker who then says “Hey the instance you are looking for is located on port 123″… But, when the broker responds, it doesn’t respond from the same IP as the SQL server.

And what do a standard IP-Sec-setup do with packages from “another” IP… drop.

So, to work around this you need to install the SQL Client on the RMS/MS.
Then set up an alias (32-Bit alias if you are using a x64, if you are using a x86 you don’t have to bother) that’s named the same as the SQL-Server and with the right port.
When that’s done you can install your RMS or MS.

Get-ChildItem \\MYSERVER\SMS_C01\inboxes -Recurse | Group-Object Directory | Where { $_.Count -gt 1 }  | Sort-Object Count -Descending | Format-Table Count, Name -AutoSize

Set oLocator = CreateObject("WbemScripting.SWbemLocator")
Set oWmi = oLocator.ConnectServer(".", "root/snmp/localhost", "")
Set oStats = oWmi.ExecQuery("select * from SNMP_RFC1213_MIB_tcpConnTable where tcpConnLocalPort = 5724 AND tcpConnLocalAddress <> '0.0.0.0' AND tcpConnLocalAddress <> '127.0.0.1'")

Set oAPI = CreateObject("MOM.ScriptAPI")
Set oBag = oAPI.CreatePropertyBag()
oBag.AddValue "ConnectedConsoles", oStats.Count
oAPI.AddItem(oBag)
oAPI.ReturnItems

$UpdateAlerts = Get-Alert | where { $_.ResolutionState -eq 0 -and $_.LastModified -lt (get-date).AddHours(-24) }

foreach ($alert in $UpdateAlerts) { $alert.Update("Renotify operators of an untouched alert open for 24 hours") }

There are a few standard SUB’s (parameters) that you can use, some are listed in this post: http://www.snowland.se/2008/05/28/sccm-console-extensions/

But if you take the example of GUID 5fb29b42-5d11-4642-a6c9-24881a7d317e that you can find under Software Distribution Packages / Packages / Some package / Package Status / Site Server / Right click on a distribution point

Say that you want to pass the server-name or the path to the package…

First off, open the E:\Program Files\Microsoft Configuration Manager\AdminUI\XmlStorage\ConsoleRoot\AdminConsole.xml in some editor.

Then search for the GUID and you will find something like this.

<NodeDescriptions>
  <ResultPaneItemDescriptions>
    <ResultPaneItemDescription NamespaceGuid="5fb29b42-5d11-4642-a6c9-24881a7d317e" DesignerName="Localize:Status scoped result panel definitioN">
      <Queries>
        <QueryDescription NamespaceGuid="54d25192-0e7a-47b2-a6f2-67ff764d41c6" Type="WQL" HelpTopic="e63a41d2-a2c2-4a52-bfbb-67dc0bd7b429">
          <SupportedTypes>
            <string>SMS_PackageStatusDetailSummarizer</string>
          </SupportedTypes>
          <Query>SELECT * FROM SMS_PackageStatusDistPointsSummarizer WHERE PackageID='##SUB:PackageID##' AND SiteCode='##SUB:SiteCode##'</Query>
          <ReturnedClassType>SMS_PackageStatusDistPointsSummarizer</ReturnedClassType>
        </QueryDescription>
      </Queries>
    </ResultPaneItemDescription>
  </ResultPaneItemDescriptions>
</NodeDescriptions>

A few lines below the GUID you find SELECT * FROM SMS_PackageStatusDistPointsSummarizer WHERE Packa… Copy that line and replace/clean it up so that it is a valid WMI-query.
Will look something like:

SELECT * FROM SMS_PackageStatusDistPointsSummarizer WHERE PackageID='XYZ00123' AND SiteCode='XYZ'

Next step is to start some WMI-browser and connect to root\SMS\site_XYZ and run the query and take a look at the columns.
(I like to use WMI Explorer)

In the query above you will have columns like ServerNALPath, SourceNALPath, SourceVersion this is what you are looking for.

Use them in your extensions like this:

<Executable>
	<FilePath>myScript.vbs</FilePath>
	<Parameters>##SUB:ServerNALPath## ##SUB:SourceNALPath## ##SUB:SourceVersion##</Parameters>
</Executable>

This VBScript will read the AdminConsole.xml and look for NameSpaceGuid’s, when it find one it will create a subdirectory (from where it is started) with the GUID and after that it will create a XML-file within that directory.
The XML-file then points to an VBS-file with a couple of parameters. (Look further down for an example of a nice VBScript to use)

Tip: Backup AdminUI\XmlStorage\Extensions\Actions before you start to play around with this.

Const cVbsFile = "testExtension.vbs" ' The file to call on right-click
Const cHKEY_LOCAL_MACHINE = &H80000002
Set oReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
oReg.GetStringValue cHKEY_LOCAL_MACHINE,"SOFTWARE\Microsoft\ConfigMgr\Setup", "UI Installation Directory", sSccmPath
Set oReg = Nothing

sSourcePath = Replace(WScript.ScriptFullName, WScript.ScriptName, "")

Set oShell = CreateObject("WScript.Shell")
Set oFso = CreateObject("Scripting.FileSystemObject")
Set oFile = oFso.OpenTextFile(sSccmPath & "\XmlStorage\ConsoleRoot\AdminConsole.xml", 1)
Do While oFile.AtEndOfStream <> True
    sText = Trim(uCase(oFile.ReadLine))
    If InStr(sText, "NAMESPACEGUID=") Then

		' Read the GUID from NameSpaceGuid param
		sGuid = sText
		sGuid = Right(sGuid, Len(sGuid) - InStr(sGuid, "NAMESPACEGUID=") - 14)
		sGuid = Left(sGuid, InStr(sGuid, """")-1)

		if not oFso.FolderExists(sSourcePath & sGuid) Then
			WScript.Echo sSourcePath & sGuid

			' Create the GUID folder
			oFso.CreateFolder sSourcePath & sGuid

			' Create the XML-file with current Guid, Name & ResourceID as parameter to source-VBScript
			Set oXmlFile = oFso.CreateTextFile(oShell.ExpandEnvironmentStrings("%TEMP%\snowland-guid-locator.xml"), True)
			oXmlFile.WriteLine "<ActionDescription Class=""Executable"" DisplayName=""GUID - " & sGuid & """ MnemonicDisplayName=""GUID locator"" Description=""snowland.se GUID locator"">"
			oXmlFile.WriteLine "<Executable>"
			oXmlFile.WriteLine "<FilePath>" & sSourcePath & cVbsFile & "</FilePath>"
			oXmlFile.WriteLine "<Parameters>" & sGuid & " ##Sub:Name## ##Sub:ResourceID## ##SUB:ItemName## ##SUB:NetworkOSPath## ##SUB:value##</Parameters>"
			oXmlFile.WriteLine "</Executable>"
			oXmlFile.WriteLine "</ActionDescription>"
			oXmlFile.Close

			' Copy XML to GUID-directory with name "snowland-GUID.xml" as name
			oFso.CopyFile oShell.ExpandEnvironmentStrings("%TEMP%\snowland-guid-locator.xml"), sSourcePath & sGuid & "\snowland-" & sGuid & ".xml"
		End if
	End If
Loop
oFile.Close

So… when you restarted the console you will se GUID’s showing up. To get those GUID’s to the clipboard use a testExtension.vbs like this

Set oFso = CreateObject("Scripting.FileSystemObject")
Set oShell = CreateObject("WScript.Shell")

' Create a temporary file
Set oFile = oFso.CreateTextFile(oShell.ExpandEnvironmentStrings("%TEMP%\sccmXmlReader.tmp"), True)

' Loop thru arguments
For i = 0 to WScript.Arguments.Count-1
	sOut = sOut & Wscript.Arguments(i) & VbCrLf
	' Write to file
	oFile.WriteLine Wscript.Arguments(i)
Next
' Close the file
oFile.Close

' Type the file to the clipboard
oShell.Run oShell.ExpandEnvironmentStrings("%SystemRoot%\System32\cmd.exe /c type %TEMP%\sccmXmlReader.tmp | %SystemRoot%\System32\clip.exe"), 1, True

' Delete the file
oFso.DeleteFile oShell.ExpandEnvironmentStrings("%TEMP%\sccmXmlReader.tmp"), True

' Send a message to the user
MsgBox sOut, vbOKOnly, "Copied to clipboard"

Will try to do a post about how to find the SUB’s…