BerryStats for NonAdmins - BlackBerryForums.com : Your Number One BlackBerry Community

Grimlin · 09:34 AM

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

jibi · 04:59 AM

very nice. thats an awesome contribution!

dev · 06-10-2005, 09:14 AM

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

bfrye · 06-10-2005, 10:13 AM

This is a little tricky going if your web server is not on the same domain as the SQL server... still working on it.

Grimlin · 06-10-2005, 10:17 AM

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?

bfrye · 06-10-2005, 11:10 AM

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.

Grimlin · 12:22 PM

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

jibi · **OS:** 4.6.1.174

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.

udontknowjack · 06-13-2005, 02:16 PM

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.

Grimlin · 04:43 PM

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.

jibi · **OS:** 4.6.1.174

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

Grimlin · 06-14-2005, 04:58 PM

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.

jibi · 09:19 PM

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.

jibi · 10:22 PM

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?

Grimlin · 06-15-2005, 10:16 AM

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

Grimlin · 06-15-2005, 10:23 AM

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.

jibi · **OS:** 4.6.1.174

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

Grimlin · 06-16-2005, 01:59 PM

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.

bfrye · 06-16-2005, 02:09 PM

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.

Grimlin · 06-16-2005, 03:34 PM

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.

jibi · **OS:** 4.6.1.174

I would think it would be simple enough to write something into the ASP script for an If/Then/Else replace statement to handle the number conversions, no? Of course, my coding clases aren't really coming back to me all that much these days... heh.

jibi · **OS:** 4.6.1.174

I compressed your screenshot and emailed it to you. 7MB is a wee bit too big for most people

Grimlin · 06-16-2005, 07:25 PM

Quote:

Originally Posted by jibi

I would think it would be simple enough to write something into the ASP script for an If/Then/Else replace statement to handle the number conversions, no? Of course, my coding clases aren't really coming back to me all that much these days... heh.

You are correct. I just hate customizing that asp page. It was written to be generic and work with any view or table regardless of the columns or content. I'll post the the code snippet for the change if you want / need. You can just fill out the Case statement with the values as we find them. The anal retentive side of me that likes the generic ASP is going into convulsive fits as I type...

Grimlin · 06-16-2005, 07:29 PM

The real question is is anyone running a resolution that allows them to view it full size without scrolling?

I tried to compress the jpg to get it small enough to attach to the post, but had no luck even at the lowest setting. So when I gave up and dropped it on my site, I decided to use high quality. I'll post one for the bandwidth challenged users later.

jibi · 08:22 PM

