Hi All,

In this blog I will show you a PowerShell script that will work out of the box to generate a CSV file with a list of devices and the name of the each device’s most recent user.

As most IT admins know, keeping an asset list up-to-date and accurate can be difficult at the best of times. Devices can often be renewed, returned as faulty, swapped out etc. and for whatever reason these events and device ownership may not always be reflected on your asset list. Finding out which user owns which device and bringing your asset list up to scratch later down the line can be tricky and time consuming.

The good news is that built-in PowerShell and Active Directory functionality can go a long way in helping you find out which users are using which devices.

The technical background

When a user authenticates to a domain controller from a device (for example, a windows logon, opening outlook or signing into Skype for Business), an event is logged in the security event log on the domain controller. This is Event ID 4776 and it tells us the username of the user and computer that the authentication request came from.

For example, if my domain username was JClyde and I was to log into my domain joined laptop – LAPTOP1795 on the corporate network, you would see Event 4776 logged on the DC that I authenticated against with the following message.

The computer attempted to validate the credentials for an account.

Authentication Package: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0
Logon Account: JClyde
Source Workstation: LAPTOP1795
Error Code: 0x0

All logon events on your network are stored in this way, so you may see 1000s of 4776 events on your domain controllers. Looking through these events can answer some of your asset list questions, you can filter event viewer by event 4776 and then use ‘Find’ to search for a username or computer name.

Manually looking through the event logs is already a great way to quickly find out which users own which devices,  however getting an overview of all device and user logon events is still going to take time. Luckily, PowerShell can completely automate this for us, as there are cmdlets available to search active directory for computer and user objects, pull data from event logs and filter that data to quickly give us valuable information.

The script below does the following:

  • Searches Active Directory for all computers on the domain
  • Searches the security log on the domain controller for all events with ID 4776 in the last 24 hours
  • One by one, for each active directory computer – it searches the event log for a logon event for that computer
    • If it finds a logon event, it puts the name of the user and computer into the CSV file
    • If it does not find a logon event, it moves onto the next active directory computer

The end result is a CSV file with 2 columns, a column for computer  name and a corresponding column with the name of the most recently logged in user.

The script

The below PowerShell script is ready to go on any domain controller in any active directory environment and no changes should need to be to the code in order for it to work, PowerShell must be running as an administrator.

The script can take a while to run, depending on the size of the security event log. When the Script has completed, the outputted CSV can be found in C:outputUsersandComputers.csv.

The script does not make any changes apart from writing and appending data to the CSV file. The script works on Server 2012 and 2012 R2 domain controllers and has not been tested on earlier versions.

<#
To start with, the path to the output CSV file is declared

‘CSV Directory’ is the path to the folder that the CSV is stored (backslash at the end is required or CSV will not be created).
If the Directory does not exist yet, then it will be automatically created

‘CSV Name’ is the name of the CSV file that will contain the users and computers after the script is ran

#>
$CSVDirectory = “C:Output”

$CSVName = “UsersandComputers.csv”

if(!(Get-Item $CSVDirectory))
{
New-Item -ItemType Directory -Path $CSVDirectory

Write-Host(“$CSVDirectory did not exist and has been created”)
}

$CSVPath = $CSVDirectory + $CSVName

Write-Host(“The CSV path will be $CSVPath when this script has completed”)

#Fetch all of the computers in Active Directory

Write-Host(“Searching active directory for computers…”)

$Devices = Get-ADComputer -Filter *

Write-Host(“Active Directory search complete”)

<#Fetch all logon events (Event 4776) from the security event log
on the local domain controller#>

Write-Host(“Searching the security log for logon events (Event ID 4776)..”)

$LogonEvents = Get-WinEvent -FilterHashTable @{LogName = “Security”;StartTime = (get-date).AddHours(-24);ID = 4776}

Write-Host(“Event search complete”)

#Create the object that will be used to export the results to CSV later

$DeviceLogons= New-Object PSObject

<#Start a ForEach loop.
This loop will be executed for every computer fetched from Active Directory ealier#>

ForEach($Device in $Devices)
{
#Store the name of the computer in a variable

$DeviceName = $Device.Name

<#Try to find a logon event for this computer
Only the most recent logon event will be used#>

Write-Host(“Searching for logon event for $DeviceName”)

$DeviceLogonEvent = $LogonEvents | ?{$_.Message -like “*$DeviceName*” -and $_.Message -notlike “*$DeviceName$*”} | select -First 1

#The following is executed if a logon event was found

if($DeviceLogonEvent)
{
#The message of the event is declared as a variable
$EventMessage = $DeviceLogonEvent.Message

#Convert the event to XML
$XMLEvent = [xml]$DeviceLogonEvent[0].ToXml()

<#Get the computer name and the user name from the event message
Store the username and computer name as variables#>

$userRow = $XMLEvent.Event.EventData.Data | Where-Object {$_.name -eq “TargetUserName”}
$computerRow = $XMLEvent.Event.EventData.Data | Where-Object {$_.name -eq “Workstation”}

$EventUser = $userRow.”#text”
$EventComputer = $computerRow.”#text”

“$EventUser logged into $EventComputer”

<#Search active directory for the username found in the event
Store the user’s name as a variable#>

$UserLookup = Get-ADUser $EventUser -errorvariable UserLookupError

#If the user lookup was successful, add the user’s name and computer name is added to the CSV
if(!$UserLookupError)
{
$NameofUser = $UserLookup.Name

Write-Host(“Logon event was found, $NameofUser used $DeviceName”) -ForegroundColor Green

#The computer name and user’s name is now appended the CSV file
$DeviceLogons | add-member -MemberType NoteProperty -Name “Computer” -Value $EventComputer -Force
$DeviceLogons | add-member -MemberType NoteProperty -Name “User” -Value $NameofUser -Force

$DeviceLogons | Export-csv -Path $CSVPath -Append -notypeinformation

}

#If the user lookup was not successfully, add the username of the user and computer name to the CSV
else
{

Write-Host(“Logon event was found but could not find user, username $EventUser used $DeviceName”)

#The computer name and user’s name is now appended the CSV file
$DeviceLogons | add-member -MemberType NoteProperty -Name “Computer” -Value $EventComputer -Force
$DeviceLogons | add-member -MemberType NoteProperty -Name “User” -Value “Username: $EventUser” -Force

$DeviceLogons | Export-csv -Path $CSVPath -Append -notypeinformation
}

}
else
{
Write-Host(“No logon event was found for $DeviceName”)
}
}

About the author