[sboardsti]

Something that might be useful to anyone that uses poweshell alot. I wrote this so I could quickly look up a users information instead of having to go the BES Admin console. I put this in my profile.ps1 along with my other custom functions so it loads when i open powershell.

Requirements:

1. Install Quest Powershell Cmdlets "http://www.quest.com/powershell/activeroles-server.aspx" and make sure the snapin is loaded in powershell. I usually load it in my profile.ps1 file.

2. To use the new functions you need to follow the following install instructions. Install these components in the following order from this web site "http://www.microsoft.com/downloads/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en" <This allows you to take advantage of the SQL 2008 cmdlets from Microsoft>

xxx8226; Microsoft SQL Server System CLR Types
xxx8226; Microsoft Core XML Services (MSXML) 6.0 (Note: This component is already part of SQL Server 2005, it will likely not need to be installed. I provide it here for completeness of prerequisites).
xxx8226; Microsoft SQL Server 2008 Native Client (Note: I only installed the client. I did not install the SDK as well.)
Microsoft SQL Server 2008 Management Objects
xxx8226; Microsoft Windows PowerShell Extensions for SQL Server

Run this from powershell.

set-alias installutil $env:windir\microsoft.net\framework\v2.0.50727\ins tallutil
installutil -i "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist\Microsoft.SqlServer.M anagement.PSProvider.dll"
installutil -i "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\Redist\Microsoft.SqlServer.M anagement.PSSnapins.dll"


Usuage : gbesuser "samaccountname"

function gbesuser ($user){

function devicetype ($type){
	switch ($type){ 
		"3"{"GSM"}
		"4"{"CDMA"}
		default{"Unknown"}
	}
}
function vendor($vendorID){
	switch ($buser.VendorID){ 
		"102"{"Cingular"}
		"103"{"NEXTEL"}
		"104"{"SprintPCS"}
		"105"{"Verizon"}
		"107"{"Rogers"}
		"109"{"Bell Mobility"}
		"120"{"Vodafone-UK"}
		"126"{"TELUS"}
		"138"{"Vodafone-AU"}
		"220"{"NNT DoCoMo"}
		default{"Unknown"}
	}
}
	$server="<enter sql server name>"
	$database = "<enter database name>"
	$mdn = get-Qaduser $user -includeallproperties | select legacyExchangeDN,AccountIsDisabled
	$bsql1 = "Select UserConfig.DisplayName,MachineName,PIN,DeviceType,ModelName,PhoneNumber,AppsVer`
	,VendorID,IMEI,ICCID,MsgsForwarded,MsgsSent,MsgsPending,MsgsExpired,MsgsFiltered,MsgsFailed`
	,Status,LastFwdTime,LastSentTime from UserConfig,SyncDeviceMgmtSummary`
	,UserStats,ServerConfig where UserConfig.ID=SyncDeviceMgmtSummary.UserConfigID `
	and UserConfig.ID=UserStats.UserConfigID and UserConfig.MailboxDN = '"+$mdn.legacyexchangedn+"' `
	and Serverconfig.ID=UserConfig.ServerConfigID"
	$buser = Invoke-Sqlcmd -query $bsql1 -serverinstance $server -database $database
	$buser | select displayname,machinename,pin,@{Name="DeviceType";expression={devicetype $buser.devicetype}}`
	,modelname,phonenumber,appsver,@{name="VendorID";expression={vendor $buser.vendorID}}`
	,imei,iccid,MsgsForwarded,msgssent,msgspending,msgsexpired,msgsfiltered,msgsfailed,status,lastfwdtime,`
	lastsenttime,@{name="AccountStatus";expression={if($mdn.accountisdisabled -eq $true){"Disabled Account"}else{"Normal Account"}}}
}

Expected Output: (I replaced company internal information with astericks)

DisplayName : ***********
MachineName : **********
PIN : ***********
DeviceType : GSM
ModelName : 8310
PhoneNumber : ***********
AppsVer : 4.5.0.110
VendorID : Cingular
IMEI : **********
ICCID : **********
MsgsForwarded : 14268
MsgsSent : 452
MsgsPending : 0
MsgsExpired : 0
MsgsFiltered : 0
MsgsFailed : 1
Status : 12
LastFwdTime : 3/1/2009 4:49:27 PM
LastSentTime : 3/1/2009 1:45:04 PM
AccountStatus : Normal Account

[mitchelrl]

Neat tool, this is what we developed internally to monitor our client's Blackberry Enterprise Servers. We have this on all of our clients whose BES's we manage.

EDIT

NM, going to unveil it in a separate post.

[sboardsti]

Originally Posted by mitchelrl

Neat tool, this is what we developed internally to monitor our client's Blackberry Enterprise Servers. We have this on all of our clients whose BES's we manage.

EDIT

NM, going to unveil it in a separate post.

Cool I would like to see that. If anyone uses my function in a multple BES DB environment I would suggest making $server and $database values pass to the function instead of hard-coding in the function that way you can use it quickly for any BES DB.

[sboardsti]

Also here is one to dump who is a BES user but is disabled in Active Directory. This function uses the above get-besusers function.

Usage: get-disabledbesusers | format-list

function get-disabledBesUsers{
  $server="<enter server name>"
  $database = "<enter database name>"
  foreach ($user in (Get-QADUser -SizeLimit 50000 -Disabled -IncludedProperties legacyexchangedn | select samaccountname, legacyexchangedn)){
    $query = "Select MailboxDN from UserConfig where MailboxDN = '"+ $user.legacyexchangedn +"'"
    $sresult = Invoke-Sqlcmd -query $query -serverinstance $server -database $database
    if ($sresult -ne $null){
      get-besuser $user.samaccountname | select displayname,machinename,pin,phonenumber,vendorid,accountstatus
    }
  }
}