#Powershell, SQL and #BlackBerry

So following on from my last post (http://blog.flaphead.com/2014/02/12/powershell-and-sql/) I wanted to add some SQL queries to it.

BlackBerry hosts all it’s information in a SQL database.  Now if you want to get a list of your BlackBerry users you can fire this SQL query at the BlackBerry SQL Database:

$SQLCommand = "
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 14 THEN 'Failed to start'
            WHEN 13 THEN 'Stopped'
            WHEN 12 THEN 'Running'
            WHEN 9  THEN 'Redirection disabled'
            WHEN 7  THEN 'No PIN'
            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]
"

$tmpCSVData = Run-SqlQuery "$sql" "$db" $SQLCommand

So this will give you an array ($tmpCSVData) with all your BlackBerry users in it ;-)

Now for some extra Powershell love, what you can do it look at the LastFwdTime and say if the user device has not forwarded any mail in say the last 60 days, create a CSV file.

$today = Get-Date
$AgeInDays = 60
$removalDate = $today.AddDays(-$AgeInDays)

$ToRemove = $tmpCSVData | where {$_.LastFwdTime -le $removaldate} | Select @{Expression={$_.SMTPAddress};Label="-u"}
$tcsv = $ToRemove | ConvertTo-Csv -NoTypeInformation
$csv= $tcsv | ForEach{$_.replace('"','')}
$csv | Out-File InactiveBlackBerryUsers.csv
$csv | Out-File $RootFolder\InactiveBlackBerryUsers.csv

Yeah and? well the CSV can be pushed in to the BESUserAdminClient resource kit tool, and you can delete the inactive accounts using the csv!

Yeah Baby!

#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
}

BlackBerry Delivery Confirmation

#BlackBerry

Couldn’t for the life of me remember the subject line .. Knew about <confirm> but its the rest I forgot.  So here you are: [More info here: http://www.blackberry.com/btsc/KB18737]

Send an email with the following subject:

<$Confirm,RemoveOnDelivery> BlackBerry Test Mail .. PLEASE DO NOT OPEN OR DELETE

Once it has been delivered to the device, you will get a return email like this:

—–Original Message—–
From: me
Sent: 11 April 2013 16:59
To: me
Subject: BlackBerry Delivery Confirmation

Your message:

TO: me@me.com
SENT: today
SUBJECT: <$Confirm,RemoveOnDelivery> BlackBerry Test Mail .. PLEASE DO NOT OPEN OR DELETE

has been delivered to the recipient’s BlackBerry Handheld.