0

So currently I have the following simple code to just build out a table for myself and look at data azure_devops_work_item_events_CL

| summarize any(WorkItemType_s, TeamProject_s, Title_s, AssignedTo_s, State_s, Reason_s) by WorkItemId_d, Relations_s

| order by WorkItemId_d desc

My problem is the Relations_s data is pulling a super long string that's different in length pretty much every instance although the format is the same for each one ex:

[ { "rel": "System.LinkTypes.Hierarchy-Reverse", "url": "https://dev.azure.com/xxxx/d88804f3-b064-4489-9705/_apis/wit/workItems/240541", "attributes": { "isLocked": false, "name": "Parent" } } ], [ { "rel": "System.LinkTypes.Hierarchy-Reverse", "url": "https://dev.azure.com/xxxx/d88804f3-b064-4489-9705/_apis/wit/workItems/240241", "attributes": { "isLocked": false, "name": "Child" } } ]

It follows that format but at times will be as many as 20 of those long strings. I have tried parsing but have been unsuccessful so far. Is there any way for me to scan Relations_s in order to pull the work item number (number at end of url that is essentially random) and name ("Child", "Parent" or whatever name appears in that spot) and have it output into a table that would be like

Child Parent
240241 240541

Or at the very least get me something like this

Item Name
240241 Child
240541 Parent
Daniel Mann
  • 57,011
  • 13
  • 100
  • 120
Hudson Carpenter
  • 39
  • 4
  • @Daniel Mann, it seems you have not read the question. – David דודו Markovitz Jun 23 '22 at 15:00
  • @DavidדודוMarkovitz It's certainly possible I misunderstood the question. Could you elaborate about how this question isn't about parsing JSON in KQL? – Daniel Mann Jun 23 '22 at 15:21
  • It's definitely about parsing JSON in KQL, but the scenario here much more completed than in the post you linked to. There is no chance the OP will solve it based on the linked post. – David דודו Markovitz Jun 23 '22 at 15:26
  • @DanielMann I'm an intern, David is 100% correct I need all the help I can get here – Hudson Carpenter Jun 23 '22 at 15:31
  • Every JSON parse i've seen people are using predetermined specific numbers in their query to pull numbers from a JSON, problem is the numbers are different in every situation in mine and there are sometimes 1 or 2 sets of numbers and other times 8, 10, even 30 sets of numbers, it varies widely, the only consistent thing is the format in which the data in the json is presented, I somehow need to build a loop to scan it and pull the data needed if that makes sense – Hudson Carpenter Jun 23 '22 at 15:33
  • Please supply data sample as a datatable (run a query that returns the data sample, right click on the result and choose "copy as datatable") – David דודו Markovitz Jun 24 '22 at 11:38

0 Answers0