Find Difference of Dates between certain fields in powershell

Question

i have the below PS custom object

Time                  DateFormat Event Type
----                  ---------- ----------
3/31/2020 11:59:35 PM 31/03/2020 AGENT_RECONNECTED
3/31/2020 4:01:56 PM  31/03/2020 AGENT_DISCONNECTED
3/31/2020 4:14:19 PM  31/03/2020 AGENT_CONNECTED
3/31/2020 4:24:12 PM  31/03/2020 AGENT_CONNECTED
3/31/2020 4:30:31 PM  31/03/2020 AGENT_DISCONNECTED
3/31/2020 4:36:08 PM  31/03/2020 AGENT_RECONNECTED
3/31/2020 5:12:49 PM  31/03/2020 AGENT_DISCONNECTED
3/31/2020 6:25:39 PM  31/03/2020 AGENT_RECONNECTED
3/31/2020 7:09:08 PM  31/03/2020 AGENT_DISCONNECTED
3/31/2020 7:38:53 PM  31/03/2020 AGENT_RECONNECTED
3/31/2020 7:44:44 PM  31/03/2020 AGENT_CONNECTED
3/31/2020 8:23:48 PM  31/03/2020 AGENT_DISCONNECTED
3/31/2020 9:33:36 PM  31/03/2020 AGENT_RECONNECTED
3/31/2020 9:35:58 PM  31/03/2020 AGENT_DISCONNECTED
3/31/2020 10:46:54 PM 31/03/2020 AGENT_RECONNECTED
3/31/2020 10:50:54 PM 31/03/2020 AGENT_DISCONNECTED
3/31/2020 10:53:10 PM 31/03/2020 AGENT_RECONNECTED
3/31/2020 11:05:56 PM 31/03/2020 AGENT_DISCONNECTED
3/31/2020 11:13:47 PM 31/03/2020 AGENT_RECONNECTED
3/31/2020 11:32:41 PM 31/03/2020 AGENT_DISCONNECTED
3/31/2020 11:34:27 PM 31/03/2020 AGENT_RECONNECTED
3/31/2020 11:35:55 PM 31/03/2020 AGENT_DISCONNECTED
3/31/2020 11:38:21 PM 31/03/2020 AGENT_RECONNECTED
3/31/2020 11:50:52 PM 31/03/2020 AGENT_DISCONNECTED
3/31/2020 1:15:02 PM  31/03/2020 AGENT_CONNECTED
3/31/2020 1:04:17 PM  31/03/2020 AGENT_DISCONNECTED

i need a help to get something complex done, the array is sorted with time, i need a logic which would start from the end of the array, find 1st instance of Event Type Agent_connected / Agent_reconnected, when its encountered check for the next item in the array see if the event type matches agent_disconnected, if it matches then - check for the next item and see if the event type is Agent_Reconnnected / Agent_connected - if its so - then find the difference between the time for the agent_disconnected event and this Agent_reconnected / agent connected date, basically if any time a connected / reconnected event type is encountered in an the array after a disconnected event then we need to find a difference in time between these events.

an example - This array has 25 objects

$obj[24] = 3/31/2020 1:15:02 PM 31/03/2020 AGENT_CONNECTED which is the first time an agent connect / disconnect is seen, if yes we see the next element

$obj[23] = 3/31/2020 11:50:52 PM 31/03/2020 AGENT_DISCONNECTED we see that we see a disconnect event, which means we check the next element if its an agent connect / reconnect event

and we see $obj 22 satisfies this condition $obj[22] = 3/31/2020 11:38:21 PM 31/03/2020 AGENT_RECONNECTED

and we get the diff as $obj[23].time - $obj[22].time, now we again go back and see the same condition for the remaining elements in the array - evaluate the above three conditions and find the difference in the time.

AdminOfThings · Accepted Answer

If $obj is an array of custom objects with the properties in your post, you can do the following:

$obj | Sort {[datetime]$_.Time} | Foreach-Object {
    if ($_.'Event Type' -eq 'AGENT_DISCONNECTED') {
        $disc = [datetime]$_.Time
    } 
    else {
        $con = [datetime]$_.Time
        if ($disc) {
            "Agent connected at $($_.Time) after being disconnected for $($con - $disc)"
            $disc = $null
        }
    }
}

Perform a sort on the array while converting Time into a [datetime] object. Then perform a subtraction of the current (re)connected time object from the previous disconnected time object. The $disc = $null is only for when multiple (re)connects happen in a row. We are only subtracting the first (re)connect instance in that case.

Find Difference of Dates between certain fields in powershell

Answers (2)

Related Questions