How to parse a csv file with powershell for specfic results

0
votes

I have a CSV file containing log on data for users and would like to use PowerShell to parse the data to find the last log on for each user.

The data is in the format:

TimeStamp                  UserName                           
---------                  --------                           
10/14/2011 12:53:12 PM     Smith                      
10/14/2011 12:54:07 PM     Jones                      
10/14/2011 12:59:45 PM     Green                      
10/14/2011 1:10:43 PM      Brown                      
8/20/2012 6:20:11 PM       Jones                     
8/20/2012 6:20:36 PM       Brown                     
8/20/2012 6:33:04 PM       Smith                      
8/20/2012 6:35:45 PM       Brown                  
8/20/2012 6:45:05 PM       Jones                  
8/20/2012 6:45:45 PM       Smith

The desired output is:

UserName                   TimeStamp                          
--------                   ---------                           
Brown                      8/20/2012 6:45:45 PM
Green                      10/14/2011 12:59:45 PM
Jones                      8/20/2012 6:45:05 PM
Smith                      8/20/2012 6:45:45 PM

I could do this with a row by row, nested loop, solution but that seems a poor way to approach it with PowerShell.

I can read in the csv data and sort it by UserName and TimeStamp thus:

$data = "\\SomePath\LastLogon.csv"
$list = Import-Csv $data |Sort @{expression="UserName";Ascending=$true},@{expression="TimeStamp";Descending=$true}
$list

But I'm now stuck on how to filter for just the last log on. Is there a simple way to do this with PowerShell or am I taking the wrong approach?

1
powershell

1 Answers

2
votes

Group-Object is your friend. Sort by TimeStamp first, then group. It will keep the sort order within the captured group, so you can just read off the first item.

$data = "\\SomePath\LastLogon.csv"
Import-Csv $data | sort {[DateTime]$_.TimeStamp} -Desc | group UserName |%{ $_.Group[0] }