Query AD to find last contact time of domain computers

PowerShell | Server 201x

19.05.25

Finding the last ‘logon’ time of a domain computer is not as straightforward as you might think, i.e. there is no column to select in ADU&C. Note that we are looking for the last time the domain-joined machine made contact with the domain controller(s), and not the last time a user logged onto a machine (nor who the user was).

Launch PowerShell, and run the following commands to output the results as a simple CSV text file:

$currentDate = Get-Date
$allComputers = Get-ADComputer -Filter {LastLogonTimeStamp -lt $currentDate} -Properties LastLogonTimeStamp | Select-Object Name, LastLogonTimeStamp
$allComputers | Export-Csv c:\temp\allComps.csv

NOTE: You could adjust the use of the current date to filter only on machines that had been out of communication for x number of days by adding an extra calculation before running the Get-ADComputer command:

$currentDate = Get-Date
$daysInactive = 30
$inactiveDate = $currentDate.AddDays(-$daysInactive)
$allComputers = Get-ADComputer -Filter {LastLogonTimeStamp -lt $inactiveDate} -Properties LastLogonTimeStamp | Select-Object Name, LastLogonTimeStamp
$allComputers | Export-Csv c:\temp\allComps.csv

In either case, once you have you exported the list you can open it in Excel. However, you will notice that the ‘date’ shown in Excel is not a date… This needs to be converted to a readable format.

Firstly, we need to serialise the number by applying a formula. In this example we assume the logon ‘date’ is in cell B3 (so could add this formula in C3 for example):