Justin Owens
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
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
ASKER
un0ri,
Here is the modified code I used (for example, get-qadcomputer rather than get-adcomputer):
Thank you for your assistance,
DrUltima
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
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
What am I doing wrong?Thank you for your assistance,
DrUltima
oops.
change
change
$finalcsv=@{}
to $finalcsv=@()
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
replace x with how many weeks you want it to have been inactive for.
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
replace x with how many weeks you want it to have been inactive for.
ASKER
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
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
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
ASKER
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
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.
Can you please clarify what AD version you are running.
ASKER
Windows Server 2003 (AD schema version 30).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No longer on this project, but the information was worth being in the KB. Thank you for the assistance.
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.