This VBScript uses native SQL connectivity via ADO to query your BlackBerry Enterprise Server configuration database for the current number of users. Since the license total is not stored in the database you have to set it as a variable in the script. It then compare the two numbers and if your defined threshold is exceeded, it will email you a notification. Just schedule the script to run daily. Download a zipped version here, or copy below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
'BES license usage monitor. Set current license total, alert threshold, And 'other variables to be notified when the license usage exceeds the threshold. Dim strSQLServer, strBESDB, iCurrentLicenseTotal, iLicenseThreshold Dim strMailRecipients, strMailSender, strMailSubject, strMailServer '************************************* '------Configurable Variables--------- iCurrentLicenseTotal = 1000 'Total number of licenses in License Management iLicenseThreshold = 20 'Free licenses threshold for notification strSQLServer = "ServerName" 'NetBIOS name of SQL Server strBESDB = "DatabaseName" 'Database name in SQL '------Notification configuration----- strMailRecipients = "user1@domain.com,user2@domain.com" 'Command-separated list of addresses strMailSender = "BESLicenseCounter@domain.com" 'From address strMailSubject = "BES license usage at critical level" 'Subject of notification message strMailServer = "smtpserver.domain.com" 'FQDN of smarthost '************************************* Dim iCurrentUserTotal, iAvailableLicenses, strSQLQuery, strSQLConn Dim objSQLConn, objSQLRecordSet, objSQLExec strSQLConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=" & _ strSQLServer & "; Initial Catalog=" & strBESDB strSQLQuery = "Select Count(*) As Total_Users From UserStats" Set objSQLConn = CreateObject("ADODB.Connection") Set objSQLRecordSet = CreateObject("ADODB.Recordset") objSQLConn.Open = strSQLConn objSQLRecordSet.Open strSQLQuery, objSQLConn Set objSQLExec = objSQLRecordSet While Not objSQLExec.EOF iCurrentUserTotal = objSQLExec.Fields("Total_Users").Value objSQLExec.MoveNext Wend iAvailableLicenses = iCurrentLicenseTotal - iCurrentUserTotal If iAvailableLicenses < iLicenseThreshold Then Dim strMailBody strMailBody = iCurrentUserTotal & " of " & iCurrentLicenseTotal & " licenses are in use." strMailBody = strMailBody & " It is time to order more licenses." SendMail End If Sub SendMail Set objEmail = CreateObject("CDO.Message") objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strMailServer objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 objEmail.Configuration.Fields.Update objEmail.From = strMailSender objEmail.To = strMailRecipients objEmail.Subject = strMailSubject objEmail.HTMLBody = strMailBody objEmail.Send End Sub |