techfreakado/tfadoscheduledworkitemquery

Send scheduled E-Mails for saved Work Item Queries right from Azure DevOps (Service/Server)

Azure DevOps Scheduled Work Item Query E-Mail

NOTE

This is the successor of Quadrotech Scheduled Work Item Query. I am the same developer of the original, but I'm not employed at Quadrotech anymore. I am continuing this under my own account, as I don't have access to the Quadrotech original anymore.

Introduction

The Goal of this extension was to keep as much functionality as possible inside Azure DevOps / Team Foundation Server. We have opted to realize this by using a standard Build Pipeline in Azure DevOps / Team Foundation Server as our "Scheduling" Engine.

The actual functionality is realized as a Build Pipeline Task called "Scheduled Work Item Query". This task executes a query that is saved in either the "My Queries" or "Shared Queries" folders, and sends the results by e-mail using either SendGrid or standard SMTP.

Setup

Introduction

Setup of the Task should be straight forward. You should probably create a new Build Pipeline, and then add as many "Scheduled Work Item Query" Tasks as needed. Each Task is able to send a single Query to multiple people. The Build Pipeline does not have to live in the same Project as the Query.

You should then add a Schedule Trigger to the Build Pipline, that it gets executed automatically whenever you need it to.

Endpoints

SendGrid Endpoint

The SendGrid endpoint can be used if you want to send e-mails using SendGrid. All you need is a SendGrid API Token with the permissions to send e-mails. For Documentation about this, see here

SMTP Endpoint

The SMTP endpoint can be used to send e-mails using standard SMTP interfaces. It supports common TLS configurations as well as authentication.

Azure Repos / Team Foundation Server Endpoint

This Endpoint is needed when you want to send a query that exists only in someone's "My Queries" folder. It is not needed if you want to send out queries living in the "Shared Queries" folder.

Task

Shared Queries

Shared queries can be scheduled without any other authentication, it uses the authentication/authorization of the Agent to connect to Azure DevOps Service / Azure DevOps Server. However, it might be possible that you need to add "Read" Permissions to the "<project> Build Service (<organization>)" User.

Personal (My) Queries

"My Queries" requires a personal access token or username/password from the person which owns the query. You need to configure an Azure DevOps Endpoint for this to work. Note: We recommend generating a Personal Access Token with minimal rights (e.g. only work_item.read scope), and to limit the created Endpoint to a specific pipeline. Make sure you don't expose the endpoint for others to use, as this might be a security risk.

Release Notes

2022-03-08

• Rebrand as TechFreak
• Update dependencies
• Improve error message when there is a permission problem
• Fix running on Linux Agent

2020-02-14

• Initial support for Work Items with direct links queries

2020-02-13

