Hi.
Here’s something to get you tinkering:
So I will describe how to create a CSV file (or output PS objects) with the data you want. Parsing Excel files seems silly when you have control over the process of gathering and storing data and can choose something much more suitable.
Firstly, I chose to create a filter from the file with usernames (SAM account names). Not sure how well this scales with many users, but I assume the number will be large before it represents a problem.
Then create an array of domain controller names to feed to Get-ADUser ($DC).
PS C:\temp> gc .\samacc.txt
joakimbs
pwdnotusr1
pwdnotusr2
PS C:\temp> $Filter = (Get-Content .\samacc.txt | %{ "samaccountname -eq '$_'" }) -join ' -or '
PS C:\temp> $Filter
samaccountname -eq 'joakimbs' -or samaccountname -eq 'pwdnotusr1' -or samaccountname -eq 'pwdnotusr2'
PS C:\temp> $DC = @('2008r2esxi', '2008r2esxi2')
Initialize a $UserData hashtable, and initialize a hash for each DC, where the keys in the inner hash will be SAM account names (identical keys in both nested hashtables).
PS C:\temp> $UserData = @{}
PS C:\temp> $DC | %{ $UserData.$_ = @{} }
Get some data from AD. To demonstrate how you’d use an array for all these other properties in your script, I added the “GivenName” attribute in addition to the LastLogon attribute.
PS C:\temp> $DC | % {
$Server = $_
Get-ADUser -Server $_ -Filter $Filter -Properties SamAccountName, LastLogon, GivenName | %{
$UserData.$Server.($_.SamAccountName) = @($_.LastLogon, $_.GivenName)
}
}
Now you will have a hash like this:
PS C:\temp> $UserData
Name Value
---- -----
2008r2esxi2 {pwdnotusr2, pwdnotusr1, joakimbs}
2008r2esxi {pwdnotusr2, pwdnotusr1, joakimbs}
PS C:\temp> $UserData.'2008r2esxi'
Name Value
---- -----
pwdnotusr2 {129614284965417969, John}
pwdnotusr1 {$null, Jane}
joakimbs {130314150136119802, Joakim}
The values of the $UserData hashtable are SAM account names, which are keys in a nested hashtable where the values you retrieve using for instance $UserData.($DC[0]).‘joakimbs’ are in the form of an array where the first index (0) is the LastLogon attribute, and the second index (1) is the GivenName. Keep adding the properties you need everywhere.
So to the real logic to handle this. Iterate each user in samacc.txt, in a nested foreach, iterate each DC, get the latest LastLogon attribute, and output it to a PowerShell object. You will have to keep adding properties here, etc. for your case.
PS C:\temp> @(foreach ($User in gc .\samacc.txt) {
$DC |
% -Begin { $Latest = 0 }
-Process { if (($UserData.$_.$User)[0] -gt $Latest) { $Latest = ($UserData.$_.$User)[0] } }
-End { New-Object PSObject -Property @{
SamAccountName = $User
LastLogon = [datetime]::FromFileTime($Latest)
GivenName = ($UserData.($DC[0]).$User)[1]
CheckedDCs = ($DC -join ', ') }
} # end of foreach-object (%)
}) | Sort LastLogon | Format-Table -AutoSize
LastLogon GivenName CheckedDCs SamAccountName
--------- --------- ---------- --------------
1/1/1601 1:00:00 AM Jane 2008r2esxi, 2008r2esxi2 pwdnotusr1
9/25/2011 2:48:16 PM John 2008r2esxi, 2008r2esxi2 pwdnotusr2
12/13/2013 2:45:07 PM Joakim 2008r2esxi, 2008r2esxi2 joakimbs
And to verify we indeed get the latest date I run the below code to inspect. Also notice the start of the epoch (1/1/1601 1:00:00 AM) for the user with $null as the last logon timestamp (never logged on) above.
PS C:\temp> [datetime]::FromFileTime($UserData.($DC[0]).'pwdnotusr2'[0]), [datetime]::FromFileTime($UserData.($DC[1]).'pwdnotusr2'[0])
Sunday, September 25, 2011 2:48:16 PM
Sunday, September 25, 2011 4:25:12 AM
And I can see that the latest date was indeed the one I got from the earlier code.
Now, if you replace “| Format-Table -AutoSize”, with “| Export-Csv -Encoding utf8 userdata.csv”, you will get a CSV file. Rather than messing around with the Excel COM object like you’re doing, you probably want this, and then to convert the CSV to XLSX. Check this link for more info on converting CSV to XLSX: Powershell - CSV to Excel - or just search the web for “powershell excel convert CSV to XLS” or similar. You can also of course import the CSV file manually in Excel.
PS C:\temp> Import-Csv .\userdata.csv | ft -a
LastLogon GivenName CheckedDCs SamAccountName
--------- --------- ---------- --------------
1/1/1601 1:00:00 AM Jane 2008r2esxi, 2008r2esxi2 pwdnotusr1
9/25/2011 2:48:16 PM John 2008r2esxi, 2008r2esxi2 pwdnotusr2
12/13/2013 2:45:07 PM Joakim 2008r2esxi, 2008r2esxi2 joakimbs
If you don’t understand this, I think this process will be painful… It’s not 100% trivial, what you ask for, and your script is not really in the vicinity of doing what you want (you only check one server and there’s absolutely no logic for comparing).
I wrote the “hard” logic for you, and these examples can be adapted to solve your problem, but you will have to write the boring parts yourself, and to combine it into a script. You can use the “crappy” Excel COM object method for populating if you want to as well…
Thanks.
