Adobe Connect User Community
Menu

#1 2007-09-21 12:57:05

**_bdr_**

Tools that provide utilization information

Does anyone have tools that provide utilization reports from the Connect Server that report on past meetings (attendance, usage, etc...) as well as a forecast of coming meetings that you can share?

Offline

#2 2007-09-21 13:16:28

**_daminsky_**

Re: Tools that provide utilization information

We created some views in the Breeze/Connect database.  We're using SQL Server 2000.  No guarantees that these will work for you or continue to work in the future.

List of Meeting Hosts group and their last login:

SELECT     P.PRINCIPAL_ID, P.LOGIN, P.NAME, P.LOGIN AS EMAIL, E.NAME AS TYPE, MAX(DATEADD(hh, - 5, S.DATE_CREATED)) AS LAST_LOGIN
FROM         dbo.PPS_GROUP_MEMBERS G INNER JOIN
                      dbo.PPS_USER_SESSIONS S INNER JOIN
                      dbo.PPS_ACLS A INNER JOIN
                      dbo.PPS_PRINCIPALS P INNER JOIN
                      dbo.PPS_EXT_ENUM_UTYPE E ON P.TYPE = E.TYPE ON A.ACL_ID = P.PRINCIPAL_ID ON S.USER_ID = A.ACL_ID ON
                      G.PRINCIPAL_ID = S.USER_ID
WHERE     (P.ACCOUNT_ID = 7) AND (P.DISABLED IS NULL) AND (P.HAS_CHILDREN = 0) AND (G.GROUP_ID = 19)
GROUP BY P.PRINCIPAL_ID, P.LOGIN, P.NAME, P.LOGIN, E.NAME

List of content, meetings, etc. and who owns them:

SELECT DISTINCT
                      SCO.URL_PATH, P.PRINCIPAL_ID, P.LOGIN, P.NAME, P.LOGIN AS EMAIL, SCO.NAME AS Title, SCO.DESCRIPTION, t.NAME AS Content_Type,
                      DATEADD(hh, - 5, SCO.DATE_CREATED) AS Date_Created, SCO.ICON, SCO.TYPE, SCO.FOLDER_ID
FROM         dbo.PPS_EXT_ENUM_TYPE t INNER JOIN
                      dbo.PPS_SCOS SCO INNER JOIN
                      dbo.PPS_ACLS A INNER JOIN
                      dbo.PPS_USER_SESSIONS S INNER JOIN
                      dbo.PPS_PRINCIPALS P ON S.USER_ID = P.PRINCIPAL_ID ON A.OWNER_PRINCIPAL_ID = S.USER_ID ON SCO.SCO_ID = A.ACL_ID ON
                      t.TYPE = SCO.ICON INNER JOIN
                      dbo.PPS_SCOS PPS_SCOS_1 ON A.PARENT_ACL_ID = PPS_SCOS_1.SCO_ID
WHERE     (P.ACCOUNT_ID = 7) AND (P.DISABLED IS NULL) AND (P.HAS_CHILDREN = 0) AND (PPS_SCOS_1.TYPE = - 1)


All users with last login:

SELECT     P.PRINCIPAL_ID, P.LOGIN, P.NAME, P.LOGIN AS EMAIL, E.NAME AS TYPE, MAX(DATEADD(hh, - 5, S.DATE_CREATED)) AS LAST_LOGIN
FROM         dbo.PPS_USER_SESSIONS S INNER JOIN
                      dbo.PPS_ACLS A INNER JOIN
                      dbo.PPS_PRINCIPALS P INNER JOIN
                      dbo.PPS_EXT_ENUM_UTYPE E ON P.TYPE = E.TYPE ON A.ACL_ID = P.PRINCIPAL_ID ON S.USER_ID = A.ACL_ID
WHERE     (P.ACCOUNT_ID = 7) AND (P.DISABLED IS NULL) AND (P.HAS_CHILDREN = 0)
GROUP BY P.PRINCIPAL_ID, P.LOGIN, P.NAME, P.LOGIN, E.NAME

Offline

#3 2007-09-21 13:21:10

**_daminsky_**

Re: Tools that provide utilization information

Unfortunately, since many of the meeting rooms are re-used and the scheduled start time is not used, it is difficult to predict future use.  All we can do is calculate the past usage for each semester and look for the trends.

