Query to list the users logged in QC for past 24hrs

[machman134]

Hi All, 

Is there any query to pull the details of the users that logged in QC for last 24hrs 

like project name>user name > login time   >logout time 

I have  access to the site admin database

[Trudy Claspill]

I believe you will find what you are looking for in the SESSIONS_HISTORY table.  You may need to combine it with other tables (like USERS) to get user names and PROJECTS to get project names.

[Barry L. Daniel · Accepted solution]

I use the below SQL query to determine logon/logoff times and duration. Written for SQL Server.

SELECT [SESSION_ID] 
      ,[START_TIME]
      ,[END_TIME]
      ,cast([END_TIME]-[START_TIME]as time(0)) as loggedIn
      ,[DOMAIN_NAME]
      ,[PROJECT_NAME]
      ,[USER_NAME]
      ,[CLIENT_TYPE]
  FROM [qcsiteadmin_db].[td].[SESSIONS_HISTORY]

[machman134]

Thanks Barry and trudy 

[ASHUPACH]

Hi,

I do not have access to the qcsiteadmin data base.

If there ant way to execute the quesry from  Dashboard Analysis view from the project and get the result?

Regards

[machman134]

you can execute this query from the proj dashboard ...you dnt need access to qcsiteadmin 

[Trudy Claspill]

I was going to refute Machman and say it couldn't be done, but I tried it and it worked!

I never knew you could report against the site admin database from within a project!

Kudos to machman!

[qcentry]

Hi Experts,

I want to make this subject active with the following issues that I've been experiencing. Appreciate if you could you throw some light on it. Currently, my sessions_history table is storing the records of past 1 year.

1) Using the below query, I'm getting a count of around 1000 users.

 SELECT USER_NAME FROM QCSITEADMIN_DB.USERS

2) Using the below query, I'm getting a count of 500 (which means 500 users are actively logging into QC since 01-Apr-2014)

SELECT DISTINCT USER_NAME FROM QCSITEADMIN_DB.SESSIONS_HISTORY WHERE END_TIME >= '01-APR-14'

3) So using the below query, I should be getting a count of 500 (1000 - 500). However, I'm getting a count of around 950 which is NOT correct. Isn't it?

SELECT QCSITEADMIN_DB.USERS.USER_NAME

FROM QCSITEADMIN_DB.USERS

WHERE USER_NAME NOT IN(SELECT DISTINCT USER_NAME FROM QCSITEADMIN_DB.SESSIONS_HISTORY where end_time >= ('01-APR-14'))

Thanks,

[aesa]

Hi,

What did you mean with "proj dashboard"

Thanks in advance.

[Trudy Claspill]

"proj dashboard" > The Dashboard module accessible when you are logged into a project.

Within the Analysis view you can create an Excel report and enter a custom query to retrieve data.

[PaulRegan]

Machman - what query

[PaulRegan]

I tried to put this query into the project's Dashboard>Analysis View>Excel Report,  logged in as TDAdmin, and it doesn't work.

I then tried to put this query into the Site Admin's SiteProject>{view project tables section}>{query box}, logged in as SiteAdmin, and it doesn't work.

Not sure how you guys are getting this to work for you.