def wiql_query(self):
"""Executes WIQL query against devops project"""
wit_client = self.connection.clients.get_work_item_tracking_client()
#First query generates the WorkItems
work_items_query = """
SELECT [System.Title]
FROM WorkItems
WHERE [System.TeamProject] = '%s'
""" % str(self.project_name)
#Second query generates the links between work items in order to get the proper hierarchy
work_items_link_query = """
SELECT [System.Id], [System.WorkItemType]
FROM WorkItemLinks
WHERE ([System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward')
AND Target.[System.TeamProject] = '%s'
""" % str(self.project_name)
#If tag is supplied, filter based on the presence of the tag
if self.tag is not None:
work_items_query += "AND [System.Tags] CONTAINS '%s'" % str(
self.tag)
work_items_link_query += """
AND Target.[System.Tags] CONTAINS '%s'
ORDER BY [System.Id]
MODE (Recursive, ReturnMatchingChildren)
""" % str(self.tag)
#Otherwise, generate an ordering based on System.Id
else:
work_items_link_query += """
ORDER BY [System.Id]
MODE (Recursive, ReturnMatchingChildren)
"""
#submit the queries, and extract the work_items and their relations
wiql_works = wit_client.query_by_wiql(
Wiql(query=work_items_query)).work_items
wiql_links = wit_client.query_by_wiql(
Wiql(query=work_items_link_query)).work_item_relations
if wiql_works:
# WIQL query gives a WorkItemReference with ID only
# Get the corresponding WorkItem from id
work_items = [
wit_client.get_work_item(int(res.id)) for res in wiql_works
]
#Generate a mapping between work id and the output string to be printed
#This generates the lookup table when printing the messages
works = {}
for work_item in work_items:
work_item = work_item.as_dict()
item_id = work_item['id']
works[item_id] = self.get_fields_output(work_item)
#return the lookup table of strings, and the raw links table
return works, wiql_links
else:
return [], []
def existing_work_items(self) -> Iterator[WorkItem]:
filters = {}
for key in self.config.unique_fields:
if key == "System.TeamProject":
value = self.render(self.config.project)
else:
value = self.render(self.config.ado_fields[key])
filters[key.lower()] = value
valid_fields = get_valid_fields(
self.client, project=filters.get("system.teamproject"))
post_query_filter = {}
# WIQL (Work Item Query Language) is an SQL like query language that
# doesn't support query params, safe quoting, or any other SQL-injection
# protection mechanisms.
#
# As such, build the WIQL with a those fields we can pre-determine are
# "safe" and otherwise use post-query filtering.
parts = []
for k, v in filters.items():
# Only add pre-system approved fields to the query
if k not in valid_fields:
post_query_filter[k] = v
continue
# WIQL supports wrapping values in ' or " and escaping ' by doubling it
#
# For this System.Title: hi'there
# use this query fragment: [System.Title] = 'hi''there'
#
# For this System.Title: hi"there
# use this query fragment: [System.Title] = 'hi"there'
#
# For this System.Title: hi'"there
# use this query fragment: [System.Title] = 'hi''"there'
SINGLE = "'"
parts.append("[%s] = '%s'" %
(k, v.replace(SINGLE, SINGLE + SINGLE)))
query = "select [System.Id] from WorkItems"
if parts:
query += " where " + " AND ".join(parts)
wiql = Wiql(query=query)
for entry in self.client.query_by_wiql(wiql).work_items:
item = self.client.get_work_item(entry.id, expand="Fields")
lowered_fields = {
x.lower(): str(y)
for (x, y) in item.fields.items()
}
if post_query_filter and not all([
k.lower() in lowered_fields
and lowered_fields[k.lower()] == v
for (k, v) in post_query_filter.items()
]):
continue
yield item
def main() -> None:
parser = argparse.ArgumentParser(
formatter_class=argparse.ArgumentDefaultsHelpFormatter
)
parser.add_argument(
"--url",
default="https://dev.azure.com/your-instance-name",
help="ADO Instance URL",
)
parser.add_argument("--areapath", default="OneFuzz-Test-Project", help="areapath")
parser.add_argument("--title", help="work item title")
parser.add_argument(
"--expected", type=int, help="expected number of work items", default=1
)
group = parser.add_mutually_exclusive_group()
group.add_argument("--pat", default=os.environ.get("ADO_PAT"), help="ADO PAT")
group.add_argument(
"--token",
default=os.environ.get("SYSTEM_ACCESSTOKEN"),
help="ADO system access token",
)
args = parser.parse_args()
if args.pat:
creds = BasicAuthentication("PAT", args.pat)
elif args.token:
creds = BasicTokenAuthentication(token={"access_token": args.token})
else:
print("either --pat or --token is required")
sys.exit(1)
connection = Connection(base_url=args.url, creds=creds)
client = connection.clients_v6_0.get_work_item_tracking_client()
query_items = ["[System.AreaPath] = '%s'" % args.areapath]
if args.title:
query_items.append("[System.Title] = '%s'" % args.title)
# Build an SQL-like query (WIQL - Work Item Query Language) using user
# provided args to a user provided ADO instance. In CICD, this ends up
# unconditionally trusting system generated reports.
query = "select [System.Id] from WorkItems where " + " AND ".join( # nosec
query_items
)
work_items = []
for _ in range(60):
work_items = client.query_by_wiql(Wiql(query=query)).work_items
if len(work_items) >= args.expected:
break
time.sleep(2)
print("trying again", flush=True)
assert (
len(work_items) >= args.expected
), "unexpected work items (got %d, expected at least %d)" % (
len(work_items),
args.expected,
)
def wiql_query(context,
top_n=None,
program_only=None,
fields_array=None,
as_of_date=None):
if as_of_date is not None:
as_of_date = datetime.datetime.strptime(str(as_of_date),
'%Y-%m-%d %H:%M:%S')
if fields_array is None:
fields_array = [
"System.WorkItemType", "System.Title", "System.State",
"System.AreaPath", "System.IterationPath", "Custom.GreenStartDate",
"Custom.RedStartDate", "Custom.GreenEndDate", "Custom.RedEndDate",
"Custom.DeliverableType", "Custom.ProgressPercentageComplete",
"System.BoardColumn", "System.ChangedDate", "System.State",
"System.Tags"
]
wit_client = context.connection.clients.get_work_item_tracking_client()
if not program_only:
query = """
select [System.Id]
from WorkItems
order by [System.ChangedDate] desc"""
else:
query = """
select [System.Id]
from WorkItems
Where [System.Tags] Contains "Prog Deliverable L1" or [System.Tags] Contains "Prog Deliverable L2"
order by [System.ChangedDate] desc"""
wiql = Wiql(query)
# We limit number of results is top_n is supplied
if top_n is None:
wiql_results = wit_client.query_by_wiql(wiql).work_items
else:
wiql_results = wit_client.query_by_wiql(wiql, top=top_n).work_items
emit("Extract Count: {0}".format(len(wiql_results)))
if wiql_results:
# WIQL query gives a WorkItemReference with ID only
# => we get the corresponding WorkItem from id
work_items = (wit_client.get_work_item(int(res.id),
fields=fields_array,
as_of=as_of_date)
for res in wiql_results)
return work_items
else:
return []
def main():
# extract arguments
parser = argparse.ArgumentParser(
description='push completed status from parent work items to children')
parser.add_argument("-o",
"--org",
required=True,
dest="url",
help="Azure DevOps Organization URL")
parser.add_argument("-p",
"--project",
required=True,
dest="project",
help="Azure DevOps Project")
parser.add_argument("-t",
"--pat",
required=True,
dest="pat",
help="Azure DevOps Personal Access Token")
parser.add_argument(
"--parent-type",
required=True,
dest="parent_type",
help="work item parent type to filter for (Bug,Feature,...)")
parser.add_argument(
"--child-type",
required=True,
dest="child_type",
help=
"work item child type to filter for (Task,Product Backlog Item,...)")
parser.add_argument(
"--age",
required=False,
dest="age",
default=120,
type=int,
help="age in days when last change of work item happened")
parser.add_argument("--update",
required=False,
action='store_true',
dest="update",
help="commit update to Azure DevOps")
args = parser.parse_args()
# create a connection to the org
credentials = BasicAuthentication('', args.pat)
connection = Connection(base_url=args.url, creds=credentials)
# get a client
wit_client = connection.clients.get_work_item_tracking_client()
# determine potential Completed+Removed states for the parent/child work item type
wi_types = wit_client.get_work_item_types(args.project)
parent_completed_states = [
s.name
for s in [t for t in wi_types if t.name == args.parent_type][0].states
if s.category == 'Completed'
]
parent_removed_states = [
s.name
for s in [t for t in wi_types if t.name == args.parent_type][0].states
if s.category == 'Removed'
]
child_completed_states = [
s.name
for s in [t for t in wi_types if t.name == args.child_type][0].states
if s.category == 'Completed'
]
child_removed_states = [
s.name
for s in [t for t in wi_types if t.name == args.child_type][0].states
if s.category == 'Removed'
]
# query relations
wiql = Wiql(query=f"""SELECT *
FROM workitemLinks
WHERE [Source].[System.TeamProject] = '{args.project}'
AND [Source].[System.WorkItemType] = '{args.parent_type}'
AND [Target].[System.WorkItemType] = '{args.child_type}'
AND [Source].[System.ChangedDate] >= @today - {args.age}
AND [System.Links.LinkType] = 'Child'
MODE (MustContain)
""")
wi_relations = wit_client.query_by_wiql(wiql, top=1000).work_item_relations
print(f'Results: {len(wi_relations)}')
# process relations
if wi_relations:
for wir in wi_relations:
if wir.source and wir.target:
# for each source (parent) / target (child) pair check completed state
wis = wit_client.get_work_item(wir.source.id)
wit = wit_client.get_work_item(wir.target.id)
if wis.fields[
'System.State'] in parent_completed_states or wis.fields[
'System.State'] in parent_removed_states:
print(
f"{wis.fields['System.WorkItemType']} {wir.source.id} ({wis.fields['System.State']}) -> {wit.fields['System.WorkItemType']} {wir.target.id} ({wit.fields['System.State']})"
)
operations = []
if wis.fields[
'System.State'] in parent_completed_states and not wit.fields[
'System.State'] in child_completed_states and not wit.fields[
'System.State'] in child_removed_states:
print(f" =>{child_completed_states[0]}")
operations.append(
JsonPatchOperation(
op='replace',
path=f'/fields/System.State',
value=child_completed_states[0]))
if wis.fields[
'System.State'] in parent_removed_states and not wit.fields[
'System.State'] in child_completed_states and not wit.fields[
'System.State'] in child_removed_states:
print(f" =>{child_removed_states[0]}")
operations.append(
JsonPatchOperation(op='replace',
path=f'/fields/System.State',
value=child_removed_states[0]))
if len(operations) > 0 and args.update:
resp = wit_client.update_work_item(document=operations,
id=wir.target.id)
print(resp)
def main():
# extract arguments
parser = argparse.ArgumentParser(
description='push characteristics from parent work items to children')
parser.add_argument("-o",
"--org",
required=True,
dest="url",
help="Azure DevOps Organization URL")
parser.add_argument("-p",
"--project",
required=True,
dest="project",
help="Azure DevOps Project")
parser.add_argument("-t",
"--pat",
required=True,
dest="pat",
help="Azure DevOps Personal Access Token")
parser.add_argument(
"--parent-type",
required=True,
dest="parent_type",
help="work item parent type to filter for (Bug,Feature,...)")
parser.add_argument(
"--child-type",
required=True,
dest="child_type",
help=
"work item child type to filter for (Task,Product Backlog Item,...)")
parser.add_argument(
"--field-list",
required=True,
dest="field_list",
help=
"comma separated list of field names to compare e.g. System.AreaPath,System.IterationPath"
)
parser.add_argument(
"--age",
required=False,
dest="age",
default=120,
type=int,
help="age in days when last change of work item happened")
parser.add_argument("--update",
required=False,
action='store_true',
dest="update",
help="commit update to Azure DevOps")
args = parser.parse_args()
# create a connection to the org
credentials = BasicAuthentication('', args.pat)
connection = Connection(base_url=args.url, creds=credentials)
# get a client
wit_client = connection.clients.get_work_item_tracking_client()
# query relations
wiql = Wiql(query=f"""SELECT *
FROM workitemLinks
WHERE [Source].[System.TeamProject] = '{args.project}'
AND [Source].[System.WorkItemType] = '{args.parent_type}'
AND [Target].[System.WorkItemType] = '{args.child_type}'
AND [Source].[System.ChangedDate] >= @today - {args.age}
AND [System.Links.LinkType] = 'Child'
MODE (MustContain)
""")
wi_relations = wit_client.query_by_wiql(wiql, top=1000).work_item_relations
print(f'Results: {len(wi_relations)}')
# process relations
if wi_relations:
fields_to_check = args.field_list.split(',')
for wir in wi_relations:
if wir.source and wir.target:
# for each source (parent) / target (child) pair check field list
wis = wit_client.get_work_item(wir.source.id)
wit = wit_client.get_work_item(wir.target.id)
print(
f"{wis.fields['System.WorkItemType']} {wir.source.id} -> {wit.fields['System.WorkItemType']} {wir.target.id}"
)
operations = []
for field in fields_to_check:
if not field in wis.fields or not field in wit.fields:
raise ValueError(f"field {field} unknown")
elif wis.fields[field] != wit.fields[field]:
print(f' =>{field}')
operations.append(
JsonPatchOperation(op='replace',
path=f'/fields/{field}',
value=wis.fields[field]))
if len(operations) > 0 and args.update:
resp = wit_client.update_work_item(document=operations,
id=wir.target.id)
print(resp)
def get_azure_devops_work_items_from_wiql_query(self,
wiql_query: str) -> []:
query_wiql = Wiql(query=wiql_query)
query_result = self._azure_devops_client.query_wiql(
query_wiql).work_items
return self.map_work_item_references_to_dtos(query_result)