Last User Logged In To Computer with SCCM and Powershell

Recently tasked with getting a list of all users who were last logged into a computer, and then finding their department (AD department attribute) based on that user account. This is to get the department each computer is associated with so they can be cost-coded to.

Add and run the following query into SCCM under Monitoring -> Queries.

SCCM 2012 R2 Query:


SELECT SMS_R_System.LastLogonUserName, SMS_R_System.Name, SMS_R_System.LastLogonTimestamp

FROM SMS_R_System

Press CTRL + A then CTRL + C to select the results of the report and copy it to a clipboard. Open up excel and paste the values in.

At this point I removed all the rows with blank usernames.

Insert a new  at the top and fill it in the respective rows with the headers username, computer, lastLogon, department

It should look like this:

Save the file as a csv  and name it something like SCCMExport.csv

Next, copy the following PowerShell script and modify the filename variables to be appropriate ($data and $outputFileName)


$data = Import-Csv "SCCMExport.csv"
$outputFileName = "ComputerbyDepartment.csv"

$outputCSV = New-Object System.Data.DataTable
$outputCSV.Columns.Add("username") | Out-Null
$outputCSV.Columns.Add("computer") | Out-Null
$outputCSV.Columns.Add("lastLogon") | Out-Null
$outputCSV.Columns.Add("department") | Out-Null

foreach ($user in $data){
$department = Get-ADUser -Identity $user.username -Properties Department | select -ExpandProperty department
$row=$outputCSV.newrow()

$row["username"]=$user.username
$row["computer"]=$user.computerName
$row["lastLogon"]=$user.lastLogon
$row["department"]=$department
$outputCSV.rows.add($row)
}
$outputCSV | Export-CSV $outputFileName -NoTypeInformation -Append -Force

All done. Check the OutputFile to see all the Departments listed neatly next to the users and computers.

Total Views: (1389)

Leave a Reply

*