asked on

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

oBdA

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

Ben Personick (Previously QCubed)

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

SAM IT

ASKER

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

SAM IT

ASKER

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 :

oBdA

$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

SAM IT

ASKER

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

oBdA

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.

SAM IT

ASKER

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

ASKER CERTIFIED SOLUTION

oBdA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SAM IT

ASKER

Thanks a Ton OBDA.... Every time you saved me in the difficult situation

Ben Personick (Previously QCubed)

Ahh, wrote tests instead of $tests