[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1262
  • Last Modified:

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
0
Justin Owens
Asked:
Justin Owens
  • 7
  • 5
2 Solutions
 
Daryl BamforthTechnical ExpertCommented:
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
 
Justin OwensITIL Problem ManagerAuthor Commented:
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
 
Daryl BamforthTechnical ExpertCommented:
oops.

change

$finalcsv=@{}

Open in new window

to
$finalcsv=@()

Open in new window

0
New Tabletop Appliances Blow Competitors Away!

WatchGuard’s new T15, T35 and T55 tabletop UTMs provide the highest-performing security inspection in their class, allowing users at small offices, home offices and distributed enterprises to experience blazing-fast Internet speeds without sacrificing enterprise-grade security.

 
Daryl BamforthTechnical ExpertCommented:
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
 
Justin OwensITIL Problem ManagerAuthor Commented:
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
 
Daryl BamforthTechnical ExpertCommented:
What DC version are you running?  Might just have to do an LDAP lookup instead
0
 
Daryl BamforthTechnical ExpertCommented:
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
 
Justin OwensITIL Problem ManagerAuthor Commented:
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
 
Daryl BamforthTechnical ExpertCommented:
Sorry for the delay in responding.

Can you please clarify what AD version you are running.
0
 
Justin OwensITIL Problem ManagerAuthor Commented:
Windows Server 2003 (AD schema version 30).
0
 
Daryl BamforthTechnical ExpertCommented:
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
 
coraxalCommented:
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
 
Justin OwensITIL Problem ManagerAuthor Commented:
No longer on this project, but the information was worth being in the KB.  Thank you for the assistance.
0

Featured Post

A Cyber Security RX to Protect Your Organization

Join us on December 13th for a webinar to learn how medical providers can defend against malware with a cyber security "Rx" that supports a healthy technology adoption plan for every healthcare organization.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now