SQL Script to grab user / handheld data
11-30-2008, 12:25 PM
|
 |
Proprietor
|
|
Join Date: Nov 2008
Posts: 2,238
|
|
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
|
12-01-2008, 05:31 PM
|
 |
Super Moderator
|
|
Join Date: Dec 2008
Location: West Michigan
Posts: 763
|
|
Boy, does THAT look familiar 
__________________
You may know me as Juwaack68
|
01-31-2009, 10:20 AM
|
|
BES Administrator
|
|
Join Date: Jan 2009
Location: Wilmington, DE
Posts: 14
|
|
is it possible to execute this script from a command line? if so how?
Can it be modified to also include SMTP email address?
|
01-31-2009, 02:30 PM
|
|
Super Moderator
|
|
Join Date: Dec 2008
Location: Germany
Posts: 597
|
|
You should be able to run it from the command line using the OSQL tool.
|
02-01-2009, 09:02 PM
|
|
Proprietor
|
|
Join Date: Nov 2008
Posts: 2,238
|
|
Quote:
Originally Posted by Mark_Venture
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/
The views expressed by me on Port3101 and its affiliated sites are my own and do not necessarily reflect the views of my employer.
|
02-02-2009, 02:48 AM
|
|
BES Administrator
|
|
Join Date: Jan 2009
Location: Wilmington, DE
Posts: 14
|
|
Thank you!!!
|
02-03-2009, 09:51 AM
|
 |
BES Activated
|
|
Join Date: Feb 2009
Posts: 2
|
|
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
|
08-22-2009, 08:59 PM
|
|
BES Activated
|
|
Join Date: Jul 2009
Location: US
Posts: 4
|
|
I thought I can use the BBM to get this information export asset summary data and then just discard the data I dont need.
|
08-31-2009, 10:40 AM
|
 |
BES Expert
|
|
Join Date: Jan 2009
Location: Alabama
Posts: 82
|
|
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!!
|
09-02-2009, 09:31 PM
|
|
Proprietor
|
|
Join Date: Nov 2008
Posts: 2,238
|
|
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/
The views expressed by me on Port3101 and its affiliated sites are my own and do not necessarily reflect the views of my employer.
|
10-26-2009, 12:49 PM
|
|
BES Activated
|
|
Join Date: Oct 2009
Location: CAD
Posts: 1
|
|
How about user list with assigned access controll pull rules?
|
02-19-2010, 09:47 AM
|
|
BES Administrator
|
|
Join Date: Jan 2009
Location: Wilmington, DE
Posts: 14
|
|
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?
|
02-19-2010, 11:20 AM
|
|
Proprietor
|
|
Join Date: Nov 2008
Posts: 2,238
|
|
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/
The views expressed by me on Port3101 and its affiliated sites are my own and do not necessarily reflect the views of my employer.
|
03-04-2010, 08:57 AM
|
 |
BES Administrator
|
|
Join Date: Apr 2009
Location: Congested Islet of Foreign Talents (42% of population) - Singapore.
Posts: 24
|
|
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
|
03-09-2010, 03:34 PM
|
|
BES Administrator
|
|
Join Date: Jan 2009
Location: Wilmington, DE
Posts: 14
|
|
Quote:
Originally Posted by hdawg
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.
|
10-04-2010, 02:47 PM
|
|
BES Administrator
|
|
Join Date: Jan 2009
Location: Wilmington, DE
Posts: 14
|
|
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?
|
 |
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
