Problem:

Is it possible to determine StarTeam Users that have had no activity?

Resolution:

• Product Name: StarTeam
• Product Version: 2006 onwards
• Product Component: SQL Query
• Platform/OS Version: All

The following SQL script will allow you to identify when StarTeam users have no activity recorded in the database. This checks for activity in Views, Files, Change Requests, Tasks, Topics, Links and Audit trail for a specified User Group.

This is useful in determining if a license can be reassigned to another user.

Please note, this script is written for SQL Server, it may not work as is against an Oracle database.

To run this script, please modify the name of the group that you want to check (highlighted below). It is advisable not to run this script for all users at a time as this may take considerable time to complete.

DECLARE @u_id int
DECLARE @tot_recs int
DECLARE @curr_recs int

DECLARE user_cursor CURSOR FOR
SELECT a.userid FROM syn_GroupMembers a WHERE a.GroupID = (
SELECT id FROM syn_group WHERE groupname = "Administrators")

set @tot_recs = 0
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @u_id
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @curr_recs = count (*) FROM syn_View WHERE CreatedUserID = @u_id OR DeletedUserID = @u_id
set @tot_recs = @tot_recs @curr_recs
SELECT @curr_recs = count (*) FROM syn_ViewMember WHERE CreatedUserId = @u_id OR ModifiedUserID = @u_id or DeletedUserID = @u_id
set @tot_recs = @tot_recs @curr_recs
SELECT @curr_recs = count (*) FROM syn_Change WHERE CreatedUserID = @u_id OR ModifiedUserID = @u_id or DeletedUserID = @u_id
set @tot_recs = @tot_recs @curr_recs
SELECT @curr_recs = count (*) FROM syn_File WHERE CreatedUserID = @u_id OR ModifiedUserID = @u_id or DeletedUserID = @u_id
set @tot_recs = @tot_recs @curr_recs
SELECT @curr_recs = count (*) FROM syn_Task WHERE CreatedUserID = @u_id OR ModifiedUserID = @u_id or DeletedUserID = @u_id
set @tot_recs = @tot_recs @curr_recs
SELECT @curr_recs = count (*) FROM syn_Topic WHERE CreatedUserID = @u_id OR ModifiedUserID = @u_id or DeletedUserID = @u_id
set @tot_recs = @tot_recs @curr_recs
SELECT @curr_recs = count (*) FROM syn_Link WHERE CreatedUserID = @u_id OR ModifiedUserID = @u_id or DeletedUserID = @u_id
set @tot_recs = @tot_recs @curr_recs
SELECT @curr_recs = count (*) FROM syn_Audits WHERE CreatedUserID = @u_id OR ModifiedUserID = @u_id or DeletedUserID = @u_id
set @tot_recs = @tot_recs @curr_recs
END

IF @tot_recs = 0
BEGIN
SELECT id, LoginName, Full_Name,
CASE
WHEN LicenseType = 2 THEN "Named"
WHEN LicenseType = 1 THEN "Concurrent"
ELSE "Unassigned"
END AS "License Type",
CASE
WHEN DeletedTime > 0 THEN "Yes"
ELSE "No"
END AS "Deleted?"
FROM syn_User WHERE id = @u_id
END

set @tot_recs = 0
FETCH NEXT FROM user_cursor INTO @u_id
END
CLOSE user_cursor
DEALLOCATE user_cursor

The results of this script will show the StarTeam User ID, Login & Full name, License type, and whether the user has been deleted.

If no results are returned, then all users in the specified User Group have had some form of activity.