BlackBerry Forums Support Community
              

Closed Thread
 
Thread Tools
Old 02-01-2010, 01:23 PM   #1
Madhart
New Member
 
Join Date: Feb 2010
Model: N/A
PIN: N/A
Carrier: N/A
Posts: 7
Default Export all Users to List

Please Login to Remove!

I'm an Exchange System Admin and have been asked to find out which users on our exchange servers are blackberry users. There is no indication within Active Directory or the Exchange servers to tell me this so I've had to Get a List of all users from our BES and cross reference them. I can manually get the users via the BlackBerry Manager v.4.1 (And Snap-In) Export to list. This works however this will have to be done everyday for reporting purposes.

Is there any sort of command? that I can use to initiate this same thing? Because Every GUI is command driven there has to be one. All I need are the users Display Names. I've read a little about using SQL but I have NO Experience with these queries and honestly have no idea to do them. I am experienced with VBScript as I've written some WMI and LDAP queries. Is there a way to preform this via VBScript?

I've read from the Blackbery.com techsupport
Export user list and statistics from a BlackBerry Enterprise Server
For BlackBerry Enterprise Server version 4.1
You can use one of the following three methods to export the BlackBerry smartphone user list.
Method 1 - Export the user list using BlackBerry Manager
1. Open BlackBerry Manager.
2. Click BlackBerry Domain, and then click the All Users tab.
3. In the top right pane, select all users and click the Service Control & Customization link.
4. Click the Export Stats To File link.
5. Select a location, type a name for the text file, and click Save.
6. When Clear user stats after export appears, click Yes if you want statistics reset in the future; otherwise,
click No.

Method 2 - Export the user list using a SQL query list
1. Open a command prompt.
2. Type the following commands in the specified order:
Code:
osql -E
1> use besmgmt
2> go
1> SELECT DisplayName, PIN FROM UserConfig
2> go
3. Copy the information that appears and paste it to a text file.

Method 3 - Export the BlackBerry smartphone user list using the BCP command on the computer hosting
the BlackBerry Configuration Database
1. Open a command prompt.
2. Type the following
Code:
bcp besmgmt..userconfig out c:\filename.txt -c -T
I'm curious if its just as simple as a command line request? I can create this in a bat file and make it a scheduled task. Are Methods 2 and 3 what I'm looking for to Export to List all the Users on the BES? I'm figure its wise to ask first before just typing commands through the command prompt.

Thank You to anyone who is able to help.
Attached Files
File Type: pdf Export user list and statis...pdf (20.6 KB, 36 views)
Offline  
Old 02-01-2010, 01:28 PM   #2
knottyrope
BlackBerry Elite
 
knottyrope's Avatar
 
Join Date: Jan 2008
Location: Massachusetts
Model: DT60
OS: 123456789
PIN: t of blood has been taken
Carrier: AT&T-US with I dee ten tee errors
Posts: 7,325
Default

some good script info here and I like method 2

SQL Script to grab user / handheld data - Port3101.org : Your BES Connection
__________________
I had to fall
To lose it all
But in the end
It doesn't even matter

Rocking the Motion with out lotion.
Offline  
Old 02-01-2010, 01:39 PM   #3
Madhart
New Member
 
Join Date: Feb 2010
Model: N/A
PIN: N/A
Carrier: N/A
Posts: 7
Default

So in a txt file renamed to .sql put one of those sql scripts and call it via command line ON the sql server hosting the database?

Not to be lazy here I just don't want to break anything. Not sure I fully understand what is going on with the SQL or the process to run it.

How would I need to edit the Below to get just Display Names(Each one on a seperate line)?
Code:
Select 
 a.DisplayName,
From (userconfig a left join groupconfig d on a.GroupConfigId = d.Id), userstats b, vHandheldSummaryInfo c
Where a.id = b.userconfigid and a.DisplayName = c.displayname
 Order by a.DisplayName
If I navigate the command line prompt to the folder housing the .sql file (DisplayNamesQuery.sql) would I just type in something like

osql -E -i DisplayNamesQuery.sql -o OutputFile.txt

Will it output the file with all the user Display Names to the same folder but named OutputFile.txt?

Is the osql command pretty common? I'm not sure what is set up or installed on this server.

Thanks Knottyrope

