Solved

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

Posted on 2012-04-11
13
863 Views
Last Modified: 2014-07-03
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
0
Comment
Question by:DrUltima
  • 7
  • 5
13 Comments
 
LVL 2

Expert Comment

by:un0ri
ID: 37834771
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.
0
 
LVL 31

Author Comment

by:DrUltima
ID: 37834920
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
0
 
LVL 2

Expert Comment

by:un0ri
ID: 37835091
oops.

change

$finalcsv=@{}

Open in new window

to
$finalcsv=@()

Open in new window

0
 
LVL 2

Expert Comment

by:un0ri
ID: 37835134
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.
0
 
LVL 31

Author Comment

by:DrUltima
ID: 37837819
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
0
 
LVL 2

Expert Comment

by:un0ri
ID: 37837910
What DC version are you running?  Might just have to do an LDAP lookup instead
0
 
LVL 2

Expert Comment

by:un0ri
ID: 37837927
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

0
 
LVL 31

Author Comment

by:DrUltima
ID: 37850791
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
0
 
LVL 2

Expert Comment

by:un0ri
ID: 37858717
Sorry for the delay in responding.

Can you please clarify what AD version you are running.
0
 
LVL 31

Author Comment

by:DrUltima
ID: 37881069
Windows Server 2003 (AD schema version 30).
0
 
LVL 2

Accepted Solution

by:
un0ri earned 300 total points
ID: 37885864
Here you go.  Change the path to your current CSV file and where you want you new CSV file to be exported to.

$csv = import-csv C:\Servers.csv
$finalcsv=@{}
$complist = @()

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

foreach ($computer in $complist)
{
	if ($csv -contains $computer.properties.name)
	{
		$working = {} | select Server, LastLogon
		$working.Server = $computer.properties.name
		$working.lastlogon = [DateTime]::FromFileTime([Int64]::Parse($computer.properties.lastlogontimestamp))
		$finalcsv += $working
	}
}
$finalcsv | Export-CSV c:\computers_lastlogon.csv -notypeinformation

Open in new window

0
 
LVL 5

Assisted Solution

by:coraxal
coraxal earned 200 total points
ID: 38786818
So I've done something similar for user accounts. As you probably know the LastLogon attribute is not replicated between DCs in the domain, and the best approach to finding a "true" LastLogon value is to query every single DC in the domain.

I modified a script that I had (didn't have a chance to test it...so I'd highly recommend you test it..maybe have 1 computer in your CSV). I'm hoping your column holds a hard identifier for each of your servers (DN, SamAccountName, etc.). If not, you might want to embed a query to get the DN (optional). Anyway, the basic script logic is something like this:

1. Import CSV file and iterate through each server
2. Create a custom object with two properties SERVER and LASTLOGON for each server
3. SERVER name will carry over from existing CSV, while a function will get the LastLogon value for the LASTLOGON property
4. An array accumulates the objects and finally exports the results to a new CSV (you can chooose to overwrite the existing or write to a new file)

As for the function, it function gets all the DCs in the domain. It then iterates through each DC and gets the LastLogon attribute for each server. Hash tables are used to hold the most current LastLogon value (compares two dates and keeps the most recent). If the LastLogon is null, an arbitrary datetime value is used.

Hope it helps.
function Get-PCLastLogon {

	param(
		[Parameter()]
		[string]
		$PCID
		)
	
	
	# Get all domain controllers in the current AD domain
	$dc_col = [System.DirectoryServices.ActiveDirectory.Domain]::GetComputerDomain() | 
		Select-Object -ExpandProperty DomainControllers
	$ldap_filter = "(objectclass=computer)"
	$ldap_scope = "dc=testdomain,dc=com" #Insert your specific AD domain
	$PCLastLogonHT = @{}
	

	# Iterate through each DC in domain
	$dc_col | % {

		$currentDC = $_.Name
		
		$PCLastLogon = (Get-QADObject -SearchRoot $ldap_scope `
						-Service $currentDC `
						-LdapFilter $ldap_filter `
						-Identity $PCID `
						-DontUseDefaultIncludedProperties `
						-IncludedProperties LastLogon,DN).LastLogon
		
		
		# LastLogon value exists
		if($PCLastLogon -ne $null){
		
			$PCCurrentLastLogon = $PCLastLogon | Get-Date -format g
						
		}else{	# LastLogon value doesn't exist
		
			[DateTime]$PCCurrentLastLogon = '1/1/1900 0:00:00'
				
		}
		
		
		# Figure out most current LastLogon value
		# Case: PC exists in hash table 
		if($PCLastLogonHT.ContainsKey($PCID)) {
				
			[DateTime]$PCTempLastLogon = $PCLastLogonHT.Get_Item($PCID)
			
			# Compare LastLogon values
			if($PCTempLastLogon -le $PCCurrentLastLogon){
					
				$PCLastLogonHT.Set_Item($PCID,$PCCurrentLastLogon)
														
			}else{
				#Keep LastLogon in hash table
			}
								
		}else{ 	# Case: PC doesn't exist in hash table 
				
			$PCLastLogonHT.Set_Item($PCID,$PCCurrentLastLogon)
								
		}
		
		[DateTime]$resultPCLastLogon =  $PCLastLogonHT.Get_Item($PCID)
									
	}
	
	return $resultUsrLastLogon
	
} #END FUNCTION

#-------------------------------------------------------------------------------

Clear-Host
$PCRpt = @()
$PC_col = Import-Csv C:\PCfile.csv
	
# Process PCs	
$PC_col | % {
	
	# Assuming "Server" is the column name
	$serverID = $_.Server
	
	# Build custom object
	$objTemp = New-Object PSObject -Property @{
			
		SERVER = $serverID
		LASTLOGON = $(Get-PCLastLogon $ServerID)
	} 

	# Use addition to collect server info
	$PCRpt += $objTemp
	
}


# Export server info to CSV file
$PCRpt | Sort-Object LASTLOGON |
	Select-Object SERVER,LASTLOGON | 
	Export-Csv "C:\server_lastlogon.csv" `
		-NoTypeInformation `
		-Encoding utf8 `
		-UseCulture

Open in new window

0
 
LVL 31

Author Closing Comment

by:DrUltima
ID: 40175257
No longer on this project, but the information was worth being in the KB.  Thank you for the assistance.
0

Join & Write a Comment

Utilizing an array to gracefully append to a list of EmailAddresses
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now