Welcome to Port3101.org : Your BES Connection Mark forums read | View Forum Leaders
Port3101.org : Your BES Connection



Reply
LinkBack (6) Thread Tools Display Modes
SQL Script to grab user / handheld data
 
  6 links from elsewhere to this Post. Click to view. #1 (permalink)  
Old 11-30-2008, 01:25 PM
hdawg's Avatar
Proprietor
 
Join Date: Nov 2008
Posts: 2,257
Blog Entries: 147
Default SQL Script to grab user / handheld data

Here 's a SQL script that can be run against your BESMgmt database (4.1 or above) to grab the following data:

Name, Group Membership, BlackBerry handheld model, Phone number, PIN, and home carrier

WARNING: This script is a SELECT statement. It will only be reading from your database. Don't go changing any data using other SQL statements as you could irrevocably destroy your BESMgmt database.

Code:
select 
 a.DisplayName,
 d.GroupName,
 c.ModelName,
 c.PhoneNumber,
 c.PIN,
 c.HomeNetwork,
 convert(varchar(10),a.CreationTime, 101)CreationDate,
 convert(varchar(10),a.ActivationTime, 101)ActivationDate, 
 b.MsgsForwarded,
 b.MsgsSent,
 convert(varchar(10),b.LastFwdTime, 101)LastFwdDate,
 convert(varchar(10),b.LastSentTime, 101)LastSentDate
 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
Reply With Quote
Sponsored Links
  #2 (permalink)  
Old 12-01-2008, 06:31 PM
Joolie's Avatar
Super Moderator
 
Join Date: Dec 2008
Location: West Michigan
Posts: 763
Default

Boy, does THAT look familiar
__________________
You may know me as Juwaack68
Reply With Quote
  #3 (permalink)  
Old 01-31-2009, 11:20 AM
BES Administrator
 
Join Date: Jan 2009
Location: Wilmington, DE
Posts: 14
Default

is it possible to execute this script from a command line? if so how?

Can it be modified to also include SMTP email address?
Reply With Quote
  #4 (permalink)  
Old 01-31-2009, 03:30 PM
Abe Abe is offline
Super Moderator
 
Join Date: Dec 2008
Location: Germany
Posts: 598
Default

You should be able to run it from the command line using the OSQL tool.
__________________

Reply With Quote
  #5 (permalink)  
Old 02-01-2009, 10:02 PM
hdawg's Avatar
Proprietor
 
Join Date: Nov 2008
Posts: 2,257
Blog Entries: 147
Default

Quote:
Originally Posted by Mark_Venture View Post
is it possible to execute this script from a command line? if so how?
Yes, absolutely. As stated by Abe you can use osql, or any other SQL command line interpreter. Login to your SQL server, go to a command prompt and type: osql /? to see all the syntax or check out Running the osql Utility, but for this, you can save the sql script as userhandhelddata.sql and execute (locally on the sql server):

osql -E -i userhandhelddata.sql -o userhandhelddata-rpt.txt

Quote:
Can it be modified to also include SMTP email address?
Yes, check below; in bold is what is changed from the original post

Code:
select 
 a.DisplayName,
 a.MailboxSMTPAddr,
 d.GroupName,
 c.ModelName,
 c.PhoneNumber,
 c.PIN,
 c.HomeNetwork,
 convert(varchar(10),a.CreationTime, 101)CreationDate,
 convert(varchar(10),a.ActivationTime, 101)ActivationDate, 
 b.MsgsForwarded,
 b.MsgsSent,
 convert(varchar(10),b.LastFwdTime, 101)LastFwdDate,
 convert(varchar(10),b.LastSentTime, 101)LastSentDate
 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
__________________
http://blog.port3101.org/hdawg/
Reply With Quote
  #6 (permalink)  
Old 02-02-2009, 03:48 AM
BES Administrator
 
Join Date: Jan 2009
Location: Wilmington, DE
Posts: 14
Default

Thank you!!!
Reply With Quote
  #7 (permalink)  
Old 02-03-2009, 10:51 AM
l0ki's Avatar
BES Activated
 
Join Date: Feb 2009
Posts: 2
Default

Nice one :)

RIM sent us following SQL query to fetch

- Name
- PIN
- EmailAddress
- Domain
- MailServer
- BlackberryDeviceModel
- BlackberryVersion
- IT Policy Name

from BESMgmt database (BES 4.1, SQL 2005):

Code:
SELECT userConfig.displayname, userConfig.PIN, userConfig.MailboxSMTPAddr, userConfig.ServerDN, ITPolicy2.PolicyName, syncdevicemgmtsummary.modelname, syncdevicemgmtsummary.appsVer 
FROM userConfig, ITPolicy2, syncdevicemgmtsummary 
WHERE userconfig.ITPolicy2ID=ITPolicy2.id AND userconfig.id=syncdevicemgmtsummary.userconfigid
Reply With Quote
  #8 (permalink)  
Old 08-22-2009, 08:59 PM
BES Activated
 
Join Date: Jul 2009
Location: US
Posts: 4
Default

I thought I can use the BBM to get this information export asset summary data and then just discard the data I dont need.
Reply With Quote
  #9 (permalink)  
Old 08-31-2009, 10:40 AM
AUTiger92's Avatar
BES Expert
 
Join Date: Jan 2009
Location: Alabama
Posts: 82
Default

Ok my SQL is terrible. Let's say I also wanted to gather Last Contact Time and OS Version.
__________________
AUTIGER92
Exchange\Blackberry Admin
4 - BES Servers (4.1.6), 3 Exchange Organizations,
~1800 BB Users, and a headache.
War Eagle!!
Reply With Quote
  #10 (permalink)  
