Link to home
Start Free TrialLog in
Avatar of Justin Owens
Justin OwensFlag for United States of America

asked on

PowerShell - Query AD from CSV to determine last logon time of server

Related to this Question.

I have a CSV file which has a single column of server names which were extracted from AD (so, I know they are there).  I need to re-query AD to determine when the last communication between the server in the list and AD happened.  I have seen several methodologies discussed, but I have not found anything terribly simple.

Ideally, I would like to open the CSV, enumerate the list, add a new column with "last logon" or something to that nature, populate that column, then save the CSV.  I would be satisfied with a new CSV being created instead of writing to the original.

While this is not an emergency, it is a "hurry up" request from above.

With thanks and respect,

DrUltima
Avatar of Daryl Bamforth
Daryl Bamforth
Flag of United Kingdom of Great Britain and Northern Ireland image

Hmmm, try this

$csv = import-csv <path to csv file>
$finalcsv=@{}
foreach ($line in $csv) 
{
     $working = {} | select Server, LastLogon
     $working.Server = $line.server
     $working.lastlogon = (get-adcomputer $line.server -properties lastlogondate).lastlogondate
     $finalcsv += $working
}
$finalcsv | Export-CSV <export path> -notypeinformation

Open in new window


This is assuming that your CSV file has a header called 'server' and that you have the AD powershell addin installed.  Also if you have more than one domain controller this will not necessarily be accurate as it will only poll whichever DC answers first.
Avatar of Justin Owens

ASKER

un0ri,

Here is the modified code I used (for example, get-qadcomputer rather than get-adcomputer):
$csv = import-csv C:\Users\MYUSERNAME\Desktop\Servers.csv
$finalcsv=@{}
foreach ($line in $csv) 
{
     $working = {} | select Server, LastLogon
     $working.Server = $line.server
     $working.lastlogon = (get-qadcomputer $line.server -properties lastlogondate).lastlogondate
     $finalcsv += $working
}
$finalcsv | Export-CSV C:\Users\MYUSERNAME\Desktop\ServersPolled.csv -notypeinformation

Open in new window

Here is the error I receive when running this:
You can add another hash table only to a hash table.
At C:\Users\MYUSERNAME\Documents\Scripts\PollADforLastLogonTime.ps1:8 char:18
+      $finalcsv += <<<<  $working
    + CategoryInfo          : InvalidOperation: (@{Server=TruncatedFQDNServerName; LastLogon=}:PSObject) [], RuntimeException
    + FullyQualifiedErrorId : AddHashTableToNonHashTable

Open in new window

What am I doing wrong?

Thank you for your assistance,

DrUltima
oops.

change

$finalcsv=@{}

Open in new window

to
$finalcsv=@()

Open in new window

I have just tested qad and it is only grabbing null values for lastlogontime

What version of AD are you running?
What are you wanting to accomplish by having the last logon time?

If you are just trying to find computers that have not connected to the domain in x number of weeks you can just run

dsquery computer -inactive x

Open in new window


replace x with how many weeks you want it to have been inactive for.
I know how to generate a list of computer which have not logged in for a specific time.  That is how I got the list I am using for the Query.  I need to know the last time those computers talked to a domain to answer a question about MAP results from an MS OVL Audit.  I have over 150 server OS machines which we think are gone, but need to provide a "When then went away" to MS, as we have already sent the original results of the MAP audits to them.

DrUltima
What DC version are you running?  Might just have to do an LDAP lookup instead
http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/f4a2ecbe-e750-4ba6-a90c-7e5f37e28d73/

Change or remove the OS depending if you need it to filter on that

$ldapQuery = "(&(objectCategory=computer)(operatingSystem=Windows 2000 Professional))"
$de = new-object system.directoryservices.directoryentry
$ads.pagesize=30000
$ads = new-object system.directoryservices.directorysearcher -argumentlist $de,$ldapQuery
$complist = $ads.findall()

$computers = @()
foreach ($computer in $complist)
{

$computers += $computer.properties.name[0] +"," +$computer.properties.lastlogon[0]
}
$computers | out-file 9mold_computers.csv

Open in new window

un0ri,

While I appreciate your response, it is not what I am asking.  I have a CSV list.  I don't want to query AD again for anything other than that specific list.  I am sure your code would work (I have not tried it to verify), but it doesn't satisfy the initial request in the Question.

Respectfully,

DrUltima
Sorry for the delay in responding.

Can you please clarify what AD version you are running.
Windows Server 2003 (AD schema version 30).
ASKER CERTIFIED SOLUTION
Avatar of Daryl Bamforth
Daryl Bamforth
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
No longer on this project, but the information was worth being in the KB.  Thank you for the assistance.