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