问题

I need help to pull out data for first and last activity for a specific user. For example to see if the first activity was Microsoft teams and when. And when was the last activity and which one

我需要帮助提取特定用户的第一次和最后一次活动数据。例如，查看第一次活动是否是Microsoft Teams以及何时发生的。以及最后一次活动是什么以及何时发生的。

I tried this and it gives all of the activity. I can't figure out the way so that the query would project only one first and last activity. The actual ones.

我尝试了这个，但它会返回所有活动。我无法弄清楚如何查询只返回第一次和最后一次活动。实际的那些。

OfficeActivity
| where UserId == 'UserId'
| summarize FirstActivity = min(TimeGenerated), LastActivity = max(TimeGenerated) by TimeGenerated, Application, Operation
| join
(
OfficeActivity
| summarize FirstActivity2 = min(TimeGenerated), LastActivity2 = max(TimeGenerated) by TimeGenerated, Application, OfficeWorkload
)
on TimeGenerated
| project FirstActivity, LastActivity, Application, OfficeWorkload, Operation
| sort by FirstActivity asc

办公活动
| 其中 UserId == 'UserId'
| 汇总 FirstActivity = min(TimeGenerated)，LastActivity = max(TimeGenerated) 按 TimeGenerated，Application，Operation
| 加入
(
办公活动
| 汇总 FirstActivity2 = min(TimeGenerated)，LastActivity2 = max(TimeGenerated) 按 TimeGenerated，Application，OfficeWorkload
)
在 TimeGenerated 上
| 投影 FirstActivity，LastActivity，Application，OfficeWorkload，Operation
| 按 FirstActivity 升序排序

OfficeActivity
| where UserId == 'UserId'
| summarize FirstActivity = min(TimeGenerated), LastActivity = max(TimeGenerated) by TimeGenerated, Application, Operation
| join 
(
    OfficeActivity
    | summarize FirstActivity2 = min(TimeGenerated), LastActivity2 = max(TimeGenerated) by TimeGenerated, Application, OfficeWorkload
)
on TimeGenerated
| project FirstActivity, LastActivity, Application, OfficeWorkload, Operation
| sort by FirstActivity asc

答案1

得分: 1

你可以尝试使用arg_max()和arg_min()聚合函数，或者使用top运算符。

例如：

let base =
    OfficeActivity 
    | where TimeGenerated > ago(1d)
    | where UserId == 'UserId'
;
union
(base | top 1 by TimeGenerated asc),
(base | top 1 by TimeGenerated desc)

或者

let base =
    OfficeActivity 
    | where TimeGenerated > ago(1d)
    | where UserId == 'UserId'
;
union
(base | summarize arg_min(TimeGenerated, *)),
(base | summarize arg_max(TimeGenerated, *))

或者，如果你只需要时间戳而不需要其他列：

let base =
    OfficeActivity 
    | where TimeGenerated > ago(1d)
    | where UserId == 'UserId'
;
let min_dt = toscalar(base | summarize min(TimeGenerated));
let max_dt = toscalar(base | summarize max(TimeGenerated));
print min_dt, max_dt

let base =
    OfficeActivity 
    | where TimeGenerated > ago(1d)
    | where UserId == 'UserId'
;
union
(base | top 1 by TimeGenerated asc),
(base | top 1 by TimeGenerated desc)

let base =
    OfficeActivity 
    | where TimeGenerated > ago(1d)
    | where UserId == 'UserId'
;
union
(base | summarize arg_min(TimeGenerated, *)),
(base | summarize arg_max(TimeGenerated, *))

let base =
    OfficeActivity 
    | where TimeGenerated > ago(1d)
    | where UserId == 'UserId'
;
let min_dt = toscalar(base | summarize min(TimeGenerated);
let max_dt = toscalar(base | summarize max(TimeGenerated);
print min_dt, max_dt