Outlook Save-Attachments macro

With all props and credit to ArcaneCode, I post here a fork of his save-attachments macro.  My needs were a bit different from ArcaneCode’s so I made some substantial modifications.

Specifically,

  • I want there to be an attachment in my email or calendar item so I can find it using “hasattachments:yes” in my search terms.
  • I want to have a link from the email to the attachment (as well as a link to the containing folder)
  • I don’t want to specify the folder each time I save attachments, so I have a variable that puts them under a root folder specified in the script in a sub-folder named sender/subject/date
  • I had to address long subject lines and long attachment names by truncating carefully, and removing characters that don’t work well in the on-disk folders.
  • It removes *all* attachments, even signature line icons and the like.  Usually small files are not things I’d care to click on, so I included a file-size indicator in the link to help me figure out which were window dressing and which were probably screenshots or other large pastings from the sender.

Make sure to add the “Microsoft Scripting Runtime” in your Tools-References in the macro editor.  Also be sure to set the root folder and micro-file attachment path.  I just used a completely empty notepad.exe file.

I left a lot of the original stuff in there, so it’s not exactly tidy, but it works reliably.

Public Sub SaveAttachments()
   ‘Note, this assumes you are in the a folder with e-mail messages when you run it.
   ‘It does not have to be the inbox, simply any folder with e-mail messages
  
  Dim App As New Outlook.Application
   Dim Exp As Outlook.Explorer
   Dim Sel As Outlook.Selection
  
  Dim AttachmentCnt As Integer
   Dim AttTotal As Integer
   Dim MsgTotal As Integer
      
    Dim EmptyAttachment As String
    Dim RootDirectory As String
   
  Dim outputDir As String
   Dim outputFile As String
   Dim fileExists As Boolean
   Dim cnt As Integer
  
  ‘Requires reference to Microsoft Scripting Runtime (SCRRUN.DLL)
   Dim fso As FileSystemObject
‘**************************************************************************************************
‘********   DEFAULTS  *****************************************************************************
‘**************************************************************************************************

RootDirectory = “C:\Users\<<USERNAME>>\Documents\Mail Attachments”

‘Note that the EmptyAttachment file needs to exist.  Just create a new text file and save it empty.

EmptyAttachment = “C:\Temp\Empty Attachment.txt”

‘**************************************************************************************************
‘**************************************************************************************************
    
  Set Exp = App.ActiveExplorer
   Set Sel = Exp.Selection
   Set fso = New FileSystemObject
  
    
  ‘Loop thru each selected item in the inbox
   For cnt = 1 To Sel.Count
    
     ‘If the e-mail has attachments…
     If Sel.Item(cnt).Attachments.Count > 0 Then

        MsgTotal = MsgTotal + 1
       
        outputDir = GetOutputDirectory(RootDirectory, Sel.Item(cnt))
      
       ‘For each attachment on the message…
        For AttachmentCnt = 1 To Sel.Item(cnt).Attachments.Count
        ‘Get the attachment
        Dim att As Attachment
        Set att = Sel.Item(cnt).Attachments.Item(AttachmentCnt)
        outputFile = att.FileName
       
        If Len(outputDir) + Len(outputFile) > 254 Then
            If Len(outputFile) > 64 Then
‘This part really should search for the last period in the filename to attach
‘a file extension suffix that may be longer than 3 chars, such as .config.  But I’m not bothering with that right now.

