Welcome to Port3101.org : Your BES Connection Mark forums read | View Forum Leaders
Port3101.org : Your BES Connection



Reply
LinkBack (1) Thread Tools Display Modes
Powershell - BES Licences Used mail script
 
  1 links from elsewhere to this Post. Click to view. #1 (permalink)  
Old 09-09-2010, 11:44 AM
BES Activated
 
Join Date: Sep 2010
Location: UK
Posts: 6
Default Powershell - BES Licences Used mail script

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.

Last edited by ScouT; 09-09-2010 at 11:46 AM.
Reply With Quote
Sponsored Links
  #2 (permalink)  
Old 09-09-2010, 11:45 AM
BES Activated
 
Join Date: Sep 2010
Location: UK
Posts: 6
Default Script

#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)
Reply With Quote
  #3 (permalink)  
Old 04-15-2011, 07:12 PM
BES Activated
 
Join Date: Apr 2011
Posts: 4
Default

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
Reply With Quote
  #4 (permalink)  
Old 04-26-2011, 10:49 AM
BES Activated
 
Join Date: Feb 2011
Posts: 3
Default

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
)
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

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

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


LinkBacks (?)
LinkBack to this Thread: http://www.port3101.org/scripts-tools-hints-tricks/2468-powershell-bes-licences-used-mail-script.html
Posted By For Type Date
Getting CAL count from database - BlackBerry Forums Support Community This thread Refback 06-19-2014 11:12 AM

Similar Threads
Thread Thread Starter Forum Replies Last Post
BES X + E-Mail Reconciliation jarbro Port 3101: The BES Admin Bar & Grill 3 07-28-2010 03:06 PM
BESX Junk Mail Abbas Port 3101: The BES Admin Bar & Grill 7 06-24-2010 10:05 AM
Powershell code to query besdatabase habs3 Scripts & Tools / Hints & Tricks 1 12-03-2009 03:20 PM
KB19055 - Unable to open a voice mail message using the visual voice mail application hdawg Featured BlackBerry KB Articles 0 08-16-2009 07:56 PM
BlackBerry Mail Flow gibson_hg Port 3101: The BES Admin Bar & Grill 3 12-11-2008 01:57 AM


All times are GMT -4. The time now is 04:58 AM.
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2018, vBulletin Solutions, Inc.


 

SEO by vBSEO 3.3.2 PL2