Forum Discussion
KQL query in sentinel for users first activity
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 proj...
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
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?
Can I connect join somehow Office Activity with SigninLogs?
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- 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 muchWith 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, lastStatusYou should investigate the use of bin() bin() - Azure Data Explorer | Microsoft Learn to help with the extra ask about seeing multiple days.