Old 09-02-2009, 09:31 PM
hdawg's Avatar
Proprietor
 
Join Date: Nov 2008
Posts: 2,257
Blog Entries: 147
Default

Sorry for not responding sooner ... bold is what I added.

Code:
select 
 a.DisplayName,
 a.MailboxSMTPAddr,
 d.GroupName,
 c.ModelName,
 c.AppsVer,
 c.PhoneNumber,
 c.PIN,
 c.HomeNetwork,
 convert(varchar(10),a.CreationTime, 101)CreationDate,
 convert(varchar(10),a.ActivationTime, 101)ActivationDate, 
 b.MsgsForwarded,
 b.MsgsSent,
 convert(varchar(10),b.LastFwdTime, 101)LastFwdDate,
 convert(varchar(10),b.LastSentTime, 101)LastSentDate,
 convert(varchar(10),b.LastContactTime, 101)LastContactTime
 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
__________________
http://blog.port3101.org/hdawg/
Reply With Quote
  #11 (permalink)  
Old 10-26-2009, 12:49 PM
BES Activated
 
Join Date: Oct 2009
Location: CAD
Posts: 1
Default

How about user list with assigned access controll pull rules?
Reply With Quote
  #12 (permalink)  
Old 02-19-2010, 10:47 AM
BES Administrator
 
Join Date: Jan 2009
Location: Wilmington, DE
Posts: 14
Default

Question... When I export the data using OSQL.EXE to an output text file, the data fields contains extra characters (usually blank spaces), which I'd like to somehow clean out to make the data more usable.

And along those lines, the userConfig.ServerDN field, I'd like to trim it down to contain just the server name, not all the "extra" stuff.

Any suggestions?
Reply With Quote
  #13 (permalink)  
Old 02-19-2010, 12:20 PM
hdawg's Avatar
Proprietor
 
Join Date: Nov 2008
Posts: 2,257
Blog Entries: 147
Default

I'd take the data you've exported and open it up with excel to clean it up like you want. Instead of changing the script around you've already got the data, so use Excel to filter / format exactly how you want.
__________________
http://blog.port3101.org/hdawg/
Reply With Quote
  #14 (permalink)  
Old 03-04-2010, 09:57 AM
noname's Avatar
BES Administrator
 
Join Date: Apr 2009
Location: Congested Islet of Foreign Talents (42% of population) - Singapore.
Posts: 24
Default

Excellent! Thanks to all SQL queries gurus!
__________________
【noname】- Native but 3rd class citizen of a nation governed by idiots who import congestions and contention.
BlackBerry® Certified Solution Designer + System Administrator
IBM Certified Advanced Application Developer + Associate System Administrator - Lotus Notes and Domino
Reply With Quote
  #15 (permalink)  
Old 03-09-2010, 04:34 PM
BES Administrator
 
Join Date: Jan 2009
Location: Wilmington, DE
Posts: 14
Default

Quote:
Originally Posted by hdawg View Post
I'd take the data you've exported and open it up with excel to clean it up like you want. Instead of changing the script around you've already got the data, so use Excel to filter / format exactly how you want.
unfortunately, I'm getting errors when trying to open the output file it in excel.
Reply With Quote
  #16 (permalink)  
Old 10-04-2010, 02:47 PM
BES Administrator
 
Join Date: Jan 2009
Location: Wilmington, DE
Posts: 14
Default

Sorry to resurect an old thread...

But using these scripts, the last contact time is returning NULL (BES for Exchange v4.1 sp7.)

Is there any way to get that last contact time?
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

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
Trackbacks are On
Pingbacks are On
Refbacks are On


LinkBacks (?)
LinkBack to this Thread: http://www.port3101.org/scripts-tools-hints-tricks/31-sql-script-grab-user-handheld-data.html
Posted By For Type Date
SQL Script to grab user / handheld data - Port3101.org : Your BES Connection This thread Refback 02-01-2012 04:31 PM
SQL Script to grab user / handheld data - Port3101.org : Your BES Connection This thread Refback 12-22-2011 06:40 PM
brs's blackberry Bookmarks on Delicious This thread Refback 06-22-2011 09:53 PM
BES 5.0, list of all device PIN's ? - BlackBerryForums.com : Your Number One BlackBerry Community This thread Refback 05-02-2010 06:55 AM
styrofoamcup's Bookmarks on Delicious This thread Refback 10-08-2009 06:10 AM
BES 4.16 - Automated Device Export?? - BlackBerryForums.com : Your Number One BlackBerry Community This thread Pingback 09-08-2009 09:48 AM

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Script to grab device data related to service books perpetual Scripts & Tools / Hints & Tricks 0 03-24-2010 03:10 AM
KB14058 - Delete all data and applications from the BlackBerry using Wipe handheld hdawg Featured BlackBerry KB Articles 1 08-20-2009 04:13 PM
KB10970 - Log file entries to confirm the Erase Data and Disable Handheld command hdawg Featured BlackBerry KB Articles 0 08-17-2009 05:51 PM
KB04529 - Use the Erase Data and Disable Handheld command hdawg Featured BlackBerry KB Articles 1 07-20-2009 03:04 PM
Erase Data & Disable handheld jletendre Port 3101: The BES Admin Bar & Grill 3 02-23-2009 11:09 PM


All times are GMT -4. The time now is 03:37 PM.
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2017, vBulletin Solutions, Inc.


 

SEO by vBSEO 3.3.2 PL2