Create new table:count rows based on date-time and 2 columns & look at last 3 rows based for each ID

Hi,

I have a quiet complicated logic that I would like to use to create a new table. The case is the following:

I have devices that each also have an ID. whenever a process has taken place it's recorded and also whether this process was a seccus or failed. I'm interested in the failed processes: If processes for an idea fail at least 3 times in a row, I assume the whole device has failed. I would like to count the cases where at least 3 fails took place after one another. 

I also want a current mode for each device, meaning: wether the device in running as usual, or is in failure mode. Failure mode is when the last 3 records for a device has failed, and no process with succes has occured after it.

The way it is noted when a process failed, is when the column "Action" has "Fail" and the column "Status" is empty/blank.

I created an example of the table that I have, what logic I want to use, an what table I would like to have in the end:

I have no idea where to start, I have never worked with cases like this so that's why I'm asking. Any tip is very welcome