Microsoft Azure Query Basics

This video is the first in a series of basic training on how to create query's in Azure DevOps

[@muhammadn82]

Thanks man. It did not give me exactly what i want but it gave me the idea and i done the ticket list by month, week etc. I appreciate your effort.

[@emmanuelajijo3332]

I can’t believe I’m seeing this 2yrs late, but for a first Video this is amazing

[@user-tf3ff4qh9q]

Excellent tutorial for absolute beginners!!

[@adessorecords]

Thanks for this, really helpful and helped me get what I needed!

[@19ang90]

Could you show how to extract an entire Epic with select Features?

[@VishwaVlogskk]

Great Work.

[@miroslavstoilov3600]

Hello! Great video! How can i make mu querie to catch only the range of user stories from the last 7 daye, not older? and then to present it as a chart?

[@ramsign3]

Hi, I am trying to write a query which will return all tickets with recent comments on tickets

[@ivanu83]

Hi @malpert1000, thanks for the video. Is it possible to do more complex queries like Condition 1 AND Condition 2 AND (Condition 3 OR Condition 4) ?? Thank you!

[@ivanu83]

It is not very friendly, but I found the way to do it: Condition 1 AND Condition 2 AND Condition 3 OR Condition 1 AND Condition 2 AND Condition 4 Is there an easier way to do it?

[@commonman4798]

I want to add new Filed Item into the Filed Dropdown

[@anoopma3733]

I am trying to extract Test case execution details of every Static Test Suite but unable to would you be able to help me out

[@gustavoboese]

Hey Virtual PM I have a problem here as I can't get all tasks is under an Epic in my actual Iteration Path Is it possible as I have Epics -> Releases -> Features -> User Stories -> Tasks?

[@malpert1000]

Hi Gustavo. You can manually add any work item as a child to a parent but this goes against the way MS developed Azure to work. So it will not track correctly in the sprint view. Is releases a work item you created manually?

[@cocofortin]

Hi there! I wanted to know if there is any option where I can use an existing query in a new query, For ex: I created one generic query, I want to use that in other queries.

[@malpert1000]

Hi. Yes there is. I bring up the query that I need to start with, then I make modifications for the new query. Run the query to make sure it works, finally click the 'Save as' button that is right next to the "Save" button. Now rename the new query. I do suggest to first locate the "save as' button first before you try it. Also it does not hurt to practice this technique on some basic queries that are easy to recreate first. Hope this helps 🙏

[@cocofortin]

@@malpert1000Apologies, Let me explain it right. For ex: I created a query that fetches all work items for Sprint 45. The Query will be: Field* Operator Value Iteration Path = Sprint 45 Let's give this a name "Sprint_Query" Now, Let's say I am creating 20 completely new queries from scratch, and I want to use above already created query in these 20 new queries (basically import query parameters by searching existing query name). Reason of doing is below. Now, If I want to see results of Sprint 44, I won't have to go to those 20 queries to change the parameter, rather I will only change my query "Sprint_Query" and it will reflect in all other queries automatically. Hope this explains.

[@malpert1000]

@@cocofortin I understand. As far as I know, the query ui doesn't offer the ability to make a call to a different query and use that first half of the query in a new query. That would be cool if it did. I know in the test area you have a concept of creating shared steps, but I have not seen this in the query area of devops

[@saunaknandi1814]

@@malpert1000 For which company you are working?

[@narangaman6]

Hey Mate Here's my problem statement, just wondering if you could help : I want to be able to see which sprints a story has been part of (after being dragged from one sprint to the other because of not being achieved/closed) . Since Azure devops doesn't let me have a view of the sprint report and retains this bit of history in the work item unlike Jira, the only way I could think of was querying by history and searching for stories which have a "changed iteration path" in the history and then use it to prepare a dashboard. But I am unable to do that. Can you help? Cheers !

[@malpert1000]

Hey Aman.. I currently I do not have an idea on how to show this cause I have not tried it. I will look into this and hopefully I will have an answer if you are interested

[@malpert1000]

Aman .. I was looking and it looks like Azure only support these field names when using the 'History' field: Changed By, Change Date, Closed Date, Created Date, History, Resolved Date, Rev, Start Change Date, Test Suite Audit, Watermark. Non of these will give us the Changes Iteration Path that is in the History. I think this is because we are suppose to close all user stories at the end of a sprint or start new ones that are related. See docs.microsoft.com/en-us/azure/devops/boards/queries/history-and-auditing?view=azure-devops&tabs=browser

[@5530alex]

​@@malpert1000 I've had the same problem to track stories that are not complete in a prior sprint. I tag them with the sprint # they have been worked. By doing this I know how many sprints it actually takes to complete a user story and (or) bug without having to dig through the history of iteration path changes. A simple query by the tag can tell me what sprints the user story or bug has been worked on.

[@luchogz8]

@@malpert1000 Hey all. And if you create a tag for each sprint and then tag the US with the tags corresponding to the sprint where it has been? Would that be an option?

[@sameerkhan-ff8xg]

Hi, How u added the percentage completion column in dasboard.

[@malpert1000]

Hi Sameer. There are a couple of ways. One way is to add a custom field in the work item type that has percentages. This percentage field will need be updated manually. Or another option is to add a 'Add a rollup column' to the backlog. In this option the system will give you percentage complete that is calculated by the system. Steps to turn this on are Go to Backlog> select Column Options> select 'Add a rollup column'> select 'From quick list'> then Progress by all Work Items> click OK. This will then give you a column called Progress by all Work Items and that column will have system generate percentages. I would always suggest to go with what Azure offers and not customizing Azure with special fields. :) I hope this helps

[@sameerkhan-ff8xg]

@@malpert1000 thank you so much . One more qs i have if you could help .I am using power bi to show all the test case data .i have one oodata query( query1 )which gives me test case id test plan id test suite id Another odata query i have( query2)which gives me testrun id. I donot find any common filed to link query 1 with query 2 .Can u suggest me how can i do that . I need to show if i have test case1 which was run 10 times so for that particular test case 10 different run ids are created i wanted to show this in my power bi report that test case 1 ran 10 times .

[@malpert1000]

Hi @@sameerkhan-ff8xg unfortunately I do not have experience on Power BI integration. This page talks about fields that you can query on. see Other Fields. I hope this might help. dev.azure.com/LamarCISDInfoServ/LamarIS/_queries/query-edit/abc6cec2-253d-4249-95fd-22e50bec352b/

[@malpert1000]

@@sameerkhan-ff8xg here is another site that might help. docs.microsoft.com/en-us/rest/api/azure/devops/test/Results/Get?view=azure-devops-rest-5.0&viewFallbackFrom=vsts-rest-5.0

[@sameerkhan-ff8xg]

@@malpert1000 thank u so very much ..