BlackBerry Forums Support Community
              

Closed Thread
 
Thread Tools
Old 06-09-2005, 10:48 PM   #1
Grimlin
CrackBerry Addict
 
Grimlin's Avatar
 
Join Date: Jun 2005
Location: Signal Mountain, TN
Model: K1
PIN: Positive Intrinsic Negative???
Carrier: T-Mobile
Posts: 728
Default BerryStats for NonAdmins

Please Login to Remove!

Links:
Original BerryStats - Created and orphaned by me.
PHP BerryStats - Created and maintained by blubyu
QC_Metal Edition - Created and maintained by QC_Metal.

Note:
For those of you planning on using the original version, don't. Unless you just need a read-only solution that isn't easy on the eyes, move along to the end of the thread or click the link above. The QC version is much better. It has everything in it that I didn't have time to add and some things I never thought of. ...and it is a hell of a lot prettier.


***********************************************
Below info left for historical integrity...or something...
***********************************************
I created a view and a couple of webpages that can be used to view berrystats out of the besmgmt database. It has helped out our company tremendously and I felt like I should share.

****EDIT****
I added a screen shot here: http://www.thegrims.com/berrystats/berrystats.jpg It was too large to attach to the post. Due to it's width, your browser will more than likely shrink it by default. You will need to expand it to be able to read it.
****EDIT****
Updated to contain User Device Status
***EDIT 20050622***
I forgot to pay my registrar this year and the name I have had for 5 years is dead until DNS refreshes throughout the NET. Sorry if anybody has tried to get the zip and can't. It should be back up by tomorrow.

***EDIT 20050622B - It's up!!!

***EDIT 20050701 - Added PHP version provided by blubyu

Last edited by Grimlin; 08-12-2008 at 08:34 AM.. Reason: Update Links
Offline  
Old 06-10-2005, 03:46 AM   #2
jibi
BlackBerry God
 
jibi's Avatar
 
Join Date: Oct 2004
Location: Jibi's Secret Place
Model: 8900
OS: 4.6.1.174
Carrier: AT&T
Posts: 11,310
Default

very nice. thats an awesome contribution!
__________________
In the beginning the Universe was created. This has made a lot of people very angry and is widely regarded as a bad move.

Last edited by jibi; 06-10-2005 at 03:59 AM..
Offline  
Old 06-10-2005, 08:14 AM   #3
dev
CrackBerry Addict
 
dev's Avatar
 
Join Date: Apr 2005
Location: Toronto
Model: 8800
Carrier: Rogers
Posts: 571
Default

Cool stuff Bjgrim....I must try this.....
Offline  
Old 06-10-2005, 09:13 AM   #4
bfrye
BBF Veteran User
 
Join Date: Aug 2004
Location: Hotwiring another Cessna
Model: OU812
Carrier: Nintendo
Posts: 3,492
Default

This is a little tricky going if your web server is not on the same domain as the SQL server... still working on it.
Offline  
Old 06-10-2005, 09:17 AM   #5
Grimlin
CrackBerry Addict
 
Grimlin's Avatar
 
Join Date: Jun 2005
Location: Signal Mountain, TN
Model: K1
PIN: Positive Intrinsic Negative???
Carrier: T-Mobile
Posts: 728
Default

Quote:
Originally Posted by bfrye
This is a little tricky going if your web server is not on the same domain as the SQL server... still working on it.
As long as you change over to SQL auth and use that for the DB connection, there should be no problem. As long as your users have accounts in the same domain as the web server, that auth piece should work. Or you could Anon that piece if you didn't care who saw the info. I assume the view worked OK for you though?
Offline  
Old 06-10-2005, 10:10 AM   #6
bfrye
BBF Veteran User
 
Join Date: Aug 2004
Location: Hotwiring another Cessna
Model: OU812
Carrier: Nintendo
Posts: 3,492
Default

nah, neither worked. Our sql server is in the domain, but IIS server is in the DMZ and a standalone. Working on getting it to work. I might just stand up a simple web server for it to try it out on my box.
Offline  
Old 06-10-2005, 11:15 AM   #7
Grimlin
CrackBerry Addict
 
Grimlin's Avatar
 
Join Date: Jun 2005
Location: Signal Mountain, TN
Model: K1
PIN: Positive Intrinsic Negative???
Carrier: T-Mobile
Posts: 728
Unhappy

Quote:
Originally Posted by bfrye
nah, neither worked. Our sql server is in the domain, but IIS server is in the DMZ and a standalone. Working on getting it to work. I might just stand up a simple web server for it to try it out on my box.
Oh.... DMZ... Yeah, you would have to open port 1433 for the SQL connection. Not sure you would want to though from a security perspective. If your webserver were ever compromised, the SQL server would be vulnerable. I would put up a small test instance of IIS on your box and play around with it first.

