azure 按主机名查询动态

7fyelxc5 于 2023-10-22 发布在其他

关注(0)|答案(1)|浏览(80)

我有一个日志分析表，其中有一个名为“UserLoggingInfo”的列，它存储每个服务器的用户日志信息（HostName），有时我会在每个服务器上获得一个用户（例如，bill和bob），有时没有用户（null），在某些情况下，多个用户登录到同一服务器（johndoe和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

在上面的截图中，我正确地得到了前两个记录，它显示了Bill和Bob，每个记录都分别返回了他们的用户名，空闲时间和登录时间，第三个记录是错误的，不应该返回，因为它没有值，最后一个记录虽然有两个有效的条目，但什么也没有返回。
正确的查询应该返回四条记录，显示每个主机名的用户名，空闲时间和登录时间，不包括空白，我确实尝试了几件事，但我不知道我需要做什么，我想要的是得到这样的东西：
| 主机名|用户名|IdleTime|登录时间|
| --|--|--|--|
| 服务器-01|法案|01：01：00| 2023-10-05T00：23：00.000000Z|
| 服务器-02|鲍勃|00：10：00| 2023-10-05T00：24：00.000000Z|
| 服务器-04| johndoe| 2.09：51：00| 2023-09-30T10：45：00.000000Z|
| 服务器-04|雅内多埃|时间00：36：00| 2023-10-05T07：23：00.000000Z|

Azure

来源：https://stackoverflow.com/questions/77247961/querying-dynamic-per-hostname

1条答案

按热度按时间

rqcrx0a61#

一个有点混乱的解决方案，可能会受益于大量的测试。尝试mv-expand拆分动态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)

赞(0）回复(0）举报 2023-10-22

我来回答

azure 按主机名查询动态

1条答案

相关问题

热门标签

最新问答