Would love to know a better solution though.  Right now, we have to login each month to check the 'peak' usage.  Although, we could probably figure out this number based on sessions that occurred within the same time range.  Will have to look into that.

-Damon

Offline

#4 2007-09-21 15:44:28

**_Jorma_at_RealEyes_**

Re: Tools that provide utilization information

Damon,

There is a product out there that seamlessly works with the Connect server, either hosted or licensed, that my help solve your problem. Emal me if you would like to know more.

Jorma_at_RealEyes

Offline

#5 2011-08-14 09:23:41

**_Gena69_**

Re: Tools that provide utilization information

daminsky wrote:

We created some views in the Breeze/Connect database.  We're using SQL Server 2000.  No guarantees that these will work for you or continue to work in the future.

List of Meeting Hosts group and their last login:

SELECT     P.PRINCIPAL_ID, P.LOGIN, P.NAME, P.LOGIN AS EMAIL, E.NAME AS TYPE, MAX(DATEADD(hh, - 5, S.DATE_CREATED)) AS LAST_LOGIN
FROM         dbo.PPS_GROUP_MEMBERS G INNER JOIN
                      dbo.PPS_USER_SESSIONS S INNER JOIN
                      dbo.PPS_ACLS A INNER JOIN
                      dbo.PPS_PRINCIPALS P INNER JOIN
                      dbo.PPS_EXT_ENUM_UTYPE E ON P.TYPE = E.TYPE ON A.ACL_ID = P.PRINCIPAL_ID ON S.USER_ID = A.ACL_ID ON
                      G.PRINCIPAL_ID = S.USER_ID
WHERE     (P.ACCOUNT_ID = 7) AND (P.DISABLED IS NULL) AND (P.HAS_CHILDREN = 0) AND (G.GROUP_ID = 19)
GROUP BY P.PRINCIPAL_ID, P.LOGIN, P.NAME, P.LOGIN, E.NAME

List of content, meetings, etc. and who owns them:

SELECT DISTINCT
                      SCO.URL_PATH, P.PRINCIPAL_ID, P.LOGIN, P.NAME, P.LOGIN AS EMAIL, SCO.NAME AS Title, SCO.DESCRIPTION, t.NAME AS Content_Type,
                      DATEADD(hh, - 5, SCO.DATE_CREATED) AS Date_Created, SCO.ICON, SCO.TYPE, SCO.FOLDER_ID
FROM         dbo.PPS_EXT_ENUM_TYPE t INNER JOIN
                      dbo.PPS_SCOS SCO INNER JOIN
                      dbo.PPS_ACLS A INNER JOIN
                      dbo.PPS_USER_SESSIONS S INNER JOIN
                      dbo.PPS_PRINCIPALS P ON S.USER_ID = P.PRINCIPAL_ID ON A.OWNER_PRINCIPAL_ID = S.USER_ID ON SCO.SCO_ID = A.ACL_ID ON
                      t.TYPE = SCO.ICON INNER JOIN
                      dbo.PPS_SCOS PPS_SCOS_1 ON A.PARENT_ACL_ID = PPS_SCOS_1.SCO_ID
WHERE     (P.ACCOUNT_ID = 7) AND (P.DISABLED IS NULL) AND (P.HAS_CHILDREN = 0) AND (PPS_SCOS_1.TYPE = - 1)


All users with last login:

SELECT     P.PRINCIPAL_ID, P.LOGIN, P.NAME, P.LOGIN AS EMAIL, E.NAME AS TYPE, MAX(DATEADD(hh, - 5, S.DATE_CREATED)) AS LAST_LOGIN
FROM         dbo.PPS_USER_SESSIONS S INNER JOIN
                      dbo.PPS_ACLS A INNER JOIN
                      dbo.PPS_PRINCIPALS P INNER JOIN
                      dbo.PPS_EXT_ENUM_UTYPE E ON P.TYPE = E.TYPE ON A.ACL_ID = P.PRINCIPAL_ID ON S.USER_ID = A.ACL_ID
WHERE     (P.ACCOUNT_ID = 7) AND (P.DISABLED IS NULL) AND (P.HAS_CHILDREN = 0)
GROUP BY P.PRINCIPAL_ID, P.LOGIN, P.NAME, P.LOGIN, E.NAME

Hi thank's for the information ...but how it work's?????

Gena

Last edited by **_Gena69_** (2011-08-23 15:48:46)

Offline

Board footer