Forum Discussion

mm83RI's avatar
mm83RI
Copper Contributor
May 22, 2023

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 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

alerts
analytics
KQL
kusto

12 Replies

  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    May 22, 2023

    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
    • mm83RI's avatar
      mm83RI
      Copper Contributor
      May 23, 2023
      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?
      • Clive_Watson's avatar
        Clive_Watson
        Bronze Contributor
        May 23, 2023

        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

          

Resources