Compare Lastlogon attribute from all DC's

This topic contains 3 replies, has 3 voices, and was last updated by Profile photo of Joakim Svendsen Joakim Svendsen 3 years, 6 months ago.

  • Author
    Posts
  • #11961
    Profile photo of nidhinck
    nidhinck
    Participant

    Hi Experts,

    I need to export all attributes of an object. but the problem is that for finding last logon time we need to extract "lastLogon" attribute from all DC's and then compare the values to find the latest date and then display in to output.
    We are considering lastLogon attribte instead of lastLogonTimestamp attribute is because lastLogonTimestamp attribute is not accurate 🙁

    I used attached script to pull the selected attributes of a user and export to Excel file. Please help me with this lastLogon attribute compare and finding latest value

  • #11982
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator
    $Filter = [scriptblock]::Create( ((Get-Content .\samacc.txt | %{ "samaccountname -eq $_" }) -join ' -or ') )

    That's clever. The AD Cmdlets filters are just strings, though; no need to create a ScriptBlock out of it. You will need to put some quotation marks around the actual value either way, though, or Get-ADUser will give you a syntax error.

    $Filter = (Get-Content .\samacc.txt | %{ "samaccountname -eq '$_'" }) -join ' -or '
    
    • #11984
      Profile photo of Joakim Svendsen
      Joakim Svendsen
      Participant

      Oops, yeah, I added the single quotes earlier (obviously, since I had test data later on), but the command history disappeared, so I took it from memory and made the same mistake twice (damn it!) and already had the data for the later steps, so I didn't notice, since I didn't rerun the Get-ADUser command.

      I see now in Get-Help Get-ADUser that the -Filter parameter indeed is just a string, so I suppose that means the script block is just called .ToString() on. Thanks for pointing it out. I'll edit and add the single quotes in the example now.

  • #11981
    Profile photo of Joakim Svendsen
    Joakim Svendsen
    Participant

    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: http://social.technet.microsoft.com/Forums/scriptcenter/en-US/e93fba66-444f-42ac-89f5-7903e26c0e6c – 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.

You must be logged in to reply to this topic.