Quick favor though, and on a related note as the above status conversions, since I know nothing of ASP. Can you pull the stat for the BlackBerry instance name, dbo.UserConfig.ServerConfigId and have it convert the value (1, 2, etc) to a static entry inputted by the user (obviously, they'd have to edit the ASP code for these values)?

So if there were users on BESPROD1 with ServerConfigId of 1, and users on BESPROD2 with ServerConfigId of 2, then the display on the page would read BESPROD1 or BESPROD2 and not 1 or 2.

I have one of our web developers looking at it in his spare time, but in case he never gets around to it, and if its something you could possibly do...

Grimlin · 06-17-2005, 05:23 PM

Wirelessly posted (BlackBerry7290/4.0.0 Profile/MIDP-2.0 Configuration/CLDC-1.1)

Will post the code snippet tonight. I'm on Berry, driving and listening to a podcast so coding would be pushing it.

jibi · 07:08 PM

haha. I got it, but see if you can do anything better (hehe). He changed this (places it in the second column, or perhaps its wherever its designated within the view?).

Be sure the view includes dbo.UserConfig.ServerConfigId within the initial column designation string.

Code:

						<TR>
			<%For COL = 0 to UBound(COLS,2)%>
				<%If ROW > 0 Then	'Set up TD coloring for odd numered rows
					If ROW/2 = Int(ROW/2) Then 'Even, don't color %>
						<TD Align="Left">
					<%Else%>
						<TD BGCOLOR="#C0C0C0" Align="Left">
					<%End If%>
				<%Else%>
						<TD Align="Left">
				<%End If%>
						<%
							If IsNull(Data(COL,ROW)) or Trim(Data(COL,ROW)) = "" Then
								Response.Write("&nbsp")
							ElseIF Trim(COLS(0,COL)) = "Server" and Trim(Data(COL,ROW)) = "1" Then
								Response.Write("<A Href='lite.asp?Server=1'>SERVER1</A>")
							ElseIF Trim(COLS(0,COL)) = "Server" and Trim(Data(COL,ROW)) = "2" Then
								Response.Write("<A Href='lite.asp?Server=2'>SERVER2</A>")
							Else							
								Response.Write("<A Href='lite.asp?" & COLS(0,COL) & "=" & Data(COL,ROW) & "'>" & Data(COL,ROW) & "</A>")
							End If
						%>

						</TD>
			<%Next%>
						</TR>
		<%Next%>

jibi · **OS:** 4.6.1.174

and this for lite.asp:

Code:

						<%
							If 

IsNull(Data(COL,ROW)) or Trim(Data(COL,ROW)) = "" Then
								

Response.Write(COLS(0,COL) & ":<BR>")
							ElseIF 

Trim(COLS(0,COL)) = "Server" and Trim(Data(COL,ROW)) = "1" Then
								

Response.Write(("Server: SERVER1") & "<BR>")
							ElseIF 

Trim(COLS(0,COL)) = "Server" and Trim(Data(COL,ROW)) = "2" Then
								

Response.Write(("Server: SERVER2") & "<BR>")
							Else
								

Response.Write(COLS(0,COL) & ": " & Data(COL,ROW) & "<BR>")
							End If
						%>

Grimlin · 06-17-2005, 11:29 PM

I was in the process of rewriting it when I went out and looked at what you are talking about. I will finish the code so that we can use it as a custom lookup for the status that isn't in the database. However, for this one, why not just change the SQL view to include the Server config table? Like this:

Code:

SELECT     SUBSTRING(CONVERT(nchar(100), UserConfig.ServerDN), 54, 3) AS Site, ServerConfig.ServiceName, UserConfig.DisplayName, UserConfig.PIN, 
                      SyncDeviceMgmtSummary.ModelName, SyncDeviceMgmtSummary.HomeNetwork AS LastNetwork, UserStats.MsgsPending AS Pending, 
                      UserStats.MsgsForwarded AS Rcvd, UserStats.MsgsSent AS Sent, UserStats.MsgsExpired AS Expired, UserStats.MsgsFiltered AS Filtered, 
                      RTRIM(CONVERT(nchar(10), DATEDIFF(hh, UserStats.LastFwdTime, GETUTCDATE()) / 24)) + ' Days' + ' ' + RTRIM(CONVERT(nchar(10), DATEDIFF(mi, 
                      UserStats.LastFwdTime, GETUTCDATE()) / 60 - DATEDIFF(hh, UserStats.LastFwdTime, GETUTCDATE()) / 24 * 24)) + ' hrs' AS Inactive, DATEADD(hh, 
                      DATEDIFF(hh, GETUTCDATE(), GETDATE()), UserStats.LastFwdTime) AS LastRcvd, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), 
                      UserStats.LastSentTime) AS LastSent, UserConfig.MailboxSMTPAddr AS SMTP, SUBSTRING(CONVERT(nchar(100), UserConfig.ServerDN), 51, 10) 
                      AS ExchServer, SyncDeviceMgmtSummary.AppsVer, SyncDeviceMgmtSummary.PhoneNumber, SyncDeviceMgmtSummary.DCID, 
                      SyncDeviceMgmtSummary.IMEI, SyncDeviceMgmtSummary.ITPolicyName, SyncDeviceMgmtSummary.FlashSize / 1048576 AS Memory, DATEADD(hh, 
                      DATEDIFF(hh, GETUTCDATE(), GETDATE()), UserConfig.CreationTime) AS Creation, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), 
                      UserConfig.ActivationTime) AS Activation, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()), UserConfig.KeyDate) AS KeyGen, DATEADD(hh, 
                      DATEDIFF(hh, GETUTCDATE(), GETDATE()), SyncDeviceMgmtSummary.HandheldConfigCheckTime) AS LastUpdate
