BlackBerry Forums Support Community               

Closed Thread
 
LinkBack Thread Tools
Old 09-11-2007, 10: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, 11:57 AM   #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, 12: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, 12: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, 05: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, 06: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, 01:55 PM   #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  




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