qc_metal
11-09-2005, 04:45 PM
This is a quick script that I wrote for our Support Desk so they could see quickly if a user was set up on our BES server.
Requirements:
The user running the query must have read permissions to the database (at least) in order to return query results.
Our environment:
BES 3.6.6 - I've been told this does not work on 4.0. I'll update the code (or you could if you know the proper SQL query and update it in the script) when we finish our migration.
We are running MSDE as the database for our BES configuration, I have admin rights on the box, whereas our support desk do not (again, only read-only access to the db).
Rename with .vbs.
When run, it will generate an html page with the query details and results. You can perform a partial string query in addition to a specific name.
Const ForWriting = 2
Dim strUserID, t, strUserIDArray
'Your server hosting your BES DB
strBESServer = "yourBESServer"
'Set some font settings for our HTML page.
fface = "arial"
fcolor = "black"
fsize = "2"
Set myconn = CreateObject("adodb.connection")
strUser = InputBox("Enter a string of text (username, etc.) you wish to " _
& "query the Blackberry server with. " & vbcrlf & vbcrlf _
& "You can enter a portion of the name, or use % to list all users.","Enter user name search text","%")
'If nothing was entered, then quit.
If strUser = "" Then wscript.quit
'set up the connection string.
connection = "Provider=SQLOLEDB;" & _
"Data Source=" & strBESServer & ";" & _
"Initial Catalog=BESMgmt;" & _
"Trusted_Connection=yes"
'Open the connection to the database.
myconn.open (connection)
'Set some objects.
Set WshShell = WScript.CreateObject("WScript.Shell")
Set WshSysEnv = WshShell.Environment("PROCESS")
Set ws = wscript.CreateObject("Scripting.FileSystemObject")
Set result = CreateObject("adodb.recordset")
'Define our temporary report index file.
tempfile = WshSysEnv("TEMP") & "\" & "BES_Report.htm"
'Open the file for writing.
Set t = ws.OpenTextFile (tempfile, ForWriting, True)
'Generate the HTML
Call setupHTM("begin")
'wscript.echo strUser
SQL = "SELECT UserMailboxDN, UserName FROM PolicyUsers WHERE UserName LIKE '%" & strUser & "%'"
'Run the query.
Call GetUserInfo
'Close the HTML
Call SetupHTM("end")
'If no search results were found, produce a msgbox and exit.
If strUserID = "" Then
msgbox "No user with the text %" & strUser & "% in the user name field does not appear " _
& "in the Blackberry database specified on the server " & strBESServer & "."
wscript.quit
Else
'otherwise, open up the html page.
Set oShell = CreateObject("WScript.Shell")
oShell.Run tempfile,1,false
End If
Sub GetUserInfo
set result = myconn.execute(SQL)
if not result.EOF then
While not result.EOF
strUserID = result("UserName")
strUser = result("UserMailboxDN")
t.writeline("<tr BGCOLOR='aliceblue'><td><font face='" & fface & "' color='" _
& fcolor & "' size='" & fsize & "'>" & result("UserName") & "</font></td><td> " _
& "<font face='" & fface & "' color='" _
& fcolor & "' size='" & fsize & "'>" & result("UserMailboxDN") & "</font></td></tr>")
'Move to the next record.
result.movenext()
Wend
Else
End If
End Sub
Function SetupHTM(strVar)
'If this is the beginning of the file, set up the head, title, and table for the page.
If LCase(strVar) = "begin" Then
t.writeline ("<html><head><title> BES server search results from " & strBESServer & "</title></head><body>")
t.writeline ("<font face='" & fface & "' color='" & fcolor & "'><strong>" _
& " Blackberry Server query results from " & strBESServer & "</strong></font><table>")
t.writeline ("<font face='" & fface & "' color='" _
& fcolor & "' size='" & fsize & "'>Searching for text:" & strUser & "</font><br>")
t.writeline ("<table border=1><tr><td><font face='" & fface & "' color='" _
& fcolor & "' size='" & fsize & "'> User name </font></td><td>" _
& "<font face='" & fface & "' color='" & fcolor & "' size='" & fsize & "'>" _
& "User object ID</font></td></tr>")
ElseIf LCase(strVar) = "end" Then
'if this is the end of the file, close out the table and html code. Then close the
' file entirely.
t.writeline ("</table></html>")
t.close
End If
End Function
Thought I would contribute something that may be handy for the rest of you!
Rob
Requirements:
The user running the query must have read permissions to the database (at least) in order to return query results.
Our environment:
BES 3.6.6 - I've been told this does not work on 4.0. I'll update the code (or you could if you know the proper SQL query and update it in the script) when we finish our migration.
We are running MSDE as the database for our BES configuration, I have admin rights on the box, whereas our support desk do not (again, only read-only access to the db).
Rename with .vbs.
When run, it will generate an html page with the query details and results. You can perform a partial string query in addition to a specific name.
Const ForWriting = 2
Dim strUserID, t, strUserIDArray
'Your server hosting your BES DB
strBESServer = "yourBESServer"
'Set some font settings for our HTML page.
fface = "arial"
fcolor = "black"
fsize = "2"
Set myconn = CreateObject("adodb.connection")
strUser = InputBox("Enter a string of text (username, etc.) you wish to " _
& "query the Blackberry server with. " & vbcrlf & vbcrlf _
& "You can enter a portion of the name, or use % to list all users.","Enter user name search text","%")
'If nothing was entered, then quit.
If strUser = "" Then wscript.quit
'set up the connection string.
connection = "Provider=SQLOLEDB;" & _
"Data Source=" & strBESServer & ";" & _
"Initial Catalog=BESMgmt;" & _
"Trusted_Connection=yes"
'Open the connection to the database.
myconn.open (connection)
'Set some objects.
Set WshShell = WScript.CreateObject("WScript.Shell")
Set WshSysEnv = WshShell.Environment("PROCESS")
Set ws = wscript.CreateObject("Scripting.FileSystemObject")
Set result = CreateObject("adodb.recordset")
'Define our temporary report index file.
tempfile = WshSysEnv("TEMP") & "\" & "BES_Report.htm"
'Open the file for writing.
Set t = ws.OpenTextFile (tempfile, ForWriting, True)
'Generate the HTML
Call setupHTM("begin")
'wscript.echo strUser
SQL = "SELECT UserMailboxDN, UserName FROM PolicyUsers WHERE UserName LIKE '%" & strUser & "%'"
'Run the query.
Call GetUserInfo
'Close the HTML
Call SetupHTM("end")
'If no search results were found, produce a msgbox and exit.
If strUserID = "" Then
msgbox "No user with the text %" & strUser & "% in the user name field does not appear " _
& "in the Blackberry database specified on the server " & strBESServer & "."
wscript.quit
Else
'otherwise, open up the html page.
Set oShell = CreateObject("WScript.Shell")
oShell.Run tempfile,1,false
End If
Sub GetUserInfo
set result = myconn.execute(SQL)
if not result.EOF then
While not result.EOF
strUserID = result("UserName")
strUser = result("UserMailboxDN")
t.writeline("<tr BGCOLOR='aliceblue'><td><font face='" & fface & "' color='" _
& fcolor & "' size='" & fsize & "'>" & result("UserName") & "</font></td><td> " _
& "<font face='" & fface & "' color='" _
& fcolor & "' size='" & fsize & "'>" & result("UserMailboxDN") & "</font></td></tr>")
'Move to the next record.
result.movenext()
Wend
Else
End If
End Sub
Function SetupHTM(strVar)
'If this is the beginning of the file, set up the head, title, and table for the page.
If LCase(strVar) = "begin" Then
t.writeline ("<html><head><title> BES server search results from " & strBESServer & "</title></head><body>")
t.writeline ("<font face='" & fface & "' color='" & fcolor & "'><strong>" _
& " Blackberry Server query results from " & strBESServer & "</strong></font><table>")
t.writeline ("<font face='" & fface & "' color='" _
& fcolor & "' size='" & fsize & "'>Searching for text:" & strUser & "</font><br>")
t.writeline ("<table border=1><tr><td><font face='" & fface & "' color='" _
& fcolor & "' size='" & fsize & "'> User name </font></td><td>" _
& "<font face='" & fface & "' color='" & fcolor & "' size='" & fsize & "'>" _
& "User object ID</font></td></tr>")
ElseIf LCase(strVar) = "end" Then
'if this is the end of the file, close out the table and html code. Then close the
' file entirely.
t.writeline ("</table></html>")
t.close
End If
End Function
Thought I would contribute something that may be handy for the rest of you!
Rob