Correction: 1433 TCP and 1434 UDP... my bad...
__________________
KEYone
Classic,Q10,Z10,9900,9670,9810,9800,9700,
8220,9000,8820,8800,7270,8700c,7290,7230,
7510,6510,5810,957,857,950

Last edited by Grimlin; 06-10-2005 at 11:22 AM..
Offline  
Old 06-13-2005, 01:00 PM   #8
jibi
BlackBerry God
 
jibi's Avatar
 
Join Date: Oct 2004
Location: Jibi's Secret Place
Model: 8900
OS: 4.6.1.174
Carrier: AT&T
Posts: 11,310
Default

Bjgrim, I just honestly want to express gratitude to you. This is quite a nice application to have. I'm adding on to the ASP script to "make it pretty" for those who will be viewing it, but your work has proven invaluable, as I'm not really all that familiar with DBA work.
__________________
In the beginning the Universe was created. This has made a lot of people very angry and is widely regarded as a bad move.
Offline  
Old 06-13-2005, 01:16 PM   #9
udontknowjack
Talking BlackBerry Encyclopedia
 
udontknowjack's Avatar
 
Join Date: Aug 2004
Location: State of Confusion
Model: 9000
Carrier: T-Mobile
Posts: 483
Default

Bjgrim, I also want to say thanks for making this available. This is very nice to have to give to my helpdesk and upper management without giving them access to the BES.
Offline  
Old 06-14-2005, 03:41 PM   #10
Grimlin
CrackBerry Addict
 
Grimlin's Avatar
 
Join Date: Jun 2005
Location: Signal Mountain, TN
Model: K1
PIN: Positive Intrinsic Negative???
Carrier: T-Mobile
Posts: 728
Default

Quote:
Originally Posted by jibi
Bjgrim, I just honestly want to express gratitude to you. This is quite a nice application to have. I'm adding on to the ASP script to "make it pretty" for those who will be viewing it, but your work has proven invaluable, as I'm not really all that familiar with DBA work.
Sorry, but pretty is something I have never been good at. I'm always about function. I find it amusing that you mention it as that is one of the things I get hounded over constantly. Please share when you have added a stylesheet or whatever you are doing with it.

If you have any questions about the reasons for the fields in the SQL, let me know. For instance.... The site field is actually a substring of the Exchange server name. Our server names are in the format XXXYYYZZZ#.

XXX=Company
YYY=3 letter Site code
ZZZ=Function (XCH for exchange, etc.)
# = incremented starting at 1.

I pull out the 3 letter site code to allow for filtering by site instead of just by exchange server. That whole column will need to be rewritten or removed from the view for most people.
__________________
KEYone
Classic,Q10,Z10,9900,9670,9810,9800,9700,
8220,9000,8820,8800,7270,8700c,7290,7230,
7510,6510,5810,957,857,950

Last edited by Grimlin; 06-14-2005 at 03:43 PM..
Offline  
Old 06-14-2005, 03:49 PM   #11
jibi
BlackBerry God
 
jibi's Avatar
 
Join Date: Oct 2004
Location: Jibi's Secret Place
Model: 8900
OS: 4.6.1.174
Carrier: AT&T
Posts: 11,310
Default

Yeah, I removed it. Here's what I'm using:

Code:
CREATE VIEW dbo.BerryStats
AS
SELECT dbo.UserConfig.DisplayName, dbo.UserConfig.PIN,dbo.SyncDeviceMgmtSummary.ModelName, dbo.SyncDeviceMgmtSummary.AppsVer, dbo.SyncDeviceMgmtSummary.HomeNetwork AS LastNetwork, dbo.UserStats.MsgsPending AS Pending,dbo.UserStats.MsgsForwarded AS Rcvd, dbo.UserStats.MsgsSent AS Sent, dbo.UserStats.MsgsExpired AS Expired,dbo.UserStats.MsgsFiltered AS Filtered, RTRIM(CONVERT(nchar(10),DATEDIFF(hh, dbo.UserStats.LastFwdTime, GETUTCDATE()) / 24)) + ' Days' + ' ' + RTRIM(CONVERT(nchar(10),DATEDIFF(mi, dbo.UserStats.LastFwdTime, GETUTCDATE()) / 60 - DATEDIFF(hh, dbo.UserStats.LastFwdTime, GETUTCDATE()) / 24 * 24 )) + ' hrs' AS Inactive,DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), dbo.UserStats.LastFwdTime) AS LastRcvd,DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), dbo.UserStats.LastSentTime) AS LastSent, dbo.UserConfig.MailboxSMTPAddr AS SMTP, dbo.SyncDeviceMgmtSummary.PhoneNumber, dbo.SyncDeviceMgmtSummary.DCID, dbo.SyncDeviceMgmtSummary.IMEI,dbo.SyncDeviceMgmtSummary.ITPolicyName
FROM dbo.UserStats RIGHT OUTER JOIN
dbo.UserConfig ON dbo.UserStats.UserConfigId = dbo.UserConfig.Id LEFT OUTER JOIN
dbo.SyncDeviceMgmtSummary ON dbo.UserConfig.Id = dbo.SyncDeviceMgmtSummary.UserConfigId
__________________
In the beginning the Universe was created. This has made a lot of people very angry and is widely regarded as a bad move.
Offline  
Old 06-14-2005, 03:58 PM   #12
Grimlin
CrackBerry Addict
 
