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

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

CodePudding user response：

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