lastlogondate from a list of users

[doncorleo]

Our edu-tenant contains 40k users. I have a list of 800 UPN’s who our Identity Team wants to know what their last logondate was.

I’ve found a script to export all users, but i’m struggling to get a foreach from the excel file.

This is what i have so far:

$csv = "c:\temp\LastLogons_$(Get-Date -format dd_MM_yyyy).csv"
$users = get-mailbox -ResultSize Unlimited | select UserPrincipalName
Foreach ($user in $users){
$mbx = get-mailboxstatistics -Identity $($user.UserPrincipalName) | Select LastLogonTime
$upn = $user.UserPrincipalName
if ($mbx.LastLogonTime -eq $null){
$res = "Never"
}else{
$res = $mbx.LastLogonTime
}
$outStr = "$upn,$res"
Out-File -FilePath $csv -InputObject $outStr -Encoding UTF8 -append
}

Can anybody help me out? Pretty pls?

[DoctorDNS]

This could do it for you:

$Path = 'C:\Foo\LastLogon.csv'
Get-ADUser -Filter {Enabled -eq $true} -Properties LastLogonTimeStamp | 
  
Select-Object Name,@{Name="Stamp"; Expression={[DateTime]::FromFileTime($_.lastLogonTimestamp).ToString('yyyy-MM-dd_hh:mm:ss')}} | Export-Csv -Path $Path –notypeinformation

do this and you see the following in excel:

[DoctorDNS]

There is one small isue with lastlogon date, however. It does not really replication. So if Jerry logs on to DC2, when you query DC1 (the other DC), you may not see that logon.

See: Understanding the AD Account attributes - LastLogon, LastLogonTimeStamp and LastLogonDate | Microsoft Learn for more details

[doncorleo]

Tnx for the quick reply. It’s being much appreciated.
That would generate a list of all 40k users, right? I’m looking for a way to use my excelfile with 800 users to select their LastLogonDate/ LastLogonTimeStamp.

[jitensh]

Run it as

$csv = "c:\temp\LastLogons_$(Get-Date -format dd_MM_yyyy).csv"
$users=get-mailbox -ResultSize Unlimited |select -ExpandProperty UserPrincipalName
$data=foreach($user in $users)
{
get-mailboxstatistics $user|
select @{N="UPN";E={$user}},@{N= "LastLogon";E= {if (($_.LastLogonTime -notlike '*')) {'Null'} Else {$_.LastLogonTime}}}

}

$data|Export-Csv $csv -NoTypeInformation

or

$csv = "c:\temp\LastLogons_$(Get-Date -format dd_MM_yyyy).csv"
get-mailbox -ResultSize Unlimited |get-mailboxstatistics|select Displayname,@{N= "LastLogon";E= {if (($_.LastLogonTime -notlike '*')) {'Null'} Else {$_.LastLogonTime}}}|
Export-Csv $csv -NoTypeInformation

[DoctorDNS]

Indeed, that snippet would create a BIG excel file if you have 40k users.

You could modify that script to:

1. Use -Searchbase in the Get-ADUser command to get just users within a given OU and below.

2. Create a text file of users whose last login you are keen on viewing and use Get-ADUser against each user i the file

3. Create conditions on which users to look at and create a -Filter to get AD users.

[eric7615]

I have a script here that pulls names from an excel file and does a query to get BIOS info but you could modify that to do the query against AD, it then puts the info back into the same excel file and saves it once finished.

[gary-m-g]

Depending on the data:

$upns = import-csv "C:\temp\UPNFile.csv"|select -exp UPNColumn
foreach ($u in $upns) {
    $sam = $u -replace "@.*",""
    $User = Get-AdUser -F {SamAccountName -eq $sam}
    if($User) {
        .....
    }
}

That is, if the SamAccountName is same as the UPN less the @contoso.com, then you have your 800 folks.

BTW, if you set the GPO which enforces msDSLastSuccessfulInteractiveLogon, you can use it as the best Last Logon time as it replicates.

[doncorleo]

Still had difficulties with the foreach loop. I need training…
I used Get-MailboxStatistics -Identity “UPN” | Select LastLogonTime and combined the csv and the script, leaving me with just pasting it in Powershell.

After it ran, i pasted the results in an Excel file. Problem solved