Ok I've been feeding of Hdawg's posts for a while now so I thought its about time I gave something back to the Port3103 community.

I spent a while on the phone to RIM discussing the possabilities of a Powershell script querying the BES DB and mailing out the current licences in use to our purchasing team to order more.

After a long while I got the usual response of "We advise against doing anything directly to the database etc etc and we dont currently have a method to do so."

So I did it myself.

I hope some of you can find use for this. Just save it and set it as a scheduled tasks on a box able to mail on port 25 if you have an SMTP relay and it will fire off a nice email to your admins to remind them to buy licences.

If there are any more powershell scripts you would find useful fire me a message and I will see what I can do.

Script to follow.

#Sets attribute emailfrom as todays date. This will be added to the SMTP address so the mails cannot be marked as junk.
$emailfrom = Get-Date -Format ddMMMyy
$SmtpClient = new-object system.net.mail.smtpClient
$MailMessage = New-Object system.net.mail.mailmessage

#Set mail server and content details
$SmtpClient.Host = "ENTER YOUR BRIDGEHEAD OR SMTP RELAY SERVER HERE" # "somerelay@domain.com"

#I added the date field in the format DDMMMYY at the request of my manager as he wanted to make sure the sender address couldnt be blocked by the recipients
#If you want to be really mean you can also add this into the subject etc so the junk filters cant be used

$mailmessage.from = $emailfrom+"@ENTER YOUR EMAIL DOMAIN HERE" #$emailfrom+"@domain.com"

$mailmessage.To.add("firstemailaddress@domain.com, secondemail@domain.com, third@someotherdomain.com") #Enter the email addresses you want to recieve the mail seperated by a ,
$mailmessage.Subject = "Blackberry Server License Report"

#Static value of our current licences. I dont have a way to pull this from the BES as I have yet to find a table or item which holds this in the database so this needs to be manually updated if we purchase more.
#If anyone finds a way please let me know
$TotalLicense = "Total Licenses : 1480"


#Setup Database connection
$SQLSERVER="DBServer\DBServerInstance" #Enter your Server and instance details. BESDBServer\BESInstance
$Database="BlackberryDatabaseName" #Enter your BesMGMT Database name

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SQLSERVER;Database=$DATABASE;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

#Run SQL Query to obtain all DeviceID's (PIN numbers) from the user tables
$SqlCmd.CommandText = "SELECT DeviceUserID FROM UserConfig"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$Listvalue=$DataSet.Tables[0]| format-list

#Takes a count of the number of records in the dataset to confirm current number of devices in use hence licences used by the server.
#Remove 4 from count to remove the added lines from the header that would throw off the count. This is static -4
$usedcount = $Listvalue.count
$usedcount = $usedcount-4

#Build a string to send out in the email to the recipients above. firstemailaddress, second, third.....
$LicenseUsed = "Total Licenses Used : " + $usedcount
$mailmessage.Body = $LicenseUsed + "`r`n" + $TotalLicense + "`r`n" + "Please be ready to buy new Licenses"
$smtpclient.Send($mailmessage)

Scout,

Love the script! Saves me a few minutes each week as this stat is part of my weekly status report to management.

I tweaked it just a bit. I added a second variable for the license count:

$TotalLicense2 = 5644

Then added some math into the email to show if we are up or down on licenses.

$mailmessage.Body = $LicenseUsed + "`r`n" + $TotalLicense + "`r`n" + "License Status: " + ($TotalLicense2 - $usedcount)

Thanks again

-sbaker33

I did someting similar just with a batch file and an old Windows 200 Resource Kit tool snmputil.exe. Also, blat.exe is just a shareware command line smtp email tool. Adjust the community straing as needed (public). I currently use it with BES 4.0.7, have not tested it with 5.x.x yet. Haven't check into doing SNMP with Powershell yet.

@echo off
for /f "tokens=4" %%a in ('snmputil.exe get localhost public .1.3.6.1.4.1.3530.5.50.1.37.1^|Find "Value"') do set bblicavail=%%a
echo %date% %time% %bblicavail%>>licstat.txt
if %bblicavail% LEQ 10 (
echo %date% %time%>tempemail.txt
echo.>>tempemail.txt
for /f "tokens=4" %%a in ('snmputil.exe get localhost public .1.3.6.1.4.1.3530.5.50.1.36.1^|Find "Value"') do echo Total licenses: %%a>>tempemail.txt
for /f "tokens=4" %%a in ('snmputil.exe get localhost public .1.3.6.1.4.1.3530.5.50.1.38.1^|Find "Value"') do echo Licenses used : %%a>>tempemail.txt
echo Licenses avail: %bblicavail%>>tempemail.txt
blat.exe tempemail.txt -of email-header.txt
)