Grimlin's Avatar
 
Join Date: Jun 2005
Location: Signal Mountain, TN
Model: K1
PIN: Positive Intrinsic Negative???
Carrier: T-Mobile
Posts: 728
Default

Quote:
Originally Posted by jibi
Yeah, I removed it. Here's what I'm using:....
I forgot to mention. Since I am compiling Last contact from the last received time and it is a text field it doesn't correctly. Just sort by last received to put the last contact in proper order. If anyone figures out some simple SQL to preface a leading zero for single digit numbers in this field, we could make it sort correctly.
__________________
KEYone
Classic,Q10,Z10,9900,9670,9810,9800,9700,
8220,9000,8820,8800,7270,8700c,7290,7230,
7510,6510,5810,957,857,950
Offline  
Old 06-14-2005, 08:07 PM   #13
jibi
BlackBerry God
 
jibi's Avatar
 
Join Date: Oct 2004
Location: Jibi's Secret Place
Model: 8900
OS: 4.6.1.174
Carrier: AT&T
Posts: 11,310
Default

Bjgrim, RIM bases LastContactTime off LastFwdTime and LastSentTime.

I'm guessing its something along the lines of getting the return from each of the above columns and comparing to see which one is greater, then using it for the base of comparison against the current time on the server and taking the difference and displaying as the LastContactTime.

Now, how to write that into a SQL statement is beyond me.

This leaves a little room for improvement of the actual LastContactTime return for your code, though. I will talk to our SQL DBAs tomorrow when I get some free time and see if I can get them to look at it.
__________________
In the beginning the Universe was created. This has made a lot of people very angry and is widely regarded as a bad move.

Last edited by jibi; 06-14-2005 at 08:19 PM..
Offline  
Old 06-14-2005, 09:18 PM   #14
jibi
BlackBerry God
 
jibi's Avatar
 
Join Date: Oct 2004
Location: Jibi's Secret Place
Model: 8900
OS: 4.6.1.174
Carrier: AT&T
Posts: 11,310
Default

Question right quick. How does the UserStats table get populated with data? I guess that could help me understand that part about SQL databases, then it'd open a whole new world to my eyes (well, maybe not that much, but something a little less impacting). I'm guessing application calls?
__________________
In the beginning the Universe was created. This has made a lot of people very angry and is widely regarded as a bad move.

Last edited by jibi; 06-14-2005 at 09:22 PM..
Offline  
Old 06-15-2005, 09:16 AM   #15
Grimlin
CrackBerry Addict
 
Grimlin's Avatar
 
Join Date: Jun 2005
Location: Signal Mountain, TN
Model: K1
PIN: Positive Intrinsic Negative???
Carrier: T-Mobile
Posts: 728
Default

Quote:
Originally Posted by jibi
Bjgrim, RIM bases LastContactTime off LastFwdTime and LastSentTime.
I couldn't find a simple SQL way to do the compare within the view without the need for a seprate table or a subselect. So I just based it off of lastFwdTime. Most of my users receive more than send so it is almost always based off of that. Plus, we use that field for tracking large spans of inactivity.

All that said...Let me know when you find it, I would love to add it
__________________
KEYone
Classic,Q10,Z10,9900,9670,9810,9800,9700,
8220,9000,8820,8800,7270,8700c,7290,7230,
7510,6510,5810,957,857,950
Offline  
Old 06-15-2005, 09:23 AM   #16
Grimlin
CrackBerry Addict
 
Grimlin's Avatar
 
Join Date: Jun 2005
Location: Signal Mountain, TN
Model: K1
PIN: Positive Intrinsic Negative???
Carrier: T-Mobile
Posts: 728
Default

