Azure Data Explorer vs JSON File

[MrDrSushi]

While working with PowerBI Desktop, I can for example, get data from a JSON file and fully utilize dynamic columns through expanding them from list to record, and transforming them into real useful data like in the example below:

My question, why can I do the same with data from a Azure Log Analytics table? I did import/direct through Azure Data Explorer (Kusto) source and the data looks like plain strings:

What can I do to transform this into real data like in the JSON example above? Is it even possible?

[lbendlin]

While working with PowerBI Desktop, I can for example, get data from a JSON file and fully utilize dynamic columns through expanding them from list to record, and transforming them into real useful data 

BIG caveat.  You cannot utilize dynamic columns, the result of the Power Query needs to be a static layout, unless you want to wake up the "Evaluating..."  monster.

Your sample data looks like you can use JSON.Parse() against it.

[MrDrSushi]

Hey Ibendin, thanks for the response, that is very helpful, Just one question, that might be very helpful to put me in the way to solve my problem, could you please show me an example on how to do the convertion like you said above?

[lbendlin]

Please post some sample data in usable format (not as a screenshot)

[MrDrSushi]

This is a sample from my data:

[
    {
        "TimeGenerated": "2023-09-12T01:01:03.9561744Z",
        "EnvironmentName": "PROJECTS",
        "EnvironmentType": "PRODUCTION",
        "HostName": "SERVER-001",
        "IPAddress": "10.240.148.209",
        "SharePointVersion": "Microsoft SharePoint Server 2019",
        "SharePointRole": "ApplicationWithSearch",
        "SharePointBuildNumber": "16.0.10398.20000",
        "SharePointCrawls": [
            {
                "Name": "Local SharePoint sites",
                "Successes": 0,
                "Warnings": 0,
                "Errors": 0,
                "Deletions": 0,
                "Started": "",
                "Completed": "2023-05-09T03:58:02.0000000Z",
                "State": "Idle"
            },
            {
                "Name": "Alfa",
                "Successes": 10553,
                "Warnings": 1622,
                "Errors": 1,
                "Deletions": 8,
                "Started": "2023-09-12T00:55:24.0000000Z",
                "Completed": "2023-09-12T00:56:54.0000000Z",
                "State": "Idle"
            },
            {
                "Name": "Beta",
                "Successes": 7278190,
                "Warnings": 389958,
                "Errors": 5985,
                "Deletions": 94569,
                "Started": "2023-09-12T00:55:24.0000000Z",
                "Completed": "",
                "State": "CrawlingIncremental"
            }
        ],
        "SharePointServices": [
            {
                "Service": "Access Database Service 2010",
                "Status": "Disabled"
            },
            {
                "Service": "Access Services",
                "Status": "Disabled"
            }
        ],
        "OSName": "Microsoft Windows Server 2019 Datacenter",
        "OSVersion": "10.0.17763",
        "OSMemory": [{
            "TotalGB": 32,
            "FreeGB": 21.51,
            "PctFree": 67.21
        }],
        "OSDriveC": [{
            "HealthStatus": "Healthy",
            "SizeGB": 126.45,
            "SizeRemainingGB": 92.73
        }],
        "OSDriveD": [{
            "HealthStatus": "Healthy",
            "SizeGB": 127.98,
            "SizeRemainingGB": 98.22
        }],
        "OSDriveE": [{
            "HealthStatus": "Healthy",
            "SizeGB": 127.98,
            "SizeRemainingGB": 96.75
        }],
        "OSDriveF": [{
            "HealthStatus": "Healthy",
            "SizeGB": 127.98,
            "SizeRemainingGB": 96.75
        }],
        "OSDriveG": [{
            "HealthStatus": "Healthy",
            "SizeGB": 510.98,
            "SizeRemainingGB": 451.29
        }],
        "OSDriveH": [{
            "HealthStatus": "Healthy",
            "SizeGB": 4094.98,
            "SizeRemainingGB": 4094.71
        }],
        "OSUpTime": "3.02:58:23.6226197",
        "OSLastBootUpTime": "2023-09-08T22:03:17.678942Z",
        "OSLoggedOnUsers": [            
            {
                "UserName": "albertg",
                "SessionName": "",
                "State": "Disc",
                "IdleTime": "03:51:00",
                "LogonTime": "2023-10-02T18:01:00.0000000Z"
            },
            {
                "UserName": "johndoe",
                "SessionName": "rdp-tcp#93",
                "State": "Active",
                "IdleTime": "00:00:00",
                "LogonTime": "2023-10-03T01:17:00.0000000Z"
            }
        ],
        "OSServices": [
            {
                "DisplayName": "IIS",
                "Status": "Stopped",
                "Name": "AdtAgent"
            },
            {
                "DisplayName": "SPTimer",
                "Status": "Stopped",
                "Name": "AJRouter"
            }
        ],
        "OSEvents": [
            {
                "Created": "2023-10-03T01:11:01.4587708Z",
                "Level": "Error",
                "Event": "System",
                "Id": 10021,
                "Category": null,
                "Source": "DistributedCOM",
                "Message": "Component Services administrative tool"
            },
            {
                "Created": "2023-10-03T01:11:14.3886952Z",
                "Level": "Error",
                "Event": "System",
                "Id": 10021,
                "Category": null,
                "Source": "Defender",
                "Message": "Update Failed"
            }
        ],
        "OSWindowsUpdates": [
            {
                "Date": "2023-10-03T00:40:59.0000000Z",
                "Status": "Succeeded",
                "Title": "Defender Antivirus - KB2267602",
                "Product": "Microsoft Defender Antivirus",
                "SupportUrl": "https://go.microsoft.com/fwlink/?LinkId=52661"
            },
            {
                "Date": "2023-10-03T00:40:56.0000000Z",
                "Status": "Failed",
                "Title": "Defender Antivirus - KB2267603",
                "Product": "Microsoft Defender Antivirus",
                "SupportUrl": "https://go.microsoft.com/fwlink/?LinkId=52661"
            }
        ],
        "OSHotFixesInstalled": [
            {
              "Description": "Update",
              "HotFixID": "KB5029331",
              "InstalledOn": "2023-08-24T00:00:00.0000000",
              "InstalledBy": "NT AUTHORITY\\SYSTEM"
            },
            {
              "Description": "Update",
              "HotFixID": "KB5018506",
              "InstalledOn": "2022-11-11T00:00:00.0000000",
              "InstalledBy": "NT AUTHORITY\\SYSTEM"
            }
          ]
    }
]