Description
A customer asked me to provide a summary report of the published application usage that needed to containing following elements.
-Published application.
-On which server was the application started.
-When did the user start the session
-When did the user end the session.
-Username of the users accessing the published application.
PrerequisitesA configured summary SQL database for you citrix farm.
SolutionAt first I thought this could easily be accomplished by creating a Billing report in resource management, but this was not the case as there was no way to link a report to published applications.
The solution I ended up using was writing a query to extract this information from the Summary Database.
The first thing I did was look for the database diagram, it ended up to be quite well document in the citrix guide Monitoring_Server_Performance.pdf on page 96 there was complete diagram of the database.
This made writing the query not that difficult.
You can open following query in the Microsoft SQL Server Management Studio, select the summary database. You can change the date as needed to select from which time you want the events in our example we want all the events after the 1st of August.
The result of this query can be copied to excell or you can use this query to create a report.
A customer asked me to provide a summary report of the published application usage that needed to containing following elements.
-Published application.
-On which server was the application started.
-When did the user start the session
-When did the user end the session.
-Username of the users accessing the published application.
PrerequisitesA configured summary SQL database for you citrix farm.
SolutionAt first I thought this could easily be accomplished by creating a Billing report in resource management, but this was not the case as there was no way to link a report to published applications.
The solution I ended up using was writing a query to extract this information from the Summary Database.
The first thing I did was look for the database diagram, it ended up to be quite well document in the citrix guide Monitoring_Server_Performance.pdf on page 96 there was complete diagram of the database.
This made writing the query not that difficult.
You can open following query in the Microsoft SQL Server Management Studio, select the summary database. You can change the date as needed to select from which time you want the events in our example we want all the events after the 1st of August.
SELECT LU_APPNAME.APPNAME,LU_SERVERNAME.SERVERNAME, SDB_SESSION.SESSIONSTART, SDB_SESSION.SESSIONEND, LU_USER.USERNAME
FROM
LU_APPNAME, SDB_SESSION, LU_USER, LU_SERVERNAME, LU_SERVER
WHERE
LU_APPNAME.PK_APPNAMEID = SDB_SESSION.FK_APPNAMEID
AND LU_USER.PK_USERID = SDB_SESSION.FK_USERID
AND SDB_SESSION.FK_SERVERID = LU_SERVER.PK_SERVERID
AND LU_SERVERNAME.PK_SERVERNAMEID = LU_SERVER.FK_SERVERNAMEID
AND SDB_SESSION.SESSIONSTART > '2009-08-01 00:00:00.000'
ORDER BY LU_APPNAME.APPNAME
FROM
LU_APPNAME, SDB_SESSION, LU_USER, LU_SERVERNAME, LU_SERVER
WHERE
LU_APPNAME.PK_APPNAMEID = SDB_SESSION.FK_APPNAMEID
AND LU_USER.PK_USERID = SDB_SESSION.FK_USERID
AND SDB_SESSION.FK_SERVERID = LU_SERVER.PK_SERVERID
AND LU_SERVERNAME.PK_SERVERNAMEID = LU_SERVER.FK_SERVERNAMEID
AND SDB_SESSION.SESSIONSTART > '2009-08-01 00:00:00.000'
ORDER BY LU_APPNAME.APPNAME
I also included another script to monitor the number of sessions started per user for a period of time.
select LU_USER.username, count(*) sessions
from SDB_SESSION, LU_USER
where LU_USER.PK_USERID = SDB_SESSION.FK_USERID
AND SDB_SESSION.SESSIONSTART > '2009-08-01 00:00:00.000'
GROUP BY LU_USER.username
from SDB_SESSION, LU_USER
where LU_USER.PK_USERID = SDB_SESSION.FK_USERID
AND SDB_SESSION.SESSIONSTART > '2009-08-01 00:00:00.000'
GROUP BY LU_USER.username
The result of this query can be copied to excell or you can use this query to create a report.
No comments:
Post a Comment