Exporting user last login to M365 or Azure to .CSV file ?

EnterpriseArchitect 5,406

How can I extract and export the last user login time to the M365 portal or Azure AD as .CSV file?

is there any PowerShell script or GUI procedure?

Olga Os - MSFT 5,921 Reputation points Microsoft Employee

2022-10-12T14:51:30.96+00:00

Hello @EnterpriseArchitect ,

I am just wondering if you have any additional question regarding your original post. I'd love to help you where I can.

Sincerely,
Olga Os
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Accepted answer

Olga Os - MSFT 5,921 Reputation points Microsoft Employee

2022-10-10T17:13:43.287+00:00
Hello @EnterpriseArchitect ,

Welcome to the MS Q&A Forum.

In the past, I used these steps to collect the last login date and export to .csv:

Go to the Azure portal => App registrations => Register a Test APP

Create a secret and save the value

Go to API permissions, select Microsoft Graph Explorer and finally select the below permissions

Make sure to add those permissions and Grant the Admin consent for the tenant.

Once this is done you need to modify the requested parameters on the script and run it.

Run PowerShell script https://github.com/12Knocksinna/Office365itpros/blob/master/GetUserSignInDataGraph.PS1 where you will use parameters from above steps.

Output example:

Hope above answers your questions and concerns.

--------------------------------------------------------

Let us know if you need additional assistance. If the answer was helpful, please accept it and complete the quality survey so that others can find a solution.

Sincerely,
Olga Os
Please sign in to rate this answer.

1 person found this answer helpful.
EnterpriseArchitect 5,406 Reputation points

2022-10-12T23:29:02.947+00:00

Thanks @Olga Os - MSFT , how long does the SignInLogs kept by Microsoft ?

Olga Os - MSFT 5,921 Reputation points Microsoft Employee

2022-10-13T07:36:30.31+00:00

@EnterpriseArchitect ,

The lastsignin property is a static value that is populated for all user accounts including guest accounts. You could see sign-ins up to 260 days in my output above.

Reference: Example 3: Get users including their last sign-in time

Sincerely,
Olga Os

EnterpriseArchitect 5,406 Reputation points

2022-10-13T12:34:53.027+00:00

Cool, thanks @Olga Os - MSFT

Graham C 26 Reputation points

2023-01-27T16:50:53.4333333+00:00

Hi @Olga Os - MSFT --Thank you for sharing this, however, I'm getting the error output below when executing the script. I don't see any culture specific date formatting and seems to be just taking my system time and adjusting from there. Any ideas how I can overcome this? I'm in the US and my system date time format is en-US: MM/DD/YYYY HH:MM AM/PM

Thank you for any suggestions.

~gC

Get-Date : Cannot bind parameter 'Date' to the target. Exception setting "Date": "Cannot convert null to type "System.DateTime"." At line:47 char:35 + Created = Get-Date($User.CreatedDateTime) -format g + ~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : WriteError: (:) [Get-Date], ParameterBindingException + FullyQualifiedErrorId : ParameterBindingFailed,Microsoft.PowerShell.Commands.GetDateCommand

Graham C 26 Reputation points

2023-01-27T19:08:48.8033333+00:00

Disregard my last! If anyone else sees this error like I did, just let it run all the way through!

...

After some searching, I found that this error is an innocuous warning and does not terminate the script. It seemed like it was throwing this for all the users at the speed it was dropping but the subsequent nextlink pages it occured less and after letting it run I was presented with the expected data and spot checked a few users in the AAD UI for validation.

Thanks!!

Meihua 0 Reputation points

2023-05-25T10:51:18.6266667+00:00

How can I set a filter in the script so that only certain outputs are generated, for example only users from a certain department are exported?

Ivan Luke Cruz 5 Reputation points

2023-06-07T05:11:26.55+00:00

Hi is there a license needed for this?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

2 additional answers

KRANTI KUMAR DANALAKOTA 6 Reputation points

2023-01-06T19:29:06.23+00:00
Please sign in to rate this answer.

1 person found this answer helpful.
Graham C 26 Reputation points

2023-01-27T15:47:00.5633333+00:00

@KRANTI KUMAR DANALAKOTA -- Did you find a way to resolve your error?

Gal Shalom 0 Reputation points

2023-02-14T14:45:34.3933333+00:00

I could really use a solution for this. i stuck on the same issue

EnterpriseArchitect 5,406 Reputation points

2023-02-16T00:23:19.65+00:00

Hi All,

This is due to the user account you are using is not registered under Enterprise Application > Microsoft Graph PowerShell | Users and groups section.

Vidar Andersen 10 Reputation points

2023-03-27T10:08:10.2666667+00:00

Same error here, adding my user to microsoft graph powershell did not help.

BenJackson 0 Reputation points

2024-06-03T19:03:57.9266667+00:00

same error here. cant use this script. Waste of my time today
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Donald Flowers 5

@Graham C @Gal Shalom You just have to ensure to change the following lines for it to work.

$AppId = "d716b32c-0edb-48be-9385-30a9cfd96155"

$TenantId = "c662313f-14fc-43a2-9a7a-d2e27f4f3478"

$AppSecret = 's_rkvIn1oZ1cNceUBvJ2or1lrrIsb*:='

It worked fine after that :)

EnterpriseArchitect 5,406 Reputation points

2023-02-16T00:24:31.7+00:00

Hi @Donald Flowers , How did you generate that App Secret?
Josue Gonzalez 70 Reputation points

