BerryStats for NonAdmins
 

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

very nice. thats an awesome contribution!

Cool stuff Bjgrim....I must try this.....

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?

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

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. :-)

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.

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.

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.

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.

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

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.

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

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 (y) . 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. :razz:

In the meantime, lets keep the thread going. I like where this is going.

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.