SQL query to get #BlackBerry User information

So following on from my last post (http://blog.flaphead.com/2013/04/23/using-powershell-to-run-a-sql-command/) I use this SQL query to dump BlackBerry User information in to a #Powershell array so I can export it out.

SELECT [ServerConfig]. [ServiceName]
      , [UserConfig]. [DisplayName]
      , [UserConfig]. [UserName]
      , [userconfig]. [MailboxSMTPAddr] as [SMTPAddress]
      , [SyncDeviceMgmtSummary]. [ModelName]
      , [SyncDeviceMgmtSummary]. [PhoneNumber]
      , [SyncDeviceMgmtSummary]. [PlatformVer]
      , [SyncDeviceMgmtSummary]. [IMEI]
      , [SyncDeviceMgmtSummary]. [HomeNetwork]
      , [SyncDeviceMgmtSummary]. [AppsVer]
      , [UserConfig]. [PIN]
      , [ITPolicy2]. [PolicyName]
      , [UserConfig]. [MailboxSMTPAddr]
      , [UserConfig]. [MailboxDN]
      , [UserConfig]. [ServerDN] as [ExchangeServer]
      , [UserConfig]. [AgentId]
      , [UserConfig]. [RoutingInfo]
      , [UserStats]. [MsgsPending]
      , [UserStats]. [LastFwdTime]
      , [UserStats]. [LastSentTime]
      , CASE [UserStats] . [Status]
            WHEN 13 THEN ‘Stopped’
            WHEN 12 THEN ‘Running’
            WHEN 9  THEN ‘Redirection disabled’
            WHEN 0  THEN ‘Initializing’
            ELSE ‘Unknown [‘ + CONVERT (varchar , [UserStats]. [Status] ) + ‘]’
        END AS UserStatus
  FROM [dbo] . [UserConfig]
   LEFT OUTER JOIN [dbo] .[UserStats]
    ON [UserConfig]. [Id] =[UserStats] . [UserConfigId]
   LEFT OUTER JOIN [dbo] .[ITPolicy2]
    ON [UserConfig]. [ITPolicy2Id] =[ITPolicy2] . [Id]
   LEFT OUTER JOIN [dbo] .[ServerConfig]
    ON [UserConfig]. [ServerConfigId] =[ServerConfig] . [Id]
   LEFT OUTER JOIN [dbo] .[SyncDeviceMgmtSummary]
    ON [UserConfig]. [Id] =[SyncDeviceMgmtSummary] . [UserConfigId]

So setting the above at $SqlQuery and then running

$SqlServer = “BlackBerry SQL Server”
$SqlDb = “BlackBerry Database Name”
RunSqlQuery  $SqlServer $SqlDb $SqlQuery

 

#Lync Integration with #BlackBerry

So I have a need to integrate Lync with BlackBerry.  So I followed this wicked guide http://talesfromc.blogspot.co.uk/2011/04/lync-bes-503-integration.html?m=1 and all was shweet.  I had to make a few tweeks to the certificate request inf file, as the SAN names just didn’t want to add themselves to the certificate request:

[Version]
Signature=”$Windows NT$”

[NewRequest]
Subject = “CN=LyncFEPool.domain”
Exportable = TRUE
KeyLength = 1024
KeySpec = 1
KeyUsage = 0xA0
MachineKeySet = True
FriendlyName = “OCSConnector”
ProviderName = “Microsoft RSA SChannel Cryptographic Provider”
ProviderType = 12
RequestType=pkcs10

[EnhancedKeyUsageExtension]
OID=1.3.6.1.5.5.7.3.1
OID=1.3.6.1.5.5.7.3.2

[Extensions]
2.5.29.17 = “{text}”
_continue_= “DNS=PrimaryBES.domain&”
_continue_= “DNS=StandbyBES.domain&”
_continue_= “DNS=LyncFEPool.domain”

Then run certreq -new c:\LyncRequest.inf c:\LyncCertNew.req and request the cert, then import it blah, blah.

However, I have an Active/Passive BlackBerry setup and when I failed over to the Passive node, Lync didn’t work from my device.

The BlackBerry Collaboration Service would start and then stop with 3 Error events in the application event log:

Source:        BlackBerry Collaboration Service
Event ID:      15000
Level:         Error
Description:
<2013-04-12 12:12:44.910 BST>:[126]:<BBIM_EMDC2BEM01_BBIM_1>:<ERROR>:<LAYER = BBIM, [OCSC] TlsFailureException: The operation failed due to issues with Tls. See the exception for more information., ErrorCode=-2146893042, FailureReason = Other, InnerExceptionCertificateInfoNative::AcquireCredentialsHandle() failed; HRESULT=-2146893042>

Source:        BlackBerry Collaboration Service
Event ID:      15000
Level:         Error
Description:
<2013-04-12 12:12:44.910 BST>:[127]:<BBIM_EMDC2BEM01_BBIM_1>:<ERROR>:<LAYER = BBIM, [OCSC] TlsFailureException: The operation failed due to issues with Tls. See the exception for more information., ErrorCode=-2146893042, FailureReason = Other, InnerExceptionCertificateInfoNative::AcquireCredentialsHandle() failed; HRESULT=-2146893042>

Source:        BlackBerry Collaboration Service
Event ID:      15000
Level:         Error
Description:
<2013-04-12 12:12:44.925 BST>:[130]:<BBIM_EMDC2BEM01_BBIM_1>:<ERROR>:<LAYER = BBIM, [OCSC] BlackBerry OCSConnector will terminate…>

Turns out that you can’t use the initial cert you created, you have to export it with the public key to a .pfx file.  Then import that on to the Standby BES.

Everyday is a school day!

 

BlackBerry “Domain Prep”

#BlackBerry, #Powershell

So I had a bit of brain freeze yesterday, and then it hit me.  BlackBerry needs AD SendAs permissions, but I thought I had done that!

Doh! The current place I am working has multiple AD Domains, and I done the root!  What I needed was an Exchange like domainprep to apply the permissions ..

So knocked up this.  I basically this assigns the permission to a group, so I can add and remove service accounts to it ;-)

$domains = ([System.DirectoryServices.ActiveDirectory.forest]::getcurrentforest()).domains | select name | sort name
ForEach($domain in $Domains){
$dom = $domain.name
Write-Host $dom
Add-ADPermission $dom -user “domain\Group” -AccessRights extendedright -ExtendedRight Send-As
}