Last edited by Madhart; 02-01-2010 at 02:01 PM..
Offline  
Old 02-01-2010, 02:04 PM   #4
nobody7290
BlackBerry Extraordinaire
 
Join Date: Mar 2006
Model: 9700
Carrier: t-mobile Germany
Posts: 1,381
Default

install the BRK useradminclient.
then one line:
BESUserAdminClient.exe -username admin -password secret -stats -users -b your-server -o text.csv

does this give you the information you need ?
Offline  
Old 02-01-2010, 02:23 PM   #5
Madhart
New Member
 
Join Date: Feb 2010
Model: N/A
PIN: N/A
Carrier: N/A
Posts: 7
Default

Thanks Nobody,

At this time I do not know if the BRK useradminclient is installed or not.
This is for a government/military server. I am actually at home now as most websites are blocked at work. I cannot install any software without going through a long process. I'd like to try to avoid the need to install stuff if I can help it. However when I go back in tonight I can certainly take a look. Thanks. Also as far as the code above? do I need to edit any of it or is it actually good?
Offline  
Old 02-01-2010, 02:37 PM   #6
nobody7290
BlackBerry Extraordinaire
 
Join Date: Mar 2006
Model: 9700
Carrier: t-mobile Germany
Posts: 1,381
Default

The BESUseradminclient is a smal install, can be installed on ther server, or, on a remote windows workstation. Its a download 9MB from RIM. You need a matching version to your servers software i think.

The line is correct, nothing else to do - your have to give your real password and the dns name of the server of course.
Output is a csv file with some stats and the usernames.
Offline  
Old 02-01-2010, 06:42 PM   #7
Madhart
New Member
 
Join Date: Feb 2010
Model: N/A
PIN: N/A
Carrier: N/A
Posts: 7
Default

Where do I insert a password and DNS in the whole thing? Thanks again. By password do you mean my admin password or some sort of BES password?
Offline  
Old 02-01-2010, 07:17 PM   #8
nobody7290
BlackBerry Extraordinaire
 
Join Date: Mar 2006
Model: 9700
Carrier: t-mobile Germany
Posts: 1,381
Default

uhm.....
does not look so simple if you have Blackberry 4.1
Sorry, I did not read your post thoroughly enough.

You have to install the BESUSeradmin first, also, the BESUserAdminClient should not run on the same computer as the Blackberry server.

I currently have a BES V5.0, I will try this out on a version 4.1, but not today.

Maybe , have a look at the documentation:
http://docs.blackberry.com/en/admin/...user_guide.pdf
Offline  
Old 02-02-2010, 03:35 AM   #9
DavidAdams
Talking BlackBerry Encyclopedia
 
DavidAdams's Avatar
 
Join Date: Sep 2007
Location: Belfast
Model: NotYe
PIN: N/A
Carrier: O2
Posts: 470
Default

Personally I use QueryExpress as this enables me to query the SQL sever directly from my PC. Its a single exe file that doesn't need to be installed.

The SQL I use is:

Code:
select
Right(DisplayName, Len(DisplayName) - CharIndex(',',DisplayName)-1) + ' ' + Left(DisplayName, CharIndex(',',DisplayName)-1) AS UserName,
vUserConfigStats.Id,
PIN,
PhoneNumber,
MsgsPending,
LastContactTime,
HomeNetwork,
IMEI,
AppsVer,
ModelName,
ITPolicyName,
PasswordEnabled,
MsgsForwarded,
MsgsSent,
MsgsExpired,
MsgsFiltered,
MsgsFailed,
LastFwdTime,
LastSentTime,
ServerDN,
vUserConfigStats.CreationTime,
GroupName
FROM vUserConfigStats JOIN GroupConfig
ON vUserConfigStats.GroupConfigID = GroupConfig.Id
ORDER BY LastContactTime
Shows me all I need and more some days.
__________________
BES, 4.1.7, was SBE now full BES
Domino v7.0.2
Windows Server 2003, standalone
Offline  
Old 02-02-2010, 01:00 PM   #10
Madhart
New Member
 
Join Date: Feb 2010
Model: N/A
PIN: N/A
Carrier: N/A
Posts: 7
Default

DavidAdams - Thanks for the QueryExpress recommendation. That looks very promising I'll have to definitely look into that further and the fact that it doesn't need to be installed is a huge plus.