• Support ordering of the query (defined in the query) (#10)

2020-02-12

• Support more than 200 results from query (#5)

2019-05-21

• Add ability to skip sending e-mail when there are no work items in the query

2019-04-03

• Initial Release

tfadoscheduledworkitemquery's Issues

[error] Project field is empty!

Project field is empty and when clicked to "gear" icon system opens "test plans" page.
There is no way to select a query since project is also empty.
Using on premise Azure Devops 2020.

Failed to query service connection API: 'https://****/DefaultCollection/_apis/projects?api-version=5.0'. Error Message: 'An error occurred while sending the request.'

I dont know, how to start.

Hi everyone.

I'm new using pipelines in Azure DevOps.
The steps mentioned in overview in marketplace (setup) are for people with a lot of experience, so I don't know how to configure it en my DevOps project with the information provided. I mean I don't know if somebody has the step by step to setup the extension.
Kind regards.

The pipeline references a task called 'ADOScheduledWorkItemQuery'.

Getting a error when the pipeline tries to run on an automated schedule. I'm able to run it manually and it works 100% correct. I'm using the 'Azure Pipelines' resource pool and the vmimage of 'windows-latest'. Any idea why this is happening?

I cant add my work item query to this code

[error]unable to get local issuer certificate

hi Team,

Getting below error while trying to run the build task:

##[debug]loaded 18
##[debug]Agent.ProxyUrl=undefined
##[debug]Agent.CAInfo=undefined
##[debug]Agent.ClientCert=undefined
##[debug]Agent.SkipCertValidation=undefined
##[debug]queryType=Shared
##[debug]SystemVssConnection=https:///tfs/DefaultCollection/
##[debug]project=82dd7cfd-01ea-46c0-81d1-b2c938508663
##[debug]queryType=Shared
##[debug]query=4904e58e-b07a-4f88-af47-99a3b0f37d5c
##[debug]emailAddresses=@abc.com
##[debug]Splitted Addresses: @abc.com
##[debug]Validating E-Mail address: "@abc.com"
##[debug]queryType=Shared
##[debug]SystemVssConnection auth param AccessToken = ***
##[debug]queryType=Shared
##[debug]SystemVssConnection=https://*****/tfs/DefaultCollection/
##[debug]queryType=Shared
##[debug]query=4904e58e-b07a-4f88-af47-99a3b0f37d5c
##[debug]task result: Failed
##[error]unable to get local issuer certificate
##[debug]Processed: ##vso[task.issue type=error;]unable to get local issuer certificate
##[debug]Processed: ##vso[task.complete result=Failed;]unable to get local issuer certificate

I have problems when I tried to create the Pipeline

Hi, how are you?

sorry to bother you, but when i want to create the Pipeline i need to select a repository

So, i don't know what i need to do

Thanks in advance
Fabian

##[error]Mail command failed: 530 5.7.57 Client not authenticated to send mail. [CH2PR18CA0034.namprd18.prod.outlook.com]

Hello I am getting authorization errors even if I put the username and password values,

##[error]Invalid login: 535 0.0.139 Authentication unsuccessful, the request did not meet the criteria to be authenticated successfully. Contact your administrator. [SN4P0051.namprd05.prod.outlook.com]

##[error]Mail command failed: 530 0.0.57 Client not authenticated to send mail. [CH2P34.namprd18.prod.outlook.com]

Do you have any idea what's the issue?

##[error]Mail command failed: 530 5.7.57 Client not authenticated to send mail. [BL1PR13CA0191.namprd13.prod.outlook.com]

Getting
##[error]Mail command failed: 530 5.7.57 Client not authenticated to send mail. [BL1PR13CA0191.namprd13.prod.outlook.com]
when trying to run pipeline? I set up the service connection with no authentication and authentication and both fail with similar areas. Trying to send query to outlook email.

Returning only 100 projects even if an organization contains more than 100 projects

Hello,

This extension works well with the organization having <=100 projects but it failed to serve the purpose if organization contains more than 100 projects.

In my case I do have around 250 projects under a single organization in azure devops so, if you modify the api parameters in such a way that it return all the projects that present under an organization then it would be a great help to solve this issue.

Thanks

[error]Result was null. Make sure <project> Build Service (<org>) has Read permissions on the Query.

I'm not sure what I'm missing. I've added Project Collection Build Service () user, Project Collection Adminstrators group, and the Project collection Build Service Accounts group to the queries security with Read only access. I also added the same user and groups to the Agent pools security that's being used for this pipeline using the Reader role....which I don't believe is necessary.

Issue since converting from original extension

Hello,

Since switching from the original extension we are seeing the following error when attempting to run the task:

##[error]Cannot read property 'path' of null

Is this something we should be including in the query?

[error]Cannot read property 'queryResultType' of null

Is this an issue with the query results or the something in the code when it references the devops api? Seems like the latter as I can run the query from TF in Excel just fine. Thanks for any future guidance.

I setup a simple query filtering based on the 'type' column on work items.
I setup a new pipeline that is only using this action and calling the mentioned work item query.