BlackBerry Forums Support Community               

Closed Thread
 
LinkBack Thread Tools
Old 09-11-2007, 11:39 AM   #1 (permalink)
New Member
 
Join Date: Sep 2007
Model: 8800
PIN: N/A
Carrier: ROGERS
Posts: 4
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Queries oh my

Please Login to Remove!

Hi, Just a question about doing an SQL query on our BES database.

What I am trying to accomplish is to get a notification when we have less than 10 licenses left.

If I use the following query, I can get the current total number of users:
Select COUNT(*) as TOTAL_USERS
FROM UserStats

And I can get the License Keys, but not the total number of Licenses.

Where can I find the total number of licenses in the BES database? Right now we have 737 Licenses and 717 users, therefore 20 free licenses.

Or should I not be doing it this way and there is some built in tool for License notification.

Thanks
Z
Offline  
Old 09-11-2007, 12:57 PM   #2 (permalink)
BlackBerry Genius
 
hdawg's Avatar
 
Join Date: Aug 2006
Model: hdawg
PIN: port3101.org
Carrier: hdawg
Posts: 6,632
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default

I've not found a way to get the license count out of the DB ... you can use SNMP to pull the # of licenses however.
Offline  
Old 09-11-2007, 01:26 PM   #3 (permalink)
New Member
 
Join Date: Sep 2007
Model: 8800
PIN: N/A
Carrier: ROGERS
Posts: 4
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by hdawg View Post
I've not found a way to get the license count out of the DB ... you can use SNMP to pull the # of licenses however.
... so then I just need to find the MIB for BES and find License Count? Ill start looking from my side, but if you know the OID I should be using then that would save me alot of time

Thanks
Z
Offline  
Old 09-11-2007, 01:38 PM   #4 (permalink)
Thumbs Must Hurt
 
jsplit's Avatar
 
Join Date: Oct 2005
Location: East Coast
Model: 8310
PIN: 2432499F
Carrier: ATT
Posts: 123
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default

License count isn't in the database currently and the SNMP license count isn't always 100% accurate :\
__________________
Jon Edmunds
[email address]
Senior Technical Consultant
Boxtone
http://www.boxtone.com
Offline  
Old 09-11-2007, 12:49 PM   #5 (permalink)
BlackBerry Genius
 
hdawg's Avatar
 
Join Date: Aug 2006
Model: hdawg
PIN: port3101.org
Carrier: hdawg
Posts: 6,632
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default

BlackBerryServer.mib is a part of the install; should be in the root of the Research In Motion directory; I don't have the OID off hand, but if you have a mib browser you can get it quickly; sorry. Should be under Dispatcher Config
Offline  
Old 09-12-2007, 01:38 PM   #6 (permalink)
New Member
 
Join Date: Sep 2007
Model: 8800
PIN: N/A
Carrier: ROGERS
Posts: 4
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default a somewhat nice workaround

I decided that I could work with what information I had.

Here is how I am receiving my notifications.

1. For starters I found what query will return our current total users
The sql query for finding out how many users we have is as follows, which I saved to a file called besusertotal.sql
Code:
USE {besdatabase}
SELECT COUNT(*) AS TOTAL_USERS
FROM UserStats
2. Next there is a tool from Microsoft called SQLCMD so here is the command for running the query and returning the result
Code:
c:\sqlcmd -S {besserver} -i besusertotal.sql
3. This returns the output as follows:
Code:
Changed database context to '{besdatabase}'.
TOTAL_Users
-----------
        717

(1 rows affected)
4. Ran the sqlcmd from a vbscript:
Code:
Set objShell = WScript.CreateObject("WScript.Shell")
Dim arrQueryOutput(5), strCurrentUserTotal
Set objWshScriptExec = objShell.exec ("sqlcmd -S {besdatabase} -i beslicensequery.sql")
SET objOutput = objWshScriptExec.Stdout
While not objOutput.AtEndOfStream
	arrQueryOutput(i) = objOutput.ReadLine
	i = i + 1
WEnd
'The following line will put line number 4 (starts at 0 "zero") into my string
'so that I can run math on it later
strCurrentUserTotal = arrQueryOutput(3)
'The strCurrentLicenseTotal is our current number of licenses, unforutnately
'This piece of informatin is not anywhere in the database so it will have to
'be updated manually, not a big issue because I am notified when licenses
'are added to the BES
strCurrentLicenseTotal = 737
strAvailableLicenses = strCurrentLicenseTotal - strCurrentUserTotal
5. Because SMTP is installed on the computer running the script, I put strAvailableLicenses variable into an e-mail and the script mails it off
Code:
If strAvailableLicenses < 10 Then
Dim strFrom, strTo, strCC, strSubject, strTextBody
	WScript.Echo "Sending E-mail"
	strTo = "e-mail addresses"
	strCC = "e-mail addresses"
	strSubject = "BES Licenses LOW !!"
	strTextbody = strNewContents
	SendmailNoAttach
End If

Sub SendMailNoAttach
	Set objEmail = CreateObject("CDO.Message")
	objEmail.From = "e-mail address"
	objEmail.To = strTo
	objEmail.CC = strCC
	objEmail.ReplyTo = "e-mail address"
	objEmail.Subject = strSubject
	objEmail.Textbody = strTextBody
	objEmail.Send
End Sub
Set the script to run as a scheduled task and thats it. Once the available license total reaches below 10, Zing goes an e-mail

I hope this helps anyone who is looking for automatic license notification. If I have completely missed something easier, please let me know

Z
Offline  
Old 10-17-2008, 08:55 AM   #7 (permalink)
New Member
 
Join Date: Oct 2008
Model: none
PIN: N/A
Carrier: vodafone
Posts: 1
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default "SNMP isn't always accurate" means "SNMP is always wrong"

see title

I wonder what dark secrets lie behind knowing the *total* count of licenses available and used.

SNMP doesn't work (total and available = 0)

Database has a nice stored procedure (GetLicenseCount) that uses a non-existent table.

The only thing you could do is *knowing* what type of licenses you buy (e.g. 5-user cals), stick to that principle and do some math based on the number of rows in table LICENSEKEYS.

Stone age techniques, and yes, we're in 2008.
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.