remove the duplicate records and keep the lastlogondate entry

need to be able to remove the duplicate records and keep the lastlogondate entry, post running below mentioned script getting error with limited output. error message attached

CSV file format:-

Name,lastlogondate
Computer1,23/09/2015 16:34:17
Computer1,23/01/2018 16:34:17
Computer1,23/09/2015 16:34:17
Computer1,23/01/2018 17:34:17
Computer2,23/01/2018 16:34:17
Computer2,23/01/2018 17:34:17
Computer2,23/01/2018 17:34:17
Computer2,23/09/2015 16:34:17
Computer3,23/01/2018 17:34:17
Computer3,23/09/2015 16:34:17


#Remove duplicates and keep the latest entry

$tests = Import-Csv C:\Temp\lastlogon\LastLogonReport.csv |
Sort-Object -property @{Expression="LastLogonDate";Descending=$true},
@{Expression="name";Descending=$false},

$tests[0]

for ($i=1; $i -le $tests.length -1; $i++)  {
 if ($tests[$i]."name" -eq $tests[$i-1]."name"){
   continue
 }
 else {$tests[$i]}

Open in new window

error123.jpg
LVL 3
SAM ITAD windows Admin Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

oBdACommented:
This should do the trick:
$DTProvider = New-Object -TypeName System.Globalization.CultureInfo -ArgumentList 'en-GB'
Import-Csv -Path C:\Temp\lastlogon\LastLogonReport.csv |
	Select-Object -Property Name, @{n='LastLogonDate'; e={[datetime]::Parse($_.lastlogondate, $DTProvider)}} |
	Group-Object -Property Name | ForEach-Object {
		$_.Group | Sort-Object -Property LastLogonDate -Descending | Select-Object -First 1
	} | Export-Csv -NoTypeInformation -Path C:\Temp\lastlogon\LastLogonReport_NoDuplicates.csv

Open in new window

0
Ben Personick (Previously QCubed)Lead Network EngineerCommented:
this should do the needful:

#Import CSV
$tests = Import-Csv "C:\Temp\lastlogon\LastLogonReport.csv"

#Generate Deduplicated results list:
$Results = tests | Foreach {
    $_.LastLogonDate = [DateTime]$_.LastLogonDate
    $_
} | Group-Object Name | Foreach-Object {
    $_.Group | Sort-Object LastLogonDate | Select-Object -Last 1
}

#Write Results list to screen:
$Results

Open in new window

0
SAM ITAD windows Admin  Author Commented:
Hello OBDA,

Almost Perfect again, As expected I am getting the output . only one concern is in the input csv for few entries lastlogon is "none"  as mentioned below. For entries lastlogon is "none" those are not getting output into "LastLogonReport_NoDuplicates.csv"  

Name,lastlogondate
Computer1,23/09/2015 16:34:17
Computer1,23/01/2018 16:34:17
Computer1,23/09/2015 16:34:17
Computer1,23/01/2018 17:34:17
Computer2,23/01/2018 16:34:17
Computer2,23/01/2018 17:34:17
Computer2,23/01/2018 17:34:17
Computer2,23/09/2015 16:34:17
Computer3,23/01/2018 17:34:17
Computer3,23/09/2015 16:34:17
Computer3,none
Computer2,none
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SAM ITAD windows Admin  Author Commented:
Hello ben,

Thanks for response.

getting below error post running the script

_______________________________________


tests : The term 'tests' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try
again.
At line:5 char:12
+ $Results = tests | Foreach {
+            ~~~~~
    + CategoryInfo          : ObjectNotFound: (tests:String) [], CommandNotFoundException
    + FullyQualifiedErrorId :
0
oBdACommented:
$DTProvider = New-Object -TypeName System.Globalization.CultureInfo -ArgumentList 'en-GB'
Import-Csv -Path C:\Temp\lastlogon\LastLogonReport.csv |
	Select-Object -Property Name, @{n='LastLogonDate'; e={Try {[datetime]::Parse($_.lastlogondate, $DTProvider)} Catch {[datetime]::MinValue}}} |
	Group-Object -Property Name | ForEach-Object {
		$_.Group | Sort-Object -Property LastLogonDate -Descending | Select-Object -First 1 -Property Name, @{n='LastLogonDate'; e={If ($_.LastLogonDate -eq [datetime]::MinValue) {'none'} Else {$_.LastLogonDate}}}
	} | Export-Csv -NoTypeInformation -Path C:\Temp\lastlogon\LastLogonReport_NoDuplicates.csv

Open in new window

0
SAM ITAD windows Admin  Author Commented:
Still Entries with "none" not getting capturing in the output. below is the output received

_____________________________

Name                                                                                                       LastLogonDate                                                                                            
----                                                                                                       -------------                                                                                            
Computer1                                                                                                  23/01/2018 17:34:17                                                                                      
Computer2                                                                                                  23/01/2018 17:34:17                                                                                      
Computer3                                                                                                  23/01/2018 17:34:17
0
oBdACommented:
Because there's an entry for both Computer2 and Computer3 with a valid logon date, so the 'none' will be discarded.
The old script did that already correctly, only difference is that computers that actually never logged on would have had no LastLogonDate at all, now those will have 'none' as before.
0
SAM ITAD windows Admin  Author Commented:
Last and final question to close this case.

In the orginal input one more column is there i.e lastlogon at as mentioed below . lastlogonat  is not reflected in the output. rest all good. any possible way that lastlogonat should aslo reflect on the output                                                        
________________________________________________

Name                                                                   lastlogondate                                                          lastlogonat                                                          
----                                                                   -------------                                                          -----------                                                          
Computer1                                                              23/09/2015 16:34:17                                                    dc1                                                                  
Computer1                                                              23/01/2018 16:34:17                                                    dc2                                                                  
Computer1                                                              23/09/2015 16:34:17                                                    dc2                                                                  
Computer1                                                              23/01/2018 17:34:17                                                    dc2                                                                  
Computer2                                                              23/01/2018 16:34:17                                                    dc8                                                                  
Computer2                                                              23/01/2018 17:34:17                                                    dc8                                                                  
Computer2                                                              23/01/2018 17:34:17                                                    dc2                                                                  
Computer2                                                              23/09/2015 16:34:17                                                    dc2                                                                  
Computer3                                                              23/01/2018 17:34:17                                                    dc2                                                                  
Computer3                                                              23/09/2015 16:34:17                                                    dc2                                                                  
Computer5                                                              none                                                                   dc2                                                                  
Computer2                                                              none                                                                   dc5
0
oBdACommented:
$DTProvider = New-Object -TypeName System.Globalization.CultureInfo -ArgumentList 'en-GB'
Import-Csv -Path C:\Temp\lastlogon\LastLogonReport.csv |
	Select-Object -Property Name, @{n='LastLogonDate'; e={Try {[datetime]::Parse($_.lastlogondate, $DTProvider)} Catch {[datetime]::MinValue}}}, LastLogonAt |
	Group-Object -Property Name | ForEach-Object {
		$_.Group | Sort-Object -Property LastLogonDate -Descending | Select-Object -First 1 -Property Name, @{n='LastLogonDate'; e={If ($_.LastLogonDate -eq [datetime]::MinValue) {'none'} Else {$_.LastLogonDate}}}, LastLogonAt
	}

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SAM ITAD windows Admin  Author Commented:
Thanks a Ton OBDA.... Every time you saved me in the difficult situation
0
Ben Personick (Previously QCubed)Lead Network EngineerCommented:
Ahh, wrote tests instead of $tests
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.