Friday, February 05, 2016

Get all project valid users from TFS

Get all project valid users from TFS using CMD

Open the developer command prompt and execute the below command by changing the collection URL

 TFSSecurity.exe /imx "Project Valid Users" /collection:http://ws2012r2:8080/tfs/FabrikamFiberCollection

 output:
Microsoft (R) TFSSecurity - Team Foundation Server Security Tool
Copyright (c) Microsoft Corporation.  All rights reserved.

 The target Team Foundation Server is http://vsalm:8080/tfs/fabrikamfibercollecti
on.
Resolving identity "Project Valid Users"...

 Error: Multiple identities found matching 'Project Valid Users'. Please specify
one of the following identities:

 - [FabrikamFiber]\Project Valid Users (vstfs:///Classification/TeamProject/fd6fa
263-b3f9-45e3-96af-ad67e75c9ff7\Project Valid Users)
- [Demo]\Project Valid Users (vstfs:///Classification/TeamProject/ac34434e-5ee2-
4351-a07f-7b2ccf02b96c\Project Valid Users)
- [DemoSP]\Project Valid Users (vstfs:///Classification/TeamProject/dbf96146-77f
8-46c4-9efc-0d6fec6b1925\Project Valid Users)
- [testing]\Project Valid Users (vstfs:///Classification/TeamProject/3fc600d0-13
96-47e3-8364-1402599732af\Project Valid Users)
- [test]\Project Valid Users (vstfs:///Classification/TeamProject/5dc525a7-4784-
4f5f-99db-2ef5a65a2a4a\Project Valid Users)
Pick one of the identity then modify the command and execute 

 TFSSecurity.exe /imx "vstfs:///Classification/TeamProject/fd6fa263-b3f9-45e3-96af-ad67e75c9ff7\Project Valid Users" /collection:http://ws2012r2:8080/tfs/FabrikamFiberCollection

 output:

 Microsoft (R) TFSSecurity - Team Foundation Server Security Tool
Copyright (c) Microsoft Corporation.  All rights reserved.

 The target Team Foundation Server is http://vsalm:8080/tfs/fabrikamfibercollection.
Resolving identity "vstfs:///Classification/TeamProject/fd6fa263-b3f9-45e3-96af-ad67e75c9ff7\Project Valid Users"...

 SID: S-1-9-1551374245-4249022485-1363183687-2676786528-2474025328-0-0-0-0-3

 DN:

 Identity type: Team Foundation Server application group
   Group type: EveryoneApplicationGroup
Project scope: FabrikamFiber
 Display name: [FabrikamFiber]\Project Valid Users
  Description: Members of this group have acess to the team project.

 15 member(s):
  [U] VSALM\Brian (Brian Keller)
a [A] [FabrikamFiber]\Project Administrators
  [U] VSALM\BHarry (Brian Harry)
  [G] VSALM\DemoUsers
  [U] VSALM\Adam (Adam Barr)
  [A] [FabrikamFiber]\Fabrikam Fiber Database Team
  [A] [FabrikamFiber]\Fabrikam Fiber Devices Team
  [A] [FabrikamFiber]\Fabrikam Fiber Web Team
  [A] [FabrikamFiber]\Fabrikam Fiber Leadership Team
  [A] [FabrikamFiber]\Contributors
  [U] VSALM\Annie (Annie Herriman)
  [A] [FabrikamFiber]\Readers
  [A] [FabrikamFiber]\Build Administrators
  [U] VSALM\Julia (Julia Ilyiana)
  [A] [FabrikamFiber]\Fabrikam Fiber Ops Team

 Member of 1 group(s):
e [A] [FabrikamFiberCollection]\Project Collection Valid Users
Done.

List of project admins from active projects who have accessed TFS 2015 at least once in past 6 months

List of project admins from active projects  who have accessed TFS 2015 at least once in past 6 months using SQL query


CREATE TABLE #TB1_PROJECTNAMES (cdate datetime,cid int,fpath nchar(1000))
CREATE TABLE #TB1_ACTIVEPROJECTNAMES (ID INT IDENTITY(1,1),ChangedDate datetime,ChangesetId int,ProjectName nchar(1000))
CREATE TABLE #TB1_ConvertProject (ID INT IDENTITY(1,1),ChangedDate datetime,ChangesetId int,ProjectName nchar(1000))
CREATE TABLE #TB1_ACTIVEPROJECTUSERS(USERNAME nchar(100),TEAMPROJECTS nchar(100))
insert into #TB1_PROJECTNAMES (cdate,cid,fpath)
SELECT
    chg_set.CreationDate,
    chg_set.ChangeSetId,
    v.FullPath
FROM dbo.tbl_ChangeSet (nolock)AS chg_set
    INNER JOIN dbo.tbl_Version (nolock)AS v ON chg_set.ChangeSetId = v.VersionFrom
    LEFT OUTER JOIN dbo.tbl_FileReference (nolock) AS f ON v.FileId = f.FileId