2023-11-29T04:26:53.45+00:00

Hi Donald, Where on the script is it required to change the lines? I don’t see the $AppId, $TenantId, and $AppSecret variables in the script.
BenJackson 0 Reputation points

2024-06-03T19:03:07.4+00:00

I have not been able to use this script. I can connect to Graph, get a token, run other basic queries, but when this script evokes some portion of itself, it gets 401 unauthorized error.
Chris Newitt 0 Reputation points

2024-08-28T13:10:07.3233333+00:00

I have downloaded the script and this part is not built into the script to change, so where about do we paste them to be able to insert the app id and tennent?

David Song 5

@Chris Newitt @BenJackson

The script Olga Os shared on github has some small, but code-breaking bugs in it. My fixes:

Whitespace is removed from the URI
The conditional that re-assigned $SignInData is removed
First for-loop now iterates on $SignInData.Value
Removed column 'created' cuz i didn't need it and it was throwing errors

# The script Olga Os shared on github has some small, 
# but code-breaking bugs in it.
# My fixes:
# Whitespace is removed from the URI
# The conditional that re-assigned $SignInData is removed
# First for-loop now iterates on $SignInData.Value
# Removed column 'created' cuz i didn't need it

Clear-Host

$AppId = "take from azure"
$TenantId = "take from azure"
$AppSecret = 'take from azure'

# Obtain the access token
$body = @{
    grant_type    = "client_credentials"
    scope         = "https://graph.microsoft.com/.default"
    client_id     = $AppId
    client_secret = $AppSecret
}

$tokenResponse = Invoke-RestMethod -Method Post -Uri "https://login.microsoftonline.com/$TenantId/oauth2/v2.0/token" -ContentType "application/x-www-form-urlencoded" -Body $body
$accessToken = $tokenResponse.access_token


# Define the headers
$Headers = @{
    "Authorization" = "Bearer $accessToken"
    "Content-Type"  = "application/json"
}

# Get User sign in data
Write-Host "Accessing the Graph to get user sign-in data..."

$URI = "https://graph.microsoft.com/beta/users?`$select=displayName,userPrincipalName,mail,id,CreatedDateTime,signInActivity,UserType&`$top=100"


[array]$SignInData = (Invoke-RestMethod -Method Get -Uri $URI -Headers $Headers  -ContentType "application/json") 


$Report = [System.Collections.Generic.List[Object]]::new() 

ForEach ($User in $SignInData.Value) {  
   [string]$LastSignInOutput = $Null
   If ($Null -ne $User.SignInActivity)     {
      $LastUserSignInDate = $User.SignInActivity['lastSignInDateTime']
      If ($Null -ne $LastUserSignInDate) {
          $LastSignInOutput = Get-Date($LastUserSignInDate) -format g
          $DaysSinceSignIn = (New-TimeSpan $LastUserSignInDate).Days
      }
   } Else { #No sign in data for this user account
      $LastSignInOutput = "Never" 
      $DaysSinceSignIn = "N/A" 
   }
 
   $ReportLine  = [PSCustomObject] @{          
     UPN                = $User.UserPrincipalName
     DisplayName        = $User.DisplayName
     Email              = $User.Mail
     ObjectId           = $User.Id     
     LastSignIn         = $LastSignInOutput
     DaysSinceSignIn    = $DaysSinceSignIn
     UserType           = $User.UserType }
   $Report.Add($ReportLine) 
} # End ForEach

# Do we have extra data to fetch?
$NextLink = $SignInData.'@odata.nextLink'


While ($Null -ne $NextLink) { # We do... so process them.
   Write-Host "Still processing..."
   $SignInData = Invoke-WebRequest -Method GET -Uri $NextLink -ContentType "application/json" -Headers $Headers
   $SignInData = $SignInData | ConvertFrom-JSon
   ForEach ($User in $SignInData.Value) {  
      [string]$LastSignInOutput = $Null
      If ($Null -ne $User.SignInActivity)     {
         $LastUserSignInDate = $User.SignInActivity.LastSignInDateTime
         If ($Null -ne $LastUserSignInDate) {
            $LastSignInOutput = Get-Date($LastUserSignInDate) -format g
            $DaysSinceSignIn = (New-TimeSpan $LastUserSignInDate).Days
        }
      } Else { #No sign in data for this user account
         $LastSignInOutput = "Never" 
         $DaysSinceSignIn = "N/A" 
      }
     
   $ReportLine  = [PSCustomObject] @{  
     UPN                = $User.UserPrincipalName
     DisplayName        = $User.DisplayName
     Email              = $User.Mail
     ObjectId           = $User.Id    
     LastSignIn         = $LastSignInOutput
     DaysSinceSignIn    = $DaysSinceSignIn
     UserType           = $User.UserType        }
   
   $Report.Add($ReportLine) } 

   # Check for more data
   $NextLink = $SignInData.'@Odata.NextLink'
} # End While

Write-Host "All done. " $Report.Count "accounts processed - output available in c:\Temp\ReportUserSignin.csv."
$Report | Sort-Object DisplayName | Out-GridView
$Report | Export-CSV -NoTypeInformation c:\Temp\ReportUserSignin.csv

Simon Goulding 0 Reputation points

2024-10-23T10:36:39.5366667+00:00

Thank you David (and Olga) - your full script work perfectly for me. I have been looking and failing to find a script that worked until i came across this - Thanks Again.

Share via

Exporting user last login to M365 or Azure to .CSV file ?

2 additional answers

Your answer