- Topics: Active | Unanswered
Pages: 1
Topic closed
#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
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.NAMEList 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
Pages: 1
Topic closed