Question 105870 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – Automate JSON substrings with semi-random data to KQL?

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:

Relations_s
[ { "rel": "System.LinkTypes.Hierarchy-Reverse", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/197138", "attributes": { "isLocked": false, "name": "Parent" } } ]
[ { "rel": "System.LinkTypes.Hierarchy-Reverse", "url": "https://dev.azure.com/xxxx/d88804f3-b064-4489-9705/_apis/wit/workItems/234449", "attributes": { "isLocked": false, "name": "Parent" } } ]
[ { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/247970", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/242838", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/242835", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/246163", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/234839", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/229566", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/228347", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/240648", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/234833", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/240647", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/242837", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/234803", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/234801", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/246352", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/242839", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/234834", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/234838", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/242836", "attributes": { "isLocked": false, "name": "Child" } }, { "rel": "System.LinkTypes.Hierarchy-Reverse", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/218335", "attributes": { "isLocked": false, "name": "Parent" } }, { "rel": "System.LinkTypes.Hierarchy-Forward", "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/234832", "attributes": { "isLocked": false, "name": "Child" } } ]

This is a small sample of what my table looks like when I query it. I’m completely lost on how I can parse the table in such a way that I’m able to automate pulling the number at the end of every link and the name portion at the end of each string. Especially when some parts of the table have 40 strings in 1. Note all the data is in the form of a JSON if that impacts this at all. The end result needs to look something like this

Child Parent
240241 240541

Or this

Item Link
240241 Child
240541 Parent

Apologies for the formatting I can’t get the massive data set to properly format into a table

Tags:

2

Answers


  1. 0

    If I understood your description correctly, you could try the following, using the mv-apply and parse operators:

    datatable(d:dynamic)
[
    dynamic([
    {
        "rel": "System.LinkTypes.Hierarchy-Reverse",
        "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/197138",
        "attributes": {
            "isLocked": false,
            "name": "Parent"
        }
    }
]), dynamic([
    {
        "rel": "System.LinkTypes.Hierarchy-Reverse",
        "url": "https://dev.azure.com/xxxx/d88804f3-b064-4489-9705/_apis/wit/workItems/234449",
        "attributes": {
            "isLocked": false,
            "name": "Parent"
        }
    }
]), dynamic([
    {
        "rel": "System.LinkTypes.Hierarchy-Forward",
        "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/247970",
        "attributes": {
            "isLocked": false,
            "name": "Child"
        }
    },
    {
        "rel": "System.LinkTypes.Hierarchy-Forward",
        "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/242838",
        "attributes": {
            "isLocked": false,
            "name": "Child"
        }
    },
    {
        "rel": "System.LinkTypes.Hierarchy-Forward",
        "url": "https://dev.azure.com/xxxx/a69ffa35-e99f-42ea-a254/_apis/wit/workItems/242835",
        "attributes": {
            "isLocked": false,
            "name": "Child"
        }
    }
]),
]
| mv-apply d on (
    parse d with * "/workItems/" item:long *
    | extend link = tostring(d.attributes.name)
)
| project item, link
    item link
    197138 Parent
    234449 Parent
    247970 Child
    242838 Child
    242835 Child
    Login or Signup to reply.
  2. 0

    According to its name Relations_s is probably a string, therefore needs conversion to JSON (by using todynamic()).

    Option 1
    azure_devops_work_item_events_CL
|   mv-apply todynamic(Relations_s) on
    (
            parse       Relations_s.url with * "/" Item:long
        |   project     Item
                       ,Link = Relations_s.attributes.name
        |   summarize   Parent   = take_anyif   (Item ,Link == "Parent")
                       ,Children = make_list_if (Item ,Link == "Child")
    )
    Parent Children
    197138 []
    234449 []
    218335 [247970,242838,242835,246163,234839,229566,228347,240648,234833,240647,242837,234803,234801,246352,242839,234834,234838,242836,234832]
    Option 2
    azure_devops_work_item_events_CL
|   mv-apply todynamic(Relations_s) on
    (
            parse       Relations_s.url with * "/" Item:long
        |   project     Item
                       ,Link = Relations_s.attributes.name
        |   summarize   Parent   = take_anyif   (Item ,Link == "Parent")
                       ,Children = make_list_if (Item ,Link == "Child")
        |   mv-expand   Children = iff(array_length(Children) == 0, dynamic(null), Children) to typeof(long)
    )
    Parent Children
    197138
    234449
    218335 247970
    218335 242838
    218335 242835
    218335 246163
    218335 234839
    218335 229566
    218335 228347
    218335 240648
    218335 234833
    218335 240647
    218335 242837
    218335 234803
    218335 234801
    218335 246352
    218335 242839
    218335 234834
    218335 234838
    218335 242836
    218335 234832
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search