BlackBerry Forums Support Community               

Closed Thread
 
LinkBack Thread Tools
Old 11-09-2005, 04:45 PM   #1 (permalink)
CrackBerry Addict
 
qc_metal's Avatar
 
Join Date: Mar 2005
Location: Rockford, IL
Model: 9530
OS: 4.7.x
Carrier: Verizon
Posts: 590
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default Quick query for user on BES server

Please Login to Remove!

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.

Code:
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

Last edited by qc_metal : 11-14-2005 at 09:48 AM.
Offline  
Old 11-09-2005, 07:45 PM   #2 (permalink)
Talking BlackBerry Encyclopedia
 
Join Date: May 2005
Model: 7100
Carrier: T-Mobile
Posts: 299
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default

I take it you wrote this for BES 3.6.
Offline  
Old 11-10-2005, 05:44 PM   #3 (permalink)
CrackBerry Addict
 
qc_metal's Avatar
 
Join Date: Mar 2005
Location: Rockford, IL
Model: 9530
OS: 4.7.x
Carrier: Verizon
Posts: 590
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by BBTechGuy
I take it you wrote this for BES 3.6.
I did - so maybe someone with 4.0 could test it out, assuming that the DB structure is the same (only queries one table)...
Offline  
Old 11-10-2005, 05:59 PM   #4 (permalink)
Talking BlackBerry Encyclopedia
 
Join Date: May 2005
Model: 7100
Carrier: T-Mobile
Posts: 299
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default

That table will not work in 4.0 for users who were not migrated from 3.6.
Offline  
Old 11-14-2005, 09:47 AM   #5 (permalink)
CrackBerry Addict
 
qc_metal's Avatar
 
Join Date: Mar 2005
Location: Rockford, IL
Model: 9530
OS: 4.7.x
Carrier: Verizon
Posts: 590
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well, we will be migrating soon to 4.0, so I'll post an update at that time.

Thanks for your response BBTG...!

Rob
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.