FROM         ServerConfig RIGHT OUTER JOIN
                      UserConfig ON ServerConfig.Id = UserConfig.ServerConfigId LEFT OUTER JOIN
                      UserStats ON UserConfig.Id = UserStats.UserConfigId LEFT OUTER JOIN
                      SyncDeviceMgmtSummary ON UserConfig.Id = SyncDeviceMgmtSummary.UserConfigId

Grimlin · 06-18-2005, 01:20 AM

Ok, I recoded it. I don't like it as it makes the ASP a bit messy, but it does extend the functionality without making us create a new lookup table in the BES database. You can now create a Case statement for a fieldname and create your own replacement values in the code. The ugly part is you have to do it in both pages. I probably could write it into an #include and another file that they share, but not tonight.

http://www.thegrims.com/berrystats/Berrystats.zip

jibi · **OS:** 4.6.1.174

hehe, thanks very much good sir.

p.s-its ASP code - how could you ever make it messy

Grimlin · 06-18-2005, 10:26 AM

Quote:

Originally Posted by jibi

hehe, thanks very much good sir.

p.s-its ASP code - how could you ever make it messy

Actually I was referring to the fact that it UNGenerecizes (I made a new word!!!) that page. ...but I just got drunk and started coding so it all worked out...

Let me know if I screwed anything up. I forgot to keep in the part I made for you about the BES Server name. You'll have to get the SQL from the post above.

jibi · **OS:** 4.6.1.174

haha. not a prob. thanks again for your hard work!

cayzac · 06-21-2005, 05:03 PM

Am I doing something wrong here? I can't get to the server.

Grimlin · 06-22-2005, 01:10 PM

Quote:

Originally Posted by cayzac

Am I doing something wrong here? I can't get to the server.

My server? Yeah... I forgot to pay up this year and my Domain got bumped from DNS. I payed today and all should be back between now and tomorrow. Sorry. In the meantime if you just really want it, email me at [email address]

and I'll send it to you via email.

cayzac · 06-22-2005, 01:32 PM

OK, I've got the code now. I'm using a simple Blackberry server setup, still using MSDE. By default its using Windows Authentication. The code seems to be connecting to the database using SQL Authentication. I'm not a SQL guru but I've tried making a connection but keep getting errors. What should the connection string look like in my case? Thanks!

jibi · **OS:** 4.6.1.174

Do you have TCP/IP enabled for the MSDE database? Go to Start then Run and type in SVRNETCN. Enable it if its not.

Grimlin · 03:27 PM

Quote:

Originally Posted by cayzac

By default its using Windows Authentication. The code seems to be connecting to the database using SQL Authentication.

You'll need to convert it to accept SQL auth. You can do this through Enterprise manager. If you can get to it, look here http://www.thegrims.com/berrystats I have some brief instructions.

If you want to maintain windows auth for this, your web server will have to be a domain controller. If not, the MS double hop authentication restriction will kick in and you won't be able to authenticate to the database.

***EDIT***
OK, really brief. I'll get more in depth later. for now:
Right Click on your BES instance
Click properties
Go to security tab
Select SQL Server and Windows
Click OK

Expand out the tree structure.
Go to Security and the Logins
RightClick logins...New Login
Key in a name
Change to SQL Auth
Key in a password
Go to Database Access
Check BESMgmt
Check Datareader
OK

Done

cayzac · 06-22-2005, 03:20 PM

It was not enabled, I enabled it and now I'm getting:
ADODB.Recordset (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/berrystats/index.asp, line 33

Line 33 is "COLS = RS.GetRows(1000)"

Any ideas? I appreciate the help.

Grimlin · 06-22-2005, 03:27 PM

Quote:

Originally Posted by cayzac

It was not enabled, I enabled it and now I'm getting:
ADODB.Recordset (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/berrystats/index.asp, line 33

Line 33 is "COLS = RS.GetRows(1000)"

Any ideas? I appreciate the help.

What happens when you run the view from enterprise manager?