Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2012-04-11
13
947 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:Justin Owens
  • 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:Justin Owens
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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:Justin Owens
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:Justin Owens
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:Justin Owens
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:Justin Owens
ID: 40175257
No longer on this project, but the information was worth being in the KB.  Thank you for the assistance.
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes my battle tested process for setting up delegation. I use this process anywhere that I need to setup delegation. In the article I will show how it applies to Active Directory
This article explains the steps required to use the default Photos screensaver to display branding/corporate images
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 from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

809 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