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

HudsonCarpenter
June 24, 2022
150 views
0 votes
2 Answers

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

Answers

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

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

Please signup or login to give your own answer.

Click here to cancel reply.

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

Answers

Option 1

Option 2