CODEWITHSUNDEEP
powershellcsv
Damir
Damir

Reputation: 99

Last repetition in csv

I have a csv input file like:

date;time;pc;state

25.01.2017;10:30:57;pc1;inactive

25.01.2017;10:35:57;pc2;active

25.01.2017;10:37:34;pc1;active

25.01.2017;10:38:35;pc3;inactive

25.01.2017;10:39:20;pc1;inactive

25.01.2017;10:42:10;pc2;inactive

25.01.2017;10:42:10;pc3;active

So, i need to show only last pc repetition with inactive state and show the difference between time and current time. So result must be:

pc1 inactive from 10:39:20

pc2 inactive from 10:42:10

Do not know how to realize it in powershell. Need help :)

Upvotes: 1

Views: 46

Answers (2)

user6811411
user6811411

Reputation:

Two solutions, a short one taking the question literal:

$csv = import-csv .\SO_41941150.csv -Delimiter ";"
$csv|group pc|%{$_.group|select -last 1|? state -eq 'inactive'|%{"$($_.pc) inactive from $($_.time)"}}

With the exact output:

pc1 inactive from 10:39:20
pc2 inactive from 10:42:10

A longer one converting date and time to datetime using ::ParseExact and giving elapsed TotalSeconds/Minutes as a result.

$csv = import-csv .\SO_41941150.csv -Delimiter ";"
$csv | Group-Object pc | Foreach-Object {
  $_.Group | Select-Object -last 1 | Where-Object state -eq 'inactive'|
  ForEach-Object {
    $DT = [datetime]::ParseExact("$($_.date) $($_.time)","dd.MM.yyyy HH:mm:ss",$null)
    $Secs = [int]((get-date) - $DT).TotalSeconds
    $mins = [int]((get-date) - $DT).TotalMinutes
    "$($_.pc) inactive since $Mins minutes or $Secs seconds"
  }
}

Output:

pc1 inactive since 9160 minutes or 549578 seconds
pc2 inactive since 9157 minutes or 549408 seconds

Upvotes: 0

Paul
Paul

Reputation: 5861

Here you go, at the moment it only outputs it via Write-Host but you should be able to rebuild it to do with the data what you want:

$csv = import-csv .\test.csv -Delimiter ";"
$pcs = $csv.pc | select -Unique
foreach($pc in $pcs) {

#get all entries for current pc and order by date/time, then select newest entry
$le = $csv | where {$_.pc -eq $pc} | sort date,time -Descending | select -First 1

#if last state is inactive
if($le.state -eq "inactive"){
    $writedate = get-date -Date $le.date -Hour $le.time.Substring(0,2) -Minute $le.time.Substring(3,2) -Second $le.time.Substring(6,2)
    $td = (get-date) - $writedate
    write-host "time difference for $pc : $($td.Days) days $($td.Hours) hours, $($td.Minutes) minutes, $($td.Seconds) seconds"
}

}

If your time field is not allways in HH:MM:DD you will have to change the script to accomodate that

Upvotes: 1

Related Questions