Showing posts with label script. Show all posts
Showing posts with label script. Show all posts

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

Wednesday, December 16, 2015

Friday, October 09, 2015

Deploy java web application in Azure Web App

Deploy java web application in Azure Web App


     1.   Create an azure webApp with Apache Tomcat (Here I created with name of     horizonsjava)
And webApp url is horizonsjava.azurewebsites.net
    2. After creating WebApp, goto DASHBOARD under quick glance, Reset the credentials




 3.      Open File Explore and enter the ftp url (provide the FTP host name)



Give the credentials of Deployment ftp  and click on “Log on”

 
   4.    Navigation to site/wwwroot/bin/apache-tomcat-8.0.26/


   5.   Now we need enable the users, Open tomcat-users.xml “/conf/tomcat-users.xml”

Now remove the comment surrounded the roles and user tag(<!-- -->)
And add two lines highlighted and save it


 6.   Restart the azure webApp

   7.   Now login to Visual Studio Online account
Push your Java app to VSO using Eclipse
Go to builds and create vnext build wi
th following activities
a.      Add a build step Ant.
b.      Ant build file: give the server path of build.xml file
c.       Target(s):war
(Note: To generate a war we need to add peace of code in build.xml file)

a.      Add Publish build Artifacts
b.      Contents: **/*.war
c.       Artifact Name: default it is “drops” if required any name you can change it.
  


a.      Add Publish build PowerShell
b.      Script filename: browse the version control path of .ps1 file
Create a power shell script and Check in to the VSO
The power shell script is

$ftp_uri = ftp://waws-prod-sg1-009.ftp.azurewebsites.windows.net/site/wwwroot/bin/apache-tomcat-8.0.26/webapps/
$user = <<FTP User>>
$pass = <<FTP Password>>
$path = $args[0]
$webclient = New-Object System.Net.WebClient
$webclient.Credentials = New-Object System.Net.NetworkCredential($user,$pass) 
    foreach($item in Get-ChildItem -recurse $path){
        $relpath = [system.io.path]::GetFullPath($item.FullName).SubString([system.io.path]::GetFullPath($path).Length + 1)
        if ($item.Attributes -eq "Directory"){
            try{
                Write-Host Creating $item.Name
                $makeDirectory = [System.Net.WebRequest]::Create($ftp_uri+$relpath);
                $makeDirectory.Credentials = New-Object System.Net.NetworkCredential($user,$pass)
                $makeDirectory.Method = [System.Net.WebRequestMethods+FTP]::MakeDirectory;
                $makeDirectory.GetResponse();
            }catch [Net.WebException] {
                Write-Host $item.Name probably exists ...
            }
            continue;
        }
        "Uploading $item..."
        $uri = New-Object System.Uri($ftp_uri+$relpath)
        "$relpath"
        $webclient.UploadFile($uri, $item.FullName)
    }
c.       Arguments : $(Agent.BuildDirectory)\Artifacts




8. And save the build


9. Queue the build.



      10. Open browser and enter the http url of your web app,
           My Web app URL is "http:// horizonsjava.azurewebsites.net/Horizons"