BlackBerry Forums Support Community - SQL Query find Last Com. Time

BlackBerry Forums Support Community (http://www.blackberryforums.com/index.php)

- BES Admin Corner (http://www.blackberryforums.com/forumdisplay.php?f=21)

- - SQL Query find Last Com. Time (http://www.blackberryforums.com/showthread.php?t=257736)

e2matt

02-15-2012 05:20 PM

SQL Query find Last Com. Time

I'm looking to generate a list of devices that have not had any communication since < 'xxxx-xx-xx'. I can't seem to find a table on the BES SQL DB that has this data but it is available in BES when viewing device properties > status.

Anyone have any suggestions?

knottyrope

02-15-2012 05:43 PM

Re: SQL Query find Last Com. Time

SQL Query to pull "Last Connected" - Port3101.org : Your BES Connection

MikeB

02-20-2012 01:58 PM

Re: SQL Query find Last Com. Time

I used :

SELECT US.LastContactTime, UC.MailBoxSMTPAddr, UC.CreationTime FROM UserStats US
INNER JOIN UserConfig UC ON UC.id = US.UserConfigId
ORDER BY US.LastContactTime

juwaack68

02-20-2012 02:10 PM

Re: SQL Query find Last Com. Time

I use this one:

Code:

select 

 a.DisplayName,

 a.mailboxsmtpaddr,

 c.ModelName,

 c.VendorID,

 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

 from userconfig a, userstats b, vHandheldSummaryInfo c

 where a.id = b.userconfigid and a.displayname = c.displayname

 order by a.displayname

All times are GMT -5. The time now is 08:52 PM.