BlackBerry Forums Support Community               

Closed Thread
 
LinkBack Thread Tools
Old 03-01-2009, 12:15 PM   #1 (permalink)
New Member
 
Join Date: Aug 2006
Model: 8703
Carrier: Verizon
Posts: 13
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default Powershell and BES

Please Login to Remove!

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"

Code:
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

Last edited by sboardsti : 03-01-2009 at 12:39 PM.
Offline  
Old 03-01-2009, 03:50 PM   #2 (permalink)
Thumbs Must Hurt
 
mitchelrl's Avatar
 
Join Date: Sep 2007
Model: 8900
PIN: N/A
Carrier: T-Mobile
Posts: 67
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
__________________
Mitchel Lewis - (Sys, BES, Exchange, SAN, Network) Admin
Current: 8900 on .168
Exchange 2007 and Blackberry Enterprise Server

Last edited by mitchelrl : 03-01-2009 at 03:56 PM.
Offline  
Old 03-01-2009, 07:27 PM   #3 (permalink)
New Member
 
Join Date: Aug 2006
Model: 8703
Carrier: Verizon
Posts: 13
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by mitchelrl View Post
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.
__________________
Aaron A
Sr. Messaging Engineer
Constellation Energy Group

Last edited by sboardsti : 03-01-2009 at 07:36 PM.
Offline  
Old 03-01-2009, 07:31 PM   #4 (permalink)
New Member
 
Join Date: Aug 2006
Model: 8703
Carrier: Verizon
Posts: 13
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Code:
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
    }
  }
}
__________________
Aaron A
Sr. Messaging Engineer
Constellation Energy Group

Last edited by sboardsti : 03-01-2009 at 07:35 PM.
Offline  
Closed Thread


Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On





Copyright 2004-2014 BlackBerryForums.com.
The names RIM and BlackBerry are registered Trademarks of BlackBerry Inc.