Automate JSON substrings with semi-random data to KQL?

user_19393776 0
Viewed 74

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

grafana kql azure-devops azure json
2 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
user_78618070

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
user_63364790
Related