Querying Dynamic Per HostName

Question

I have a Log Analytics Table with a column called "UserLoggingInfo", it stores users logging information per server (HostName), sometimes I get one user per server (for example, bill and bob), sometimes no users (null), and in some cases multiple users logged to the same server (johndoe, and janedone):

datatable(timegenerated:datetime, hostname:string, userlogginginfo:dynamic)
[
    '2023-10-05T08:00:06', 'server-01', dynamic({
        "UserName": "bill",
        "IdleTime": "01:01:00",
        "LogonTime": "2023-10-05T00:23:00.0000000Z"
    }),
    '2023-10-05T08:00:17', 'server-02', dynamic({
        "UserName": "bob",
        "IdleTime": "00:10:00",
        "LogonTime": "2023-10-05T00:24:00.0000000Z"
    }),
    '2023-10-05T08:00:28', 'server-03', dynamic(
        null
    ),
    '2023-10-05T08:00:29', 'server-04', dynamic([
        {
            "UserName": "johndoe",
            "IdleTime": "2.09:51:00",
            "LogonTime": "2023-09-30T10:45:00.0000000Z"
        },
        {
            "UserName": "janedoe",
            "IdleTime": "00:36:00",
            "LogonTime": "2023-10-05T07:23:00.0000000Z"
        }
    ])
]
| evaluate bag_unpack(userlogginginfo)
| extend UserName = userlogginginfo.UserName, IdleTime = userlogginginfo.IdleTime, LogonTime = userlogginginfo.LogonTime

In the screenshot above I get the first two records correctly, it shows Bill and Bob, each one is returned with their UserName, IdleTime, and LogonTime respectively, the third record is wrong and shouldn't be returned because it has no value, and the last record returns nothing although it has two valid entries.

The correct query should return four records, showing UserName, IdleTime, and LogonTime, per HostName, excluding blanks, I did try several things but I can't figure out what I need to do, all I want is to get to something like this:

HostName	UserName	IdleTime	LogonTime
server-01	bill	01:01:00	2023-10-05T00:23:00.0000000Z
server-02	bob	00:10:00	2023-10-05T00:24:00.0000000Z
server-04	johndoe	2.09:51:00	2023-09-30T10:45:00.0000000Z
server-04	janedoe	00:36:00	2023-10-05T07:23:00.0000000Z

Answer 1

A bit of a messy solution, and would probably benefit from plenty of testing. Try mv-expand to split up the dynamic userlogginginfo.

let T = datatable(timegenerated:datetime, hostname:string, userlogginginfo:dynamic)
[
    '2023-10-05T08:00:06', 'server-01', dynamic({
        "UserName": "bill",
        "IdleTime": "01:01:00",
        "LogonTime": "2023-10-05T00:23:00.0000000Z"
    }),
    '2023-10-05T08:00:17', 'server-02', dynamic({
        "UserName": "bob",
        "IdleTime": "00:10:00",
        "LogonTime": "2023-10-05T00:24:00.0000000Z"
    }),
    '2023-10-05T08:00:28', 'server-03', dynamic(
        null
    ),
    '2023-10-05T08:00:29', 'server-04', dynamic([
        {
            "UserName": "johndoe",
            "IdleTime": "2.09:51:00",
            "LogonTime": "2023-09-30T10:45:00.0000000Z"
        },
        {
            "UserName": "janedoe",
            "IdleTime": "00:36:00",
            "LogonTime": "2023-10-05T07:23:00.0000000Z"
        }
    ])
];
let SingleT = T
| mv-expand userlogginginfo
| extend UserName = userlogginginfo.UserName, IdleTime = userlogginginfo.IdleTime, LogonTime = userlogginginfo.LogonTime
| summarize make_set(UserName), make_set(IdleTime), make_set(LogonTime) by hostname, timegenerated
| where array_length(set_LogonTime) == 1
| summarize by hostname, timegenerated, UserName = tostring(set_UserName[0]), IdleTime = tostring(set_IdleTime[0]), LogonTime = tostring(set_LogonTime[0]);
let MultipleT = T
| mv-expand userlogginginfo
| extend UserName = userlogginginfo.UserName, IdleTime = userlogginginfo.IdleTime, LogonTime = userlogginginfo.LogonTime
| summarize make_set(UserName), make_set(IdleTime), make_set(LogonTime) by hostname, timegenerated
| where array_length(set_LogonTime) >= 2
| summarize by Key = strcat(hostname, '-', timegenerated)
| join kind=leftouter (
T
| extend Key = strcat(hostname, '-', timegenerated)
) on Key
| project hostname, timegenerated, userlogginginfo
| mv-expand userlogginginfo
| extend UserName = userlogginginfo.UserName, IdleTime = userlogginginfo.IdleTime, LogonTime = userlogginginfo.LogonTime
| summarize by hostname, timegenerated, UserName = tostring(UserName), IdleTime = tostring(IdleTime), LogonTime = tostring(LogonTime);
union (SingleT), (MultipleT)