Highlighted
Absent Member.. Absent Member..
Absent Member..
479 views

How to remove ALM QC users by using SQL query who never logged in to QC?

Jump to solution

Q1.   Please let me know the SQL query to remove the QC users who never logged in to QC?

Q2.   How to find the ALM QC user creation date in the ALM QC database (Table, Column name)? Actually need a query to delete those QC users who never access QC and created in QC database more than 6 months before.

Q3. We are mainting the users from ALM QC database by executing a monthly query which finds those users who did not access QC more than 6 months by session history. This query can capture only those users who at leat access QC once from the session history. However it does not show those who never access QC because it does not create any session history for them.

Select USER_NAME, Full_name, EMAIL from Users where User_name in (

Select USER_NAME from

(Select User_name, MAX(Start_Time)as MaxStartTime from SESSIONS_HISTORY group by User_name) LastRecord

Where LastRecord.MaxStartTime < '2015-10-28'

)order by USER_NAME

Please suggest me to modify the above query to capture additonal users who never access the ALM QC more than 6 months.

Thanks,

 

 

0 Likes
1 Solution

Accepted Solutions
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

A basic query to get the users in the database that never logged during the time covered by SESSIONS_HISTORY:

SELECT USER_NAME FROM USERS WHERE USER_NAME NOT IN (SELECT USER_NAME FROM SESSIONS_HISTORY) 
Jan Czajkowski

[Please do not contact me off line for receiving support. Use the forum!]

View solution in original post

0 Likes
2 Replies
Highlighted
Absent Member.
Absent Member.

Hello,

 

You need to check the AUDIT_LOG and AUDIT_PROPERTIES tables, those tables contain the information of users logged in and logged out.

You need to ask your DBA to perform a search on those tables and requested assistance to your dba to create a query or modify your query.

 

Check this article in order to check inactive users:

https://softwaresupport.hpe.com/group/softwaresupport/search-result/-/facetsearch/document/KM203251

 

You can also delete a bulk of users in ALM with API reference guide in the ALM help, there you will find DeleteUser Method:

Here comes the query that you should modify according to your needs, so you will be able to delete a bunch of users.

 

The syntax is 

 

Public Function DeleteUser( _

   ByVal UserName As String _

) As String

 

You can follow the next example 

 

Private Sub DeleteUser()

 

'The following Visual Basic example deletes a user.

    Dim sReply As String

 

    On Error GoTo err

    sReply = m_SAClient.DeleteUser("alex_td")

    If (sReply = "1") Then

        MsgBox "User Deleted"

    End If

    Exit Sub

 

err:

    MsgBox "Program failed:" + err.Description

End Sub

 

In case you do not know how modify queries you can contact PSO since this kind task are in charge of them.

 

Regards,

Sue Sevilla | SW Technical Support Consultant.

Application Lifecycle Management

Hewlett-Packard

 

If you find that this or any other post resolves your issue, please be sure to mark it as an accepted solution.

If you are satisfied with anyone's response please remember to give them KUDOS by clicking on the STAR at the bottom left of the post and show your appreciation."

 

 

 

0 Likes
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

A basic query to get the users in the database that never logged during the time covered by SESSIONS_HISTORY:

SELECT USER_NAME FROM USERS WHERE USER_NAME NOT IN (SELECT USER_NAME FROM SESSIONS_HISTORY) 
Jan Czajkowski

[Please do not contact me off line for receiving support. Use the forum!]

View solution in original post

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.