WHERE (chg_set.CreationDate > '2012-05-01')
ORDER BY chg_set.CreationDate, v.FullPath

delete from #TB1_PROJECTNAMES where fpath='$\'
UPDATE #TB1_PROJECTNAMES
SET fpath = left(fpath, charindex('\', fpath, charindex('\', fpath)+1)-1)
UPDATE #TB1_PROJECTNAMES
SET fpath = REPLACE (fpath, '$\', '')
UPDATE #TB1_PROJECTNAMES
SET fpath = REPLACE (fpath, '"', '-')



insert into #TB1_ACTIVEPROJECTNAMES (ChangedDate,ChangesetId,ProjectName)
select a.* from #TB1_PROJECTNAMES a
inner join
(
 select distinct fpath, max(cid) as cid from #TB1_PROJECTNAMES
  group by fpath
) as b
on a.fpath = b.fpath and a.cid = b.cid



insert into #TB1_ConvertProject (ChangedDate,ChangesetId,ProjectName)
Select ChangedDate,ChangesetId,(select project_name from tbl_projects b where b.project_id=a.ProjectName) as ProjectName from #TB1_ACTIVEPROJECTNAMES a

DELETE FROM #TB1_ConvertProject WHERE ID NOT IN (SELECT MIN(ID) _
       FROM #TB1_ConvertProject GROUP BY ChangedDate,ChangesetId,ProjectName)


DECLARE @COUNT INT = 0;
SET @COUNT = (SELECT COUNT(ID) AS NOOFPROJECTS FROM #TB1_ConvertProject)
DECLARE @LOOP INT = 1;
WHILE @LOOP <= @COUNT
 BEGIN
      
              DECLARE @PROJECTNAME VARCHAR(50);
             
              DECLARE @QRY VARCHAR(1000);
              SET @PROJECTNAME=(SELECT ltrim(rtrim(ProjectName)) FROM #TB1_ConvertProject WHERE ID = @LOOP)
              SET @QRY= 'INSERT INTO #TB1_ACTIVEPROJECTUSERS (USERNAME,TEAMPROJECTS)
              SELECT member.SamAccountName,'''+@PROJECTNAME+'''
              FROM [ADObjects] grp JOIN ADObjectMemberships om ON om.ObjectSID = grp.ObjectSID
              JOIN ADObjects member ON om.MemberObjectSID = member.ObjectSID
              WHERE grp.SamAccountName = ''Project Administrators'' and grp.Displayname LIKE ''%'+@PROJECTNAME+'%'''

        SET @LOOP=@LOOP+1
              exec(@QRY)
              --print @QRY
END
select * from #TB1_ACTIVEPROJECTUSERS



DROP TABLE #TB1_PROJECTNAMES
DROP TABLE #TB1_ConvertProject
DROP TABLE #TB1_ACTIVEPROJECTNAMES
DROP TABLE #TB1_ACTIVEPROJECTUSERS

Get the latest changeset ID of all the project in TFS

Get the latest changeset ID of all the project in TFS using SQL query




USE Tfs_FabrikamFiberCollection
CREATE TABLE #TB1_PROJECTNAMES (cdate datetime,cid int,fpath nchar(1000))
CREATE TABLE #TB1_ACTIVEPROJECTNAMES (ID INT IDENTITY(1,1),ChangedDate datetime,ChangesetId int,ProjectName nchar(1000))

insert into #TB1_PROJECTNAMES (cdate,cid,fpath)
SELECT
    chg_set.CreationDate,
    chg_set.ChangeSetId,
    v.FullPath
FROM dbo.tbl_ChangeSet (nolock)AS chg_set
    INNER JOIN dbo.tbl_Version (nolock)AS v ON chg_set.ChangeSetId = v.VersionFrom
    LEFT OUTER JOIN dbo.tbl_File (nolock) AS f ON v.FileId = f.FileId
WHERE (chg_set.CreationDate > '2013-05-01')
ORDER BY chg_set.CreationDate, v.FullPath

UPDATE #TB1_PROJECTNAMES
SET fpath = left(fpath, charindex('\', fpath, charindex('\', fpath)+1)-1)
UPDATE #TB1_PROJECTNAMES
SET fpath = REPLACE (fpath, '$\', '')

insert into #TB1_ACTIVEPROJECTNAMES (ChangedDate,ChangesetId,ProjectName)
select a.* from #TB1_PROJECTNAMES a
inner join
(
 select distinct fpath, max(cid) as cid from #TB1_PROJECTNAMES
  group by fpath
) as b
on a.fpath = b.fpath and a.cid = b.cid

UPDATE #TB1_ACTIVEPROJECTNAMES
SET ProjectName = RTRIM(LTRIM(ProjectName))
select * from #TB1_ACTIVEPROJECTNAMES

DROP TABLE #TB1_PROJECTNAMES
DROP TABLE #TB1_ACTIVEPROJECTNAMES

All project admins in collection

Get all project admins in collection using SQL query


USE Tfs_FabrikamFiberCollection
SELECT member.SamAccountName,grp.Displayname
FROM
    [ADObjects] grp
    JOIN ADObjectMemberships om ON om.ObjectSID = grp.ObjectSID
    JOIN ADObjects member ON om.MemberObjectSID = member.ObjectSID
WHERE
    grp.SamAccountName = 'Project Administrators'

List of project admins from active projects who have accessed TFS at least once in past 6 months

List of project admins from active projects  who have accessed TFS 2013 at least once in past 6 months using SQL query


USE Tfs_FabrikamFiberCollection
CREATE TABLE #TB1_PROJECTNAMES (cdate datetime,cid int,fpath nchar(1000))
CREATE TABLE #TB1_ACTIVEPROJECTNAMES (ID INT IDENTITY(1,1),ChangedDate datetime,ChangesetId int,ProjectName nchar(1000))
CREATE TABLE #TB1_ACTIVEPROJECTUSERS(USERNAME nchar(100),TEAMPROJECTS nchar(100))
insert into #TB1_PROJECTNAMES (cdate,cid,fpath)
SELECT
    chg_set.CreationDate,
    chg_set.ChangeSetId,
    v.FullPath
FROM dbo.tbl_ChangeSet (nolock)AS chg_set
    INNER JOIN dbo.tbl_Version (nolock)AS v ON chg_set.ChangeSetId = v.VersionFrom
    LEFT OUTER JOIN dbo.tbl_File (nolock) AS f ON v.FileId = f.FileId
WHERE (chg_set.CreationDate > '2013-05-01')
ORDER BY chg_set.CreationDate, v.FullPath

UPDATE #TB1_PROJECTNAMES
SET fpath = left(fpath, charindex('\', fpath, charindex('\', fpath)+1)-1)
UPDATE #TB1_PROJECTNAMES
SET fpath = REPLACE (fpath, '$\', '')

insert into #TB1_ACTIVEPROJECTNAMES (ChangedDate,ChangesetId,ProjectName)
select a.* from #TB1_PROJECTNAMES a
inner join
(
 select distinct fpath, max(cid) as cid from #TB1_PROJECTNAMES
  group by fpath
) as b
on a.fpath = b.fpath and a.cid = b.cid

DECLARE @COUNT INT = 0;
SET @COUNT = (SELECT COUNT(ID) AS NOOFPROJECTS FROM #TB1_ACTIVEPROJECTNAMES)
DECLARE @LOOP INT = 1;
WHILE @LOOP <= @COUNT
 BEGIN
       
              DECLARE @PROJECTNAME VARCHAR(50);
             
              DECLARE @QRY VARCHAR(1000);
              SET @PROJECTNAME=(SELECT ltrim(rtrim(ProjectName)) FROM #TB1_ACTIVEPROJECTNAMES WHERE ID = @LOOP)
              SET @QRY= 'INSERT INTO #TB1_ACTIVEPROJECTUSERS (USERNAME,TEAMPROJECTS)
              SELECT member.SamAccountName,'''+@PROJECTNAME+'''
              FROM [ADObjects] grp JOIN ADObjectMemberships om ON om.ObjectSID = grp.ObjectSID
              JOIN ADObjects member ON om.MemberObjectSID = member.ObjectSID
              WHERE grp.SamAccountName = ''Project Administrators'' and grp.Displayname LIKE ''%'+@PROJECTNAME+'%'''

        SET @LOOP=@LOOP+1
              exec(@QRY)
              --print @QRY
END
select * from #TB1_ACTIVEPROJECTUSERS
DROP TABLE #TB1_PROJECTNAMES
DROP TABLE #TB1_ACTIVEPROJECTNAMES
DROP TABLE #TB1_ACTIVEPROJECTUSERS

Customizing the TFS work item state


Adding value to TFS task work item state "Re Open"

witadmin exportwitd /collection:http://vsalm:8080/tfs/FabrikamFiberCollection /p:FabrikamFiber /n:Task /f:C:\templates\Task.xml


Now go to task template and add new state “Re Open”

Now add the transition flow in same task template -- transition flow is (Done- Re Open)


Now get the processconfig template for the same project

witadmin exportprocessconfig /collection:http://vsalm:8080/tfs/FabrikamFiberCollection /p:FabrikamFiber /f:C:\templates\processconfig.xml
 




Now go to processconfig template and add the state “Re Open”
 




Now import task and processconfig templates back to the team project
 
First import the task template.
 
witadmin importwitd /collection:http://vsalm:8080/tfs/FabrikamFiberCollection /p:FabrikamFiber /f:C:\Users\Julia\Desktop\ChangedTemplates\Task.xml


Now import the processconfig tamplate.

witadmin importprocessconfig /collection:http://vsalm:8080/tfs/FabrikamFiberCollection /p:FabrikamFiber /f:C:\Users\Julia\Desktop\ChangedTemplates\processconfig.xml
 













Now go to task board or Task work item