Dear Microsoft Community,
I hope this message finds you well. I am reaching out for assistance with a Kusto Query Language (KQL) script that I've been working on to retrieve the last sign-in of all users in our environment.
The goal of the script is to avoid duplicate users and ensure that we only capture the latest sign-in event for each user. However, despite several attempts and adjustments to the script, we haven't been successful in achieving this goal. Additionally, whenever we try to incorporate the timestamp field to filter the results, the functionality of the script seems to be compromised.
Here's the current version of the script we're using:
SigninLogs
| where TimeGenerated > ago(30d)
| extend DeviceId = tostring(parse_json(DeviceDetail).deviceId),
DeviceDisplayName = tostring(parse_json(DeviceDetail).displayName),
OperatingSystem = tostring(parse_json(DeviceDetail).operatingSystem),
LocationDetailsString = tostring(LocationDetails),
UserDomain = tostring(split(UserPrincipalName, "@")[1])
| where UserDomain == "acme.com"
| extend City = tostring(parse_json(LocationDetailsString).city),
State = tostring(parse_json(LocationDetailsString).state),
CountryOrRegion = tostring(parse_json(LocationDetailsString).countryOrRegion)
| summarize arg_max(TimeGenerated, *) by UserPrincipalName, City, State, CountryOrRegion, DeviceDisplayName
| project UserPrincipalName, City, State, CountryOrRegion, DeviceDisplayName, OperatingSystem, AppDisplayName, ResourceDisplayName
We would greatly appreciate any insights, suggestions, or corrections you could provide to help us improve the functionality of this script and achieve our objective of retrieving the last sign-in of all users without duplicates.
Thank you very much for your assistance.
Best regards,
Sergio