Quote:
Originally Posted by jibi
Question right quick. How does the UserStats table get populated with data? I guess that could help me understand that part about SQL databases, then it'd open a whole new world to my eyes (well, maybe not that much, but something a little less impacting). I'm guessing application calls?
I am not sure which part of the BES server handles that. I would assume the dispatcher or the controller, but that is merely a guess. I really know very little about the internal workings of it all. I just poked around the tables until I found the data I was looking for and put it all together. I am still looking for a translation matrix for the current status. It appears to just be a number in the tables. They must have the values hardcoded in the app somewhere instead of in the database.
__________________
KEYone
Classic,Q10,Z10,9900,9670,9810,9800,9700,
8220,9000,8820,8800,7270,8700c,7290,7230,
7510,6510,5810,957,857,950
Offline  
Old 06-15-2005, 05:31 PM   #17
jibi
BlackBerry God
 
jibi's Avatar
 
Join Date: Oct 2004
Location: Jibi's Secret Place
Model: 8900
OS: 4.6.1.174
Carrier: AT&T
Posts: 11,310
Default

well, we can compile what we do know...

0 = Initializing
7 = No PIN
9 = Redirection disabled
10 = In cradle - message redirection to handheld disabled
12 = Running
__________________
In the beginning the Universe was created. This has made a lot of people very angry and is widely regarded as a bad move.
Offline  
Old 06-16-2005, 12:59 PM   #18
Grimlin
CrackBerry Addict
 
Grimlin's Avatar
 
Join Date: Jun 2005
Location: Signal Mountain, TN
Model: K1
PIN: Positive Intrinsic Negative???
Carrier: T-Mobile
Posts: 728
Default

Quote:
Originally Posted by jibi
well, we can compile what we do know...
Correct... I had a similar list that I started. However, without it being in the tables or building it into the ASP, we would have to hand out decoder rings to our stats users . We could create a new table in the database to pull from, but I do not know what RIM would think of that from a support and maintenance perspective. They probably wouldn't be too thrilled about the fact that we added a view to begin with, let alone a new table. At least the view though should be considered innocuous enough that they wouldn't grumble too much. When I get some more time, I am going to go through the table structure again to see if anything pops out. They might be in there in Hex (ascii or unicode) or binary. I just can't believe that they would hardcode the messages into the app. That just seems silly.

In the meantime, lets keep the thread going. I like where this is going.
__________________
KEYone
Classic,Q10,Z10,9900,9670,9810,9800,9700,
8220,9000,8820,8800,7270,8700c,7290,7230,
7510,6510,5810,957,857,950
Offline  
Old 06-16-2005, 01:09 PM   #19
bfrye
BBF Veteran User
 
Join Date: Aug 2004
Location: Hotwiring another Cessna
Model: OU812
Carrier: Nintendo
Posts: 3,492
Default

Bjgrim. You should actually attach a few screenshots to the original post so users quickly browsing through the thread might hang out a little longer and try it if they see it off the bat.
Offline  
Old 06-16-2005, 02:34 PM   #20
Grimlin
CrackBerry Addict
 
Grimlin's Avatar
 
Join Date: Jun 2005
Location: Signal Mountain, TN
Model: K1
PIN: Positive Intrinsic Negative???
Carrier: T-Mobile
Posts: 728
Default

Quote:
Originally Posted by bfrye
Bjgrim. You should actually attach a few screenshots to the original post so users quickly browsing through the thread might hang out a little longer and try it if they see it off the bat.
I could, but I would have to Kill all of you. Propietary information and all I will take a screen shot and black out the names and pins and such... Thanks for the suggestion.
__________________
KEYone
Classic,Q10,Z10,9900,9670,9810,9800,9700,
8220,9000,8820,8800,7270,8700c,7290,7230,
7510,6510,5810,957,857,950
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


BLACKBERRY MEAD Advertising Vinyl Banner Flag Sign Many Sizes MEDIEVAL picture

BLACKBERRY MEAD Advertising Vinyl Banner Flag Sign Many Sizes MEDIEVAL

$174.84



BLACKBERRY MEAD Advertising Vinyl Banner Flag Sign Many Sizes MEDIEVAL picture

BLACKBERRY MEAD Advertising Vinyl Banner Flag Sign Many Sizes MEDIEVAL

$118.19



FARM FRESH BLACKBERRIES CLEARANCE BANNER Advertising Vinyl Flag Sign INV picture

FARM FRESH BLACKBERRIES CLEARANCE BANNER Advertising Vinyl Flag Sign INV

$174.84



Blackberry Advertising Banner Vinyl Mesh Sign Fruit Vegetable Berry Farm Fresh picture

Blackberry Advertising Banner Vinyl Mesh Sign Fruit Vegetable Berry Farm Fresh

$219.95



GM WA549F, Blackberry Pearlcoat picture

GM WA549F, Blackberry Pearlcoat

$285.95



BLACKBERRY MEAD Advertising Vinyl Banner Flag Sign Many Sizes MEDIEVAL picture

BLACKBERRY MEAD Advertising Vinyl Banner Flag Sign Many Sizes MEDIEVAL

$99.19







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