Forum Discussion
Create Alert on First Seen computer through Azure AD authentication
How we can create KQL query to trigger an alert when the existing user (helpdesk guy) signs in to a new windows device for the very first time using azure active directory. Example: Helpdesk logins t...
I would recommend using IntuneAudit for this, but for SigninLogs you could start with something like this, although I would not recommend it, I have just looked into this and the DeviceDetail data is very weak:
let seenDvc = SigninLogs
| where TimeGenerated >ago(lookback)
| extend deviceName = tostring(DeviceDetail.displayName)
| summarize min(TimeGenerated) by UserPrincipalName, deviceName
| project UserPrincipalName, deviceName, firstSeen=min_TimeGenerated;Let me know how you get on.
camc , thank you for your prompt response. I have developed the below query however its not giving any results, any idea where the issue is?
let seenDvc =
SigninLogs
| where TimeGenerated > ago(100m)
| extend deviceName = tostring(DeviceDetail.displayName)
| summarize min(TimeGenerated) by UserPrincipalName, deviceName
| project UserPrincipalName, deviceName, firstSeen = min_TimeGenerated;
SigninLogs
| where TimeGenerated > ago(5m)
| extend newDeviceName = tostring(DeviceDetail.displayName)
| join kind = anti (seenDvc) on $left.UserPrincipalName == $right.UserPrincipalName
| project UserPrincipalName, newDeviceName
In this modified query, we first create the seenDvc table using the previous query logic to find the earliest sign-in time for each UserPrincipalName and deviceName combination within the last 100 minutes.
Then, we perform a new query on the SigninLogs table to find sign-in events within the last 5 minutes. We extend the newDeviceName column with the displayName from the DeviceDetail. The join kind=anti operation is used to filter out any records where the UserPrincipalName from the new query matches the UserPrincipalName from the seenDvc table, but the newDeviceName does not match the corresponding deviceName from the seenDvc table.
Finally, the project statement displays the UserPrincipalName and the newDeviceName, which represents the names of the new devices that do not match the device names from the previous query.
let seenDvc =
SigninLogs
| where TimeGenerated > ago(100m)
| extend deviceName = tostring(DeviceDetail.displayName)
| summarize min(TimeGenerated) by UserPrincipalName, deviceName
| project UserPrincipalName, deviceName, firstSeen = min_TimeGenerated;
SigninLogs
| where TimeGenerated > ago(5m)
| extend newDeviceName = tostring(DeviceDetail.displayName)
| join kind = anti (seenDvc) on $left.UserPrincipalName == $right.UserPrincipalName
| project UserPrincipalName, newDeviceName
In this modified query, we first create the seenDvc table using the previous query logic to find the earliest sign-in time for each UserPrincipalName and deviceName combination within the last 100 minutes.
Then, we perform a new query on the SigninLogs table to find sign-in events within the last 5 minutes. We extend the newDeviceName column with the displayName from the DeviceDetail. The join kind=anti operation is used to filter out any records where the UserPrincipalName from the new query matches the UserPrincipalName from the seenDvc table, but the newDeviceName does not match the corresponding deviceName from the seenDvc table.
Finally, the project statement displays the UserPrincipalName and the newDeviceName, which represents the names of the new devices that do not match the device names from the previous query.