May 22 2023 07:42 AM
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
May 22 2023 03:48 PM
@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
May 23 2023 12:51 AM
May 23 2023 01:18 AM
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
May 23 2023 01:53 AM
May 23 2023 04:14 AM
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.
May 23 2023 05:43 AM
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
May 23 2023 05:57 AM
May 23 2023 07:56 AM
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?
May 23 2023 11:25 AM
May 24 2023 12:07 AM - edited May 24 2023 12:14 AM
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?
May 24 2023 01:33 AM
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:
May 24 2023 01:38 AM - edited May 24 2023 01:51 AM
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