vinijmoura / azure-devops

Data extraction and Power BI report that generate management informations about your Azure DevOps organization. Using Azure DevOps CLI and Azure DevOps REST API, PowerShell scripts extract data from Azure DevOps, store this information in an Azure SQL Database and shows them in a Power BI report.

Muito prazer, eu sou o Vinicius Moura 👋

• Sou consultor DevOps há 12 anos, ajudando clientes em seus desafios e na melhoria contínua dos processo de DevOps.
• Minha missão como Microsoft MVP é ajudar as pessoas, compartilhar conhecimento e trazer um pouco da minha experiência e vivência nos clientes. Exerço com paixão essa minha missão desde 2015.
• Sou Tech Solution Architect Manager na Accenture
• Tenho o maior canal do mundo (em quantidade de videos) focado exclusivamente em Azure DevOps. Caso queira acessá-lo, procure por Azure DevOps Sprints no Youtube.
• Tenho um blog no Medium, onde compartilho muitos artigos, voltados principalmente a automações e relatórios do Azure DevOps.

Minhas redes sociais:

azure-devops's Issues

Addition of Team admin and Project Admin details in the report

**Is your feature request related to a problem?

The report is complete in itself. But, it will be really helpful if it reflects the list of team admins per project per team.

The solution I would like:

There should be a page in power bi report where user can filter project afterwards filter team. On selecting any teams it will shows us the list of team administrator of the team selected.

**Aternative I have tried
I tried to extract this information using odata feed but found it is not possible using odata feed as of now.

Cannot validate argument on parameter 'InputObject'

I was trying to run the AllReport script as below. But throw an error for table inserts. Can you help me to how to resolve this.

.\Processes.ps1 -PAT PAT -Organization Org -Connstr "Server=tcp:dbname-dev.database.windows.net,1433;Initial Catalog=azuredevopsreports;Persist Security Info=False;User ID=sqladmin;Password=Password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

Write-SqlTableData: C:\Users\AzureDevOpsReports\Azure-DevOps\AllReports\PowerShell\Extensions.ps1:31:69
Line |
31 | … rite-SqlTableData -InputData $InstalledExtensions -InputObject $table
| ~~~~~~
| Cannot validate argument on parameter 'InputObject'. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.
InvalidOperation: C:\Users\AzureDevOpsReports\Azure-DevOps\AllReports\PowerShell\Users.ps1:15:1

When I troubleshoot more I notice the DB connection is not made from the script. Failed with the following error.

DB Connection error with Processes.ps1

I was getting the folowing error when I executed the Processes.ps1 code

Line |
  10 |  $table = $db.Tables["FeedPackageVersions"]
     |  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Cannot index into a null array.

I did some refactoring of the code and ultimately it seemed like there was something wrong with how the $srv object is being created because it wasn't using the $connstr, and I really don't like that even when I turned on Debug the current solution doesn't give the "root cause" connection error. Ultimately, I think I found a better way.

Here is how I fixed it using the Get-SqlDatabase cmdlet. Replace the following commented out code with one line of code that follows

# $sqlcc = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $Connstr
# $sc = New-Object -TypeName Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList $sqlcc
# $srv = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $sc
# $db = $srv.Databases["azuredevopsreports"]
$db = Get-SqlDatabase -Name "azuredevopsreports" -ConnectionString $Connstr

Error when trying to add data to the db.Projects table

When trying to run the Processes.ps1 script I realized that the Projects table was not receiving data, after performing the debug I realized that a -UseBasicParsing parameter is missing in the Projects.ps1 script

AllReports Project Stats only shows 1001 work items created and completed

AllReports Project Stats only shows exactly 1001 work items created and the same number completed. My project has well over 20000 work items because I regularly hit query limits in the Boards UI.

You can see in the image below that I have more than 20K closed work items.

And below shows more than 15K created in just the last 60 days.

I haven't looked at the code yet, but I'm guessing it is a paging issue.

Report: Last activities at Projects

Hello @vinijmoura ,
FOr a good maintenance of AzDO org, i think that it would be great to have a report where is a list of projects (and the owners of the projects) and last activity regarding last update in Boards / work items, Repos last update, TEst Plans last update, Pipelines last update, Wiki last update, and so on.
Then it will be useful to decommission projects or contact with admins to know what happens with the project.

;)

Issue with JSON

Hi,

The dashboard is very useful and all the functions used to work. However, now there is an error that says that there are extra characters at the end of JSON input:

Any idea how it can be fixed?

Is it possible to get the YAML based Stage approvers names?

We have approvers in the production stage. We use environment for adding approvers. We would like to get the approvers name. For pipeline we use YAML based pipeline.

In the report I can see there is a report for approvers. is it possible to get approvers name from it

Environment and Approval/Checks Report

Is your feature request related to a problem? Please describe.
Not related to a problem - it extends your https://vinijmoura.medium.com/how-to-create-dashboard-to-visualize-environments-and-deployments-on-azure-devops-319ddb1614f4 report.

Describe the solution you'd like
The https://vinijmoura.medium.com/how-to-create-dashboard-to-visualize-environments-and-deployments-on-azure-devops-319ddb1614f4 report generates a great view on the "life of the pipelines and environments", invaluable for release management. Would be great to have another view/report of the environments and their approvals/checks, used to verify that environments have been correctly configured and are not drifting in terms of configuration consistency.

List all users and group permissions on OnPrem

Can we do the same for OnPrem Azure DevOps?
Looking for audit report for listing all users and group permissions on Azure devops