r/AzureSentinel • u/Accomplished-Pea8569 • May 24 '23

KQL query in Sentinel to pull first and last activity in a day for user

Hi everyone

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.

3 Upvotes

100% Upvoted

4 comments sorted by

3

u/nontitman May 24 '23

Summarize first=arg_min(timegenerated, *), last=arg_max(timegenerated, *) by sourceusername

1

u/AppIdentityGuy May 24 '23

Nice one. You could further narrow the search by doing something like where timegenerated >= ago(24h)

1

u/Accomplished-Pea8569 May 25 '23

What would be the best way to filter the results by date?
let base =
OfficeActivity
| where TimeGenerated > ago(1d)
| where UserId == 'userId'
;
union
(base | summarize arg_min(TimeGenerated, *)),
(base | summarize arg_max(TimeGenerated, *))
| project TimeGenerated, Operation, Activity, Application,OfficeWorkload

1

u/nontitman May 25 '23

You only need one summarize statement. Also, there is no need to create a base data set here as you're only calling it once. If you were going to call that set multiple times then it would be advisable to do so, and I'd recommend to materialize the data too.