Jump to content


Photo

User Logon Information on "Citrix server" for past 48 Hours

Started by Abhay Thakur , 20 April 2012 - 01:45 PM
13 replies to this topic

Abhay Thakur Members

Abhay Thakur
  • 6 posts

Posted 20 April 2012 - 01:45 PM

Hi Citrix Team,

Please guide me to pull citrix server information where the user has logged in in the past
say 24 hours.

currently the user is not active. User has logged off his session.
we need to find on which server the user had the session and the application he accessed.

Note: please suggest resolution without using any 3rd party tool (session monitoring) ,edgesight etc.

Regards
Abhay

Edited by: abhaykec on Apr 20, 2012 9:45 AM



Sam Jacobs Members

Sam Jacobs
  • 6,297 posts

Posted 20 April 2012 - 01:47 PM

Are you using either Resource Manager or EdgeSight?



Abhay Thakur Members

Abhay Thakur
  • 6 posts

Posted 20 April 2012 - 01:52 PM

Hi Sam,

We are using Resource Manager. we have rmsummary database configured in the environment. Also access management console is available to pull reports via report center.



Sam Jacobs Members

Sam Jacobs
  • 6,297 posts

Posted 20 April 2012 - 01:56 PM

Hi Abhay,

Resource Manager has built-in reports that can give you the information you are looking for.
You can also write a query directly against the RM Summary Database, if you wish.

Sam



Abhay Thakur Members

Abhay Thakur
  • 6 posts

Posted 20 April 2012 - 02:01 PM

Sam,

1. Which built-in reports do i need to generate from Resource Manager to get my desired result.
2. which query can i have to get information from RM Summary Database.

Thanks in Advance!



Jarian Gibson Members

Jarian Gibson
  • 6,419 posts

Posted 20 April 2012 - 02:04 PM

If you are using Resource Manager then I would look at Application Usage or Environmental Usage reports.



Vincent Papoz Citrix Employees

Vincent Papoz
  • 386 posts

Posted 20 April 2012 - 02:15 PM

To answer question 2, you can run the following query against the RMDB. You will need to edit the username and the sessionstart accordingly:

select se.PK_SDB_SESSIONID ,u.USERNAME, sn.SERVERNAME, se.SESSIONSTART, se.SESSIONEND from SDB_SESSION se
join LU_SERVER s on s.PK_SERVERID=se.FK_SERVERID
join LU_SERVERNAME sn on sn.PK_SERVERNAMEID=FK_SERVERNAMEID
join LU_USER u on u.PK_USERID=se.FK_USERID
where FK_USERID in (select pk_userid from LU_USER where USERNAME = 'EnterUsernameHere')
and SESSIONSTART > '2012-04-19 14:00:00.000'

Thanks,
Vincent


Helpful Answer

Sam Jacobs Members

Sam Jacobs
  • 6,297 posts

Posted 20 April 2012 - 02:15 PM

Here is query when run from the Summary Database that will give you for each session in the past 48 hours:
- Logon Time (adjusted for time zone),
- User Name,
- User IP,
- Server Name,
- Application launched,
- Logoff Time (if session has ended).

SELECT top 20 PK_SDB_SESSIONID AS ID,
dateadd(minute,-serverUTCBIAS,SessionStart) as LogonTime,
USERNAME, CLIENTADDRESS, SERVERNAME, APPNAME,
dateadd(minute,-serverUTCBIAS,SessionEnd) as LogoffTime
FROM SDB_SESSION,
LU_CLIENT,
LU_APPNAME,
LU_SERVER,
LU_SERVERNAME,
LU_USER,
LU_WINSTATION
WHERE dateadd(minute,-serverUTCBIAS,SessionStart) > getDate()-2
AND PK_USERID = FK_USERID
AND PK_SERVERID = FK_SERVERID
AND PK_SERVERNAMEID = FK_SERVERNAMEID
AND PK_APPNAMEID = FK_APPNAMEID
AND PK_CLIENTID = FK_CLIENTID
AND PK_WINSTATIONID = FK_WINSTATIONID


Helpful Answer

Abhay Thakur Members

Abhay Thakur
  • 6 posts

Posted 20 April 2012 - 02:23 PM

thanks let me check it! Thanks for the Quickie...

Abhay

Edited by: abhaykec on Apr 20, 2012 10:24 AM



Sam Jacobs Members
  • #10

Sam Jacobs
  • 6,297 posts

Posted 20 April 2012 - 02:26 PM

Abhay,

Sorry, I don't understand ... the query above returns the server name, but what do you mean by the server accessed "after logoff" ?

Sam



Abhay Thakur Members
  • #11

Abhay Thakur
  • 6 posts

Posted 20 April 2012 - 03:08 PM

Sam,

I mean the user has finished his work and then logged off completely and left for the day. Now we don't have any active session of the user.

Now i would like to get the info :

1. To which Citrix server did the user logged in?

Abhay



Sam Jacobs Members
  • #12

Sam Jacobs
  • 6,297 posts

Posted 20 April 2012 - 03:16 PM

The following query will give you the last server that a specific user logged into:

declare @user varchar(255)
SET @user = 'username' -- <= modify for specific user

SELECT top 1 PK_SDB_SESSIONID AS ID,
dateadd(minute,-serverUTCBIAS,SessionStart) as LogonTime,
USERNAME, CLIENTADDRESS, SERVERNAME, APPNAME,
dateadd(minute,-serverUTCBIAS,SessionEnd) as LogoffTime
FROM SDB_SESSION,
LU_CLIENT,
LU_APPNAME,
LU_SERVER,
LU_SERVERNAME,
LU_USER,
LU_WINSTATION
WHERE USERNAME = @user
AND PK_USERID = FK_USERID
AND PK_SERVERID = FK_SERVERID
AND PK_SERVERNAMEID = FK_SERVERNAMEID
AND PK_APPNAMEID = FK_APPNAMEID
AND PK_CLIENTID = FK_CLIENTID
AND PK_WINSTATIONID = FK_WINSTATIONID

ORDER BY SessionStart DESC



Abhay Thakur Members
  • #13

Abhay Thakur
  • 6 posts

Posted 02 May 2012 - 07:55 PM

Hi Sam/Vincent,

The solution given by you were very helpful. I got the desired results.

Now i have a Question, can we get the same information by using session state monitor tool(CTX127491). Could you please provide me more details on it or POC document or so....

Regards
Abhay



Sam Jacobs Members
  • #14

Sam Jacobs
  • 6,297 posts

Posted 02 May 2012 - 08:36 PM

No ... that is a real-time tool (you can't see historical data), and needs to be running inside the user's session.