0

I have written some powershell, which checks a bunch of users mailboxes, and checks a specific property using a Mapitable. However I need to count the UserProperties also, and i'm struggling to do this in conjunction with the MapiTable - Ideally i'd like it to be "select blah from blah where userproperties.count = 4 AND crmregardingID IS NOT NULL"

Here's the snippet of code:

$sSqlQuery = 'Select Subject, senderName, MessageClass, ReceivedTime From $oFolName where "http://schemas.microsoft.com/mapi/string/{00020329-0000-0000-C000-000000000046}/crmRegardingId" IS NOT NULL'
$CurrFold = $RSession.GetFolderFromID($oDefFolder.EntryID)

$RTable.Item = $CurrFold.Items

$RecordSet = $RTable.ExecSQL($sSQLQuery)
if($recordset.recordcount -gt 0){
write-host "running loop"
Do STUFF
$recordset.Movenext()} until
    ($Recordset.EOF -eq $true )
$recordset.close()
$RSession.Logoff()
}

All help much appreciated :)

Madgeni
  • 65
  • 4

1 Answers1

0

User properties definitions are stored as a blob in a special named property. The only way to get the UserProperties collection is to open the message as RDOMail object; you cannot do that using ExwecSQL.

Dmitry Streblechenko
  • 62,942
  • 4
  • 53
  • 78
  • Thanks Dmitry - came to that conclusion, so do a mash up of both: $RecordSet = $RTable.ExecSQL($sSQLQuery) if($Recordset.recordcount -gt 0){ Do { $mail = $rsession.GetMessageFromID($Recordset.Fields.Item("EntryID").Value) $UserProps = $mail.UserProperties if ($Userprops.Count -eq 4){ Do STUFF} – Madgeni Apr 25 '13 at 08:05
  • 1
    You might want to filter out messages that do *not* have the user properties blob by including something like the following in your SQL - "http://schemas.microsoft.com/mapi/id/{00062008-0000-0000-C000-000000000046}/85400102" IS NOT NULL – Dmitry Streblechenko Apr 25 '13 at 16:46
  • Looks like Stackoverflow removes the "http" prefix for the property and the "/" between the id and GUID. Look at an item with with the user properties blob set using OutlookSpy - click IMessage, select the user properties blob property, look at the DASL edit box. – Dmitry Streblechenko Apr 25 '13 at 16:48
  • Thanks again - i need to verify UserProperties has returned 4, as this is a determining factor in a failure with CRM. My workaround seems to be ok – Madgeni Apr 29 '13 at 16:22
  • You can still do that, but my point was that you can speed up your code significantly by filtering out the items with no user properties at all. Then you can check UserProperties.Count for each itemi in the restricted collection. – Dmitry Streblechenko Apr 30 '13 at 18:12
  • Hi - Is it possible to update the Type of an existing UserProperty? I'm struggling here - any help appreciated @Dmitry – Madgeni May 09 '13 at 16:13
  • No, once that property was already used with a particuklar type, the message store remembers the mapping (at least Exchange does). The only workaround is to create a new name. – Dmitry Streblechenko May 09 '13 at 19:30