How to get list of users from CSV file whose LastSignInDate column has a date that is before 90 days from current date or is empty?

Question

I have the below PowerShell Code which returns the list of my active users along with their LastSignInDate and some of them as blank value in LastSignInDate column. Like shown below, I want to further update the PS script to get the list of users whose LastSignInDate is before 90 days or is empty from current date.
Could anyone please guide me how can I can achieve this?

Connect-MgGraph -Scopes Directory.Read.All,AuditLog.Read.All

Get-MgUser -All -Filter "accountEnabled eq true" -Property 'UserPrincipalName','SignInActivity','Mail','DisplayName', 'AccountEnabled' |
    Select-Object @{N='UserPrincipalName';E={$_.UserPrincipalName}}, @{N='DisplayName';E={$_.DisplayName }}, @{N='LastSignInDate';E={$_.SignInActivity.LastSignInDateTime}}, @{N='AccountEnabled';E={$_.AccountEnabled }} |
    Export-Csv -Path "C:\Suraj\New folder\usernew1.csv" -NoTypeInformation -NoClobber

Output:

UserPrincipalName          DisplayName      LastSignInDate    AccountEnabled
[email protected]      Akshay kumar     07-11-2023 13:17    TRUE
[email protected]       Arijit Kale                          TRUE

Answer 1

You can use signinActivity/lastSignInDateTime in your filter however do note that querying this attribute requires AuditLog.Read.All. Also the limitation when querying this attribute is that you can't filter for other attributes, meaning that:

signInActivity/lastSignInDateTime le $date and accountEnabled eq true

Is not valid, so you need to filter later on for accountEnabled with Where-Object:

$date = [datetime]::UtcNow.AddDays(-90).ToString('s') + 'Z'

Get-MgUser -All -Filter "signInActivity/lastSignInDateTime le $date" -Property 'UserPrincipalName', 'SignInActivity', 'Mail', 'DisplayName', 'AccountEnabled' |
    Where-Object AccountEnabled |
    Select-Object UserPrincipalName, DisplayName, @{ N='LastSignInDate'; E={ $_.SignInActivity.LastSignInDateTime }}, AccountEnabled |
    Export-Csv -Path 'C:\Suraj\New folder\usernew1.csv' -NoTypeInformation -NoClobber