New Ranks & Badges For The Community!
Notice something different? The ranks and associated badges have gone "Star Fleet". See what they all mean HERE
Highlighted
Vice Admiral Vice Admiral
Vice Admiral
6522 views

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

Jump to solution

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

0 Likes
1 Solution

Accepted Solutions
Highlighted
Commodore Commodore
Commodore

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]

 

View solution in original post

12 Replies
Highlighted
Absent Member.. Absent Member..
Absent Member..

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.

[If this post solves or helps solve your issue, mark the thread as solved and give KUDOS to the author for their assistance.]

(Opinions expressed in my postings are mine alone, and do not reflect the opinions of my employer.No warranties express or implied for any solution/suggestion posted.)
Highlighted
Commodore Commodore
Commodore

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]

 

View solution in original post

Highlighted
Vice Admiral Vice Admiral
Vice Admiral

Thanks Barry and trudy 

0 Likes
Highlighted
Absent Member.
Absent Member.

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

 

0 Likes
Highlighted
Vice Admiral Vice Admiral
Vice Admiral

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

Highlighted
Absent Member.. Absent Member..
Absent Member..

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!

[If this post solves or helps solve your issue, mark the thread as solved and give KUDOS to the author for their assistance.]

(Opinions expressed in my postings are mine alone, and do not reflect the opinions of my employer.No warranties express or implied for any solution/suggestion posted.)
Highlighted
Cadet 2nd Class Cadet 2nd Class
Cadet 2nd Class

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,

 

Highlighted
Absent Member.
Absent Member.

Hi,

 

What did you mean with "proj dashboard"

 

Thanks in advance.

 

0 Likes
Highlighted
Absent Member.. Absent Member..
Absent Member..

"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.

[If this post solves or helps solve your issue, mark the thread as solved and give KUDOS to the author for their assistance.]

(Opinions expressed in my postings are mine alone, and do not reflect the opinions of my employer.No warranties express or implied for any solution/suggestion posted.)
0 Likes
Highlighted
Cadet 2nd Class Cadet 2nd Class
Cadet 2nd Class

Machman - what query

0 Likes
Highlighted
Cadet 2nd Class Cadet 2nd Class
Cadet 2nd Class

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.

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.