Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test_data (
  id integer PRIMARY KEY
, message varchar(10)
, datetimestamp timestamp NOT NULL
, server varchar(10)
, system varchar(10)
);

CREATE TABLE


INSERT INTO test_data VALUES
  (09, 'Normal' , '2022-07-13 08:59:09', 'Server 1' , 'System 1')
, (10, 'Normal' , '2022-07-13 08:59:10', 'Server 4' , 'System 2')
, (11, 'Normal' , '2022-07-13 08:59:11', 'Server 3' , 'System 3')
, (12, 'Warning', '2022-07-13 08:59:12', 'Server 35', 'System 27')
, (13, 'Warning', '2022-07-13 08:59:13', 'Server 5' , 'System 5')
, (14, 'Warning', '2022-07-13 08:59:14', 'Server 9' , 'System 6')
, (15, 'Warning', '2022-07-13 08:59:15', 'Server 8' , 'System 7')
, (16, 'Error'  , '2022-07-13 08:59:16', 'Server 12', 'System 8')
, (17, 'Error'  , '2022-07-13 08:59:17', 'Server 15', 'System 9')
, (18, 'Warning', '2022-07-13 08:59:18', 'Server 29', 'System 10')
, (19, 'Warning', '2022-07-13 08:59:19', 'Server 22', 'System 11')
, (20, 'Warning', '2022-07-13 08:59:20', 'Server 13', 'System 12')
, (21, 'Normal' , '2022-07-13 08:59:21', 'Server 16', 'System 13')
, (22, 'Normal' , '2022-07-13 08:59:22', 'Server 19', 'System 14')
, (23, 'Normal' , '2022-07-13 08:59:23', 'Server 21', 'System 15')
, (24, 'Warning', '2022-07-13 08:59:24', 'Server 24', 'System 16')
, (25, 'Warning', '2022-07-13 08:59:25', 'Server 27', 'System 17')
, (26, 'Warning', '2022-07-13 08:59:26', 'Server 25', 'System 18')
, (27, 'Error'  , '2022-07-13 08:59:27', 'Server 30', 'System 23')
, (28, 'Error'  , '2022-07-13 08:59:28', 'Server 31', 'System 20')
;

INSERT 0 20


with mg as(
  select message, count(*) n
    , min(datetimestamp) start_time
    , max(datetimestamp) end_time
    , array_agg(server order by datetimestamp) asrv
    , array_agg(system order by datetimestamp) asys
    , lag(message, 1, 'Normal') over(order by min(datetimestamp)) prevmsg
    , lead(message,1, 'Error') over(order by min(datetimestamp)) nxtmsg
  from (
    select *, row_number() over(order by datetimestamp) - row_number() over(partition by message order by datetimestamp) grp
    from test_data
  ) t
  group by message, grp
)
select start_time, end_time
  , asrv[1] start_server, asrv[n] end_server
  , asys[1] start_system, asys[n] end_system
from mg
where mg.message ='Warning' and mg.prevmsg='Normal' and mg.nxtmsg='Error'


start_time
end_time
start_server
end_server
start_system
end_system


2022-07-13 08:59:12
2022-07-13 08:59:15
Server 35
Server 8
System 27
System 7

2022-07-13 08:59:24
2022-07-13 08:59:26
Server 24
Server 25
System 16
System 18



SELECT 2

start_time	end_time	start_server	end_server	start_system	end_system
2022-07-13 08:59:12	2022-07-13 08:59:15	Server 35	Server 8	System 27	System 7
2022-07-13 08:59:24	2022-07-13 08:59:26	Server 24	Server 25	System 16	System 18