Here's what I've been able to do to satisfy my needs so far. If anyone has any suggestions or know of ways to improve please post em.
All this is done on the SQL Server itself on the Root C:\ Drive:

In a File with extention .sql
Code:
SELECT [DisplayName]
FROM [BESMgmt].[X].[UserConfig]
In a File with extention .cmd
Code:
C:
osql -E -S SERVERNAME -i FileName.sql -o OutputFileName.csv
Copy C:\FilePath\OutputFileName.csv C:/DestinationPath
YES
I have taken out and changed the sensitive Info which is why it kinda looks generic but it gives you the idea.

I've then set the .cmd file up as a scheduled task in the Task Manager. It runs fine and puts the csv into the network folder that I need.

Only Problem which isn't really a problem but on the output there is tons of extra spacing and empty/blank lines between each entry. This more an inconvenience than anything else cause I just use a vbscript to copy the into and I can just ignore the extra lines and that. Thanks for all the help and suggestions.

Again if anyone has a good suggestion on how to call or get this query from a remote computer/server please let me know as RDP'ing into the SQL server or running it from the SQL server is not ideal but as a last resort...it works. I'm curious what sort of credentials would need to be passed or if it would be able to use the Windows Authentication as we can't store passwords in scripts.
Offline  
Old 02-02-2010, 01:57 PM   #11
TargetIT
CrackBerry Addict
 
Join Date: Jan 2008
Model: 9700
PIN: N/A
Carrier: Rogers
Posts: 709
Default

osql isn't great for outputing - if you use bcp, you'll find the format much easier to deal with. With the -T flag, you can use windows credentials (assuming you've set the correct permissions in SQL Manager).

Here's one I use to capture out of network BBs (I created a view in SQL Manager):

Code:
bcp "BESMgmt.dbo.OutOfNetworkBBsWithNulls" out "%~dp0BBOutput.txt" -c -t, -S "ServerName\BESMgmt" -T

Last edited by TargetIT; 02-02-2010 at 02:02 PM..
Offline  
Old 02-02-2010, 02:09 PM   #12
Madhart
New Member
 
Join Date: Feb 2010
Model: N/A
PIN: N/A
Carrier: N/A
Posts: 7
Default

I'll give that a try tonight. Thanks
Offline  
Old 02-03-2010, 10:42 PM   #13
Madhart
New Member
 
Join Date: Feb 2010
Model: N/A
PIN: N/A
Carrier: N/A
Posts: 7
Default

The Following Command Works Perfect:

Code:
bcp "SELECT DisplayName FROM BESMgmt.X.UserConfig" queryout
\\$$$.$$$.$$$.$$$\Remote Folder Path\BBExport.csv -c -t -S
ServerName\DB -T
The BCP command works a lot better then the OSQL command and the output format is so much better. Tonight I'm going to try to Execute it Via WMI from a Remote Computer VBscript. Thanks for all your help.
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

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


Dec/Adac Corp. D4-10390 Rev 6 Model 1664ATTL Output Pulse Board 1983 (B16) picture

Dec/Adac Corp. D4-10390 Rev 6 Model 1664ATTL Output Pulse Board 1983 (B16)

$189.99



Niedermaier vintage Mannequins Visual Display Props Dec. 1991 Catalogue picture

Niedermaier vintage Mannequins Visual Display Props Dec. 1991 Catalogue

$141.00



VINTAGE Bruker Board DEC W4P3042 for SpectroSpin 250 picture

VINTAGE Bruker Board DEC W4P3042 for SpectroSpin 250

$99.99



VINTAGE WESTERN ELECTRIC METER ORDNANCE A322525 WWII ERA DECEMBER 1943 picture

VINTAGE WESTERN ELECTRIC METER ORDNANCE A322525 WWII ERA DECEMBER 1943

$30.00



Adec Vintage Adjusting Dentist Doctor Medical Stool Rolling Swivel Chair picture

Adec Vintage Adjusting Dentist Doctor Medical Stool Rolling Swivel Chair

$199.99



HSS 12pcs Valve Seat & Face Cutter 45dec 30dec 20dec for Vintage Car, Bikes ,Jee picture

HSS 12pcs Valve Seat & Face Cutter 45dec 30dec 20dec for Vintage Car, Bikes ,Jee

$235.94







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