KQL query in sentinel for users first activity

[mm83RI]

Hi all,

since I am new to writing queries I would really appreciate your help.

I need to write a query that will show a specific users first activity and last activity in a day. I need to also project which activity it was and to sum it all up, I need the results to be in a row for each day.

I would really appreciate any suggestions or help.

Thank you

[#2]

@mm83RI This should get you started

// Find the firstSeen for a User
SigninLogs
| summarize arg_min(TimeGenerated,*) by UserPrincipalName
// join to last seen data for that user
|join 
(
  SigninLogs
  | summarize arg_max(TimeGenerated,*) by UserPrincipalName
  // any column that ends in a "1" is a last seen
) on UserPrincipalName
// Note, the "*" in arg_min and arg_max will return all columns, 
// to reduce the noise you can name them or just project the needed ones.  e.g.? 
| project UserPrincipalName, TimeGenerated, TimeGenerated1, OperationName

[#3]

This is great. But I would like to project which Activity was his first (for example Teams, Sharepoint site, etc) and the time.
Can I connect join somehow Office Activity with SigninLogs?

[#4]

@mm83RI

This will do that, if the UserPrincipalName in SigninLogs matches the UserID in OfficeActivity (you wont see people that do not have activity)

// firstSeen
SigninLogs
| summarize arg_min(TimeGenerated,*) by UserPrincipalName
// join to last seen data
|join 
(
  SigninLogs
  | summarize arg_max(TimeGenerated,*) by UserPrincipalName
  // any column that ends in a "1" is a last seen
) on UserPrincipalName
// the "*" in arg_min and arg_max will return all columns, 
// to reduce the noise you can name them or just project the needed ones? 
| project UserPrincipalName, TimeGenerated, TimeGenerated1, OperationName
| join 
(
OfficeActivity
// add any extra colums you need to the list
| summarize arg_min(TimeGenerated, OfficeWorkload, ResultStatus) by UserId
 ) on $left.UserPrincipalName == $right.UserId
| project UserPrincipalName, FirstSeen=TimeGenerated, LastSeen=TimeGenerated1, OperationName, FirstActivity=TimeGenerated2, OfficeWorkload, ResultStatus

[#5]

This is almost everything that I need. Awesome.
Can you tell me please how to add Last activity?
And is it possible to get the results for a couple of days, so that the each day would be in a separate row?

Thank you so much

[#6]

@mm83RI 

With added Last Activity 

// firstSeen
SigninLogs
| summarize arg_min(TimeGenerated,*) by UserPrincipalName
// join to last seen data
|join 
(
  SigninLogs
  | summarize arg_max(TimeGenerated,*) by UserPrincipalName
) on UserPrincipalName
| project UserPrincipalName, TimeGenerated, TimeGenerated1, OperationName
| join 
(
OfficeActivity
| summarize FirstActivity=arg_min(TimeGenerated, firstWorkload=OfficeWorkload, firstStatus=ResultStatus), LastActivity= arg_max(TimeGenerated, LastWorkload=OfficeWorkload, lastStatus=ResultStatus) by UserId
 ) on $left.UserPrincipalName == $right.UserId
| project UserPrincipalName, FirstSeen=TimeGenerated, LastSeen=TimeGenerated1, OperationName, FirstActivity, LastActivity, firstWorkload, LastWorkload, firstStatus, lastStatus

You should investigate the use of bin() bin() - Azure Data Explorer | Microsoft Learn  to help with the extra ask about seeing multiple days.

[#7]

@Clive_Watson 

I got what I need from your query.. Thank you so much..

Could you help me with this one 

OfficeActivity
| where UserId == 'UserId'
| summarize FirstActivity = min(TimeGenerated), LastActivity = max(TimeGenerated) by bin(TimeGenerated, 1d)
| join 
(
OfficeActivity
| summarize FirstActivity = min(TimeGenerated), LastActivity = max(TimeGenerated) by bin(TimeGenerated, 1d), Application, OfficeWorkload
)
on TimeGenerated
| sort by TimeGenerated asc 

I need to change the time zone.. I suppose I should use "expand" but not sure how

[#8]

You can add a new column with the Local time, use this:

https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/datetime-utc-to-local-function

e.g. (add the timezone of choice, I used 'CET' as an example)

| extend localtime_ = datetime_utc_to_local(TimeGenerated,'CET')

[#9]

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

This is the query that shows the results that I need.

The problem is the time is the same for all the actions.
how to project the exact time of each activity?

@Clive_Watson 

[#10]

TimeGenerated, as you used bin() is no longer a precise time, its midnight for each day. So just dont project it, First and Last are the columns you need

..
on TimeGenerated
| project FirstActivity, LastActivity, Application, OfficeWorkload
| sort by FirstActivity asc

[#11]

I did what you said and it still shows the same time for all applications.

do you have any idea why is that?

Could it be because I used bin()?

Is there any other way to do that?

@Clive_Watson 

[#12]

@mm83RI 

I dont see that, if I run this (for all users):

OfficeActivity
//| where UserId == 'UserId'
| where TimeGenerated  > ago(3d)
| summarize FirstActivity = min(TimeGenerated), LastActivity = max(TimeGenerated) by bin(TimeGenerated, 1d), UserId
| join 
(
    OfficeActivity
    | where TimeGenerated  > ago(3d)
    | summarize FirstActivity2 = min(TimeGenerated), LastActivity2 = max(TimeGenerated) by bin(TimeGenerated, 1d), Application, OfficeWorkload, UserId
)
on TimeGenerated
| project FirstActivity, LastActivity, Application, OfficeWorkload, FirstActivity2, LastActivity2
| sort by FirstActivity asc

 I then see:

[#13]

this is what I get

I don't get it how times are so messed up. when i run the default query, times are different.

What I need to get is to get the first and last activity for each app in one day, for a specific user

from 00:00h until 23:59

@Clive_Watson