Hi folks, Hope you are doing well! I'm using a PowerShell script based on MG Graph module to get the last sign-in date of all users. PS script: Now as specified here Azure AD only stores Sign-ins data until 30 days in P2 license, but in the result of my PowerShell script, I am getting data which are older than even a year. Date export: Could someone please be kind enough to throw some light on this. Does Mg Graph work differently than other mechanisms (Azure AD module, Entra Id GUI etc.), or am I messing around somewhere in the script. Any help would be highly appreciated! Thanks in advance! Cheers!

You are confusing things. The cmdlet above is not querying the Azure AD sign-in logs, but just getting a property on the user object that is updated by background processes, based on user's last sign-in data. Think of it as querying a separate service (Azure AD Reporting service). If you want to get the actual sign-in logs, you can use the Get-MgAuditLogSignIn cmdlet instead. The data you will get from it should match the data from the Sign-In logs page in the portal.

Mg Graph Sign-In logs showing data more than 30 days older

Accepted answer

Vasil Michev 95,581 Reputation points MVP

2024-04-19T18:02:40.08+00:00

You are confusing things. The cmdlet above is not querying the Azure AD sign-in logs, but just getting a property on the user object that is updated by background processes, based on user's last sign-in data. Think of it as querying a separate service (Azure AD Reporting service).

If you want to get the actual sign-in logs, you can use the Get-MgAuditLogSignIn cmdlet instead. The data you will get from it should match the data from the Sign-In logs page in the portal.
Please sign in to rate this answer.

1 person found this answer helpful.
Apurva Pathak 310 Reputation points

2024-04-20T14:08:12.4866667+00:00

Thanks a lot for the clarification @Vasil Michev . Could you please also confirm if this cmdlet is getting me the Last login date of the users? If yes, then isn't it a good way to overcome the data retraining limitation on Azure AD. And what do you mean by actual sign-in log, is this not the data related to sign-in logs of the users?

Vasil Michev 95,581 Reputation points MVP

2024-04-20T15:56:38.44+00:00

If you are only interested in the last sign-in date, yes, the cmdlet above will work fine. But it's not a replacement for being able to get the full set of login attempts for a given user, for example when troubleshooting login issues, or investigating potential breach, etc.

Apurva Pathak 310 Reputation points

2024-04-20T16:50:04.89+00:00

Thanks again!

Sorry last thing before we close this off, why doesn't the limit of 30 days data retention being applied here? Because even though this property is being called out as an attribute of the accounts but if the source data (sign-in log) itself is not present in AD how AD maintains this to the profiles.

This might sound a really silly question but I got confused by this. I had to export last login details of users and because of this data retention limitation, I used LAW to query the historical data and did sorting to make it usable. But this seems a really useful way which, I wonder why, is not so clearly recommended over forums/docs/sites.

Cheers!

Vasil Michev 95,581 Reputation points MVP

2024-04-20T17:39:24.95+00:00

The source is not the sign-in logs directly. It's another service that runs on the same backend, a different database/table if you will. When you request the signInActivity property, you hit this different endpoint, and not the regular "graph" one, as you can verify by appending an $whatIf to the Graph query. It exposes a "users" table, which stores only a handful of properties, which in turn are updated at each user sign-in (not in real time though). The retention policy does not apply to said "database", but to the sign-in audit log database, the one that contains all the sign-in records, not the signInActivity property.

In fact, said endpoint can even return entries that seemingly do not match any of your user's GUIDs, which also caused me some confusion. I wrote about it here: https://www.michev.info/blog/post/5845/non-existent-users-show-up-in-sign-in-activity-data-or-how-logs-continue-to-disappoint

The reason why people rarely recommend this approach is that it stayed in beta for years, only recently it graduated to GA status. And for example the lastSuccessfulSignInDateTime property is still only available under /beta and not /v1.0

Apurva Pathak 310 Reputation points

2024-04-20T18:25:25.8666667+00:00

Thanks a lot @Vasil Michev

Really very thankful for you kind help!

Apurva Pathak 310 Reputation points

2024-04-20T18:41:21.21+00:00

Hi @Vasil Michev ,

Really sorry for being so cheeky!

May I please request another clarification on below:

I'm using below query in KQL to get the last login data for users (this basically gets me the data of all such users who haven't logged in for 30 to 90 days), what should be the trusted and recommended way if one wants to choose between the KQL and the Mg Graph cmdlets to get this (as you also have mentioned in your blog that this cmdlet still has some issues such as differentiate between failed and successful sign-in attempts etc.)

SigninLogs | where TimeGenerated > ago(90d) | where UserType == "Member" | where UserPrincipalName endswith "@xyz.com" | summarize arg_max(TimeGenerated, *) by tolower(UserPrincipalName) | project UserPrincipalName, TimeGenerated = format_datetime(TimeGenerated, "dd-MM-yyyy HH:mm:ss"), ["DaysSinceLastLogon"]=datetime_diff("day", now(), TimeGenerated) | where ["DaysSinceLastLogon"] >= 30 | sort by ["DaysSinceLastLogon"] desc | project UserPrincipalName, LastLoginTime = TimeGenerated

Vasil Michev 95,581 Reputation points MVP

2024-04-22T06:57:17.9833333+00:00

Either way would do, but if the task is to simply report on last login, fetching the SignInActivity property is much easier compared to collecting/exporting/querying the SignInLogs. I'd recommend using the latter only if you need additional details, i.e. which IP address or device was the last login from, how many times a user has logged in from country XX, etc.

You can use the /beta endpoint in order to get the lastSuccessfulSignInDateTime property, which only gets updated on successful login.

Apurva Pathak 310 Reputation points

2024-04-22T07:02:10.5833333+00:00

Thanks, a mil again, @Vasil Michev !

I'd rather go with the latter one because:

We need some additional details to process this data.

Since lastSuccessfulSignInDateTime is still in beta I am a bit hesitant to use this in Prod.
Sign in to comment

Mg Graph Sign-In logs showing data more than 30 days older

0 additional answers