‘also note that there is logic in the “GetOutputDirectory” function that has to match these settings, such as folder length 184 and filename length 55
            FullFolderPath = Left(outputDir, 184) + “(…)\”
            FullFilePath = FullFolderPath + Left(outputFile, 55) + “(…)” + Right(outputFile, 4)
            Else
                FullFolderPath = Left(outputDir, 254 – Len(outputFile) – 6) + “(…)\”
                FullFilePath = FullFolderPath + outputFile
            End If
        Else
            FullFolderPath = outputDir
            FullFilePath = outputDir + outputFile
        End If
       
        FullFilePath = Replace(FullFilePath, “>”, “”)
        fileExists = fso.fileExists(FullFilePath)
        
        Do While fileExists = True
            outputFile = InputBox(“The file ” + outputFile _
             + ” already exists in the destination directory of ” _
             + outputDir + “. Please enter a new name, or hit cancel to skip this one file.”, “File Exists”, outputFile)
            ‘If user hit cancel
            If outputFile = “” Then
                ‘Exit leaving fileexists true. That will be a flag not to write the file
                Exit Do
            End If
            fileExists = fso.fileExists(FullFilePath)
        Loop
        
        ‘Save it to disk if the file does not exist
        If fileExists = False Then
           
            att.SaveAsFile FullFilePath
            FileSize = CStr(Int(att.Size / 1024 / 1024)) + “.” + Right(“00” + (CStr(Int(((att.Size / 1024 / 1024) – Int(att.Size / 1024 / 1024)) * 100))), 2)
            If Sel.Item(cnt).Class = olMail Then
                If Sel.Item(cnt).BodyFormat = olFormatHTML Or Sel.Item(cnt).BodyFormat = olFormatRichText Then
                    Dim BodyStart As Integer
   
                    ‘The following lines are for debugging, to allow inspection of the raw content of an HTML or Rich Text email
                    ‘Content Before:
                    ‘                Dim FileObject As File
                    ‘                Dim Stream As TextStream
                    ‘                Set Stream = fso.CreateTextFile(“c:\temp\test.txt”, True)
                    ‘                Stream.Write (Sel.Item(cnt).HTMLBody)
                   
                    ‘Make the change to the body
                   
                    BodyStart = InStr(1, Sel.Item(cnt).HTMLBody, “<Body”, vbTextCompare)
                    Sel.Item(cnt).HTMLBody = Mid(Sel.Item(cnt).HTMLBody, 1, BodyStart) + Replace(Sel.Item(cnt).HTMLBody, “>”, “>Attachment Saved to <A HREF=””file:///” + FullFolderPath + “””>(.)\</A><A HREF=””file:///” + FullFilePath + “””>” + outputFile + ” (” + CStr(FileSize) + “MB)” + “</A><BR/>”, BodyStart + 1, 1, vbTextCompare)
   
                    ‘Content After
                    ‘                Stream.Write (Sel.Item(cnt).HTMLBody)
                    ‘                Stream.Close
                    ‘               Sel.Item(cnt).Save
                End If
           
                If Sel.Item(cnt).BodyFormat = olFormatPlain Then
                    Sel.Item(cnt).Body = “Attachment Saved to “”HYPERLINK “”” + FullFolderPath + “””(.)\””file:///” + FullFilePath + “””” + ” (” + CStr(FileSize) + “MB)” + vbCrLf + Sel.Item(cnt).Body
                End If
            Else
                Sel.Item(cnt).Body = “Attachment Saved to “”HYPERLINK “”” + FullFolderPath + “””(.)\””file:///” + FullFilePath + “””” + ” (” + CStr(FileSize) + “MB)” + vbCrLf + Sel.Item(cnt).Body
            End If ‘ if item.class = olMail
            AttTotal = AttTotal + 1

         End If

       Next ‘ Attachment

‘Remove all attachments
        While Sel.Item(cnt).Attachments.Count > 0
            Set att = Sel.Item(cnt).Attachments(1)
            att.Delete
        Wend

        If Sel.Item(cnt).Class = olMail Or Sel.Item(cnt).Class = olAppointment Then
            Dim oItem As Object
           
            Set oItem = Sel.Item(cnt)
            oItem.Attachments.Add (EmptyAttachment)
        End If
       
     End If ‘ There are attachments
    
   Next ‘ Selected Item
  
  ‘Clean up
    Set Sel = Nothing
    Set Exp = Nothing
    Set App = Nothing
    Set fso = Nothing
    Set att = Nothing
    Set oItem = Nothing
    ‘Let user know we are done
‘    Dim doneMsg As String
‘   doneMsg = “Completed saving ” + Format$(AttTotal, “#,0″) + ” attachments in ” + Format$(MsgTotal, “#,0″) + ” Messages.”
‘   MsgBox doneMsg, vbOKOnly, “Save Attachments”
  
    Exit Sub
  
ErrorHandler:
   Dim errMsg As String
   errMsg = “An error has occurred. Error ” + Err.Number + ” ” + Err.Description
   Dim errResult As VbMsgBoxResult
   errResult = MsgBox(errMsg, vbAbortRetryIgnore, “Error in Save Attachments”)
   Select Case errResult
     Case vbAbort
       Exit Sub
      
    Case vbRetry
       Resume
      
    Case vbIgnore
       Resume Next
      
  End Select
    
End Sub
 ‘Found this code in a google groups thread here:
 ‘http://groups.google.com/group/microsoft.public.scripting.vbscript/browse_thread/thread/7187886c3c83a570/c278a2753e9e7ceb%23c278a2753e9e7ceb
 ‘or http://shrinkster.com/l0v
 Public Function GetOutputDirectory(RootDirectory As String, oItem As Object) As String

 

‘Code to prompt user to browse for folder has been commented out.
‘In exchange, the mail object is parsed, and the folder is created from SenderName/Subject/DateTime

 
‘  Dim retval As String ‘Return Value
  
‘  Dim sMsg As String
‘   Dim cBits As Integer
‘   Dim xRoot As Integer
   
 
‘  Dim oShell As Object
‘   Set oShell = CreateObject(“shell.application”)
‘   sMsg = “Select a Folder To Output The Attachments To”
‘   cBits = 1
‘   xRoot = 17
  
‘  On Error Resume Next
‘       Dim oBFF
‘       Set oBFF = oShell.BrowseForFolder(0, sMsg, cBits, xRoot)
‘       If Err Then
‘         Err.Clear
‘         GetOutputDirectory = “”
‘         Exit Function
‘       End If
‘   On Error GoTo 0
  
‘  If Not IsObject(oBFF) Then
‘     GetOutputDirectory = “”
‘     Exit Function
‘   End If
  
‘  If Not (LCase(Left(Trim(TypeName(oBFF)), 6)) = “folder”) Then
‘     retval = “”
‘   Else
‘     retval = oBFF.self.Path
    
    ‘Make sure there’s a \ on the end
‘     If Right(retval, 1) <> “\” Then
‘       retval = retval + “\”
‘     End If
‘   End If

  
    Dim oFSO As FileSystemObject
    Dim FolderName As String
   
    Set oFSO = New FileSystemObject
   
    ‘Quickly check for the attachment with the longest name
    For i = 1 To oItem.Attachments.Count
        If Len(oItem.Attachments(i).FileName) > LongestFileName Then
            LongestFileName = Len(oItem.Attachments(i).FileName)
        End If
    Next
   
    FolderName = RootDirectory
   
    If Not oFSO.FolderExists(FolderName) Then
        oFSO.CreateFolder (FolderName)
    End If
 
If oItem.Class = olMail Then
    FolderName = FolderName + “\” + oItem.SenderName
Else
    If oItem.Class = olAppointment Then
        FolderName = FolderName + “\” + oItem.Organizer
    End If
End If

If Not oFSO.FolderExists(FolderName) Then
    oFSO.CreateFolder (FolderName)
End If

‘Certain characters that are just fine in subject lines are problematic for filesystem folder names
‘Remove them
‘(I know there’s a better way to do this, but I’ll leave that for someone else to enhance)

    Dim Subject
    Subject = Replace(oItem.Subject, “RE:”, “”, 1, -1, vbTextCompare)
    Subject = Replace(Subject, “FW:”, “”, 1, -1, vbTextCompare)
    Subject = Replace(Subject, “”””, “”, 1, -1, vbTextCompare)
    Subject = Replace(Subject, “:”, “”, 1, -1, vbTextCompare)
    Subject = Replace(Subject, “/”, “”, 1, -1, vbTextCompare)
    Subject = Replace(Subject, “?”, “(q)”, 1, -1, vbTextCompare)
    Subject = Replace(Subject, “*”, “”, 1, -1, vbTextCompare)
    Subject = Replace(Subject, “>”, “”, 1, -1, vbTextCompare)
    Subject = Replace(Subject, “<“, “”, 1, -1, vbTextCompare)
    Subject = Replace(Subject, “.”, “”, 1, -1, vbTextCompare)
    Subject = LTrim(RTrim(Subject))

    FolderName = FolderName + “\” + Subject
   
    ‘Limit total length to 254, but remember to leave 22 characters for the datetime segment of the folder path
   
    If Len(FolderName) + LongestFileName > 254 – 22 Then
        If Len(LongestFileName) > 64 Then
‘This part really should search for the last period in the filename to attach
‘a file extension suffix that may be longer than 3 chars, such as .config.  But I’m not bothering with that right now.
‘also note that there is logic in the main function that has to match these settings, such as folder length 184 and filename length 55
        FolderName = Left(outputDir, 184)
        Else
            FolderName = Left(FolderName, 254 – 22 – LongestFileName – 6) + “(…)”
        End If
    End If
   
   
    If Not oFSO.FolderExists(FolderName) Then
        oFSO.CreateFolder (FolderName)
    End If

‘Remove colons and slashes in DateTime
    If oItem.Class = olMail Then
        FolderName = FolderName + “\” + Replace(Replace(oItem.ReceivedTime, “/”, “-“, 1, -1, vbTextCompare), “:”, “.”, 1, -1, vbTextCompare)
    Else
        If oItem.Class = olAppointment Then
            FolderName = FolderName + “\” + Replace(Replace(oItem.Start, “/”, “-“, 1, -1, vbTextCompare), “:”, “.”, 1, -1, vbTextCompare)
        End If
    End If
   
    If Not oFSO.FolderExists(FolderName) Then
        oFSO.CreateFolder (FolderName)
    End If
   
    GetOutputDirectory = FolderName + “\”
  
End Function

 

Posted in Uncategorized | Leave a comment

BaseManagedEntityId

Did you ever notice that GUIDs in error messages can seem consistent across management groups?

Did you ever wonder why you could count on your Managed Object IDs being the same across management groups?

Probably not, but it can actually be useful to know in advanced cases.  And since this took me hours to reverse engineer, I’m going to post it.  As I said in my profile, I post things I find interesting.  🙂

BaseManagedEntityId GUIDs are deterministic because they are hashes of known information.  OpsMgr does not use NewID() to generate random GUIDs on the fly.

If you want to know what your BaseManagedEntityId is going to be, you can use SQL Server functions to figure it out.  You just have to get the string exactly right.

The format of the string is:

TypeId={ManagedTypeId-GUID},{KeyProperty1Id-GUID}=some value,{KeyProperty2Id-GUID}=other value

ManagedTypeId GUID is available using Powershell

Get-MonitoringClass –Name “Microsoft.Windows.Computer” | fl Id

Or from the database

Select ManagedTypeId from BaseManagedType where FullName like ‘%Windows.Computer%’

 

PropertyId GUIDs are also available from Powershell

Get-MonitoringClass –Name “Microsoft.Windows.Computer” | Get-MonitoringObject | %{Get-MonitoringObjectProperty} | fl Name, Id

Or from the Database

Select * From ManagedTypeProperty where ManagedTypePropertyName = ‘PrincipalName’

 

Your string should look like this:

TypeId={EA99500D-8D52-FC52-B5A5-10DCD1E9D2BD},{5C324096-D928-76DB-E9E7-E629DCC261B1}=SERVER1.MYDOMAIN.LOCAL

Now you get your hash by plugging this string into SQL Server’s Hashbytes function:

declare @hashstring nvarchar(max)
select @hashstring = ‘TypeId={EA99500D-8D52-FC52-B5A5-10DCD1E9D2BD},{5C324096-D928-76DB-E9E7-E629DCC261B1}=SERVER1.MYDOMAIN.LOCAL’
select convert(UniqueIdentifier, HASHBYTES(‘SHA1’,’@hashstring)

If you did everything right, your result will be the same ID as the one in the database for your SERVER1.MYDOMAIN.LOCAL object of type Microsoft.Windows.Computer.

Be careful:

  • GUIDs and Property Values (Server1.MyDomain.Local in the above example) need to be all-capitals.  Also, interestingly enough, you need to alphabetize your property GUIDs.  For example, if your class type has two key properties, you have to put them in alphabetical order in the string, even if the order isn’t meaningful.
  • Hosting objects’ key properties are key properties of the object you are looking for
  • You have to use the least-derived non-abstract managed entity that has a key property.  So if your class inherits from an abstract type that has a key property defined, and that abstract type is hosted by another object, you have to provide all those keys, but your ManagedType is the non-abstract one just below the abstract one.
  • The runtime code that does this in R2 has a bug.  If you start to mess with this stuff, be sure to get the rollup of fixes (due Nov 09).  Otherwise your hashes won’t match.

I haven’t figured out how these GUIDs are generated for Singleton types without key properties defined.  I also am not sure how other IDs (e.g. ManagedTypeId, ManagedTypePropertyId) are generated, but I believe they are also hashes, not random GUIDs.

This is certainly SCOM Esoterica, but is actually useful for some posts I hope to write shortly involving creating alerts and setting monitor states for objects other than the one targeted by the rule/monitor.  Cool stuff, actually.

Update (10/14/09)

The Id of a singleton object is the same as its class Id.

Posted in Uncategorized | 3 Comments

A little thing about GroupCalc

It only works on the RMS.  In hindsight this seems intuitive, but I didn’t figure it out until I banged my head on it for hours.  GroupCalc creates these neat Containment relationship objects using all kinds of cool query abilities.

But only if you target an object that is managed by the RMS HealthService.  Sigh….

Just for posterity (and web searches) here’s what the error looks like on the remote agent.

Event Type:    Error
Event Source:    HealthService
Event Category:    Health Service
Event ID:    4508
Date:        6/3/2009
Time:        11:11:02 PM
User:        N/A
Computer:    OMDEV1
Description:
Loading managed module type in assembly "Microsoft.Mom.DatabaseQueryModules, Culture="", PublicKeyToken="31bf3856ad364e35", Version="6.0.4900.0"" with type name "Microsoft.EnterpriseManagement.Mom.DatabaseQueryModules.GroupCalculationModule" failed with error code "80FF0001" in rule "MYMP.Discovery.SourceObject_Contains_TargetObject" running for instance "My Object – Source" with id:"{08A72EB6-327D-7E62-FC1C-2A868F13964B}" in management group "OMR2MG".
This may be because the type or assembly could not be found or the type does not have the MonitoringModuleAttribute.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Posted in Uncategorized | Leave a comment

SQL Query to get Group Members

I didn’t see the posted anywhere.  If you know of someone with a script like this, please put it in the comments. 

Boris has a nice PS script to do this here.

To get a quick list of the objects in all groups, the following SQL query works

Select BMESource.FullName as [Group], BMETarget.DisplayName as [Member]
  From BaseManagedEntity BMESource
    Inner Join Relationship R
      On R.SourceEntityId = BMESource.BaseManagedEntityId
    Inner Join BaseManagedEntity BMETarget
      On R.TargetEntityId = BMETarget.BaseManagedEntityId
  Order by BMESource.FullName

Note that this applies to all objects that are the source of any containment relationship.  If you just want groups, you can add the where clause

Select BMESource.FullName as [Group], BMETarget.DisplayName as [Member]
  From BaseManagedEntity BMESource
    Inner Join Relationship R
      On R.SourceEntityId = BMESource.BaseManagedEntityId
    Inner Join BaseManagedEntity BMETarget
      On R.TargetEntityId = BMETarget.BaseManagedEntityId
    Inner Join ManagedEntity MT1
      on BMESource.BaseManagedEntityId = MT1.ManagedEntityId
    Inner Join ManagedEntity MT2
      on MT1.BaseManagedEntityId = MT2.ManagedEntityId
    Where MT2.TypeName = ‘System.Group’
  Order by BMESource.FullName

Obviously, you can add other needed fields, such as ID in the initial select

Select BMESource.FullName as [Group], BMESource.BaseManagedEntityId as [Group ID], BMETarget.DisplayName as [Member], BMETarget.BaseManagedEntityId as [Member ID]
From…

This is a hack job, but I need to run.

Posted in Uncategorized | 2 Comments

Database MP Phase II

I decided to add database discovery to the MP.  The attached MP is an expansion of my prior post.

Start by creating a database to hold information about the objects.

And fill it with some rows

Next create a new custom datasource

Note in particular that the Query used the OleDB module, and the ClassId and InstanceSettings used by the Mapper are passed as parameters.  This allows you to reuse the datasource with lots of different discoveries where each discovery only has to know its database query, classtype, and the mapping between the two.

Next, create a class that we’ll discover with a database query.

Next, create a discovery using the datasource.

Here’s the XML for the Configuration:

<Configuration p1:noNamespaceSchemaLocation="C:\Documents and Settings\Administrator\Local Settings\Temp\DBMP.DiscoverMyClass.xsd" xmlns:p1="http://www.w3.org/2001/XMLSchema-instance">
  <IntervalSeconds>600</IntervalSeconds>
  <Query>
  Select
  [ID] as [1],
  [Description] as [2],
  [Color] as [3],
  [AlcoholContent] as [4]
  from ObjectTable
  </Query>
  <ClassId>$MPElement[Name=’DBMP.MyClassStoredInDB’]$</ClassId>
  <InstanceSettings>
    <Settings>
      <Setting>
        <Name>$MPElement[Name=’DBMP.MyClassStoredInDB’]/ID$</Name>
        <Value>$Data/Columns/Column[1]$</Value>
      </Setting>
      <Setting>
        <Name>$MPElement[Name=’DBMP.MyClassStoredInDB’]/Description$</Name>
        <Value>$Data/Columns/Column[2]$</Value>
      </Setting>
      <Setting>
        <Name>$MPElement[Name=’DBMP.MyClassStoredInDB’]/Color$</Name>
        <Value>$Data/Columns/Column[3]$</Value>
      </Setting>
      <Setting>
        <Name>$MPElement[Name=’DBMP.MyClassStoredInDB’]/AlcoholContent$</Name>
        <Value>$Data/Columns/Column[4]$</Value>
      </Setting>
      <Setting>
        <Name>$MPElement[Name=’System!System.Entity’]/DisplayName$</Name>
        <Value>$Data/Columns/Column[3]$ object number $Data/Columns/Column[1]$ </Value>
      </Setting>
    </Settings>
  </InstanceSettings>
</Configuration>

 

Note the embedded SQL Query and mapping details. 

I added a column to the MessageLog table called ItemID to act as a foreign key to the the ObjectTable table.  I also added a rule to the attached MP targeting the discovered objects from the database, and monitoring the MessageLog table.  So now the objects are described and stored in the database, and the health data about those objects also comes from the database.  I’ve also posted the SQL code to create the database, but you’ll have to stick some data in it.

Please let me know if this is useful. 

-Mike

 

http://cid-f2cbe6bae5bb51e1.skydrive.live.com/embedrowdetail.aspx/Public/DBMP2.ziphttp://cid-f2cbe6bae5bb51e1.skydrive.live.com/embedrowdetail.aspx/Public/CreateMyAppDB.sql

Posted in Uncategorized | Leave a comment

Reading a database table like a log file

Some applications log status updates to a database table, rather than drop them in a text file or eventlog.  I was able to get OpsMgr to read a database table like a log file, and thought I should post the how-to here, since I’ve seen a few other requests for how to do it.

At a high level, the methodology is to create a new data source, optionally map that data to something resembling Event Data, and then create rules against the data source.  I may be using the built-in OleDB module in an unintended way (I think it was just meant to probe for application database availability), but it has proven very successful so far.

Demo Setup

Let’s start with the database table (I’m making this up for example purposes)

I wrote a quick script to write data to the table as follows.  Pretty basic.  I thought about randomizing the data, but didn’t…

You’ll have to modify the values to get rules and monitors to fire in the MP

declare @i int
select @i =1
while @i<10
begin
    insert MessageLog ([EventCode], Severity, [Message], [TimeStamp], [User],[Computer])
    values             (1, 2, ‘Test Message’, getdate(), ‘Mike’,’Demo’)
    select @i=@i + 1
    waitfor delay ’00:00:03′
end

Other log sources have some way to persist a last-read-entry mark.  There’s no easy way to persist this within OpsMgr.  You could write script or your own external data source using managed code, but that’s not what I was trying to accomplish.  I didn’t want to have to deploy any additional bits on my boxes.  So I used a persistence table.

Initialization

You either have to initialize these manually (e.g. put a "1" in the MessageLogLastRead column) or create it on the fly in the datasource itself.  On the fly would mean detecting whether or not the DBMPPersistence table exists, and if not create it and initialize it with a valid value such as the current max(id) of the MessageLog table.  The behavior would then be that your MP would start reading rows that got logged after the MP was loaded.  You probably don’t want to load an MP and start reading from the beginning of a Message Log table…

Now comes the good stuff. 

Building the MP

RunAs Profile

First, create a Run As Account that can access the database table from OpsMgr.  This is kind of a pain, since creating one in the OpsMgr UI creates it in the Default MP, and the Auth Console doesn’t appear to expose a way to create one.  It goes between </EntityTypes> and <ModuleTypes> in the MP xml.  The format is:

</EntityTypes>
<SecureReferences>
   <SecureReference ID="DBMP.DbAccessAccount" Accessibility="Public" Context="System!System.Entity" />
</SecureReferences>
<ModuleTypes>

You’ll also want a friendly name for this, so enter something useful in the LanguagePack section.  For example:

   <DisplayString ElementID="MyMP.DbAccessAccount">
     <Name>Database Message Log Access Account</Name>
     <Description>Used by MyMP to read from the MessageLog table of the MyAppDB database.</Description>
   </DisplayString>

Save the MP and load it into the authoring console.  I’ve uploaded a completely blank MP, except for having this RunAs account, here.

Remember that once you import the MP, you need to create a RunAs Account, assign it to the DbAccessAccount profile you just created, and associate the profile to the HealthService where the Query will execute.

The DataSource – The root of it all

Now, create a new data source that polls the database table.  The datasource should consist of a System.Scheduler and a System.OleDBProbe module, and should use the RunAs account just created.  I recommend making the Scheduler a configurable parameter, but it can be hardcoded in the datasource.  Similarly, the ConnectionString and Query parameters can either be hardcoded or passed as parameters.  Both have advantages, but it’s important to not make either one overrideable.

There’s no editor page for the System.OleDbProbe module, so you’re getting into XML regardless.  Below is the XML configuration for the System.OleDbProbe module.  More on this later.

Note that the query renames the fields as "1", "2", "3" and so on.  Unlike the the recordset returned from an ADODB.Connection object (e.g. what you might use in VBScript) there is no way to refer to the columns by name.  The recordset is transformed into OpsMgr’s XML OLEDB.DataItem which only allows for referring to column by ordinal number.  The renaming operation (select [eventcode] as [1]) allows you to easily see how to refer to that column in downstream modules (e.g. $Data/Columns/Column[1]$ or $Data/EventData/DataItem/Columns/Column[1]$ as used in this sample MP).

<Configuration p1:noNamespaceSchemaLocation="C:\Documents and Settings\Administrator\Local Settings\Temp\DBProbe – System.OleDbProbe.xsd" xmlns:p1="http://www.w3.org/2001/XMLSchema-instance">
  <ConnectionString>Provider=SQLOLEDB;Server=.;Database=MyAppDB;Trusted_Connection=Yes</ConnectionString>
  <Query>
  –First get the most-recent-row in the MessageLog table.  Only read up to this in case there are writes during reads.
  –The query will pick up any late-written rows on the next poll

  declare @LastRow bigint
  select @LastRow = max([ID]) from MessageLog

  –Do the select.  The column names are not friendly, until you understand how these columns are referenced
  –by downstream workflow modules.  There is no way to refer to columns by name, only by sequence number.

  select
    [EventCode] as [1],
    isnull([User],’n/a’) as [2],
    isnull([Computer],’n/a’) as [3],
    [Severity] as [4],
    [Message] as [5],
    convert(varchar(15),[TimeStamp],106) as [4]
  from MessageLog
  Where
    [ID] &gt; (select MessageLogLastRow from DBMPPersistence)
  and
    [ID] &lt;= @LastRow
  Update DBMPPersistence set MessageLogLastRow = @LastRow
  </Query>
  <GetValue>true</GetValue>
  <OneRowPerItem>true</OneRowPerItem>
</Configuration>

The OleDB module returns a RecordSet in XML form.  RecordSets can have zero records, and indeed that’s what you’ll have if there were no rows to read.  It’s worth putting in an ExpressionFilter module to filter for this condition so that the datasource itself returns no data at all, rather than an empty recordset.

 

You can also optionally add a Mapper module to convert the OleDB data returned from the above module into something that looks more like an event.

 

 

Consuming Data

The rest of the work fans out quickly.  You can create a basic rule to read from the datasource and filter for values.

where the filter expression uses relatively familiar Event terms thanks to the Mapper:

You could also drill into the OleDBData dataitem that gets returned and embedded in the event.  Remember, the syntax for this is by column number:

$Data/EventData/DataItem/Columns/Column[2]$ would give you the username.  (in the XPathQuery tag of an ExpressionFilter, you don’t use the dollar signs or the leading "Data" tag, in other words filtering for username would just have

<XPathQuery>EventData/DataItem/Columns/Column[2]</XPathQuery>

)

You can also do more interesting things like create MonitorTypes and look for state based on information in a database..

At this point, there are too many screenshots to post here.  See the MP for the MonitorType definition and a couple of example uses.

Note that, using the DiscoveryMapper module, you can also do discovery of classes where the information is stored in a database.

 

Caveats and Gotchas

RunAs assignment

Remember, when you import your MP, you must create a RunAs account, assign it to the profile, and associate the profile with the agent where the SQL Query will run (where the rule is targeted).  Otherwise you’ll see this:

Event Type:    Warning
Event Source:    Health Service Modules
Event Category:    None
Event ID:    11852
Date:        6/17/2008
Time:        9:58:23 AM
User:        N/A
Computer:    SERVER1
Description:
OleDb Module encountered a failure 0x80040e09 during execution and will post it as output data item. 

Workflow name: DBMP.SimpleDBRule
Instance name: server1.manage.com
Instance ID: {093355C8-0283-EED8-A6BB-393E82B1FA19}
Management group: OM-MG

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

 

Persistence conflicts

The MP built above creates an environment where there is one common data provider that feeds information to all OpsMgr workflows that look at that data.  There will be one query run against the database every $IntervalSeconds$, and the rows returned will be processed as dataitems by each consuming workflow.

If you have rules or monitors running at different intervals, or do anything that results in multiple instances of the DataSource itself remember to create different rows in the persistence table.  Otherwise you will have an issue where your once-every-minute workflow reads the table and updates the LastReadRow value, then your once-every-10-minutes workflow will come along and only read since the LastReadRow, in other words it will miss 9 minutes of data.  You can avoid this by not using $IntervalSeconds$ as a parameter and just hard-coding the interval in the Datasource workflow.

Fields and Datatypes

You can get into a lot of trouble with very cryptic errors if you don’t do exactly the right things with your query.  These right things aren’t documented.  Good luck.  Some issues to look out for:

  • cast or convert Date/Time fields as nvarchar
  • watch for nulls and convert to strings.  To do this use select isnull(fieldname,”) from dbname syntax
  • cast TEXT fields as nvarchar (Select cast(fieldoftext as nvarchar (500)) as [1] from dbname)
  • Be liberal with use of rtrim to trim whitespace off the end of values
  • use <[CDATA[  ]]> wrappers around your query text in the MP XML to make them more readable and copy/paste-able between SQL Query Analyzer and the MP XML editor
  • Event log errors indicating 0x80040e4d mean that you don’t have your ConnectionString exactly and perfectly right, and there’s no documentation for which OleDB syntax to use.  I’m saving you hours just by telling you about this, even without telling you how to write your ConnectionString.  This event looks like

    Event Type:    Warning
    Event Source:    Health Service Modules
    Event Category:    None
    Event ID:    11852
    Date:        6/16/2008
    Time:        1:06:23 PM
    User:        N/A
    Computer:    SERVER1
    Description:
    OleDb Module encountered a failure 0x80040e4d during execution and will post it as output data item. 

    Workflow name: DBMP.CollectAllDBEntries
    Instance name: server1.manage.com
    Instance ID: {093355C8-0283-EED8-A6BB-393E82B1FA19}
    Management group: OM-MG

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

UPDATE – additional gotchas I just found (4/1/09 – no joke)

  • Event log 0x80040e14 indicates a syntax error in the SQL query itself.  If you’re using CDATA tags you can easily copy and paste the query from your MP into a SQL Query Analyzer to get to the root of it.  In my case, I was missing a closing parenthesis on a Cast call
  • Event log Error 1000’s are hard to troubleshoot because whatever broke is buried in SCOM source code, not in yours.  The SQL query runs just fine, just not from within the MP.  In my case, the following error (which had the same Stamp, but different fault addresses at different times) was because I had used a substring() without wrapping it with a cast().  The solution was cast(substring(blah, 1, 200) as varchar(200))

    Event Type:    Error
    Event Source:    HealthService
    Event Category:    None
    Event ID:    1000
    Date:        4/1/2009
    Time:        11:41:45 AM
    User:        N/A
    Computer:    HORIZONDEMO
    Description:
    Faulting application monitoringhost.exe, version 6.0.6278.0, stamp 47b71437, faulting module unknown, version 0.0.0.0, stamp 00000000, debug? 0, fault address 0x029f72f8.

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
    Data:
    0000: 41 00 70 00 70 00 6c 00   A.p.p.l.
    0008: 69 00 63 00 61 00 74 00   i.c.a.t.
    0010: 69 00 6f 00 6e 00 20 00   i.o.n. .
    0018: 46 00 61 00 69 00 6c 00   F.a.i.l.
    0020: 75 00 72 00 65 00 20 00   u.r.e. .
    0028: 20 00 6d 00 6f 00 6e 00    .m.o.n.
    0030: 69 00 74 00 6f 00 72 00   i.t.o.r.
    0038: 69 00 6e 00 67 00 68 00   i.n.g.h.
    0040: 6f 00 73 00 74 00 2e 00   o.s.t…
    0048: 65 00 78 00 65 00 20 00   e.x.e. .
    0050: 36 00 2e 00 30 00 2e 00   6…0…
    0058: 36 00 32 00 37 00 38 00   6.2.7.8.
    0060: 2e 00 30 00 20 00 34 00   ..0. .4.
    0068: 37 00 62 00 37 00 31 00   7.b.7.1.
    0070: 34 00 33 00 37 00 20 00   4.3.7. .
    0078: 69 00 6e 00 20 00 75 00   i.n. .u.
    0080: 6e 00 6b 00 6e 00 6f 00   n.k.n.o.
    0088: 77 00 6e 00 20 00 30 00   w.n. .0.
    0090: 2e 00 30 00 2e 00 30 00   ..0…0.
    0098: 2e 00 30 00 20 00 30 00   ..0. .0.
    00a0: 30 00 30 00 30 00 30 00   0.0.0.0.
    00a8: 30 00 30 00 30 00 20 00   0.0.0. .
    00b0: 66 00 44 00 65 00 62 00   f.D.e.b.
    00b8: 75 00 67 00 20 00 30 00   u.g. .0.
    00c0: 20 00 61 00 74 00 20 00    .a.t. .
    00c8: 6f 00 66 00 66 00 73 00   o.f.f.s.
    00d0: 65 00 74 00 20 00 30 00   e.t. .0.
    00d8: 32 00 39 00 66 00 37 00   2.9.f.7.
    00e0: 32 00 66 00 38 00 0d 00   2.f.8…
    00e8: 0a 00                     ..     

 

http://cid-f2cbe6bae5bb51e1.skydrive.live.com/embedrowdetail.aspx/Public/DBMP.zip

Posted in Management Packs | 1 Comment

Member Health

A couple of posts (1,2) in the newsgroup got my interest recently.

Rollup monitors allow you to set the health of a container object to the worst health of any member, the best health of any member, or the health of the object at some user-defined percentage point in the middle of the distribution.  There is no way to really roll together the health of the contained objects.

Unfortunately, there really is no way to do this natively in OpsMgr today.  You have to look up and aggregate the health "externally" and then plug it back in.

I wrote a management pack to do this, using a Powershell datasource and custom monitortype.  You have to place a Management Pack Reference on the SpecialRollupMonitoring management pack.  You have to use the Authoring Console to create a custom monitor using the

The data source requires

  • $Target/Id$ (hardcoded for you)
  • ClassTypeId
  • RMS (defaults to "." since singleton and DA groups will be on the RMS)

The ClassTypeId is there because a group can contain multiple class types (e.g. computers and network components), but you may want the health of the monitor to only represent computers.  In this case, you would enter

ClassTypeId = $MPElement[Name="Windows!Microsoft.Windows.Computer"]$

RMS is the host computer name of your Root Management Server.  This is needed for the Powershell script that gets the information and compiles it together.

The datasource returns a propertybag with the following properties:

  • ErrorPercentage – The fraction of objects in the group that are in Error state
  • WarningPercentage – The fraction of objects in the group in warning state
  • UnhealthyPercentage – The fraction of objects in the group not in Success state
  • ErrorCount – Raw count of objects in Error state
  • WarningCount – Raw count of objects in Warning state
  • UnhealthyCount – Sum of ErrorCount and WarningCount

The MonitorType compares the PropertyBag results to parameters you feed it.  The parameters you’ll have to provide are

  • IntervalSeconds – how frequently to update the state of the group or container object.
  • ClassTypeId – This is where you put $MPElement[Name="Windows!Microsoft.Windows.Computer"]$ or whatever class you want to be included in the rollup.
  • ErrorPercentageIndicatingDegraded – What fraction of member objects have to be in an Error state for the group state to go Yellow?
  • UnhealthyPercentageIndicatingDegraded – What fraction of member objects have to be in Error or Warning state for the group to go Yellow?
  • ErrorPercentageIndicatingDown – What fraction of member objects have to be in Error state for the group to go Red?
  • UnhealthyPercentageIndicatingDown – What fraction of member objects have to be in Error or Warning state for the group to go Red?

In my testing, I created a Distributed Application and added the objects I wanted to roll up.  I then exported the management pack containing the DA to the Authoring console and created a Custom Unit Monitor targeting the SC_2348789…. group that the DA created.  You could do the same with any group.

Feel free to post comments or questions.  The MP is in the zip file in both sealed and unsealed forms so you can see how it works. 

 

Update (9-May-2009): The script in the MP is flexible, but would be simpler using the GetRelatedPartialMonitoringObjects() method on $Group.  If you don’t need to select the member classtype, you can just replace everything from the "#Next get the class type object" through the line before "$oAPI = New-Object -comobject MOM.ScriptAPI;" with the following one line:

$Group.GetRelatedPartialMonitoringObjects() | % {$MemberObjects ++; if ($_.HealthState -eq "Error") {$ErrorObjects++} elseif ($_.HealthState -eq "Warning") {$WarningObjects++}}

http://cid-f2cbe6bae5bb51e1.skydrive.live.com/embedrowdetail.aspx/Public/SpecialRollupMonitoring.zip

Posted in Uncategorized | Leave a comment